[dba-SQLServer] Schema names and security boundaries - second try...

Asger Blond ab-mi at post3.tele.dk
Thu Jun 20 17:58:55 CDT 2013


Hi all,

Some weeks ago I posted a question about your usage of schema names but didn't get any response.
I'll try once more:

-----

How do you use schema names in SQL Server if you want to use them as meaningful namespaces, and also intent to use them as security boundaries but don't want to grant permissions on the base tables to any other than admins?


Some background:
As you probably know Microsoft made an important change in SQL Server 2005 separating schema names and owner names.
Prior to 2005 the schema name of an object always identified the owner of the object. For example an object named JohnDoe.Customer would imply that the owner of the object was a user named JohnDoe. You couldn't have an object with a more meaningful name prefix such as Sales.Customer unless you had a user named Sales, who would then be the owner of the object.
Quite an awkward situation which for ages led to schema names not being used as intentional by the SQL standard: an identifier of meaningful groupings, i.e. of "namespaces". The net effect of SQL Server not conforming to the SQL standard was that objects most often wasn't identified by a meaningful namespace names but was just prefixed by dbo, implying that the object was owned by the standard database owner user dbo.
IMO it's good practice to have all objects owned by the build-in standard database owner user named dbo. But IMO it's really bad practice to junk all objects into the same namespace.
Fortunately Microsoft changed its practice in 2005 and brought the usage of object prefixes in accordance with standard SQL. From 2005 and onwards the prefix doesn't imply any ownership. You can now have an object named JohnDoe.Customer which is actually owned by DonaldJones (but normaly I would prefer to have it owned by the standard user dbo).  And you can have an object named Sales.Customer which is actually not owned by a user named Sales but by a user named DonaldJones (but again normally I would prefer to have it owned by the standard user dbo).
So far so good. But there's more to it: Security.
Schema names can be used as security boundaries: If you grant a permission on a schema to a user or role you are granting this permission on each and every objects in the schema. Quite effective...
But a NO GO if you are member of the school saying "Don't ever grant missions to tables, only grant permissions on programming objects accessing the tables (such as views, stored procedures, and functions)".  I belong to this school.
So - to repeat my question: How do you use schema names in SQL Server if you want to use them as meaningful namespaces, and also intent to use them as security boundaries but don't want to grant permissions on the base tables to any other than admins?

My practice is: Create separate schemas for tables and user-accessible objects within each meaningful grouping of the objects. For example:
Table Customer:

Sales_Table.Customer (for the table and only accessible for admins)

Sales.CustomerView (for a user accessible view on the Sales_Table.Customer table)

Sales.CustomerProcedure (for a user accessible procedure on the Sales_Table.Customer table)

Sales.CustomerFunction (for a user accessible function on the Sales_Table.Customer table)

Note this is not about naming conventions - whether to use prefixes such as vw for views, usp for procedures, and udf for functions. That's a different discussion. Here I'm only applying the names CustomerView etc. as placeholders for user accessible objects.
My question is about separating user accessible object from tables using schema names.

Curious to hear your thoughts on best practices.

/ Asger



_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com





More information about the dba-SQLServer mailing list