CODEWITHSUNDEEP
sqlsql-server
Sency
Sency

Reputation: 2878

Restrict update Column

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.

Upvotes: 1

Views: 3744

Answers (4)

Conrad Frix
Conrad Frix

Reputation: 52675

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

Upvotes: 5

gbn
gbn

Reputation: 432662

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

Upvotes: 3

Sudantha
Sudantha

Reputation: 16224

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

Upvotes: 1

ray1
ray1

Reputation: 4068

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

Upvotes: 0

Related Questions