Understanding the Views (ii) - Ownership chaining

This is my second article about the Views and is a continuation from my previous one. In this one I will only focus on the security part of the Views. In my previous article I explained how you can secure data with Views limiting the scope of the returned information depending on the person who wants to access the data. In this article I will talk about the ownership chaining.

As we saw in my previous article when we create a View for querying our tables we do not need to give permissions on those tables but the View only. And why is that? It's because SQL Server knows that we are the owner of those tables and so it uses the ownership chaining to bypass the necessary permissions checks. This behavior it's how the engine works and can't be changed.

It's possible to grant permission on a database object and allow the granted user to grant his permission on the object to others. In my experience this very rarely has to happen but since it is possible to do I decided to write this article to call some attention to when and if you need to do that.

Imagine that the Chief Financial Officer (CFO) of a company needs to have access to salary information of the company. We won't give him access to the main table but will create a View that selects all data from the salary's table:

CREATE VIEW AllSalaries AS
SELECT *
FROM Salary

Then grant him the select permission on the View. But during his absence he needs to hand over his work to his deputy so we'll need to give him the option to grant himself the necessary permission to give access to his deputy, so the WITH GRANT OPTION will do the trick:

GRANT SELECT ON AllSalaries TO [MyDomain\CFO] WITH GRANT OPTION

Can you see a security hole here?

If we are talking about a View that works with sensitive data the information can be spread to unwanted people. Of course we could give immediately the SELECT permission to the CFO and his deputy but imagine that the CFO wants to control when his deputy can access the data, or even if he has more than one deputy he wants to choose to whom he's going to give permission depending on his abscence period.

So, how to avoid this without saying NO to our CFO and ensure that his deputy won't give permissions on the View to more people?

DENY command is the answer since it precedes the GRANT permission. Of course if there are a lot of users in the company we would need to write a DENY command for each user. To avoid that work we can ask for creation of an AD group where these users will be added except the CFO and his deputy or deputies. Then the DENY command will be something like this:

DENY SELECT ON AllSalaries TO [MyDomain\DeniedUsers]

Then even someone gives access to one of the users in the Denied group the AllSalaries view won't return an error but will raise the following error if that user try to access the View:

The SELECT permission was denied on the object 'AllSalaries', database 'EE_DB', schema 'dbo'. 

Understanding the Views (i) - Limiting the access to data

It can be said that a View is a virtual table defined by a query and that it also has columns and rows. There are several occasions to use a View, but in this article I will focus only in the security mechanism that is one of the principal reasons for using a View.

One of the purposes for creating a View is to allow users to access data without granting them permissions to the base tables. Imagine your company's Human Resource database. Every employee has access to his data, but how can  you ensure that each employee can only see information related to him?

For example, if you give read permission to the Salary table:

GRANT SELECT ON Salary TO [MyDomain\Employees]

MyDomain\Employees represents an AD group with all Employees users

Everyone now can see his own information by running a simple SELECT command:

SELECT *
FROM Salary
WHERE EmployeeID = USER_NAME()

Let's keep things simple so for this company the EmployeeID is the user's LogonID, so we can match the AD with the table primary key.

But they can also see any colleague's information by replacing the USER_NAME() with the corresponding colleague's ID... or even worse, they can see the salaries of all employees with a simple SELECT * FROM Salary command.

To avoid these situations we can create a View and give permissions only to that View instead of the Table. Below is the View definition:

CREATE VIEW EmployeeSalary AS
SELECT *
FROM Salary
WHERE EmployeeID = USER_NAME()

Now let's revoke the permissions that we gave to the Salary Table and give permission only for the View:

REVOKE SELECT ON Salary TO [MyDomain\Employees]
GO

GRANT SELECT ON EmployeeSalary TO [MyDomain\Employees]
GO

The user will have only access to the View and can't even see the Tables or other Views that may exists in the database.
That can be confirmed if the user tries to run a SELECT statement over the Salary Table. Will raise the following error:

Msg 229, Level 14, State 5, Line 1

The SELECT permission was denied on the object 'Salary', database 'EE_DB', schema 'dbo'.

Each employee now can only have access to the information that respects to him and none else.

SQL Server Security: Server authentication mode

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties screen, Security tab:

By default Windows Authentication mode is selected since is the one recommended by Microsoft and also the one that is more secure because it uses Windows Security tokens so no passwords will be passed through the network when connecting to the SQL Server instance. It is also the one that is more easy to use since the user doesn't need to remember one more login name and password and it is more easy for maintenance since it will be managed by the Active Directory (AD) so it will respect the user AD policies.
The creation of a Windows authentication login is as simple as providing the domain and user name as the Login name:
The same can me performed using the following T-SQL command:

CREATE LOGIN [MyDomain\DomainUserName] FROM WINDOWS

In Windows authentication mode the sa login is disabled so ensure that someone else has the sysadmin role.

SQL Server logins are managed inside the SQL Server engine. The passwords are stored in the SQL Server master database and when connecting to the SQL Server instance the user credential is passing through the network. So, when creating a SQL Server authentication login a login name and password need to be provided:
The same can me performed using the following T-SQL command:

CREATE LOGIN [SQLLoginName] WITH PASSWORD=N'Pwd' MUST_CHANGE, CHECK_EXPIRATION=ON, CHECK_POLICY=ON

By default a SQL Server Login will use the local password policy. If you do not want to enforce a password policy then you just need to clear the "Enforce password policy" checkbox or use the following T-SQL command:

CREATE LOGIN [SQLLoginName] WITH PASSWORD=N'Pwd' CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

NOTES:

• I encourage you to enforce an account policy to guarantee strong passwords and also set a policy to enforce the password to be changed regularly. It is also a good policy to lock the account if some attempts with wrong password happens since it can be someone trying to hack an account.
• When enforcing a policy remember that previous logins does not have the new policy enforced so inform those users that they need to change their passwords. You can set the "User must change password at next login" to all those users so it will assure that they will change their passwords next time they log in.

The Windows authentication mode respects the Domain Password Policy so everything will be managed by the AD administrators.
The SQL Server authentication mode respects the Local Password Policy. For verify or change the local policies open the Local Security Policy utility in the Administration Tools and go to Account Policies where the Password and Account Lockout policies can be found:


Windows authentication logins can not be disable in SQL Server as also their passwords can not be changed in SQL Server. Those are tasks that need to be performed in the Active Directory by the respective administrator.
On the other way, SQL Server authentication logins are managed in SQL Server and enabling/disabling logins and changing their passwords are tasks that need to be done with someone with administrator role. Follow are respectively the T-SQL command to enable, disable and change a SQL Server login:

ALTER LOGIN [SQLLoginName] ENABLE;
ALTER LOGIN [SQLLoginName] DISABLE;
ALTER LOGIN [SQLLoginName] WITH PASSWORD = 'New Password';

NOTES:

• All this can be performed in SQL Server Management Studio in the Login Properties screen.
• Only an user with sysadmin or securityadmin roles can change other users passwords. An user without those permissions can change only his own password but then he needs to provide the old password as well:

ALTER LOGIN [SQLLoginName]
WITH PASSWORD = 'New Password'
OLD_PASSWORD = 'Old Password';

If the password policy is enforced and the new password does not match the policy then the following error is returned:
Msg 15116, Level 16, State 1, Line 2
Password validation failed. The password does not meet Windows policy requirements because it is too short.

As you can see, using a SQL Server authentication mode let a lot of tasks to be managed by a Database Administrator (DBA) so whenever possible use Windows authentication mode and let these tasks to be performed by AD administrators as it is more secure and give the DBAs time to perform other important tasks.
But if is not possible and you will need to use SQL Server authentication then do not forget the Security part: Enforce strong account policies.