Data Management

SQL Server: Grant access but maintain security

Using SQL Server requires interaction with objects inside the database. And interaction requires that users be granted permission to access objects. This installment of our series on SQL Server security provides an overview of how user access is granted.


By Susan Sales Harkins and Mike Gunderloy

Whenever users try to do something in SQL Server, they're interacting with one or more objects. Their permissions on those objects determine whether they succeed. In this third installment of our series on SQL Server security, you'll learn about objects, ownership, and permissions.

Builder.com's series on SQL Server security
Our goal is to give you the tools and the confidence you need to set up SQL Server securely so that your data is protected from accidental or deliberate theft and corruption. Our series started with some of the basic decisions and techniques for making sure that you have a secure platform to build upon. Throughout the series, we'll concentrate on SQL Server 2000, which introduces some new security features to help protect your data. In case you missed the previous articles in this series, you can read them here:

Objects
An object in SQL Server is any component of a database. Many types of objects can be stored in a SQL Server database, including:
  • Tables
  • Views
  • Stored procedures
  • Indexes
  • Triggers
  • Views
  • Keys
  • Constraints
  • Defaults
  • Rules
  • User-defined data types

Objects are identified by their name, which includes the server name, the database name, the owner name (you'll learn about owners in a bit), and the object name. These four parts, known as identifiers, make up a fully qualified object name using the following form:
[[[server.][database].][ownername].objectname]

As in other syntax diagrams, square brackets indicate optional elements. The first three components, more specifically known as qualifiers, aren't required in all instances. For example, MyServer.MyDatabase.Charlie.Customers is a fully qualified name that identifies the Customers table created by the user Charlie in the database MyDatabase on the server named MyServer. Fortunately, you don't have to include each component every time you reference an object. Other valid references for this same name include the following:
MyServer.MyDatabase..Customers
MyServer..Charlie.Customers
MyServer...Customers
MyDatabase.Charlie.Customers
MyDatabase..Customers
Charlie.Customers
Customers

Notice that each reference includes a period character (.) wherever one of the qualifiers is missing.

The entire list of references has only one common element—objectname. That's because the objectname is the only required component in each reference. The only other important rule to remember at this point is that each object must have a unique, fully qualified name. In other words, you can have more than one table named Customers, but only one table named Customers belonging to Charlie in MyDatabase on MyServer.

You might be wondering how to determine the right qualifying name for each object. Most of the time, you can let SQL Server do that for you by not explicitly specifying the three qualifiers:
  • Serveralways defaults to the local server if none is explicitly specified.
  • Databasealways defaults to the current database if none is explicitly specified.
  • Ownernamealways defaults to the current username in the current database (as determined by the login of the current connection), if none is explicitly specified.

When actually using object names, you'll find most documentation recommends that you use the fully qualified name to avoid confusion and mistakes. If you fail to specify a qualifier, SQL Server will try to determine one for you using the same rules mentioned earlier for naming qualifiers when there's no explicit reference—with one addition. If you fail to specify ownername, SQL Server will look for the object associated with the current connection's login. If it doesn't find one, SQL Server will look for the specified object owned by the database owner—the dbo user. So if you're working with remote tables which, by definition, are not part of the current database, you must use the fully qualified name.

When creating code, explicitly including all qualifiers is certainly a good practice because it's also self-documenting. However, when working in the current database with well-known objects, continually typing in all those qualifiers could quickly become tedious, and it's perhaps unnecessary. If you make one typo in a work session that involves the use of many names entered many times, the small amount of time it takes to fix the one typo will be far less than the time spent repeatedly typing qualifiers.

If the current database contains more than one object with the same name, you will have to qualify the object to the smaller common unit. For example, if you have two tables named Customers, but Charlie owns one and Joe owns another, you will have to qualify your reference with at least the ownername—Joe.Customers or Charlie.Customers. In the end, just use your own judgment and rely on your skills to determine what works best for you.

Ownership
Every database and database object has an owner. The database object owner is the user who created the object. This could be the dbo (database owner) account or a particular user with permission to create objects within the database. The owner is responsible for controlling access to the object via permissions. (More about that in a moment.)

A database object owner has special permissions for the object:
  • Owners can execute any Transact-SQL statement related to the object (INSERT, UPDATE, DELETE, SELECT, EXECUTE, and so on).
  • Owners can manage permissions for the object—give permissions for certain actions against the object to other valid users.

When a database object owner is removed from the database, you must transfer ownership to another valid user or drop the object. A database can't contain unowned objects. To transfer ownership, use the system stored procedure sp_changeobjectowner with this syntax:
sp_changeobjectowner [@objectname =] 'object', [@newowner =] 'owner'

where object identifies the database object, which should include the current database object owner, and owner is the valid user (the security account username) to which you're transferring ownership. Any permissions granted to other users by the database object owner must be reapplied when ownership is transferred. You transfer only the ownership; all other permissions are dropped. For the record, only members of sysadmin, db_owner, db_ddladmin, or db_securityadmin can execute sp_changeobjectowner.

As mentioned, the database itself also has an owner. Occasionally, you'll need to reassign ownership to it, just as you do database objects. When this is the case, use the system stored procedure sp_changedbowner with this syntax:
sp_changedbowner [@loginame =] 'login' [, [@map =] remapaliasflag

where login identifies the login to which you're transferring ownership and remapaliasflag is a Boolean value indicating whether the existing aliases to the old dbo should be mapped to the new owner or dropped. You can't transfer ownership to a login account that already has access to the database through an existing alias or user security account. First, you must drop the alias user account within the database, and then you can transfer ownership. After running the stored procedure, the new owner is known as the dbo user for that database. Only members of sysadmin can execute this stored procedure.

Permissions
Ownership is important in SQL Server because the owner controls all access to the object. When an object is created, only the owner can perform actions with that object. For other users to access an object, the owner must specifically grant them permission.

Specifically, a table or view's database owner can grant INSERT, UPDATE, DELETE, SELECT, and REFERENCES permissions or simply grant ALL permissions. A valid user must have the specific permission to take the corresponding action against the table or view. Assigning REFERENCES permission allows the owner of another table to use columns in the table to which they've been granted that permission as the target of a REFERENCES FOREIGN KEY constraint with his or her table. However, that person won't be allowed to change the structure of the table they've been granted the permission for.

The owner of a stored procedure can grant EXECUTE permissions to other valid users to allow them to execute that stored procedure. The dbo must grant permissions to valid users to run specific Data Definition Language (DDL) statements, such as CREATE TABLE and CREATE VIEW. In addition to the object owner, members of some fixed roles can manage permissions:
  • Members of the sysadmin role can manage permissions on any object on the server.
  • Members of the db_owner or db_securityadmin roles can manage permissions on any object in the database.

Permissions do more than grant access to objects. They can also deny or revoke access. To manage permissions, use the following SQL statements:
  • GRANT creates an entry in the security system that allows a user in the current database to work with its data or execute specific Transact-SQL statements.
  • DENY creates an entry in the security system that denies a specific permission to a security account in the current database. It also prevents the security account from inheriting the permission through its group or role memberships.
  • REVOKE removes a previously granted or denied permission from a user in the current database.

A number of permissions are available for the above management statements:
  • CREATE DATABASE
  • CREATE DEFAULT
  • CREATE FUNCTION
  • CREATE PROCEDURE
  • CREATE RULE
  • CREATE TABLE
  • CREATE VIEW
  • BACKUP DATABASE
  • BACKUP LOG

Ownership chains
The ownership chain of a view or stored procedure consists of all the users who own objects on which the view or stored procedure depends. For example, if View2 selects data from View1, and View1 selects data from Table1 and Table2, the ownership chain of View2 includes the owners of View1, Table1, and Table2, as well as the owner of View2 itself.

If all objects in the ownership chain are owned by the same user, SQL Server checks permissions of only the object that the user is accessing. In this case, if all four objects are owned by the same user, and you try to open View2, SQL Server checks the permissions only on View2 when deciding whether you should be allowed to view the data.

The situation is different if some of the objects involved have different owners. This is referred to as a broken ownership chain. For example, suppose Joe owns View2, View1, and Table1, but Mary owns Table 2. With this broken ownership chain, SQL Server checks permissions on every object whose next lower link is owned by a different user—in this case, View2 and View1.

Using views for partitioned security
If you keep ownership chains unbroken, you can use views to grant someone permission to look at only part of a table you own. For instance, suppose you own a table named Employees that contains some public information (such as employee names) and some confidential information (such as employee salaries). In this case, you could create a view named PublicEmployees:
CREATE VIEW PublicEmployees
AS
SELECT EmployeeName FROM Employees


If you grant another user SELECT permission on the PublicEmployees view, he or she can view employee names even if you do not grant any permissions on the underlying Employees table. Because the ownership chain is unbroken, SQL Server doesn't even look at the permissions on the Employees table.

Finding the balance
To be useful, a database must be accessed—but how that access is granted and to whom is a design aspect that must be carefully planned. Through various levels of object and ownership permissions, you can grant access and maintain security—a delicate balancing act for sure, but one that must be perfected.

Coming up next
In the next article of this series, we'll step back and look at some big-picture application issues. You'll learn about application roles and encryption in SQL Server—both parts of the complete SQL Server security system.

Mike Gunderloy and Susan Sales Harkins are coauthors of Que's Absolute Beginner's Guide to Microsoft Access 2002.Their latest collaboration, Absolute Beginner's Guide to Microsoft Access 2003, also published by Que, is due to be released this summer.

Editor's Picks

Free Newsletters, In your Inbox