Authorization

Forms of authorization on parts of the database

• Read - allows reading, but not modification of data

• Insert - allows insertion of new data, but not modification of existing data

• Update - allows modification, but not deletion of data

• Delete - allows deletion of data

Forms of authorization to modify the database schema

• Index - allows creation and deletion of indices

• Resources - allows creation of new relations

• Alteration - allows addition or deletion of attributes in a relation

• Drop - allows deletion of relations

Granting Privileges

The grant statement is used to confer authorization:

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

<user list> is:

• a user-id

• public, which allows all valid users the privilege granted

• a role:

  create role instructor1

  grant select on relation to instructor1

Granting a privilege on a view does not imply granting any privileges on the underlying relations!

The grantor of the privilege must already hold the privilege on the specified item (or be the database administrator).

The following privileges can be granted:

• select: allows read access to relation, or the ability to query using the view

• insert: the ability to insert tuples

• update: the ability to update using the SQL update statement

• delete: the ability to delete tuples

• all privileges: used as a short form for all the allowable privileges

Revoking Authorization

The revoke statement is used to revoke authorization.

revoke <privilege list> on <relation name or view name> from <user list>

<privilege-list> may be all to revoke all privileges the revokee may hold

If <user-list> includes public, all users lose the privilege except those having them granted explicitly

If the same privilege was granted twice to the same user by different grantees, the user may retain the privilege after the revocation

All privileges that depend on the privilege being revoked are also revoked