Granting and Revoking Privileges
To grant or revoke a privilege using one of the SQL GRANT or REVOKE statements, the user must have the following permissions for the GRANT/REVOKE statement to succeed:
- Superuser or privilege WITH GRANT OPTION
- USAGE privilege on the schema
- Appropriate privileges on the object
The syntax for granting and revoking privileges is different for each database object, such as schema, database, table, view, sequence, procedure, function, resource pool, and so on.
Normally, a superuser first creates a user and then uses GRANT syntax to define the user's privileges or roles or both. For example, the following series of statements creates user Carol and grants Carol access to the apps
database in the PUBLIC schema and also lets Carol grant SELECT privileges to other users on the applog
table:
=> CREATE USER Carol; => GRANT USAGE ON SCHEMA PUBLIC to Carol; => GRANT ALL ON DATABASE apps TO Carol; => GRANT SELECT ON applog TO Carol WITH GRANT OPTION;
In This Section