Security: Authorization

This shall be my last blog post for the year 2009 and what better to end security month than the actual post that started the security month idea. Before continuing, I suggest reading the previous security posts this month: Understanding Server Security Principals, Introducing Database Users and Roles and Schemas.
Two years ago I read an interesting white paper : SQL Server 2005 Security Best Practices - Operational and Administrative Tasks. The paper highlighted the definition of authorization as the process of granting permissions on securables and also stated that a best practice for authorization is to encapsulate access through modules (e.g. stored procedures, functions). This paper prompted the idea of allowing application users to only access or modify some database objects (e.g. tables) via procedural code. For example, if user 'Dave' needs to update the Customer table then a stored procedure to update the table can be created and he can be given EXECUTE access to that procedure rather than granting him UPDATE access to the Customers table.
While this is a great method for a DBA to secure the instance lets consider a scenario where a database has more than a hundred stored procedures and due to upgrades it is possible that new stored procedures will be added, how will you give a user EXECUTE access to each stored procedure? It is not feasible to grant access to each stored procedure one by one and remembering to grant access to each new one when they are created is troublesome. The answer to the question is schemas. Once all objects are part of the same schema then it just a matter of granting permissions at the schema level in order for a user to have permissions to all current objects and any new objects created in the schema. So in the scenario presented earlier, if the stored procedures are part of the dbo schema then the user has to be granted EXECUTE permission on the dbo schema and then the user will be able to execute all current procedures and any new stored procedures created in the dbo schema.

The syntax to grant permission on a schema is:
GRANT [Permission] ON SCHEMA::[schema_name] TO [Database User/Role]

e.g. GRANT EXECUTE ON SCHEMA::dbo to Dave

If the database user didn’t exist then use the following syntax to create a database user with a default schema :
CREATE USER [Username] FOR LOGIN [login_name] WITH DEFAULT_SCHEMA=[schema_name] 

e.g.
CREATE USER Appuser FOR LOGIN Dave WITH DEFAULT_SCHEMA=work
GRANT EXECUTE ON SCHEMA:: work TO Appuser