GRANT/REVOKE

 

Since more than one user can access one database, there exists the need to restrict user from using entire database in any way. Setting up access rights can do this. Any right can be granted to or revoked from user with taken of the GRANT and REVOKE statements. Each right can be granted or revoked individually or in a group. Such group of rights is called ROLE and rights can be granted or revoked into in the same way as to the user. To see how the roles are created and deleted see the CREATE and DROP chapters.

 

Syntax of the GRANT and REVOKE statements

GRANT {statement|role_name} ON {object_name} TO {user|role_name_2}[ WITH ADMIN OPTION]
REVOKE {statement|role_name} FROM {user|role_name}
statement A statement to be granted or revoked
role_name The name of a role to be granted or revoked.
object_name The name of an object (table, view or role) on which the rights will be changed
user The name of a user, the rights will be granted to or revoked from. All users are represented by the keyword PUBLIC.
role_name_2 The name of a role, the rights will be granted to or revoked from.
WITH ADMIN OPTION Gives the administration right on object. I.e. a user with this right can grant rights, revoke rights, create role, change role and delete role.

 

Examples