How to: Restrict access to public on server/database objects? What are its implications? What will happen to ownership chains?

I've been asked multiple times questions related to SQL Security where DBAs need to harden the security of their 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 few common questions that I've asked multiple times:

1. I want to remove permissions for a guest/public user from viewing server and database like views like sys.databases etc..
2. Whenever I disable guest account, windows users are not able to access the databases.
3. I want to revoke all access from public role and give it to other user roles.
4. I want to know that even after revoking the select permission on the table, why I'm 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.


Question 1: What happens when I disable ‘guest’ account?
Answer: 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.

Question 2. I want to revoke all access from public role and give it to other user roles.
Answer: 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

SELECTState_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_idwhere 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.

Question 3. Okay, I got it, but I still want to remove permissions from the public role in case make SQL more secure. what would be the implications?
Ans:wer 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,
http://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 http://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 http://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  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"
    I get same access denied when running the view

5. Created a Stored Procedure to select from the test table "SELECT * FROM TEST.dbo.test"
    I 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
http://msdn.microsoft.com/en-us/library/ms188676(SQL.90).aspx

Other Useful Documentation regarding SQL Server Security
Security Best Practises Document
http://download.microsoft.com/download/8/5/e/85eea4fa-b3bb-4426-97d0-7f7151b2011c/SQL2005SecBestPract.doc

Troubleshooting Metadata Visibility
http://technet.microsoft.com/en-us/library/ms190785.aspx

Ownership Chains
http://msdn.microsoft.com/en-us/library/ms188676(SQL.90).aspx