SQL Server Security – Best Practices for Restricting Write-Access on a Table

permissionsSecuritysql server

I have a table, which I'd like to be read-only, except for a few specific stored procedures which need permissions to UPDATE rows in the table.

After that, I want only a select group of additional stored procedures to have permissions to EXECUTE the aforementioned UPDATE procedures.

I've been reading about GRANT and DENY on MSDN/TechNet, and I've also been reading about schemas, but the documentation is so vague and non-relatable that I'm having trouble figuring out how to accomplish real-world security goals based on the descriptions provided.

I can see that you can GRANT or DENY permissions for individual principles, one at a time – but I need to deny INSERT UPDATE and DELETE permissions for ALL principles, EXCEPT for the few stored procedures to which I am granting UPDATE permissions (as well as sa and/or db_owner I suppose).
I don't want any new users to have UPDATE permissions on this table by default, and I would really like to avoid having to manually deny permissions every time we create/modify users or other objects.

Best Answer

By default, no direct access is permitted to either tables or stored procedures. One must explicitly grant access in order for non-privileged users to have access. The best practice to simplify security administration is to grant permissions to roles, and control access via role membership.

If you want to restrict access only via stored procedures, take a look at ownership chaining (https://technet.microsoft.com/en-us/library/ms188676). This allows you to grant execute permissions to individual roles/users without allowing direct access to underlying tables as long as the object involves have the same owner (default is dbo).

Using ownership chaining in your scenario, you could grant only SELECT permission on the tables, with updates allowed only by users with execute permission on the update procs. No direct table modifications would be allowed.

Edit:

Below is an ownership chaining example. Note that neither user can update the table directly and the write user can update the table only using the stored procedure. The implication of ownership chaining is that when you grant execute permissions on a stored procedure, users can access all of the encapsulated objects without direct object permissions as long as the objects have the same owner. 

--create objects
CREATE TABLE dbo.MyTable(
      MyTableID int IDENTITY NOT NULL 
        CONSTRAINT PK_MyTable PRIMARY KEY
    , SomeData int
    );
GO

CREATE PROC dbo.UpdateMyTable
      @MyTableID int
    , @SomeData int
AS
UPDATE dbo.MyTable
SET SomeData = @SomeData
WHERE MyTableID = @MyTableID;
GO

--create roles
CREATE ROLE TableReaderRole;
CREATE ROLE TableWriterRole;
GO

--grant permissions to roles
GRANT SELECT ON dbo.MyTable TO TableReaderRole;
GRANT EXECUTE ON dbo.UpdateMyTable TO TableWriterRole;
GO

--create user with only read permissions
CREATE USER SomeReadOnlyUser FOR LOGIN SomeReadOnlyUser;
ALTER ROLE TableReaderRole
    ADD MEMBER SomeReadOnlyUser;
GO

--create user with both read and writer permissions
CREATE USER SomeAllowedWriteUser FOR LOGIN SomeAllowedWriteUser;
ALTER ROLE TableReaderRole
    ADD MEMBER SomeAllowedWriteUser;
ALTER ROLE TableWriterRole
    ADD MEMBER SomeAllowedWriteUser;
GO

Be aware that it is the owner rather than the schema name that is relevant for ownership chaining. If the table and proc were in different schemas, the ownership chain would still be unbroken as long as the schemas had the same owner (AUTHORIZATION). The object owner is typically inherited from the schema owner.

Related Question