INF: SQL Security - Restricting access to public on server/database objects, its implications and ownership chains
INF: SQL Security - Restricting access to public on server/database objects, its implications and ownership chains
There are times when you need to harden the security of your SQL Server and prevent the public/guest users from having access to certain information like server or database level management views. This hardening is required due to company standards, policies or more generally a security best practise.
Here are some scenarios that I am going to cover in this article :-
1. Remove permissions for a guest/public user from viewing server and database like views like sys.databases etc..
2. Whenever you disable to guest account, windows users are not able to access the databases.
3. You Want to revoke all access from public role and give it to other user roles.
4. You want to know that even after revoking the select permission on the table, why you are still able to access the rows using the same select statement in a stored proc.
Let's first talk about the GUEST account. Here is some information on the GUEST account, which may come as a surprise to some of you. But i've put it together as an FAQ.
Q.1: What happens when we disable ‘guest’ account ?
Ans: By default the GUEST account is disabled in each user database. You cannot drop this guest account.
To grant access to guest account from a sysadmin login,
USE <DBNAME TO ENABLE GUEST>
GRANT CONNECT TO GUEST
To revoke access to guest account from a sysadmin login,
USE <DBNAME TO DISABLE GUEST>
REVOKE CONNECT FROM GUEST
Once the GUEST account is disabled on each database, only logins which have a database mapping will be able to access (USE) the database.Guest user itself exists to permit access to a database for logins that are not mapped to a specific database user.
If a login, which does not have a mapping(user defined in the database) and they try to access it, it will fail with following error:-
Msg 916, Level 14, State 1, Line 1
The server principal "USERNAME" is not able to access the database "DBNAME" under the current security context.
Q.2. Want to revoke all access from public role and give it to other user roles.
Ans: By default every database user automatically belongs to the fixed Public Server role. This cannot be changed as its hardcoded internally.
You can use the following query to check server permissions for public role,
SELECT
State_Desc, Permission_Name, class_desc,
COALESCE(OBJECT_NAME(major_id),DB_NAME(major_id)) SecurableName, SCHEMA_NAME(O.schema_id) [Schema],
Grantees.Name GranteeName, Grantees.Type_Desc GranteeType
FROM sys.server_permissions Perms
INNER JOIN sys.server_principals Grantees ON Perms.Grantee_Principal_Id = Grantees.Principal_Id
LEFT OUTER JOIN sys.all_objects O ON Perms.major_id = O.object_id
where Grantees.Name = 'public'
ORDER BY SecurableName
You can use the following query to identify permissions for public in EVERY database,
sp_msforeachdb 'BEGIN PRINT ''Permissions For PUBLIC ROLE in Database: ?'' PRINT '' '' select a.principal_id as [PrincipalID],
a.name as [UserName], b.major_id as [ObjectID], object_name(b.major_id) as [ObjectName],
b.permission_name as [PermissionDesc], b.state_desc as [PermissionState] from ?.sys.database_principals a
inner join ?.sys.database_permissions b
on a.principal_id = b.grantee_principal_id
where a.principal_id=0 -- Filtering on PUBLIC role
PRINT '''' END'
You can identify from above output as to what permissions are required for your application and then move that to other user-created roles. Keep in mind, once guest is disabled on each database, only logins that have a user mapping will be able to use PUBLIC database role.
Q.3. Okay, I got the message, but I still want to remove permissions from the public role in case make SQL more secure.
Ans: Allright. Here are some implications of revoking access to public :-
1. Regular users cannot use SQL Server Management Studio to connect to SQL Server.
e.g. If you revoke CONNECT TO
use master
go
DENY CONNECT SQL TO PUBLIC
Or you can specifically revoke CONNECT permission for each database to the guest account
USE <DBNAME TO DISABLE GUEST>
go
REVOKE CONNECT FROM GUEST
2. If you revoke permissions from public you will break functionality in features like replication, agent and logshipping. We don't have a list of what (if) will break.
But specifically regarding system objects, here we give a warning on possible functionality break,
https://msdn.microsoft.com/en-us/library/ms178634(SQL.90).aspx
3. Catalog Views have restricted visibility to a normal user so the select grants to public do not impact security. Likewise, system stored procedures with Execute granted to public have internal checks for role membership or possession of some additional permission.
However we can prevent users from public looking at server information by revoking access to public ON VIEW DEFINITION & VIEW ANY DEFINITION to view catalog views (system and database specific)
Reference https://technet.microsoft.com/en-us/library/ms190785.aspx (do the DENY for objects mentioned here)
use databasename
go
DENY VIEW DEFINITION TO testlogin;
-> Above restricts the user from getting information from the information_schema views and any other database scoped views
-> Note: This does NOT mean a user will get access denied, but will have "restricted view" which means results may be empty or displayed only for the public principal.
use master
go
DENY VIEW ANY DEFINITION TO testlogin;
-> Above restricts the user from getting information from the server scoped views like sys.databases etc.
As an additional hardening step, we can do the above commands even for the public role.
use master
DENY VIEW ANY DEFINITION TO public;
use <dbname>
DENY VIEW DEFINITION TO public;
4. In master database, you can revoke access to xp_cmdshell for public. This is more hardenning. Its upto to you to decide if you want to or not.
use master
go
DENY EXECUTE ON sys.xp_cmdshell TO public;
Q.4. I denied SELECT permissions for a user on a table, but I am still able to access the rows using the same select statement inside a stored procedure. Is this a bug?
Ans. No! Here is why I say that...
Reviewing this article https://msdn.microsoft.com/en-us/library/ms188332(SQL.90).aspx tells me this
<snippet> Permissions are not required to run the EXECUTE statement. However, permissions are required on the securables that are referenced within the EXECUTE string. For example, if the string contains an INSERT statement, the caller of the EXECUTE statement must have INSERT permission on the target table. Permissions are checked at the time EXECUTE statement is encountered, even if the EXECUTE statement is included within a module.
</snippet> I did a repro on my local SQL instance to test this behavior. Here are the steps i followed:-
1. Created a new TEST login and gave it access to TEST database. By default it took DBO schema on the TEST database.
2. Logged in as test login and ran the query "select * from test" and I was able to query the table.
3. Denied access on table TEST to login test.
DENY SELECT ON OBJECT::dbo.test TO [test]
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'Test', database 'test', schema 'dbo'.
4. Created a view to select from the test table "SELECT * FROM TEST.dbo.test"
We get same access denied when running the view
5. Created a Stored Procedure to select from the test table "SELECT * FROM TEST.dbo.test"
We get same access denied when EXECUTING the SP.
6. Granted EXECUTE permissions to user TEST on the stored procedure created.
Now am able to select the rows, even though a direct SELECT fails!
Hmmm, strange behavior. Did some more testing. I checed the owner of the table and SP.
EXEC sp_help 'testproc'
EXEC sp_help 'test'
Name Owner Type
--------- ------- -----------------
testproc dbo stored procedure
Name Owner Type
--------- ------- -----------------
Test dbo user table
In this case, the owner of table and SP were the same. This is because I created the SP when logged in SA. So what seems to be happening is that when actually doing the SELECT on the table, we DO NOT check permissions but check schema or owner.
To verify the above theory I changed the owner of SP/Table to something else (either 1 of below will do)
EXEC sp_changeobjectowner 'dbo.testproc', 'guest';
EXEC sp_changeobjectowner 'dbo.test', 'Domain\username';
I got this warning when changing owner,
Caution: Changing any part of an object name could break scripts and stored procedures.
Now when I tried to execute the stored procedure it failed because owning schema mismatches between table and SP. So this looks like ownership chaning. A Bing search brought up this gem of an article. I would highly recommend you read it as its very simple & a pictorial representation of security chaining in SQL Server.
Ownership Chains https://msdn.microsoft.com/en-us/library/ms188676(SQL.90).aspx
Other Useful Documentation regarding SQL Server Security
Security Best Practises Document
https://download.microsoft.com/download/8/5/e/85eea4fa-b3bb-4426-97d0-7f7151b2011c/SQL2005SecBestPract.doc
Troubleshooting Metadata Visibility
https://technet.microsoft.com/en-us/library/ms190785.aspx
Ownership Chains
https://msdn.microsoft.com/en-us/library/ms188676(SQL.90).aspx
Regards,
Sudarshan Narasimhan
Technical Lead, Microsoft SQL Server CSS