SQL Server 2005 Security – Part 2 Authorization

Following the discussion of new or enhanced authentication-related
functionality in SQL Server 2005 Beta 2 in our previous
, 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

    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:

      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

      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.

      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.

      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

      – 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.

      – 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.
  • 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.


See All Articles by Columnist
Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.
Get the Free Newsletter!
Subscribe to Cloud Insider for top news, trends & analysis
This email address is invalid.
Get the Free Newsletter!
Subscribe to Cloud Insider for top news, trends & analysis
This email address is invalid.

Latest Articles