Teach Yourself Microsoft SQL Server 6.5 in 21 Days

Previous chapterNext chapterContents


Day 7

User Permissions


The skills you have learned up to now have allowed you to install SQL Server, create devices and databases, and create tables in your database. You also have learned how to create SQL Server logins and how to assign user names in a database.

Today you learn skills that enable you to assign permissions to users in the database. The permissions enable users to do what they need to, but not perform restricted operations.

Why Use Permissions?

Until now you have done all your work in SQL Server using the sa (system administrator) login. The sa login has no restrictions on what it can do in SQL Server, which is not only convenient but necessary for many of the administrative tasks you must perform in SQL Server. Ordinary users, however, should not be connecting to SQL Server using the sa login because they would have all permissions to the SQL Server--enough to delete all the databases and shut down the server!

By designing and implementing a good security plan for SQL Server you can eliminate many problems before they happen, instead of spending your time trying to figure out how your data (or SQL Server) became damaged.

Introduction to Database Permissions

In order to understand permissions you must understand that permissions within a database are assigned to user names and that each database contains unique and separate security from other databases. For example, Sue might have all permissions to the library database (she is a senior librarian), she might have SELECT (read) permissions on tables in the purchasing database (she can see books on order but only her supervisors can order new books), and she might have no permissions in the accounting database (only the accountants have permissions in the accounting database).

In this example, Sue must connect to SQL Server with her SQL Server login ID of Sue. Her individual user IDs in each database are used to determine her security rights in both the library and purchasing databases.


NOTE: Security within a database is tracked by the sysprotects system table, which exists in each database. That's why security is database specific. This is also true for the master database.

Levels of Permissions in SQL Server

Four levels of permissions functionality exist within SQL Server. The following are the different levels:

These four levels of functionality are discussed in detail in the following sections.

SA Permissions

A person logs onto SQL Server using the sa login under two conditions.

Many operating systems have a privileged user who operates outside the bounds of normal security. Netware 3.x has a user called SUPERVISOR, UNIX has a user called ROOT, Windows NT has a user called Administrator, and SQL Server has SA. The SA is considered a superuser because he has the ability to perform any action within SQL Server.

The system administrator (SA) is given complete rights over SQL Server so that he can perform repairs, reconfigure the server, and troubleshoot SQL Server problems. Some tasks only the SA can perform. For that reason, the SA account should be closely guarded and always password protected.

The following are permissions that only an SA has (these cannot be assigned to others):

The SA can create databases but can also grant this permission to others, although it is not recommended for most situations.


TIP: If a permission affects more than one database, it is probably an SA-only permission.

DBO Permissions

The database owner (DBO) of a database has all rights within that particular database. Because security in SQL Server is individually determined for each database, the DBO of one database might be an ordinary user in another database and have no permissions at all in a third database.

You are considered the DBO of a database in any of the following four situations:

Permissions Unique to the DBO

When a user owns a database, he can do anything he wants inside that database. Additionally, he can assign some permissions to other users inside his database. The following are the DBO-only rights that can't be granted to others:

Permissions of the DBO That Can Be Delegated

Many of the permissions that the DBO has can be delegated to other users. Those permissions include:

DBOO Permissions

A user who creates a database object is the owner (DBOO) of that object. A user who owns a database object is automatically granted all permissions to that object. The appropriate permissions for each type of object are given (or granted, to use SQL terms) to the owner. For example, when a user creates a table, she will be granted the SELECT, UPDATE, INSERT, DELETE, REFERENCES, and DUMP permissions on that table. Ownership of an object cannot be changed except through direct manipulation of the system tables (which is not suggested for beginning users). A user cannot be dropped if he still owns objects in a database. To be able to drop a user who owns objects, all the objects the user owns must also be dropped.

Each DBOO will have unique rights for objects he or she owns. The following list of rights cannot be granted to others:

User Permissions

Most people will fall into the category of an ordinary database user. The database user has no inherent rights or permissions other than those given to the public group. All rights must be explicitly granted or assigned to the user, the user's group (if he belongs to one), or the public group.

Permissions granted to users can be categorized into statement and object permissions. Statement permissions enable users to create new databases, create new objects within an existing database, or back up the database or transaction log. Statement permissions enable you to run particular commands instead of operate on particular objects. Object permissions enable users to perform actions on individual objects. For example, a user might be able to read (select) data from a table, execute a stored procedure, or modify data in a table or view (with INSERT, UPDATE, and DELETE permissions). Details for both types of permissions, as well as how to grant them, are discussed next.

Statement Permissions

Statement permissions enable a user or a group to perform various tasks such as creating databases, creating objects, or backing up data. Statement permissions do not apply to a particular object because they are designed to control who can create and back up objects overall.

You must carefully consider granting the user permission to create an object. When a user creates an object he becomes the owner of that object and has all the permissions associated with database object ownership. Later, you will see that having objects owned by different owners can create some difficult permission situations. Statement permissions should be granted only when explicitly needed. The haphazard granting of statement permissions can leave a database with unnecessary, and even unusable, objects.


WARNING: Broken ownerships can become very complex very quickly. If you let users create objects, your database security will soon resemble a bowl of spaghetti. For this reason all objects should be created by the DBO or users who are aliased to the DBO. Note that this applies to production servers and might not apply to development servers.

You are able to grant permissions to individual users, specific groups, or the PUBLIC group. The following statement permissions can be granted or revoked:

These permissions can be granted individually or all at once (using the keyword ALL). Each of these commands has implications that you must consider before you use them.

The CREATE DATABASE Permission

The CREATE DATABASE permission enables users to create their own databases and thus become the DBOs of those databases. Database ownership can later be changed with the sp_changedbowner stored procedure. Only the SA is allowed to grant a user the CREATE DATABASE permissions. Because permissions are always granted to users (and never to logins), you must grant this permission in the master database.

Assigning this permission is usually a bad idea because the SA cannot control the way databases are assigned to devices, and can lose control over disk space and device usage on his server.

The CREATE TABLE, VIEW, PROCEDURE, DEFAULT, and RULE Permissions

CREATE TABLE, VIEW, PROCEDURE, DEFAULT, and RULE permissions enable users to create objects in the databases where the permissions were given. Programmers will frequently be given these permissions to let them create the resources they need in a database during development. An object is owned by the user who created the object, and object ownership cannot be changed without direct manipulation of the sysobjects system table.


WARNING: All create permissions include the right to drop any objects created by a user. This can cause serious problems in your database, because users can drop objects they are finished with, only to find that they weren't the only ones using those objects.

The DUMP DATABASE and TRANSACTION Permissions

DUMP DATABASE and DUMP TRANSACTION permissions can be assigned to individual users. Although backing up the database and transaction logs usually is an automated process carried out by the server without user intervention, some environments may require that individual users be given the ability to perform these backups.

The ability to assign this permission to users is helpful when the SA or DBO needs someone to help with database administration. This permission probably shouldn't be given on databases on which security is a major concern because the backup file potentially could be restored on another system, allowing unknown users to access the data at their leisure.

Granting Statement Permissions

You can use Transact-SQL or SQL Enterprise Manager to grant and revoke statement permissions.

Granting Statement Permissions Using Transact-SQL

Transact-SQL commands used to grant and revoke statement permissions can be executed from the SQL Query Tool, ISQL, or ISQL/w. The syntax for granting or revoking permissions is as follows, where ALL stands for all possible permissions:

GRANT {ALL | statement_list} TO {PUBLIC | name_list}
REVOKE {ALL | statement_list} FROM {PUBLIC | name_list}

Note that granting the CREATE DATABASE permission works only if granted in the master database.

Now work through a few examples to see how to implement these commands.

To grant a user named Joe permission to create a view in a database, you could run the following:

GRANT CREATE VIEW TO JOE

To revoke the permission to create views and tables from Joe and Mary you could run the following:

REVOKE CREATE TABLE, CREATE VIEW FROM MARY, JOE

To grant Joe all permissions in a database you would run the following:

GRANT ALL TO JOE


NOTE: If the GRANT ALL command is executed in the master database the user specified would be given all permissions in that database. If it is executed in any other database the user would be given all permissions except CREATE DATABASE, because that particular permission can be granted only in the master database.

Granting Statement Permissions Using Enterprise Manager

Enterprise Manager provides a graphical interface for implementing statement permissions. To edit statement permissions in Enterprise Manager, open the databases folder for your SQL Server. Double-click the master database (or right-click the master database and select Edit). Select the Permissions tab to view the statement permissions. You should see something similar to Figure 7.1.

Figure 7.1. The Statement Permissions tab.

As you grant and revoke permissions, the boxes contain one of five indicators:

Note that if you look at statement permissions on any database other than the master database the CREATE DB permission is not present, because that right can be assigned only from the master database (see Figure 7.2).

Figure 7.2. Editing statement permissions from the pubs database using Enterprise Manager.

You can click the box where you want to change permissions until the appropriate symbol appears. Click OK to apply the changes.


WARNING: The ability to create objects in a database is a serious matter. Do not grant permissions to do so unless it is necessary for the user to complete her job.

Object Permissions

Object permissions enable a user or a group to perform actions against a particular object in a database. The permissions apply only to the specific object named when granting the permission, and not to all the objects contained in the entire database. Object permissions allow the DBO (or SA) to give individual user accounts the rights to run specific T-SQL statements on an object. Object permissions are the most common types of permissions granted.

Here is a list of the object permissions:



NOTE: The REFERENCE permission (abbreviated DRI in Enterprise Manager) enables users (or applications) to compare a value against those in another table without being able to actually see the data in that other table. For example, this permission can be used to allow an application to find a matching social security number without giving enough rights for the application to browse and see all the social security numbers.

Granting Object Permissions Using Transact-SQL

The syntax to grant or revoke object permissions can be executed from the SQL Query Tool, ISQL/w or ISQL.

GRANT {ALL [PRIVILEGES][column_list] | permission_list
[column_list]}
ON {table_name [(column_list)] | view_name [(column_list)]
| stored_procedure_name}
TO {PUBLIC | name_list }[WITH GRANT OPTION]
REVOKE [GRANT OPTION FOR]{ALL [PRIVILEGES] | permission_list }
[(column_list)]
ON { table_name [(column_list)]| view_name [(column_list)]
| stored_procedure_name}
FROM {PUBLIC | name_list}[CASCADE]

At first glance the syntax seems intimidating, but with practice it becomes relatively easy. Look through the following examples to see how to implement these commands. To grant a user named Joe permission to add data to MyTable, run the following:

GRANT INSERT ON MYTABLE TO JOE

To revoke permission to add or remove data from MyTable for Joe and Mary, run the following:

REVOKE INSERT, DELETE ON MYTBALE FROM JOE, MARY

To allow Joe to grant the insert permission to others, you would run the following:

GRANT INSERT ON MYTABLE TO JOE WITH GRANT OPTION

Note the use of WITH GRANT OPTION. This allows Joe to grant the INSERT permission to others. This option should be used only if you want the user specified to be able to grant others the same permissions they received.

To revoke from Joe the ability to grant the INSERT permission, run the following:

REVOKE GRANT OPTION FOR INSERT ON MYTABLE FROM JOE

To revoke the INSERT option from Joe and revoke all the INSERT permissions Joe has given out to others, use the CASCADE option:

REVOKE INSERT ON MYTABLE FROM JOE CASCADE

Granting Object Permissions Using Enterprise Manager

Object permissions are part of system administration. The granting and revoking of these permissions is a very common event that you will be performing on a day-to-day basis.

Enterprise Manager provides a fast, easy, and visual way to control object permissions. Permissions can be viewed based on objects or on users. The ability to view the information in two different ways can make tracking down errors much easier.

To edit object permissions in Enterprise Manager, follow these steps:

1. In the Server Manager window click the plus sign next to your server to drill down to the databases folder. Expand (drill down) the databases folder and select a database (pubs in this example).

2. Select Object | Permissions from the Object menu.

3. Select the By User tab to view permissions by user or group (see Figure 7.3).

Figure 7.3. You can edit permissions based on users and groups in Enterprise Manager.

You can use the object filters to reduce the amount of information on the screen and display only particular objects. If you would like to see an individual object and who has access to it, click the By Object tab, shown in Figure 7.4.

Figure 7.4. You can edit permissions based on objects in Enterprise Manager.

Click the appropriate boxes to grant or revoke object permissions. When you are finished setting your permissions, click Set to apply the changes.

Effective Rights

In the last two sections you learned how to implement statement and object permissions. You are now able to give permissions to groups and users. Membership in a group gives users the permissions assigned to the group, as well as any permissions they might have been assigned to their own user account.

SQL Server implements group and user permissions in a simple but effective way. Users and groups are covered in detail on Day 6, "SQL Server Login and User Security," but just to refresh your memory remember the following:

When determining whether a database user has permissions for an object or statement, consider the hierarchy of effective permissions:


NOTE: SQL Server 6.0 behaves differently when determining effective rights. If you are using SQL Server 6.0 please refer to the 6.0 documentation for details on determining effective permissions.

Now look at an example.

In Figure 7.5 Mary is a member of the group Accounts and has the following permissions assigned to the Authors table. What are Mary's effective permissions? SELECT: There is no assignment directly to Mary, so look at the group assignment. Accounts also does not have the SELECT permission, but the Public group does, so Mary has SELECT permission on this table.

INSERT: There is no assignment directly to Mary, so look at the group assignment. Accounts has INSERT permission, so Mary has INSERT permission on this table.

UPDATE: Mary has a direct assignment, so she has UPDATE permission on this table.

DELETE: There is no assignment directly to Mary, so look at the group assignment. The Accounts group has been granted DELETE, so Mary has DELETE permissions on this table. REFERENCE: Although REFERENCE (or DRI in Enterprise Manager) has been revoked from the Accounts group, Mary has a direct assignment, so she has DRI permission on this table.

Figure 7.5. Examining rights using the SQL Enterprise Manager.

Note that you can assign column-level permissions by selecting the Column Level Permissions box, which then lists all columns of that particular table (see Figure 7.6).

Figure 7.6. Assigning column-level permissions.


TIP: Views should be strongly considered as an alternative to assigning column-level permissions when providing column-level security.

Permissions on Views

Views, and how security affects them, must be looked at in a bit more detail. Note that creating and editing views is covered on Day 15, "Views, Stored Procedures, and Triggers." For now, think of a view as a stored query that appears to be a table to users.

In order to restrict certain users from accessing particular columns, you can create a view that refers to only selected columns of a table. You can then assign permissions to the view for those users, and they won't have rights to see the underlying table. They will only be able to view data from the table through the view. Figure 7.3 illustrates this point. In Figure 7.3 there is a table called Employees, with columns for first name, last name, address, and salary. If Mary were assigned the task of updating everyone's address, she would not have (and shouldn't have) permissions to the salary column. You have two choices on how to do this: You can assign Mary permissions on a column-by-column basis (an awkward solution) or create a view based on only those columns you want her to be able to see and update (View_1 as shown in Figure 7.7).

Figure 7.7. Using a view to provide column-level security.


NOTE: Although a view can reference two or more tables, you can't update data through a view that references more than one table because that violates ANSI specifications for relational database systems. See Day 15 for more information about views.

Ownership Chains

Every object in SQL Server will have an owner assigned to it. Although the owner of a database can be changed, the owner of an object within a database can't be changed without direct manipulation of the system tables. Although it is best if only the DBO (including aliases to the DBO and the SA) own all the database objects, if you permit it ordinary users can own objects in your database. The ramifications of DBO ownership versus user ownership are discussed later in this chapter.

New Term: For example, a user can create a view based on another user's tables and views. Users can also create stored procedures that use another user's tables, views, and stored procedures. These types of objects are called dependent objects. In a large database, there could be a long series of dependencies and owners. This series is the chain of ownership.

Consider this example. The DBO owns a table and gives Mary the right to select from his table. Mary creates a view based on the original table and gives Paul permission to select from her view. When Paul attempts to select information from Mary's view, the data is actually coming from the original (owned by the DBO) table. Did the DBO ever give Paul permission to select from the original table? Should Paul be allowed to view this data?

SQL Server handles these type of cases by looking at the ownership chain of objects and where permissions have been assigned. There are two distinct types of ownership chains: the single-owner chain and the broken ownership chain, where more than one user owns objects.

Single-Owner Chain

A single-owner chain is created when the same user owns all the dependent objects within a chain. In this case, SQL Server will check permissions only on the first object in the chain being accessed, but will not check permissions on objects later in the chain.

For example, in Figure 7.8 the DBO owns all the objects in the chain. The DBO creates View1 based on the two tables (which are also owned by the DBO), and then creates a second view (View2) based on View1 (which is based on the tables). If the DBO gives Melissa permission to SELECT from View2, permissions would be checked only once when Melissa attempted the SELECT on View2. SQL doesn't bother to check objects at a higher level, because the owner (DBO in this example) is the same. SQL Server assumes that if the owner is the same, the original owner would have granted permissions, had SQL Server required it.

Figure 7.8. Example of a single-user ownership chain.

Broken Chain of Ownership

When an object is dependent on other objects that are owned by different users, you have a broken ownership chain. Permissions are checked on the first object and every object where there is a change of ownership. In Figure 7.9, Melissa has created a view (View2) based on the DBO's View1. If Melissa gives Scott permission to SELECT from her View2, permissions for Scott will be checked on Melissa's View2, and then again on the DBO's View1. If Scott does not have permissions on the DBO's View1, then he cannot use the SELECT command.

Figure 7.9. Example of a broken ownership chain.



WARNING: Broken ownerships can become very complex very quickly. If you let users create objects your database security will soon resemble a bowl of spaghetti. For this reason all objects should be created by the DBO or users who are aliased to the DBO.

Ownership Chains, Stored Procedures, and Views

Because unbroken ownership chains cause SQL Server to check permissions on only the first object in a chain of objects, stored procedures can be executed by users that have the EXECUTE permission assigned to them, even though the user might not have explicit permissions to the data that the stored procedure manipulates.

Views are similar, in that users will be able to use a view (when given appropriate permissions) even if they don't have explicit permissions to the underlying tables, as long as the ownership chain remains unbroken.

Designing a Permission Strategy

Up until now you have focused on how to implement security. The next section discusses why and when to implement a permissions scenario and presents a list of Dos and Don'ts.

Best Practices

SQL Server allows very flexible security, which can present a problem when you are trying to find the best way to secure your system. In general, you should follow several rules as well as a general guideline for assigning permissions. However, as usual in the computer industry, your situation might be different enough that the rules won't apply exactly as written.

If a person is in charge of the entire SQL Server he will need to connect as the SA. If a user is in charge of a single database he should be assigned as the DBO of that particular database. If a user doesn't need special permissions in order to do her job, she should be treated as a normal database user and get her permissions from either the public group, a group of which she is a member, or from permissions that have been directly assigned to her.

When assigning permissions, maintaining and documenting your security implementation is easiest if you do the following:

Dos and Don'ts

Here are some general guidelines in the form of a Do/Don't list that will help you better manage your security. Most of these guidelines deal with the fact that users should have only those permissions they really need.


DO grant users the permissions they need to do their jobs. For example, if all the users must see the data, be sure to grant SELECT permissions, probably to the Public group.

DON'T grant permissions unless necessary. Users like to have permissions even when they don't need them. For example, if a certain user needs only SELECT permissions, grant only that permission, even though the user might request all permissions.

DO keep track of the permissions you have granted. Keep a log of what is done to your SQL Server. Another option is to generate scripts that document all the objects and permissions contained in the database (see Day 5, "Creating Tables," and the section "Generating Scripts for Security," later in this chapter).

DON'T grant users all permissions to fix a problem. Take the time to find out exactly which permission they really need, and grant only those permissions. For example, solving issues due to lack of permissions can easily be accomplished by making the user an alias to the DBO. Although this will fix the original security problem, it introduces new, more critical problems in that users have too many permissions and can easily damage the database.

DO assign a user to be the DBO of a database if she is responsible for that database. If other users need permissions associated with the DBO, you will need to assign other users as aliases to the DBO, because only one person can be assigned as the DBO.

DON'T allow ordinary users to create databases or create objects within databases. If you allow users to make databases and objects, you not only lose control over what SQL Server contains and where databases and objects reside, you also must deal with broken ownership chains. All objects within a database should be created by the DBO (or aliases to the DBO) and be documented.


Another problem about granting users excessive permissions is that it is often difficult to take those excessive permissions away later. In a perfect world, all users could log in as SA, and they would make changes only to the database that they are supposed to. Of course we don't live in a perfect world, so having everyone with SA permissions is just asking for trouble sooner or later (usually sooner). Believe it or not, there are actually some systems out there in which everyone does indeed connect with the SA account.

In cases in which excessive permissions exist, everything might be fine until one of the following occurs:

A wise administrator will guard against these situations through the judicious use of permissions.

Generating Scripts for Security

SQL Server has the capability to "reverse engineer" database objects and security, and can generate a script that can be run at a later time to reconstruct objects and security. To access the scripting function from Enterprise Manager, highlight a database and choose Object/Generate SQL Scripts (see Figure 7.10).

Figure 7.10. Selecting to generate SQL scripts.

When you are in the Generate Script screen, you must choose which kind of script is generated. Note that the default is to generate a script that will drop and create objects, although no objects are chosen by default. In order to generate scripts that will re-create the security of a database (including re-creating SQL logins, database users, and groups), you must select All Objects, deselect Object Drop, and select All Users/Groups, All Logins, and Permissions under the Security section (see Figure 7.11).

Figure 7.11. Selecting options to generate a script to re-create the security of a database.

In order to do the actual scripting, you can select the Script button, which will prompt you for a filename for the script, or you can select Preview, which will cause the scripts to be generated. If you chose Preview, you can save the script after you look at it by choosing Save As (see Figure 7.12).

Figure 7.12. A preview of a script.

Note that you can select different options from the main Generate SQL Scripts screen, depending on your desired outcome.

Summary

SQL Server provides a secure environment through the use of permissions. A user connected to SQL can have certain rights based on the type of user they connect as. Security in SQL Server is a true hierarchy; the SA account has all permissions, the DBO account has all permissions in his particular database, and the DBOO has all permissions to the individual object he owns. Database users have no inherent permissions, only those they have been granted.

The combination of statement and object permissions enables a system administrator to control exactly what occurs on the SQL Server. Statement permissions enable you to create objects and back up your databases and transaction logs. Object permissions enable you to specify who can do what with the data contained in the objects. Common object permissions are SELECT, for reading data; INSERT, for adding data; and UPDATE, for editing data.

Permissions can be assigned or revoked using Transact-SQL commands or by using Enterprise Manager.

Chains of ownership demonstrate some of the challenges that can occur by allowing different users to create objects within a database.

Permissions must be implemented correctly and thoroughly to ensure the security and usability of your data.

Objects and permissions can be reverse engineered by SQL Server so that scripts can be saved that can easily and quickly reconstruct the security of a database.

Q&A

Q This seems like a lot of work. Are permissions really that important?

A
It depends. The more secure you want to keep your database, the more control and maintenance you must perform using the permission hierarchy.

Q Do I need to put a password on my SA account? It doesn't have one by default.


A
Yes, the SA account should be assigned a password, and that password should be closely guarded because, as you have seen in this chapter, the SA account has all permissions in SQL Server, including enough permissions to delete databases and even deinstall SQL Server itself.

Workshop

This Workshop is designed to quiz you on the materials from this lesson. The quiz is a good indicator of your level of understanding when working with permissions. If you do not do well on the quiz, you might want to re-read the chapter or play with the exercises a bit more. The exercises are designed to give you some hands-on experience with using the permissions scheme in SQL Server.

Quiz

1. How would you grant a user Mary permissions to read data from the table MyTable?

2. Mary created a table called MaryTable and gave Joe SELECT permissions on it. Joe created a view called JoeView. Joe wants Paul to have permissions to SELECT from his view. What requirements must be met?

3. Who can create a device?

4. Joe is granted permission to SELECT on MYTABLE. Public is revoked SELECT on MYTABLE. What are the effective permissions?

5. What is the preferred way to prevent broken chains of ownership?

6. You are the owner of a database. You want all users to be able to query the table MYTABLE. What command would you execute?

7. You execute the command GRANT ALL TO JOE in a user database. What permissions does Joe have?

Exercises

Do the examples from today's lesson using both Transact-SQL and Enterprise Manager.

Use the users and groups created on Day 6. Design and give them permissions in the pubs database.

By doing these examples you should not only get better at assigning permissions, you should begin to visualize (especially using Enterprise Manager) how security for groups and users works.


Previous chapterNext chapterContents


Macmillan Computer Publishing USA

© Copyright, Macmillan Computer Publishing. All rights reserved.