Integrity and Security:Authorization in SQL.

Authorization in SQL

The SQL language offers a fairly powerful mechanism for defining authorizations. We describe these mechanisms, as well as their limitations, in this section.

Privileges in SQL

The SQL standard includes the privileges delete, insert, select, and update. The select privilege corresponds to the read privilege. SQL also includes a references privilege that permits a user/role to declare foreign keys when creating relations. If the relation to be created includes a foreign key that references attributes of another relation, the user/role must have been granted references privilege on those attributes. The reason that the references privilege is a useful feature is somewhat subtle; we explain the reason later in this section.

The SQL data-definition language includes commands to grant and revoke privileges. The grant statement is used to confer authorization. The basic form of this statement is:

grant <privilege list> on <relation name or view name> to <user/role list>

The privilege list allows the granting of several privileges in one command.

The following grant statement grants users U1, U2, and U3 select authorization on the account relation:

grant select on account to U1, U2, U3

The update authorization may be given either on all attributes of the relation or on only some. If update authorization is included in a grant statement, the list of at- tributes on which update authorization is to be granted optionally appears in paren- theses immediately after the update keyword. If the list of attributes is omitted, the update privilege will be granted on all attributes of the relation.

This grant statement gives users U1, U2, and U3 update authorization on the amount

attribute of the loan relation:

grant update (amount) on loan to U1, U2, U3

The insert privilege may also specify a list of attributes; any inserts to the relation must specify only these attributes, and the system either gives each of the remaining attributes default values (if a default is defined for the attribute) or sets them to null.

The SQL references privilege is granted on specific attributes in a manner like that for the update privilege. The following grant statement allows user U1 to create relations that reference the key branch-name of the branch relation as a foreign key:

grant references (branch-name) on branch to U1

Initially, it may appear that there is no reason ever to prevent users from creating foreign keys referencing another relation. However, recall from Section 6.2 that foreign- key constraints restrict deletion and update operations on the referenced relation. In the preceding example, if U1 creates a foreign key in a relation r referencing the branch-name attribute of the branch relation, and then inserts a tuple into r pertaining to the Perryridge branch, it is no longer possible to delete the Perryridge branch from the branch relation without also modifying relation r. Thus, the definition of a foreign key by U1 restricts future activity by other users; therefore, there is a need for the references privilege.

The privilege all privileges can be used as a short form for all the allowable privileges. Similarly, the user name public refers to all current and future users of the system. SQL also includes a usage privilege that authorizes a user to use a specified

domain (recall that a domain corresponds to the programming-language notion of a type, and may be user defined).

Roles

Roles can be created in SQL:1999 as follows

create role teller

Roles can then be granted privileges just as the users can, as illustrated in this statement:

grant select on account

to teller

Roles can be asigned to the users, as well as to some other roles, as these statements show.

grant teller to john

create role manager

grant teller to manager

grant manager to mary

Thus the privileges of a user or a role consist of

• All privileges directly granted to the user/role

• All privileges granted to roles that have been granted to the user/role

Note that there can be a chain of roles; for example, the role employee may be granted to all tellers. In turn the role teller is granted to all managers. Thus, the manager role inherits all privileges granted to the roles employee and to teller in addition to privileges granted directly to manager.

The Privilege to Grant Privileges

By default, a user/role that is granted a privilege is not authorized to grant that privilege to another user/role. If we wish to grant a privilege and to allow the recipient to pass the privilege on to other users, we append the with grant option clause to the appropriate grant command. For example, if we wish to allow U1 the select privilege on branch and allow U1 to grant this privilege to others, we write

Thus, to revoke the privileges that we granted previously, we write

As we saw in Section 6.5.4, the revocation of a privilege from a user/role may cause other users/roles also to lose that privilege. This behavior is called cascading of the revoke. In most database systems, cascading is the default behavior; the keyword cascade can thus be omitted, as we have done in the preceding examples. The revoke statement may alternatively specify restrict:

revoke select on branch from U1, U2, U3 restrict

In this case, the system returns an error if there are any cascading revokes, and does not carry out the revoke action. The following revoke statement revokes only the grant option, rather than the actual select privilege:

revoke grant option for select on branch from U1

Other Features

The creator of an object (relation/view/role) gets all privileges on the object, including the privilege to grant privileges to others.

The SQL standard specifies a primitive authorization mechanism for the database schema: Only the owner of the schema can carry out any modification to the schema. Thus, schema modifications — such as creating or deleting relations, adding or drop- ping attributes of relations, and adding or dropping indices — may be executed by only the owner of the schema. Several database implementations have more powerful authorization mechanisms for database schemas, similar to those discussed earlier, but these mechanisms are nonstandard.

Limitations of SQL Authorization

The current SQL standards for authorization have some shortcomings. For instance, suppose you want all students to be able to see their own grades, but not the grades of anyone else. Authorization must then be at the level of individual tuples, which is not possible in the SQL standards for authorization.

Furthermore, with the growth in the Web, database accesses come primarily from Web application servers. The end users may not have individual user identifiers on the database, and indeed there may only be a single user identifier in the database corresponding to all users of an application server.

The task of authorization then falls on the application server; the entire authorization scheme of SQL is bypassed. The benefit is that fine-grained authorizations, such as those to individual tuples, can be implemented by the application. The problems are these:

• The code for checking authorization becomes intermixed with the rest of the application code.

• Implementing authorization through application code, rather than specifying it declaratively in SQL, makes it hard to ensure the absence of loopholes. Be- cause of an oversight, one of the application programs may not check for authorization, allowing unauthorized users access to confidential data. Verifying that all application programs make all required authorization checks involves reading through all the application server code, a formidable task in a large system.