SQL Server 2000 Security – Part 6 – Ownership and Object Permissions

We have described, so far, authorization based on a predefined fixed server
(determining a set of SQL server-wide privileges) and database (applying to
database objects and activities) roles. We have also discussed application
roles, which makes the level of permissions independent of those assigned to a
SQL Server login or a database user account. Now it is time to look into
permissions from the point of view of database objects. There are two main
factors that play a role in determining how access rights to them are evaluated
– their ownership and custom permissions. We will discuss the first one of
these topics in this article and will continue with the other one in the next
installment of this series.

Just as with NTFS files and directories, ownership of a database object
implies full management rights to it, which includes the ability to grant or
deny arbitrarily chosen permissions to any other database user or Windows users
and groups, as well as execute any of relevant Transact-SQL statements (such as
INSERT, UPDATE, DELETE, SELECT, or EXECUTE). In addition, ownership of an
object is granted by default to its creator, although it is possible to
manually alter this behavior or transfer ownership to another user (when it
comes to our file system analogy, this last option became available natively
only recently with the advent of Windows 2003). As far as databases are
concerned, they can be created by members of sysadmin and dbcreator roles.
Their SQL logins are automatically mapped to the dbo user account of the
database, which is also the first member of the db_owner fixed database role.
Changing existing database ownership can be handled with the help of sp_changedbowner
system stored procedure (starting with SQL Server 2000 Service Pack 3, access
to it is limited to members of sysadmin fixed server role). The stored
procedure takes @loginame parameter, referring to the SQL login or a Windows
account of a new database owner (you can obtain a list of valid logins with sp_helplogins
stored procedure). Note that if this account is already mapped to an existing
alias or a user account within this database, you will have to remove this
mapping first. sp_changedbowner also becomes handy in situations where dbo user
has been orphaned, which can happen as the result of deleting a corresponding
Windows account or transfer of a database from one Windows domain to another,
without a trust relationship between them.

There is also a similar sp_changeobjectowner system stored procedure, which
is intended for changing ownership of database objects. The stored procedure
takes two parameters: @objectname indicating an existing table, view,
user-defined function or stored procedure in the current database, and @newowner,
which is a valid database user, database role, or Windows user or group. Keep
in mind that sp_changeobjectowner removes all existing permissions from the
object, so you might want to store them first, in the form of a script, so they
can be applied afterwards. This can be done from the SQL Enterprise Manager
interface, by selecting the All tasks option from the database context
sensitive menu, then choosing Generate SQL Script… from the secondary menu,
picking the object to be scripted, and checking on the Script object-level
permissions checkbox on the Options tab. Execution of this stored procedure is
limited to members of sysadmin server fixed role as well as db_owner, db_dlladmin,
and db_securityadmin fixed database roles.

It is generally recommended to use the dbo user account for object
ownership. This simplifies references to it (since qualifying it with the
owner’s name is no longer required – although doing this is considered as a
good practice) and simplifies handling of ownership chaining (discussed next).
If this is not feasible in your environment, use database fixed roles or define
your own custom ones, and assign ownership to them. Try to avoid granting
ownership to database users, otherwise you will have to deal with complications
in case these users need to be dropped.

Ownership not only affects privileges of those to which it is assigned, but
also has potential impact on how permissions for other database users are
determined. This phenomenon is called ownership chaining and takes place in
situations where an access to objects in a database is granted via a view, a
stored procedure or a user-defined function. Since this involves two or more
objects (for example, in cases where a stored procedure references another
stored procedure or a view), the way effective permissions are evaluated
depends on whether all these objects are owned by the same user. If this is not
the case, ownership chain is considered to be broken and permission on each of
the objects constituting its links needs to be analyzed separately (i.e. for a
user to access the top level object, appropriate permissions need to be granted
explicitly for every object where the chain is broken). This not only has some
impact on performance but, more importantly, also complicates management and
troubleshooting of permissions. If ownership chain is intact (i.e. each of the objects
in the chain has the same owner), then all that is required is a permission
check on the object directly accessed by the user. Another benefit of such
configuration is the ability to prevent direct access to each subsequent object
in the chain, forcing clients to use views, stored procedures or user-defined
functions instead.

Ownership chaining, however, also has its drawbacks. In particular, it can
introduce a potential vulnerability, especially when used across databases. You
might be surprised to find out that this is possible, since the set of users is
separate for each database, but in this case, what matters are login accounts
to which users from various databases are mapped. This means that if objects in
two databases are owned by users mapped to the same login stored in sysxlogins
table in the master database, then the ownership chain is considered unbroken.
Based on the same principle, if the objects are owned by users corresponding to
distinct logins, then the ownership chain is broken and permissions on each
object need to be evaluated separately. Another case where ownership chain
might play a role is a situation where ownership of database objects is
assigned directly to Windows users and groups, which creates the possibility of
the same Windows account owning objects in two separate databases. As long as
the cross-database ownership chaining is enabled, this permits controlling
permissions on objects residing in one database via a view, a stored procedure,
or a user-defined function located in another.

Starting with SQL Server 2000 Service Pack 3, this feature is configurable
and turned off by default (it is turned on in the earlier versions of the
product). The first time you are prompted to confirm its settings is during
installation of SQL Server 2000 Service Pack 3 (by marking appropriately
"Enable cross-database ownership chaining for all databases" option).
You can also change it at any point afterwards by running sp_configure stored
procedure using the following syntax:

EXEC sp_configure 'Cross DB Ownership Chaining', '1'; RECONFIGURE

Alternatively, you can use the "Allow cross-database
ownership chaining" option on the Security tab of the SQL Server
Properties dialog box in the SQL Server Enterprise Manager. Either one of these
actions effectively enables cross-database ownership chaining for all databases
in the current instance of the SQL Server 2000. If the server-wide
cross-database ownership chaining is disabled, you have an option of enabling
it for individual databases with sp_dboption stored procedure by executing the
following statement:

EXEC sp_dboption DBName, 'db chaining', 'true'

where DBName is the name of a target database (you
need to run this statement for both databases between which chaining is to be
allowed). You can also use the same stored procedure to determine which
databases had the default modified.

Note, however that you should avoid using cross database ownership chaining
whenever possible as it creates potential security vulnerability. For example,
since members of fixed database roles dbo_ddladmin and db_owner can create objects
owned by other users (or simply transfer their ownership), they can use this
ability as a back door, granting access to objects owned by the same users in
another database. The same applies to any logins with CREATE DATABASE
permissions, who automatically (by the virtue of being mapped to the dbo user)
become members of the db_owner fixed database role.

This concludes discussion on object ownership. In our next article, we will
cover management of permissions on database objects and operations.

»


See All Articles by Columnist
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.

Latest Articles