The GRANT command confers database, table, and other privileges to database user authorization IDs. All relevant objects must exist in the database. Granted privileges are recorded in the appropriate privileges tables in the SYSTEM schema for the database. Specific privileges can be revoked subsequent to their granting, using the REVOKE statement.
To grant a privilege on a database object, the user authorization must own the object, possess the privilege WITH GRANT OPTION on the object, or possess DBA privileges.
This specifies an optional list of columns in the table on which SELECT, INSERT, UPDATE, or REFERENCES is being granted. The column list, if specified, must be enclosed in parentheses, with each column name separated from the next by a comma. If the column list is omitted, all columns in the target table are updatable by the grantee.
These specify the name of an existing table/view, domain, or schema, respectively. If the table, view, or domain does not belong to the current schema, preface the object name with the schema name and a period ( . ), that is, schema-name.object-name. If privileges are being granted on a schema or domain, the object type keyword (SCHEMA or DOMAIN) must be included before the object name. If no object type is specified, the target object is assumed to be a table or view, and privileges are granted on the table/view having the specified name.
The user name argument identifies one or more user authorization IDs (separated by commas) being granted the privilege(s) specified. The PUBLIC keyword can be included in place of (or along with) an authorization ID (or a list of authorization IDs): this will grant the specified privileges to all current and future authorization IDs.
This statement format is used to grant privileges on specified database objects. One or more of the privilege option keywords may be specified after GRANT (separated by commas), allowing multiple privileges on a single database object to be granted in one statement.
The privileges resulting from the execution of a GRANT command statement are recorded as one or more individual grants in the privileges tables in the SYSTEM schema for the database. Each individual grant involves the granting of one privilege by a grantor to one grantee, except in the case of ALL PRIVILEGES, which is a shorthand designation for multiple privileges on a schema or table. A grantee can be any authorization ID (as recorded in the system catalog for that database), or the PUBLIC designation.
The same privilege can be granted to a single grantee by several different grantor authorization IDs. The grantee retains the privilege as long as one (or more) of these grants remains recorded in the appropriate privilege table in the SYSTEM schema. Grants (individual or groups) can be removed from the system tables by executing the appropriate REVOKE commands.
This gives the user all privileges over all objects in the database; therefore, care should be taken when granting DBA privileges. Only a user with DBA privileges can execute the following SQL commands:
This allows the recipient to create, alter, manipulate, drop, and grant/revoke privileges on objects in a schema that the user does not own. The OWNER privilege does not confer the ability to GRANT schema privileges. This privilege is only valid with schemas; attempting to grant the OWNER privilege on a table or domain will produce an error message.
This allows the user to retrieve data from the specified table, or all tables in the specified schema. The privilege can be granted on specific columns within the table by listing their names (within parentheses, and separated by commas) after the SELECT keyword. If a column list is not included, the privilege applies to all columns in the table.
This allows the user to insert data into the specified table, or all tables in the specified schema. The privilege can be granted on specific columns within the table by listing their names (within parentheses, and separated by commas) after the INSERT keyword. If a column list is not included, the privilege applies to all columns in the table.
This allows the user to update data in the specified table, or all tables in the specified schema. The privilege can be granted on specific columns within the table by listing their names (within parentheses, and separated by commas) after the UPDATE keyword. If a column list is not included, the privilege applies to all columns in the table.
This allows the user to use the specified column(s) in the table as the parent column(s) in a FOREIGN KEY constraint. Note that this is not equivalent to SELECT privileges on these columns.
This allows the user to delete data from the specified table, or all tables in the specified schema.
This allows the user to specify domain name as the domain argument in a column definition.
The following table lists the individual privileges granted on a database object with ALL PRIVILEGES:
Table Schema Domain SELECT - INSERT - UPDATE - DELETE - REFERENCES - USAGE -
Note: When a privilege is granted on a schema, that privilege applies to all objects belonging to the schema. For instance, granting SELECT on a schema allows the grantee to SELECT from any table in the schema.
WITH GRANT OPTION clause
If the WITH GRANT OPTION clause is included, the grantee can, in turn, grant the privileges specified in the GRANT command to other valid authorization IDs.
The WITH GRANT OPTION clause cannot be included in a GRANT statement if either the privilege being granted is OWNER, or the recipient of the privilege is PUBLIC.
To grant privileges to all users in the database, specify PUBLIC as the grantee. Users who have not been specifically granted any privileges have only those privileges granted to PUBLIC. When a privilege is granted to PUBLIC, the system continues to maintain the list of user names that have been specifically granted the privilege. Privileges granted to PUBLIC are automatically granted to all new user authorizations subsequently created in the database.
The following example grants all authorization IDs the ability to update two specific columns (“col1” and “col3”) in a table called “my_table”:
GRANT UPDATE (col1, col3) ON TABLE my_table TO PUBLIC;
In this case, users without DBA privileges, who possess no other privileges on my_table, cannot update any other columns.
The following example grants DBA privileges to an authorization ID called “marketing”:
GRANT DBA TO marketing;
The following example grants to authorization IDs “clive”, “bertrand”, and “nigel” the ability to specify domain “d_char” as a domain argument in a column definition (and also to GRANT USAGE on the domain to other users):
GRANT USAGE ON DOMAIN d_char
TO clive, bertrand, nigel WITH GRANT OPTION;