Sql-server – DENY overrides GRANT in SQL Server

sql server

I'm studying for exam 70-462 and I'm practicing with some exercises I find around the web. In the site "Microsoft Questions and Answers" there's one solution I don't understand.

The solution's last step is to "Deny SELECT access to the SalaryDetails table to the Employees role." However, all company employees were added to this role and because "HR Administrators are also company employees," then HR Administrators should end up without any access to the SalaryDetails table, which is not what's being requested.

Is the solution wrong, or am I missing something?

Thanks in advance for any help!

Best Answer

It has to do with the hierarchy of permissions and using role membership over granting permissions directly to each login. This SQLMag article shows a good image of the hierachy in SQL Server. [In the event the article ever goes dead I have copied the image below.]

You are granting DENY to the role Employees so the HR Administrators being a member of a higher level role should mean they do not directly inherit the DENY. At least that is how I understand it.

The particular section of the article referenced speaks on DENY:

The third permission statement is DENY. A DENY statement also revokes a permission, but does it in such a way that the principal cannot inherit the permission through role membership. When you deny a permission, that principal doesn't have it, period. Denying permissions can let you get very granular with permissions. A common example is to grant everyone in a department a set of permissions through their membership in a role. But then you can fine tune the scheme by denying permissions either to certain users or to another role that only a subset of users belong to, taking advantage of the ability of users to belong to multiple roles.

I stand corrected

Let me first state I apoligize for not first testing what I was saying as I ended up proving myself wrong. I should have known better.

First lesson to learn: Test everything.

Second lesson to learn: Not all Microsoft Exam questions are perfect. I would suggest sticking with those test engines that Microsoft supports (e.g. MeasureUp.com)

Answer before the details

I will first give you what I discovered, then show you how I tested it. So my finding is that basically option D can be correct if you use REVOKE instead of DENY. This is based on the statement from Bob Beauchemin's whitepaper on SQL Server 2005 Security Best Practices:

In SQL Server, authorization is accomplished via Data Access Language (DAL) rather than DDL or DML. In addition to the two DAL verbs, GRANT and REVOKE, mandated by the ISO-ANSI standard, SQL Server also contains a DENY DAL verb. DENY differs from REVOKE when a user is a member of more than one database principal. If a user Fred is a member of three database roles A, B, and C and roles A and B are GRANTed permission to a securable, if the permission is REVOKEd from role C, Fred still can access the securable. If the securable is DENYed to role C, Fred cannot access the securable. This makes managing SQL Server similar to managing other parts of the Windows family of operating systems.

So basically REVOKE removes the permissions and basically would be not applying any permissions to the table itself. You are not explicitly denying anyone access to the table. So if you really wanted to deny permissions you would have to remove the HRAdmins group from the Employees roles for it to work correctly.

I do not have access to Azure SQL so I could only test this on a local SQL Server 2014 instance. The script below is what I went through, included comments as I go.

USE [master];
GO
--Create a database to work with
CREATE DATABASE [Database_WorkingWithPermissions];
GO

--Create the logins (I can't create Windows Groups but should work the same way)
CREATE LOGIN HRAdmin1 WITH PASSWORD = N'Apple123';

CREATE LOGIN Employee1 WITH PASSWORD = N'Apple123';

--Create the users in the database
USE [Database_WorkingWithPermissions];
GO

CREATE USER Employee1 FROM LOGIN Employee1;
CREATE USER HRAdmin1 FROM LOGIN HRAdmin1;
GO

--Create the role
CREATE ROLE Employees

--Create the tables noted in the question
CREATE TABLE [Employees] (employeeID int IDENTITY(1,1), employeeName varchar(15));
CREATE TABLE [SalaryDetails] (employeeID int, salaryAmount decimal(12,3));
CREATE TABLE [OtherTable] (column1 int);

--Grant permissions to the roles according to requirements
GRANT SELECT ON [dbo].[Employees] TO [Employees];
GO
GRANT SELECT ON [dbo].[OtherTable] TO [Employees];
GO
DENY SELECT ON [dbo].[SalaryDetails] TO [Employees];
GO

--Assign membership to the role based on requirements
ALTER ROLE [Employees] ADD MEMBER [Employee1];
GO
ALTER ROLE [Employees] ADD MEMBER [HRAdmin1]
GO
ALTER ROLE [db_datareader] ADD MEMBER [HRAdmin1];
GO

--Go through and see what permissions
EXECUTE AS USER = 'HRAdmin1'
SELECT * FROM [dbo].[Employees];
SELECT * FROM [dbo].[SalaryDetails];
REVERT;

EXECUTE AS USER = 'Employee1'
SELECT * FROM [dbo].[Employees];
SELECT * FROM [dbo].[SalaryDetails];
REVERT;

--So HRAdmin1 does not have permissions as it should
-- So they need to be removed from the Employee role
ALTER ROLE [Employees] DROP MEMBER [HRAdmin1]
GO

--Run the test again
--Go through and see what permissions
EXECUTE AS USER = 'HRAdmin1'
SELECT * FROM [dbo].[Employees];
SELECT * FROM [dbo].[SalaryDetails];
REVERT;

EXECUTE AS USER = 'Employee1'
SELECT * FROM [dbo].[Employees];
SELECT * FROM [dbo].[SalaryDetails];
REVERT;

--According to a SQL Server 2005 whitepaper Bob Beauchemin wrote on Security Best Practices
-- Revoke should be used
ALTER ROLE [Employees] ADD MEMBER [HRAdmin1]
GO
REVOKE SELECT ON [dbo].[SalaryDetails] TO [Employees];
GO

--Run the test again
--Go through and see what permissions
EXECUTE AS USER = 'HRAdmin1'
SELECT * FROM [dbo].[Employees];
SELECT * FROM [dbo].[SalaryDetails];
REVERT;

EXECUTE AS USER = 'Employee1'
SELECT * FROM [dbo].[Employees];
SELECT * FROM [dbo].[SalaryDetails];
REVERT;

Related Solutions

Sql-server – Using the correct database when calling a system stored procedure in SQL Server 2008

You need to mark your stored procedure as a system object to get the behaviour that you want (full example below tested as working on 2008 SP3)

USE master;
GO

CREATE PROCEDURE dbo.sp_mysp
AS
SELECT *
FROM INFORMATION_SCHEMA.TABLES

GO

USE tempdb;
EXEC dbo.sp_mysp /*Returns tables from master*/

GO

USE master;
EXEC sys.sp_MS_marksystemobject sp_mysp

GO

USE tempdb;
EXEC dbo.sp_mysp /*Returns tables from tempdb*/

This is an undocumented approach and may not work in future versions.

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”