Sql-server – Restrict use of tables

loginssql server

I am looking to restrict SELECT/INSERT for the 60-80 tables in the database.
The database has very complex security structure, and want to restrict INSERT/SELECT for all users (and except SysAdmin, no other login should have access to those tables).

is that possible? thank you in advance.

Best Answer

By default, SELECT and INSERT access will not be granted. You can REVOKE any SELECT / INSERT perms as follows:

USE MyDatabase;

DECLARE @sql NVARCHAR(MAX) = '';

SELECT  @sql +=
        'REVOKE ' + p.[permission_name] COLLATE DATABASE_DEFAULT + ' ON [' + s.[name] COLLATE DATABASE_DEFAULT  + '].[' + o.[name] COLLATE DATABASE_DEFAULT + '] FROM [' + pr.[name] COLLATE DATABASE_DEFAULT + ']'
FROM    sys.database_permissions p
        JOIN sys.database_principals pr
            ON p.grantee_principal_id = pr.principal_id
        JOIN sys.objects o
            ON o.object_id = p.major_id
        JOIN sys.schemas s
            ON s.schema_id = o.schema_id
WHERE   p.[permission_name] IN ('SELECT','INSERT');
-- you can also filter on o.name to restrict to specific objects
-- and pr.name for specific prinicipals

EXEC sp_executesql @command = @sql;

As Unkush said in the comment, this will only REVOKE any existing access to that explicitly granted. access can also be implictly granted via built the built in db_datareader role and other avenues such as db_owner group and schema ownership.

To harden this further, you can place a deny on the public role (which all users fall into but this won't affect sysadmins) at the database level as follows:

USE MyDatabase;
DENY SELECT ON DATABASE:: MyDatabaseTO [public];

Here is a re-producible test:

First, set up the test db:

USE [master];

/* Create our test db*/
IF EXISTS (SELECT 1 FROM sys.databases WHERE name = 'PermissionsTest')
BEGIN
    ALTER DATABASE PermissionsTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE PermissionsTest;
END;

CREATE DATABASE PermissionsTest;
GO

USE PermissionsTest;

/* Create some tables*/
CREATE TABLE MyTable1
(
    a INT,
    b INT
);

CREATE TABLE MyTable2
(
    a INT,
    b INT
);

CREATE TABLE MyTable3
(
    a INT,
    b INT
);

CREATE TABLE MyTable4
(
    a INT,
    b INT
);

CREATE TABLE MyTable5
(
    a INT,
    b INT
);

CREATE TABLE MyTable6
(
    a INT,
    b INT
);

/* set up some users */
IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = 'MyLogin1') DROP LOGIN MyLogin1;
CREATE LOGIN MyLogin1 WITH PASSWORD = 'MySuperStrongPass', CHECK_POLICY = OFF;
CREATE USER MyUser1 FOR LOGIN MyLogin1;

IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = 'MyLogin2') DROP LOGIN MyLogin2;
CREATE LOGIN MyLogin2 WITH PASSWORD = 'MySuperStrongPass', CHECK_POLICY = OFF;
CREATE USER MyUser2 FOR LOGIN MyLogin2;

IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = 'MyLogin3') DROP LOGIN MyLogin3;
CREATE LOGIN MyLogin3 WITH PASSWORD = 'MySuperStrongPass', CHECK_POLICY = OFF;
CREATE USER MyUser3 FOR LOGIN MyLogin3;

IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = 'MyLogin4') DROP LOGIN MyLogin4;
CREATE LOGIN MyLogin4 WITH PASSWORD = 'MySuperStrongPass', CHECK_POLICY = OFF;
CREATE USER MyUser4 FOR LOGIN MyLogin4;

/* grant some explicit permissions */
GRANT SELECT ON MyTable1 TO MyUser1,MyUser2;
GRANT SELECT ON MyTable2 TO MyUser1,MyUser2;
GRANT SELECT ON MyTable3 TO MyUser1,MyUser2;

/* grant some permissions via a custom role */
CREATE ROLE MyRole;
ALTER ROLE MyRole ADD MEMBER MyUser2;
ALTER ROLE MyRole ADD MEMBER MyUser3;

GRANT SELECT ON MyTable4 TO MyRole;
GRANT SELECT ON MyTable5 TO MyRole;

/* add a user to the standard db role */
ALTER ROLE db_datareader ADD MEMBER MyUser4;

Then we can see how these permissions work:

EXECUTE AS LOGIN = 'MyLogin1'
    SELECT * FROM MyTable1 /* should return 0 rows (explicit permision)*/
    GO
    SELECT * FROM MyTable4 /* should error*/
REVERT

EXECUTE AS LOGIN = 'MyLogin2'
    SELECT * FROM MyTable1 /* should return 0 rows (explicit permission)*/
    GO
    SELECT * FROM MyTable4 /* should return 0 rows (via MyRole membership)*/
REVERT

EXECUTE AS LOGIN = 'MyLogin4'
    SELECT * FROM MyTable1 /* should return 0 rows (db_datareader)*/
    GO
    SELECT * FROM MyTable4 /* should return 0 rows (db_datareader)*/
    GO
    SELECT * FROM MyTable6 /* should return 0 rows (db_datareader)*/
REVERT

Then we can run the dynamic revoke :

USE PermissionsTest;

DECLARE @sql NVARCHAR(MAX) = '';

SELECT  @sql +=
        'REVOKE ' + p.[permission_name] COLLATE DATABASE_DEFAULT + ' ON [' + s.[name] COLLATE DATABASE_DEFAULT  + '].[' + o.[name] COLLATE DATABASE_DEFAULT + '] FROM [' + pr.[name] COLLATE DATABASE_DEFAULT + ']'
FROM    sys.database_permissions p
        JOIN sys.database_principals pr
            ON p.grantee_principal_id = pr.principal_id
        JOIN sys.objects o
            ON o.object_id = p.major_id
        JOIN sys.schemas s
            ON s.schema_id = o.schema_id
WHERE   p.[permission_name] IN ('SELECT','INSERT');

EXEC sp_executesql @command = @sql;

And test (this will leave the db_datareader perms in place)

EXECUTE AS LOGIN = 'MyLogin1'
    SELECT * FROM MyTable1 /* should error*/
    GO
    SELECT * FROM MyTable4 /* should error*/
REVERT

EXECUTE AS LOGIN = 'MyLogin2'
    SELECT * FROM MyTable1 /* should error*/
    GO
    SELECT * FROM MyTable4 /* should error*/
REVERT

EXECUTE AS LOGIN = 'MyLogin4'
    SELECT * FROM MyTable1 /* should return 0 rows (db_datareader)*/
    GO
    SELECT * FROM MyTable4 /* should return 0 rows (db_datareader)*/
    GO
    SELECT * FROM MyTable6 /* should return 0 rows (db_datareader)*/
REVERT

We can then either place a deny on the entire database (a deny is the most robust way as there are various other avenues users can get SELECT permissions such as db_datareader, schema ownership, db_owner etc)

DENY SELECT ON DATABASE:: PermissionsTest TO [public];

Alternatively, you can supply a more granular DENY at a schema or table level:

DENY SELECT ON TABLE:: MyTable1 TO [public]

Running the Test again:

EXECUTE AS LOGIN = 'MyLogin1'
    SELECT * FROM MyTable1 /* should error*/
    GO
    SELECT * FROM MyTable4 /* should error*/
REVERT

EXECUTE AS LOGIN = 'MyLogin2'
    SELECT * FROM MyTable1 /* should error*/
    GO
    SELECT * FROM MyTable4 /* should error*/
REVERT

EXECUTE AS LOGIN = 'MyLogin4'
    SELECT * FROM MyTable1 /* should error*/
    GO
    SELECT * FROM MyTable4 /* should return 0 rows (db_datareader)*/
    GO
    SELECT * FROM MyTable6 /* should return 0 rows (db_datareader)*/
REVERT

Related Solutions

Sql-server – SQL Server column level security

Column level security does not work that way. I'm aware of no mechanism to globally deny access to a certain column for a given user. The GRANT/DENY only works on specific statements like SELECT, UPDATE and so on in combination with a given object.

So in your case, if you removed access on SELECT for column X on table Y the user can still happily execute "select *" views on that table because the view is a different object and is unaffected by this security setting!

The good news is that you can use column permissions on views also. It works just the same as with tables, but you have to set the permission on every view that includes the SSN column.

SQL Server – Using sp_setapprole for Creating Users and Logins

to do it correctly is not very simple, although once setup, your done.

Data users or application roles default have database scoped permissions. Database users are linked to Logins and therefore can have server wide permissions from their Login. Application roles aren't linked and can only get server wide permission by using a form of impersonation.

Any way to give these server scope permissions by means of impersonating will fail by default.This is by design.

You have 2 options:

Tell SQL Server that you as a DBA trust any user in that database to have privilegde escalation to server wide permission if impersonation is used. You acomplish this by setting the option TRUSTWORTHY to ON However if you don't fully understand what this option does first read: SQL Server 2012 Best Practice Whitepaper (Specifically page 18 and 19 about "Database Ownership and Trust")
Use signed stored procedures to accomplish your task.

I'll first give you the example of option 1:

CREATE LOGIN [appRoleProxy] WITH PASSWORD ='1234abcd!@'
GO

EXEC sp_addsrvrolemember    @loginame='appRoleProxy',
                            @rolename='securityadmin'

GO

CREATE DATABASE [Stefaan]
go

ALTER DATABASE [Stefaan] SET TRUSTWORTHY ON
GO

USE [Stefaan]
GO

--add the proxy account to the dbo role since your stored procedure will be running in the context of this account and is also accessing your user database.
ALTER ROLE [db_owner] ADD MEMBER [appRoleProxy]
GO

CREATE APPLICATION ROLE appRole WITH PASSWORD ='Passw0rd!';
GO

CREATE PROCEDURE dbo.usp_testproc 
@LoginName nvarchar(128),
@Password nvarchar(128),
@DBName nvarchar(128)
WITH EXECUTE AS 'appRoleProxy' --Impersonate a SQL server login with the proper server level permissions
AS
DECLARE @SQL varchar(max)
BEGIN
    IF NOT EXISTS ( SELECT  name
                    FROM    sys.server_principals
                    WHERE   name = @LoginName )
        BEGIN
            SET @SQL = 'CREATE LOGIN [' + @LoginName + '] WITH PASSWORD = '''
                + @Password + ''', DEFAULT_DATABASE=[' + @DBNAME
                + '], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF';
            EXECUTE(@SQL);
        END

    IF NOT EXISTS ( SELECT  name
                    FROM    sys.database_principals
                    WHERE   name = @LoginName )
        BEGIN
            SET @SQL = 'CREATE USER [' + @LoginName + '] FOR LOGIN ['
                + @LoginName + ']';
            EXECUTE(@SQL);
        END

    IF EXISTS ( SELECT  name
                FROM    sys.server_principals
                WHERE   name = @LoginName )
        BEGIN
            EXEC sp_addsrvrolemember @LoginName, 'securityadmin'
        END
END
GO

GRANT EXECUTE ON [dbo].[usp_testproc] TO [appRole]
GO


sp_setapprole   @rolename='appRole',
                @password='Passw0rd!'



EXEC usp_testproc 'testlogin','password23@','stefaan'

Now for an example of option 2 I'd like to point out a answer a gave on another question: Msg 4834 “You do not have permission to use the bulk load statement”

Best Answer

Related Solutions

Sql-server – SQL Server column level security

SQL Server – Using sp_setapprole for Creating Users and Logins

Related Question