Restrict update Column

Question

Is it possible to restrict updating a column in SQL without using a trigger ? If so how ? (need the query)

PS:

I mean, I have a table

CREATE TABLE MYBUDGET.tbl_Income
(
    [IncomeID]          INT             NOT NULL IDENTITY(1,1),
    [IncomeCatID]       INT             NOT NULL,
    [IncomeAmnt]        MONEY           NOT NULL,
    [IncomeCurrencyID]  INT             NOT NULL,
    [ExchangeRateID]    INT             NOT NULL,
    [IncomeAmnt_LKR]    MONEY           NOT NULL,
    [AddedOn]           DATETIME        NOT NULL,
    [Remark]            VARCHAR(250),
)

I need to allow users to update only [ExchangeRateID] and [IncomeAmnt_LKR] fields. All other fields can not be updated. only insert.

Restrict how? Prohibit update, or rather restrict to a set of values? — ramn, Jan 02 '11 at 04:21

score 5 · Accepted Answer · edited May 23 '17 at 12:10

5

Use DENY to block update. e.g.

DENY UPDATE ON

MYBUDGET.tbl_Income
(
    [IncomeID],
    [IncomeCatID],
    [IncomeAmnt]        ,
    [IncomeCurrencyID]  ,
    [AddedOn]           ,
    [Remark]
)
TO Mary, John, [Corporate\SomeUserGroup]

One should still consider how ownership chaining can override the DENYs see gbn's answer

edited May 23 '17 at 12:10

Community

1
1

answered Jan 02 '11 at 05:47

Conrad Frix

51,984
12
96
155

This will be the correct answer if the approach is in triggers ! – Sudantha Jan 02 '11 at 06:03

score 3 · Answer 2 · edited May 23 '17 at 09:58

It comes down to permissions.

You DENY UPDATE on the columns as per Conrad Frix's answer.

However, these will be ignored with db_owner/dbo and sysadmin/sa so you need to ensure your permission model is correct.

If you have views or stored procs that write to the table, then permissions won't be checked either if the same DB users owns both code and table. This is known as ownership chaining

I mention all this because there was another question 2 days ago where permissions were bypassed

If your permission-based approach fails and you can't/won't change it, then you'll need to use triggers

Sudantha · Answer 3 · 2011-01-02T04:30:37.690

1

Create a view using the table and hide the column you want ..and give acess to that view to the users.

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

edited Jan 02 '11 at 04:30

answered Jan 02 '11 at 04:25

Sudantha

15,684
43
105
161

As per the other answer suggesting a view, this does not prevent an authorised user from **updating** the columns in the underlying table, which is what the OP wants to prevent. – slugster Jan 02 '11 at 05:55

score 0 · Answer 4 · answered Jan 02 '11 at 04:29

0

Make a VIEW from that table and then obscure the column you need, also give the users access of that VIEW

answered Jan 02 '11 at 04:29

steve

3,878
7
34
49

This does not prevent an authorised user from **updating** the columns in the underlying table, it simply prevents unauthorised users from seeing those columns. – slugster Jan 02 '11 at 05:54

Restrict update Column

4 Answers4