Restrict INSERT and UPDATE on a particular column to a user role-CodePudding

I have a column in a table that I'd like to prevent ordinary users inserting data into, while keeping the ability for certain user roles.

for update I can do this with:

DENY UPDATE ON table (col) TO "user";

However this doesn't work with INSERT:

DENY INSERT ON table (col) TO "user";

Error:

Sub-entity lists (such as column or security expressions) cannot be specified for entity-level permissions.

Is there an equivalent method to achieve the same functionality with INSERT?

It seems that INSERT would always insert against all columns, is there a way to restrict inserts on this particular column to be NULL for certain user groups?

CodePudding user response：

To expand on my comment, I would use a "simple" VIEW that only exposes the columns you want the USER to be able to affect.

--Create sample table
CREATE TABLE dbo.SomeTable (ID int IDENTITY,
                            SomeDate date NOT NULL,
                            SomeInt int NOT NULL,
                            SomeString varchar(10) NULL);
GO
--Sample user
CREATE USER YourUser WITHOUT LOGIN;
GO
--Create VIEW with SoemString missing
CREATE VIEW dbo.SomeView AS
    SELECT ID,
           SomeDate,
           SomeInt
    FROM dbo.SomeTable;
GO
--Give permissions on the VIEW
GRANT SELECT, UPDATE, DELETE, INSERT ON dbo.SomeView TO YourUser;
GO
--Testing
EXECUTE AS USER = 'YourUser';
GO
INSERT INTO dbo.SomeTable (SomeDate, SomeInt)
VALUES(GETDATE(),12); --Fails
GO
INSERT INTO dbo.SomeTable (SomeDate, SomeInt, SomeString)
VALUES(GETDATE(),4, 'abc'); --Fails
GO
INSERT INTO dbo.SomeView (SomeDate, SomeInt)
VALUES(GETDATE(),15) --Success
GO
INSERT INTO dbo.SomeView (SomeDate, SomeInt, SomeString)
VALUES(GETDATE(),19, 'abc'); --Fails, as column doesn't exist
GO
SELECT *
FROM dbo.SomeView;
GO
REVERT;
GO
--Cleanup
DROP USER YourUser;
DROP VIEW dbo.SomeView;
DROP TABLE dbo.SomeTable;

If the USER does, for some reason, need access to see the data in the TABLE, you could also GRANT them SELECT on the table. Note that they don't require any permissions on dbo.SomeTable in the above solution, as they both have the same owner permission chaining occurs.