SQL Notes: Security

Databases contain objects (eg, tables or views) that have privileges granted to certain users. The GRANT and REVOKE commands give/remove privileges for users to do certain operations with database objects.

Users and Owners

The exact nature of user ids (authorization ids) depends on the particular system. Typically there are both individual and group ids.

The user that creates a table is the owner. The table owner has full privileges, and can assign privileges to other users. When a user creates a view, the privilege of the owner depends on the privileges of the referenced tables.

Privileges

Below are the four most common privileges. SQL2 add REFERENCES, and some databases have additional privileges such as INDEX and ALTER.

GRANT / REVOKE Commands

SQL2 also includes the REFERENCES privilege and USAGE of objects such as domains, etc. Some systems may also allowing lists of tables.
GRANT privileges,... ON table TO users,... [WITH GRANT OPTION]
privileges::= SELECT
   | DELETE
   | INSERT (column,...)
   | UPDATE (column,...)
   | ALL PRIVILEGES
users::= user-name | PUBLIC
REVOKE [GRANT OPTION FOR] privileges,... ON table FROM user,... revoke-action
grant-priv::= GRANT OPTION FOR
revoke-action::= CASCADE | RESTRICT
The CASCADE | RESTRICT option concerns the effect of removing GRANT privileges from users. If the CASCADE option is given, then privileges are also revoked from those users who were granted privileges by the user whose GRANT privilege is being revoked. If RESTRICT is specified, the REVOKE command will fail if others had been granted privileges by the user whose grant privilege is being revoked.