Although the applications that use a database have a role in controlling access to data, it is extremely unwise to think that this is sufficient. Databases must have their own built-in access-control. This applies to all databases, even those that store only innocuous data that doesn’t include personal or financial information. In such cases, access control acts as a simple second-line defence against SQL Injection and other external attacks. It is also an excellent defence against data being accidentally or maliciously overwritten, or deleted.
Access control boils down to the principle that the users of an application should have permissions to view, modify or delete only the data in the live database that is relevant to their job. This is what is meant by ‘executing with minimum necessary privileges’.
However, different groups of users, even in one application, may have different requirements, and in a large database, with many users, it’s easy to get overwhelmed by the hierarchy of principals, securables and permissions required to allow each user the correct level of access to the data they need and are entitled to see, and only that data.
A fundamental mistake is to try to assign permissions on an object-by-object basis. As the size and complexity of a database increases, so this approach becomes more and more difficult to manage, especially if attempting to assign object-level permissions to individual logins. The more difficult it becomes, the more likely it is that snags appear during deployment, access-control mistakes happen, and security loopholes appear. This leads inevitably to the path of least resistance where, for example, all user accounts end up as members of the db_datawriter and db_datareader roles or, even worse, realistic access control is entirely abandoned in favour of ‘getting the application working’. This is the back-story for countless data-heists.
This article describes the various access control technologies in SQL Server that make it easier for developers to design a simple database access-control system that also provides the freedom of choice to meet the particular requirements of the application. It advocates a simple, structured approach to access control, based on use of schemas to group together database objects, and assigning specific permissions to roles that can access only the objects in that schema.
This article sticks to describing the principles of a structured, schema-based security model. See the SQL Server Security Workbench to see some of these principles represented in simple SQL examples.
The Principles of Least Privileges applies to everyone
Nobody is exempt from the principle of ‘least privileges’; it does not apply only the end users of the database application. It is easier to develop a secure database if the developers and DBAs use only an account that gives them sufficient privileges for the role they are undertaking, referred to as using the ‘least-privileged user account’.
DBAs should only use the SysAdmin role, for example, when its privileges are essential for the work they need to do. Developers who have access to privileged accounts should only use the more powerful accounts when they really need one of its privileges, and should use a normal account with fewer privileges for their regular work.
Working in this way will highlight security problems as early as possible in development, and prevent accidental deletion or alteration. By using appropriately-restricted accounts during development rather than ubiquitous god-like powers, a developer will become aware of potential security problems more quickly.
Securable, principals and permissions
Associated with each securable are a set of permissions that we can grant to a principal. Securables range from coarse-grained, at the server-level and database-level, down through the schema-level and then to the most fine-grained; individual objects, such as tables and routines within a database.
A principal is merely anything that can request a SQL Server resource and be authenticated by the system, such as a user or process. We grant or revoke permissions to each principal using DCL (Data Control Language). Permission to a securable can be assigned to an individual user or process, but it is recommended to assign them to collections of users that belong to a single group or role. There are three different levels in the Principals hierarchy; Windows, SQL Server, or Database, as follows:
- Windows level: windows domain, or local logins, If they are windows domain group logins, then all members of that group get the permissions that are assigned to the login,
- Server level: SQL Server userid/password logins or server roles.
- Database-level: either users or roles.
The ‘group’ principals, such as Roles and Windows groups, allow for a great deal of versatility. Ideally, in a company of any size, individual access to systems are best managed via the domain’s active directory, rather than become a DBA chore. This means using windows groups as principals. Although SQL Server logins are best avoided for security reasons, they are unavoidable if access is required from outside the domain. We will return to the topic of assigning permissions to roles later on in this article!
Let’s simplify this as much as I dare.
You can, of course assign permissions for securables directly to users but the result will soon become unmanageable.
By assigning windows groups, if these are available and appropriate for the security requirements of your application, you can make things rather simpler.
You can map things out much more clearly by using database roles, but even now, you’re likely to need a lot of permissions. However you are making access-control a lot easier to maintain.
If you add in the idea of using schemas, and applying a structured approach, then suddenly the task of providing access control becomes a lot easier.
If this were sufficient to get you up and running with schema-based access-control, then it would be a nice short article, but now we must fill in the detail.
A Structured Approach: Schema-based security
A simple, structured approach to access control advocates using the permission hierarchy in ANSI SQL (including SQL Server) to create database roles that have the minimum necessary privileges at the highest possible level. The highest possible securable-level, in practice, is almost always the schema level, but can be at the database (catalog) or server level. We then assign principles to each roles, as required.
Of course, some flexibility may be required. It could be that, for a particular purpose, schema-based security is too general, and you need to use object-level permissions, or to ‘DENY’ access to specific objects within a schema, but this is exceptional and is best assigned to a special user-defined database role. In general, however, schema-based security is a good way of controlling access to a SQL Server database.
NOTE:A possible alternative to schema-based security is module-signing, but we won’t discuss this in the article. For Module-signing, see Signing Procedures with Certificates
The permissions given to a principal on the schema itself will apply to all ‘securable’ objects belonging to the schema and that principal will have the same permission on all those objects.
Schemas therefore provide an easy way to specify permissions for a large collection of securable objects. By combining this with the use of database roles and ownership chaining, you can implement one of several possible security designs that will allow you to restrict access.
Another good reason for adopting this structured, schema-based approach is that it avoids use of object-level DCL. Ideally, logins, and the assignment of logins to roles really should be held separately from DML and DDL, in source control. This is particularly the case in the Enterprise environment where individual deployments for test, integration and staging will need their own access control settings. The problem with object-level DCL is that it has to be kept together with the object’s code to keep it manageable, and that makes deployment a lot more complicated. It is much more practical to use the alternative of using database roles as principals, and creating Schema-based access-control. Then, the code can be deployed to dev, test, staging and production with the appropriate logins and users that are then assigned to these roles via DCL. In this way, the same access-control model works in all settings the same way that it did in Development when created using ‘least privileges’.
The Three Purposes of Schemas
Schemas have three main, overlapping purposes:
- Namespace: Schemas can divide databases up into logical areas, or ‘workspaces’, as in the AdventureWorks sample database. This will allow individual schemas to be scripted out and in and allow the same object name to be used in different schemas.
- Access-Control: Schemas can simplify assigning permissions because these are inherited from the schema by all objects within the schema. Any database user can, upon being assigned to a particular database role, then obtain permissions to access every object within any schema to which that role that has been assigned permissions. This simplifies access control and can also greatly assist team-working on a shared development database.
- Application-Interface: Schemas can provide an application interface that abstracts the base tables behind views, procedures and functions, providing just the data appropriate for the application, and allow the application and database to be looser-coupled so as to avoid mismatches between application and database versions.
These three uses, namespace and access-control, and application interface aren’t necessarily compatible, though an application interface is likely to build on access control.
The schema as a namespace
The Namespace schema model is versatile, but can make life difficult for access control if it is mapped purely to logical application areas, in much the same way as one might do an application. Base tables are often stored in these namespace-schemas, even when the information in the table is required by several different categories of users, each with their own security requirements. This complicates access-control. Consider the AdventureWorks example, where HR, Purchasing, Sales and DBO all want to access the Person.BusinessEntity table. To implement all the permission for this sort of design, to support the essential table-joins, could result in an unmaintainable rat’s nest of complication, unless you merely throw up your hands and assign all users to the db_owner role.
The Schema as a means of assigning permissions
From SQL Server 2005 onwards, every database object such as a procedure, view, function or table, is in a schema. By assigning a permission to a schema, you apply that permission to all objects within the schema, unless this permission is then explicitly denied at object-level.
There is always at least one schema, the dbo schema, which exists in an empty database, and which is owned, somewhat confusingly, by the dbo user account. The user of any database is defaulted to this ‘dbo’ schema, unless explicitly assigned to a different schema. Unless objects are referenced by schema as well as name, they are assumed by the database engine to be in the user’s default schema, and if not there, in the dbo schema.
Using a Schema as an interface.
Part of the art of database design is to provide robust security for the live database by providing one or more application-interfaces, each corresponding to a distinct job role or task. For each job role, everything necessary, and nothing that is unnecessary, for a member of that role should be provided by the application via this interface.
Each database role then can be made ‘owner’ of a schema that maps to the job role and is assigned to that schema by default. A scheduled ETL task could, for example, be given a schema that would contain all the necessary staging tables and procedures it needs to perform its tasks. This would constitute an ‘application interface’ that groups together all of those objects that are to be used for a particular job role, such as a department or division within a company.
The application interface could use a combination of procedures, views, report-tables, aggregation-tables and functions that, between them, provide to the application a subset of the database that is appropriate for the role. Ownership Chaining makes this possible and easy to implement.
Ownership Chaining
So far, we’ve described how we can confine each type of user to their own schema, but allow them unfettered access to all objects within their schema.
However, this isn’t enough, because a user will need to see parts of a table outside that schema, and to which they have no permissions. If the data has a security requirement, we are very unlikely to want to give them permission on the whole table, because this access model requires us to allow users to see only the data that they need to see.
It is easy to partition a table using a view, to restrict access to only certain rows or columns, but how would you give SELECT permission on just a view, or any other type of routine, without granting a SELECT permission on the underlying table as well? The answer is simple: using the principal that created the table (e.g. dbo), you create the view in the schema where the principal has SELECT privilege. Because the owner of the base table referenced by the view is the owner of the view, then the principal will be able to use the view, but not the base table.
It would be difficult to provide a simple but effective security model using schemas but without ownership chaining. Take the example of an application that requires a scrolling data-widget for browsing and searching customer data. It specifies the need for direct access to a data table. In the case where the dbo user owned the base tables, but a different user (such as the Customer user) owned the interface objects, i.e. without Ownership-chaining, there would be a problem. Since the appropriate logins for the application have been given permissions or ownership of the CUSTOMER schema, application users would be able to use a view within the CUSTOMER schema. However, the system would want to check permissions when the Customer user selects from the view and then again when the view makes reference to the underlying table, so the second check would fail because the user has no permission on the table. You might then imagine that you have to grant permission on the table as well, but that would defy the purpose of the view; the application logins would have direct access to the table, including columns with sensitive data. The only way to prevent that would be to use column-level permissions, which is far more work to set up and maintain.
Ownership-chaining avoids this by bypassing the second permission check that would otherwise be done when the view is referencing the table. The permissions check is bypassed if the invoked object has the same owner as the object directly calling it. To make this work, the owner of the table (the dbo user, in our example) would also be the owner of the view. This works for a whole chain of routines. A user, for example, with only EXECUTE permission for a stored procedure that uses a view that in turn refers to a table, would be able to access that data even if it had no permissions on the view or table. Permission checks are completely bypassed when the chained object has the same owner as the invoking object. As soon as the ownership changes, permissions are again checked.
Getting around the limitations
Ownership chaining applies only to SELECT, INSERT, DELETE, UPDATE and MERGE DML statements as well as the EXECUTE of stored procedures and functions. Thankfully, it does not apply to DCL, DDL and administrative commands such as TRUNCATE TABLE, CREATE TABLE or BULK INSERT.
It also doesn’t work with routines that use dynamic SQL, for obvious security reasons. To do operations such as dynamic SQL within procedures, functions, and triggers you’d need to use certificates or impersonation, using the WITH EXECUTE AS clause. This EXECUTE AS clause can be used, with all due caution, whenever the caller doesn’t have permissions on the database objects referenced within the dynamic SQL. It does this by switching the execution context to a proxy user. All code, even nested modules, then executes under the security context of the proxy user. The currently executing batch temporarily acquires the rights and permissions of the routine’s owner while executing the code, but not the owner’s identity. As such, the user cannot exploit the privileges for any other purpose other than by altering the code being executed. The execution context reverts back to the original caller only after execution of the procedure finishes, or on a REVERT statement.
If, of course, there is an ill-judged use of the EXECUTE (Execute a character string) or sp_ExecuteSQL that allows injection, then all bets are off. By using WITH EXECUTE AS OWNER in combination with dynamic SQL that allows strings to be injected, or even that is insufficiently tested, you are taking a terrible risk that can end in disaster for you and the company you work for.
Breaking the chain
So far, so good, but how can you prevent a chain getting to places it shouldn’t? If you are mistakenly using the security model of denying access to objects to secure them, while assigning the ownership of all objects to dbo, then it becomes too easy to grant access unintentionally. The problem is that ownership chaining bypasses permission checks entirely, and even takes precedence over DENY ACCESS for all links but the first. If you wish to deny a principal usage of a chain, then you must DENY access at the start of the chain.
The fact that Ownership Chaining ignores the DENY in a link worries some database designers. Although Ownership chaining can’t be turned off, a chain can, if necessary, be broken deliberately by assigning one of the links to a different owner. The easiest way of doing this is to use ALTER AUTHORIZATION ON OBJECT. I prefer to use a ‘dummy’ or ‘login-less’ principal to do this, so that it becomes purely a security object for manipulating ownership. You can create a login-less user by using CREATE USER…WITHOUT LOGIN. These login-less users are strange characters that exist as automata either to provide a convenient owner of a schema and objects used in any given application in parts of a database, break or create chains, or to mark modules with EXECUTE AS and granting only permissions to these principals. This device is rarely necessary in normal circumstances but I mention it purely as a potential way of controlling ownership chaining if your security model dictates it.
Cross-Database Ownership-Chaining
Ownership-chaining can be enabled to work across databases, as cross-database Ownership Chaining, as long as the login querying the first object has access rights on to connect to the other database. Module signatures are generally more effective because they provide provide a tool to impersonate users for executing dynamic SQL without changing the execution context, but they don’t work on views.
Ownership
The owner of an object has all possible permissions on that object and cannot be denied those permissions. Ownership is subtly different from permission. It is confusing because if you have CONTROL permissions, it allows you to perform much the same actions as an object owner. The owner of an object is usually its creator, but a different owner, usually a login-less user, can be specified at creation time using the AUTHORIZATION clause. Any user can only ‘see’ the metadata of the database objects that they own, or have permission to use. A schema can be owned by any principal.
When an object is created without having an owner explicitly assigned with the ALTER AUTHORIZATION phrase, the owner of the object is assigned to be the owner of the schema to which the object belongs. You’ll see this in sys.objects because the principal_id column for such objects is NULL and so when the database engine checks permission and finds a null it falls through to its parent object, the schema. You are perfectly able, however, to change the ownership of individual objects in a schema to principals other than the schema owner, in which case the principal_id column in sys.objects would show the assigned owner.
The owner of a schema will be the default owner of all objects within that schema. If you leave the schema owner blank it defaults to the dbo user. If you assign everything to dbo, then ownership chaining works with few restrictions, but you lose fine control over the process and risk ownership chaining that you never intended!
No matter who actually owns the schema, any principal can be granted permission to execute the routines within the schema by assigning it via GRANT EXECUTE on the schema itself. You can then explicitly deny execute on a particular routine to the principal because the routine is going to be at the head of the chain, and it is only the subsequent links on which a DENY is ignored.
Role Membership
The database role provides a simple means of insulating the database itself from having to hold any information about individual database users or groups. Therefore, it is an essential part of any access-control strategy that seeks to simplify the way permissions are assigned.
A role can inherit the permissions of another role by becoming a member of the latter role. A database user or user-defined database role can be a member of one or more built-in or user-defined roles, and will therefore hold permissions granted to those roles. The ALTER ROLE <role_name> can add or drop a principal (the principal can be a user or a user-defined database role)
If, for example, a role requires access to permissions assigned to other less privileged, roles, they can be given then by making them a member of those roles.
Normally, a database script will contain the logical assignment of roles to collections of objects (schemas) and there will be a separate script that assigns roles to individual or group database users. Where possible, it is best to assign roles to groups so that individual users and processes can be assigned their access rights entirely by the domain administrator via the active directory.
The Default Schema
Since Windows 2012, even a windows group can be assigned a default schema, as well as a role, making the maintenance of security even simpler since the actual membership of the role, in terms of the windows users, can then be done by maintaining the membership to the windows group in the domain’s Active Directory. If the user creates a database object without explicitly specifying the schema, it will be in created in that user’s default schema.
Because schemas always behave like namespaces, those unreferenced objects in the code that a principal executes are assumed to be in that schema. This allows the same unqualified object name to be used in different schema. It is almost never a good idea to use unqualified object names. For a start, they come at a cost because they cannot use cached plans that relate to the schema-qualified object. The Parser takes longer to resolve ambiguous names. However, it has been used to allow different versions of the same interface to be supported at the same time, each in its own schema, so as to support multiple versions of an application without the application being aware of the switch. It can quickly descend into chaos.
The database engine will, if an object is unqualified, assume the principal’s default schema to be the correct schema and check there first. If the user is creating objects, they will be created in that users default schema. There are occasions when unqualified objects can get created in the wrong schema, because a user has no default schema.
Every principal that has access to a database is generally assigned a ‘default schema’. This is usually the DBO schema. Things can go wrong if no default schema is assigned to a user. If that user that has no default schema creates an unqualified object, a new schema will be created and named the same as the user, and the object will be associated with that user schema. This can happen with older versions of SQL Server if windows groups are used as principals. In older versions of SQL Server before 2012 any user that is authenticated by SQL Server as a member of a Windows group will have no default schema associated with them. A windows group can now have a default schema associated with it so the problem should disappear. Unfortunately you can’t assign a default schema to a database role, and so if you need ensure that the principals who are assigned to a role have a default schema, you need to assign a default schema to every user. It is far better, if possible, to enforce a rule of qualifying all object names.
Implementing the Schema-based security model
Before you start building a database, it is important to identify the different applications that will access it. This would include automated services: database-wide ETL processes, for example, that are likely to constitute a separate logical application. From each application, identify each group of users that have separate, or even conflicting, access requirements or ‘user-roles’ (e.g. Web-User, Admin, HR, Finance and marketing) From this, create the database roles. The security plan of the database should mirror the business domain. Start with the least-privileged ‘mere-mortal’ roles and build up to the ‘demi-god’ roles.
Any strategy you use for the access-control system will work by assigning permissions and ownership predominately at the schema-level, and so must ensure that objects are correctly located within the right schema. For ownership chaining, it is occasionally handy to use ‘proxies’, which are ‘wrapper procedures’ for the head of the chain, which is then used to access the required object(s).
Schemas can be used mainly as interface-schemas or namespace-schemas, but are linked to their role by the permissions assigned to the role on the schema. The dbo schema is used only for tables, views and modules that need to be shared between roles, but no permissions are given for the normal user for direct access to them. Normally this will mean in practice that all objects will be in the dbo schema, except for those at the ‘head of the chain’, i.e. the views and routines that are used by the job role within their schema, but are owned by the dbo role.
It is perfectly reasonable for other database objects such as tables and views to be owned by the schema owner, but these will be used for data that is private for that role. In more complex databases, there will be other schemas that represent ‘owners’, in addition to dbo. You’ll meet this in cases where more than one team of database developers are maintaining a single database, or even where groups of users are doing complex reporting. Ownership should mirror the inter-relationships between tables, in that, to assist chaining, tables which are commonly joined should have the same owner. The owner of the base tables should create and own the modules (procedures, views and functions) that are placed in the individual schemas for the various categories of users to access the category of data they need, a separate module for each schema according to access needs. There can be circumstances where several roles will need the same routine in which case it can be appropriate to have a shared schema where all appropriate roles have permission to use the views and procedures unless there is an explicit DENY for that role.
Each schema can become an application-interface, and can be scripted separately so as to place it in source control with the application. It is a good idea to do any automated scripting via a database role that has just the right permission (VIEW DEFINITION) on those schemas that comprise the application-interface. Alternatively, the scripting task can be assigned to the person creating the objects. Normally, the object creation and scripting process is done on a dev server, where permission issues are less critical.
Users are merely given the access they require to database objects by using ‘ownership-chaining’: This allows users to access data without giving them direct permission on the objects themselves. Ownership chaining allows granting permissions to containing objects, such as views or procedures, and protecting the underlying tables. This allows the application users to access the data only by the interface rather than by accessing the base tables.
All objects that are used internally and are not accessed externally are stored in one or more separate schemas that are, by convention, owned by dbo. In fact, there is no technical reason why a base table shouldn’t be in a ‘private’ schema with a more restrictive ownership, but, to keep the permission model simple, it should be placed in the same schema as its dependent tables, and any other tables that need to be joined to it.
All interface routines that are placed in individual interface-schemas are owned by the owner of the tables that are to be accessed, but inherit the permissions of the schema.
This arrangement makes it impossible for a user to directly obtain information to which it is not entitled, even if there is a ‘SQL Injection’ in the application layer, though it is still possible for database developers to accidentally introduce it in the database itself by injudicious use of dynamic SQL.( see the SQL Server Security Workbench)
Load comments