SQL Server Access Control & Synonyms

OK. So, say you have a SQL Server database and you want to provide varying levels access to different users groups.   This database was created for one application, and now you are being asked to provide access to a report development team.   How do you go about doing that in some rational way, say the way an admin assigns access to files on a file server. 

Well, that would be great, only there aren't any folders in SQL Server.  BUT, since SQL Server 2005 we've been given real Schema objects and Synonyms to boot.

SO, how does that help us?  Lets take a look, say you built your database and like any rational developer you built everything in DBO.  Here's a list of your tables:

dbo.Customers
dbo.PurchaseOrders
dbo.Invoices
dbo.UserLogins
dbo.ApplicationSettings

Obviously you don't want the report developers having access to the UserLogins and ApplicationSettings.  One, they just don't need it, and two, there's sensitive stuff in there.

Our approach:

• Use an active directory group (My_Domain\Report Writers) to control access.
• Create a Schema for the report writers to access
• Assign access to the new schema and not the old one.

 Step 1) Create the new schema:
CREATE SCHEMA reports

Step 2) Add some table synonyms to the schema:
CREATE SYNONYM reports.PurchaseOrders FOR dbo.PurchaseOrders
CREATE SYNONYM reports.Invoices FOR dbo.Invoices
CREATE SYNONYM reports.Customers FOR dbo.Customers

 Step 3) Map the AD group to your database
CREATE LOGIN [MY_DOMAIN\Report Writers] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
CREATE USER [MY_DOMAIN\Report Writers] FOR LOGIN [MY_DOMAIN\Report Writers]

(This is the same as adding a Login at the server level, and mapping to the public role on a database catalog).

Step 4) Give the report writers access to the reports schema.
GRANT SELECT ON SCHEMA :: reports TO [MY_DOMAIN\Report Writers]

What have we accomplished?

• Your report writer team can log into your database
• Your report writer team can view all of the table synonyms in Management Studio
• Your report writer team doesn't have any write permissions (INSERT/DELETE/UDPATE) to anything.
• Your report writer team cannot query the objects in DBO directly, so they don't have access to sensitive tables like UserLogins and ApplicationSettings.

Why did this work?

Turns out that in SQL Server Synonyms are like file system hard links.  So if you had a file in one directory, and took away permissions on that directory.  Then created a hard link in another directory and give permissions, the user would have access.  The same idea works here.   Since the report writers didn't have access to the DBO schema, they can't view the tables there.  But since they have access to REPORTS they may read the synonyms and query them as well.

Turns out that you can customize access to the synonyms once they are created.  All of the GANT/DENY/REVOKE commands work the same.  You'll even be able to apply column level security!