SQL Series...42

Granting Privileges to Users

The DBA, by virtue of his or her position, has all privileges on all objects in the database. After all, the owner of an object has all privileges with respect to that object — and the database itself is an object. No one else has any privileges with respect to any object, unless someone who already has those privileges (and the authority to pass them on) specifically grants the privi-leges. You grant privileges to someone by using the GRANT statement, which has the following syntax:

GRANT privilege-list

ON object

TO user-list

[WITH GRANT OPTION] ;

In this statement, privilege-list is defined as follows:

privilege [, privilege] ...

or

ALL PRIVILEGES

Here privilege is defined as follows:

SELECT

| DELETE

| INSERT [(column-name [, column-name]...)]

| UPDATE [(column-name [, column-name]...)]

| REFERENCES [(column-name [, column-name]...)]

| USAGE

| UNDER

| TRIGGER

| EXECUTE

In the original statement, object is defined as follows:

[ TABLE ] <table name> | DOMAIN <domain name>

| COLLATION <collation name>

| CHARACTER SET <character set name> | TRANSLATION <transliteration name>

| TYPE <schema-resolved user-defined type name> | SEQUENCE <sequence generator name>

| <specific routine designator>

And user-list in the statement is defined as follows:

login-ID [, login-ID]...

| PUBLIC

The preceding syntax considers a view to be a table. The SELECT, DELETE, INSERT, UPDATE, TRIGGER, and REFERENCES privileges apply to tables and views only. The USAGE privilege applies to domains, character sets, collations, and translations. The UNDER privilege applies only to types, and the EXECUTE privilege applies only to routines. The following sections give examples of the various ways you can use the GRANT statement and the results of those uses.

Roles

A user name is one type of authorization identifier but not the only one. It identifies a person (or a program) who is authorized to perform one or more functions on a database. In a large organization with many users, granting privileges to every individual employee can be tedious and time-consuming. SQL:2003 addresses this problem by introducing the notion of roles.

A role, identified by a role name, is a set of zero or more privileges that can be granted to multiple people who all require the same level of access to the database. For example, everyone who performs the role SecurityGuard has the same privileges. These privileges are different from those granted to the people who have the role SalesClerk.

As always, not every feature mentioned in the SQL:2003 specification is avail-able in every implementation. Check your DBMS documentation before you try to use roles.

You can create roles by using syntax similar to the following:

CREATE ROLE SalesClerk ;

After you’ve created a role, you can assign people to the role with the GRANT statement, similar to the following:

GRANT SalesClerk to Becky ;

You can grant privileges to a role in exactly the same way that you grant priv-ileges to users, with one exception: It won’t argue or complain.

Inserting data

To grant a role the privilege of adding data to a table, follow this example:

 

GRANT INSERT

ON CUSTOMER

TO SalesClerk ;

This privilege enables any clerk in the sales department to add new customer records to the CUSTOMER table.

Looking at data

To enable people to view the data in a table, use the following example:

GRANT SELECT

ON PRODUCT

TO PUBLIC ;

This privilege enables anyone with access to the system (PUBLIC) to view the PRODUCT table’s contents.

This statement can be dangerous. Columns in the PRODUCT table may con-tain information that not everyone should see, such as CostOfGoods. To provide access to most information while withholding access to sensitive information, define a view on the table that doesn’t include the sensitive columns. Then grant SELECT privileges on the view rather than the underly-ing table. The following example shows the syntax for this procedure:

CREATE VIEW MERCHANDISE AS

SELECT Model, ProdName, ProdDesc, ListPrice

FROM PRODUCT ;

GRANT SELECT

ON MERCHANDISE

TO PUBLIC ;

Using the MERCHANDISE view, the public doesn’t get to see the PRODUCT table’s CostOfGoods column or any other column except the four listed in the CREATE VIEW statement.

Modifying table data

In any active organization, table data changes over time. You need to grant to some people the right and power to make changes and to prevent everyone else from doing so. To grant change privileges, follow this example:

GRANT UPDATE (BonusPct)

ON BONUSRATE

TO SalesMgr ;

The sales manager can adjust the bonus rate that salespeople receive for sales (the BonusPct column), based on changes in market conditions. The sales manager can’t, however, modify the values in the MinAmount and

MaxAmount columns that define the ranges for each step in the bonus sched-ule. To enable updates to all columns, you must specify either all column names or no column names, as shown in the following example:

GRANT UPDATE

ON BONUSRATE

TO VPSales ;

Deleting obsolete rows from a table

Customers go out of business or stop buying for some other reason. Employees quit, retire, are laid off, or die. Products become obsolete. Life goes on, and things that you tracked in the past may no longer be of interest to you. Someone needs to remove obsolete records from your tables. You want to carefully control who can remove which records. Regulating such privileges is another job for the GRANT statement, as shown in the following example:

GRANT DELETE

ON EMPLOYEE

TO PersonnelMgr ;

The personnel manager can remove records from the EMPLOYEE table. So can the DBA and the EMPLOYEE table owner (who’s probably also the DBA). No one else can remove personnel records (unless another GRANT statement gives that person the power to do so).

Referencing related tables

If one table includes a second table’s primary key as a foreign key, information in the second table becomes available to users of the first table. This situation potentially creates a dangerous “back door” through which unauthorized users can extract confidential information. In such a case, a user doesn’t need access rights to a table to discover something about its contents. If the user has access rights to a table that references the target table, those rights often enable him to access the target table as well.

Suppose, for example, that the table LAYOFF_LIST contains the names of the employees who will be laid off next month. Only authorized management has SELECT access to the table. An unauthorized employee, however, deduces that the table’s primary key is EmpID. The employee then creates a new table SNOOP, which has EmpID as a foreign key, enabling him to sneak a peek at LAYOFF_LIST. It’s high on the list of techniques every system administrator should know.)

CREATE TABLE SNOOP

(EmpID INTEGER REFERENCES LAYOFF_LIST) ;

Now all that the employee needs to do is try to INSERT rows corresponding to all employee ID numbers into SNOOP. The table accepts the inserts for only the employees on the layoff list. All rejected inserts are for employees not on the list.

SQL:2003 prevents this kind of security breach by requiring that a privileged user explicitly grant any reference rights to other users, as shown in the fol-lowing example:

GRANT REFERENCES (EmpID)

ON LAYOFF_LIST

TO PERSONNEL_CLERK ;

Using domains, character sets, collations, and translations

Domains, character sets, collations, and translations also have an effect on security issues. Created domains, in particular, must be watched closely to avoid their use as a way to undermine your security measures.

You can define a domain that encompasses a set of columns. In doing so, you want all these columns to have the same type and to share the same con-straints. The columns you create in your CREATE DOMAIN inherit the type and constraints of the domain. You can override these characteristics for specific columns, if you want, but domains provide a convenient way to apply numerous characteristics to multiple columns with a single declaration.

Domains come in handy if you have multiple tables that contain columns with similar characteristics. Your business database, for example, may con-sist of several tables, each of which contains a Price column that should have a type of DECIMAL(10,2) and values that are nonnegative and no greater than 10,000. Before you create the tables that hold these columns, create a domain that specifies the columns’ characteristics, as does the fol-lowing example:

CREATE DOMAIN PriceTypeDomain DECIMAL (10,2)

CHECK (Price >= 0 AND Price <= 10000) ;

Perhaps you identify your products in multiple tables by ProductCode, which is always of type CHAR (5), with a first character of X, C, or H and a last character of either 9 or 0. You can create a domain for these columns, too, as in the following example:

CREATE DOMAIN ProductCodeDomain CHAR (5)

CHECK (SUBSTR (VALUE, 1,1) IN (‘X’, ‘C’, ‘H’)

AND SUBSTR (VALUE, 5, 1) IN (9, 0) ) ;

With the domains in place, you can now proceed to create tables, as follows:

CREATE TABLE PRODUCT

(ProductCode ProductCodeDomain,

ProductName CHAR (30),

Price PriceTypeDomain) ;

In the table definition, instead of giving the data type for ProductCode and Price, specify the appropriate domain. This action gives those columns the correct type and also applies the constraints you specify in your CREATE DOMAIN statements.

Certain security implications go with the use of domains. What if someone wants to use the domains you create — can this cause problems? Yes. What if someone creates a table with a column that has a domain of PriceTypeDomain? That person can assign progressively larger values to that column until it rejects a value. By doing so, the person can determine the upper bound on PriceType that you specify in the CHECK clause of your CREATE DOMAIN statement. If you consider that upper bound private informa-tion, you don’t want to enable others to use the PriceType domain. To pro-tect you in situations such as this example, SQL enables only those to whom the domain owner explicitly grants permission to use domains. Thus only the domain owner (as well as the DBA) can grant such permission. You can grant permission by using a statement such as the one shown in the following example:

GRANT USAGE ON DOMAIN PriceType TO SalesMgr ;

Different security problems may arise if you DROP domains. Tables that con-tain columns that you define in terms of a domain cause problems if you try to DROP the domain. You may need to DROP all such tables first. Or you may find yourself unable to DROP the domain. How a domain DROP is handled may vary from one implementation to another. SQL Server may do it one way, whereas Oracle does it another way. At any rate, you may want to restrict who can DROP domains. The same applies to character sets, collations, and translations.

Causing SQL statements to be executed

Sometimes the execution of one SQL statement triggers the execution of another SQL statement, or even a block of statements. SQL:2003 supports triggers. A trigger specifies a trigger event, a trigger action time, and one or more triggered actions. The trigger event causes the trigger to execute or “fire.” The trigger action time determines when the triggered action occurs, either just before or just after the trigger event. The triggered action is the execution of one or more SQL statements. If more than one SQL statement is triggered, the statements must all be contained within a BEGIN ATOMIC...

END structure. The trigger event can be an INSERT, UPDATE, or DELETE statement.

For example, you can use a trigger to execute a statement that checks the validity of a new value before an UPDATE is allowed. If the new value is found to be invalid, the update can be aborted.

A user or role must have the TRIGGER privilege in order to create a trigger. An example might be:

CREATE TRIGGER CustomerDelete BEFORE DELETE

ON CUSTOMER FOR EACH ROW

WHEN State = NY

INSERT INTO CUSTLOG VALUES (‘deleted a NY customer’) :

Whenever a New York customer is deleted from the CUSTOMERS table, an entry in the log table CUSTLOG will record the deletion.