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:- 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. - 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. - 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. - 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.
- EXECUTE
-
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:- CONTROL
– functionally equivalent to all permissions granted to the object’s owner and
inherited by all subentities within its scope, - 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. - IMPERSONATE
– as mentioned earlier, permits impersonating another user (without requiring SysAdmin or dbo privileges, as
was the case in SQL Server 2000), - VIEW
DEFINITION – gives read access to an object’s metadata via catalog views.
- CONTROL
-
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.
