|Schema Clarification (SQL 2005)
||[Mar. 3rd, 2006|03:08 pm]
February 11, 2005
SQL Server 2005 Security - Part 2 Authorization
By Marcin Policht
Following the discussion of new or enhanced authentication-related functionality in SQL Server 2005 Beta 2 in our previous article, we are shifting our focus to authorization features, which determine the level of access rights once the user's logon process is successfully completed. Among topics that will be covered here, are separation of user and schema, modifiable context of module execution, increased permission granularity, and improved catalog security.
User-schema separation - this feature tends to be a bit confusing due to a lack of clarity regarding the concept of "schema" in earlier versions of SQL Server. In essence, schema can be thought of as a container where individual database objects reside. Characteristics of this container satisfy two main rules, defined by ANSI SQL-92 standards. According to the first rule, schema forms the namespace boundaries, which means that no two objects within it can be assigned the same name. This is because for every object, its fully qualified name, consists of four parts - ServerName.DatabaseName.SchemaName.ObjectName - must be unique. The second rule states that all objects within the schema have the same owner.
In SQL Server 2000, the schema name was equivalent to the database user who owned it, which means that the fully qualified name of every object was inherently tied to its owner, which means that you could create two identically named objects in a SQL Server 2000 database, as long as they had different owners. Such dependency had a number of undesired side effects. In particular, deleting a user required either dropping all objects in this user's possession or reassigning their ownership. This, in turn, had the potential of breaking any application that included hard-coded references to fully qualified object names. In SQL Server 2005, the schema is no longer equivalent to the name of the database user, which eliminates this problem. Furthermore, schema can be owned not only by a single user but also database roles or Windows groups. This allows for shared ownership of objects or the creation of application-specific schemas, which simplifies their development and maintenance, since you can refer to their objects using consistent schema names.
Each SQL Server 2005 database contains a number of automatically created schemas. Four of them correspond to pre-defined database users (dbo, guest, INFORMATION_SCHEMA, and sys) and the remaining ones correspond to fixed database roles (db_owner, db_accessadmin, db_securityadmin, db_dlladmin, db_backupoperator, db_datareader, db_datawriter, db_denydatareader, and db_denydatawriter). You can view a list of schemas along with the principals associated with them, by examining the content of the sys.schemas catalog view and cross-referencing it with the sys.database_principals catalog view. In addition, SQL Server 2005 provides the ability to assign a new "default schema" (other than the initial dbo default) on a per database user-basis, using the DEFAULT_SCHEMA option of CREATE USER and ALTER USER T-SQL statements. When attempting to resolve object names specified in a non-fully qualified format, SQL Server 2005 will use the default immediately following a check of the sys schema (this takes priority regardless of what the default has been set to). Assigning a common default schema for a number of database users offers the benefit of a common name resolution mechanism (useful for all users of the same application). Creation of a new schema is accomplished by executing the CREATE SCHEMA T-SQL statement, with the AUTHORIZATION clause designating the user or role as its owner (the ALTER SCHEMA statement can be used to alter the ownership).
Module execution context - to fully understand the benefits of this feature, let's first take a look how the execution context of programmable modules (such as stored procedures or functions) on dependent objects was handled in SQL Server 2000. The ability to launch such modules was (and still is, in SQL Server 2005) determined by their permissions (for example, a user would need permissions to execute a specific stored procedure in order to do so). If their execution required access to another object (a stored procedure that reads or modifies a table), then an evaluation of the permissions necessary to accomplish this would depend on whether both the module and the object have the same owner. This mechanism is called ownership chaining and still applies in SQL Server 2005. If this were the case, then no additional checks would be performed. Otherwise, (whenever the module and the object belong to two different users), explicit permissions to that object would be required as well. However, in either case, all actions performed as part of the module execution would take place in the security context of the user who invoked it. Additionally, ownership chaining has its limitations, since it applies only to a data modification language statement, not to a data definition language statement or dynamic queries included in programmable modules.
SQL Server 2005 provides the ability to alter the execution context with the EXECUTE AS clause available as part of the definition of stored procedures, functions, queues, and triggers. This affects which user account is used to evaluate permissions required by objects referenced by the running module. The caller, however, still requires permissions to invoke the module. Execution context information is maintained in the sys.sql_modules catalog view. SQL Server 2005 Beta 2 allows the following values to be used as part of the EXECUTE AS clause:
o EXECUTE AS CALLER - the default value, matching the SQL Server 2000 behavior described above, where the module executes in the security context of its caller. In this case, if access to any object is needed, it is evaluated based on the standard ownership chaining mechanism. However, you should keep in mind (as we already mentioned in the User-schema separation section) that in SQL Server 2005, objects are not owned directly by users, but instead are part of schemas, which, in turn, have users or roles assigned to them.
This value can be applied to functions, stored procedures, both data modification language and data definition language (introduced in SQL Server 2005) triggers (with database and server scopes), as well as in Service Broker queues.
o EXECUTE AS USER='user_name' (or 'login_name') - assigns the security context of a specific database user for the duration of the module execution. This means that permissions on all referenced objects are evaluated against this user_name, rather than against the original caller (regardless of whether the module and objects have the same ownership or not). This value can be applied to functions, stored procedures, both data modification language and data definition language (introduced in SQL Server 2005) triggers with database scope, as well as queues. 'login_name' (designating a valid SQL Server 2005 login) is applicable to data definition language triggers with server scope. Note that in order for the module to work as intended, its creator needs to either be a member of SysAdmin server role, db_owner database role, or have Impresonate permissions on the user account whose security context is used.
o EXECUTE AS SELF - designates the user who creates (or modifies) the module definition (not necessarily the same as its owner) as the one whose security context will be used to execute it (useful in scenarios where applications create modules for its users on demand). The Principal ID of this user is stored as part of the metadata (in the execute_as_principal_id column in the sys.sql_modules catalog view) and is referenced during module execution. This value can be applied to functions, stored procedures, both data modification language and data definition language triggers (with database and server scopes), as well as in queues.
o EXECUTE AS OWNER - designates the owner of the module (or rather the module's schema) as the one whose security context will be used to execute it (note that in this case, the owner must be an individual user, not a Windows group or database role). As with the previous option, the Principal ID value is stored in the execute_as_principal_id column in the sys.sql_modules catalog view and used subsequently during module execution. This value can be applied to functions, stored procedures, and data modification language triggers with database scope.
More granular permissions - SQL Server 2005 still supports all of the fixed server and database roles introduced in SQL Server 2000, along with their default permissions. However, custom database roles, application roles, as well as server logins and database users can be granted (or denied) more granular permissions on a much wider range of objects. All securable objects (access to which is controllable via permissions) have been grouped into three scopes - server, database, and schema (each of which can also be secured). As in earlier versions of SQL Server, server level permissions are applicable to logins, while database level permissions can be assigned to users, custom database roles, and application roles. Server level permissions are stored in the sys.server_permissions catalog view, and database related ones reside in sys.database_permissions catalog view.
Permissions can be assigned one of three states - Grant, Deny, and Revoke (which removes existing Grant or Deny permissions). Permissions applied on higher levels imply the same states on lower levels, unless Deny (which always takes precedence) is explicitly used. Increased granularity simplifies implementing the rule of the least privilege (by allowing delegating individual tasks without granting membership in privileged server or database roles). Some of the most commonly used permission types present in earlier version of SQL Server, such as EXECUTE, SELECT, or TAKE OWNERSHIP offer new possibilities in their new implementation (since they can be applied on different levels and offer inheritance capabilities). There are also others, newly introduced ones, including, for example:
o CONTROL - functionally equivalent to all permissions granted to the object's owner and inherited by all subentities within its scope,
o ALTER - provides the ability to alter properties of an object. Depending on the scope, you can limit its inheritance to objects of a specific type (for example, its variation in the form ALTER ANY 'object_type' grants permissions to modify every instance of 'object_type' within server or database scope). ALTER TRACE allows running Profiler without membership in SysAdmin fixed server role.
o IMPERSONATE - as mentioned earlier, permits impersonating another user (without requiring SysAdmin or dbo privileges, as was the case in SQL Server 2000),
o VIEW DEFINITION - gives read access to an object's metadata via catalog views.
Improved catalog security - direct access to system tables is no longer allowed - instead, they are exposed through catalog views, encompassing both server and database-wide settings. Their permissions are set on the row level, with minimal access granted to public role. Visibility of objects via catalog views is limited to owners or users with permissions granted on them (as mentioned above, it is also possible to grant read access to catalog views via VIEW DEFINITION permission). In addition to catalog views, you can still use ANSI INFORMATION_SCHEMA views to review their content (although this requires the same types of permissions). Catalog views can be accessed either via graphical interface (they are listed in the System Views subfolder of the Views folder for each database in the SQL Server Management Studio) or by referencing sys.system_views via a T-SQL statement.
In the next installment of our series, we will present the remaining security-related features in SQL Server 2005 Beta 2.