SQL Server Brain Basher of the Week #055 - Security

You are the Database Administrator and you maintain a database with following characteristics;

• Database contains sensitive data.
• Database authentication is set to Mixed Mode.
• Users can access only tables they are authorized. Authorization is completely handled with SQL Server.
• Tables related to finance data can be accessed only by users who are in Finance User-Defined Database Role.

Sales team uses their own application to access the database. There is a new requirement from the Sales team as below;

• Sales team needs to see some financial data through the application.
• Sales team members should NOT be able to execute impromptu SELECT, INSERT, UPDATE, and DELETE statements against financial tables.
• The application will prompt for the second password when the screen related to finance data is opened. Sales person needs supervisor to input the second password for seeing data.

Here is the question of the week;

Being a DBA, what is your suggestion to achieve above requirement?

There are couple of things you need to consider. First, although users access the database via applications, users credentials are used for authorization, means users need permissions on finance data in order to access them. But, secondly, if users are granted permissions, they will be able to execute impromptu queries against tables, which should not be avoided. How can you grant permission to these specific tables without giving explicit permissions to users or roles?

If you are a smart DBA, now you should think about Application Roles. 

An Application Role is a database level role that permissions can be set with an application via the role, without setting permissions to users or other roles. You create it with a password, grant permissions as needed, in this case, let's say two tables related to finance, and then open the Application Role when two tables are required to access.

Since Supervisor enters the password, it can be used as the Application Role password. Once the application accepts the password, it calls a special stored procedure called sp_setapprole with the name of the Application Role and password. This creates New Security Context for the application connection and current user's context becomes inactive. User's security context will only be back once the sp_unsetapprole is called, until that, only Application Role and Public tokens are available for accessing resources.

Note that once the Application Role is active, user can only access other databases via guest account only.

For more info on Application Role, read this: https://msdn.microsoft.com/en-gb/library/ms181491.aspx