User Authorization in SQL

• The authorization mechanism in SQL is similar to the traditional file permissions mechanism in the UNIX file system.

• An authorization ID is the user name, except that PUBLIC is a special authorized person, meaning everyone.

• The table-related privileges are

  • SELECT
  • INSERT
  • DELETE
  • UPDATE

• The SELECT, INSERT, and UPDATE privileges may have an associated list of attributes.

User Authorization in SQL (cont)

• The non table-related privileges are

  • REFERENCES—the right to refer to a relation in an integrity constraint, may have an attached list of attributes

  • USAGE—right to use schemas other than relations and assertions, e.g., domains, character sets, collations, grant statements, and stored modules in one’s own declarations

  • TRIGGER—right to define triggers on a relation

  • EXECUTE—right to execute a piece of code such as a stored module

  • UNDER—right to create subtypes

Example

• To be able to execute the SQL statement

  INSERT INTO Studio(name)
  SELECT DISTINCT studioName
  FROM Movies
  WHERE studioName NOT IN
      (SELECT name
       FROM Studio);

  requires the minimum necessary privileges of

  • SELECT(studioName) on the Movies table
  • SELECT(name) on the Studio table
  • INSERT(name) on the Studio table

Creating Privileges

• There are three occasions when an SQL ownership is established.

1. Schema creation time

   CREATE SCHEMA MovieSchema
       CREATE Domain ...
       CREATE TABLE MovieStar ...
       CREATE VIEW MovieProd ...
       CREATE ASSERTION RichPres ...
       CREATE CHARACTER SET ...
       CREATE COLLATION ...
       CREATE TRIGGER ...
       CREATE TYPE ...
       CREATE FUNCTION ...
       ...

2. Database server connection time

   CONNECT to mysql-server AS conn AUTHORIZATION bob;

3. Module creation time

   CREATE PROCEDURE myProc() AUTHORIZATION sam
       ...

How privileges are checked

• Every SQL operation involves 2 parties

  1. the database elements to be operated upon
  2. the agent causing the operation

• The current authorization ID is defined to be

  1. the module authorization ID if the module to execute has one, or
  2. the session authorization ID if not.

• The SQL operation is allowed only if the current authorization ID possess all the privileges needed to carry out the operation on the database elements involved.

Granting privileges

• One user possessing a privilege on an SQL object may grant that same privilege to another user.

• The ability to further grant a granted privilege is also a privilege that can be granted!

• So we can write a grant statement like this

  GRANT <privilege list> ON <database element> TO <user list>

  or like this

  GRANT <privilege list> ON <database element> TO <user list>
  WITH GRANT OPTION

• E.g.

  GRANT SELECT, INSERT(name) ON Studio TO kirk, picard
      WITH GRANT OPTION;
  GRANT SELECT, INSERT ON Studio TO sisko;
  GRANT SELECT ON Movies TO sisko;

Grant Diagram

Revoking privileges

• Granted privileges can be revoked at any time using the syntax

  REVOKE <privilege list> ON <database element> FROM <user list>

• This revoke statement can end with either of these two options

  1. CASCADE—revoke any privileges granted only because of the revoked privileges.
  2. RESTRICT—revoke statement cannot be executed if the cascading rule would result in the revoking of any privileges due to the revoked privileges having been passed on to others.