Structured Query Language/Managing Rights

For multiuser systems like DBMSs, it is necessary to grant and revoke rights for manipulating its objects. The GRANT command defines which user can manipulate (create, read, change, drop, ...) which object (tables, views, indexes, sequences, triggers, ...).

The REVOKE statement deprives the granted rights.

The example statement grants SELECT and INSERT on table person to the user hibernate. The second statement removes the granted rights.

Privileges
Privileges are actions that users can perform. The SQL standard supports only a limited list of privileges, whereas real implementations offer a great bunch of different privileges. The list consists of: SELECT, INSERT, UPDATE, DELETE, CREATE , DROP , EXECUTE, ... .

Object Types
The list of object types, to which privileges may be granted, is short in the SQL standard and long for real implementations. It consists of tables, views, indexes, sequences, triggers, procedures, ... .

Roles / Public
If there is a great number of users connecting to the DBMS, it is helpful to group users with identical rights to a role and grant privileges not to the individual users but the role. To do so, the role must be created by a CREATE ROLE statement. Afterward, users are joined with this role.

Instead of individual usernames, the keyword PUBLIC denotes all known users.

Grant Option
If a DBA wants to delegate the managing of rights to special users, he can grant privileges to them and extend the statement with the phrase 'WITH GRANT OPTION'. This enables the users to grant the received privileges to any other user.