skip to main content
SQL statements : DCL statements : GRANT
 

GRANT

This statement grants system privileges and roles to users and roles.
To support GRANT processing, the IP GETSUPPORT operation must return TRUE for IP_SUPPORT_GRANT.
A privilege is a right to execute a particular type of SQL statement, or a right to access another user’s object. A role groups several privileges and roles, so that they can be granted and revoked privileges simultaneously from users.
Two kinds of privileges are supported:
• System privileges determine what kinds of operations can be done – for CREATE TABLE, CREATE USER, and so on. See Table 18 for a description of supported system privileges.
• Object privileges determine what kinds of operations can be done on a specified object, for example, SELECT on a TABLE, and so on. See Table 19 for a description of supported object privileges.
 
Table 18: System privileges 
Privilege name
Operations permitted
INDEX
 
CREATE ANY INDEX
Create an index (in any schema) on any table.
ALTER ANY INDEX
Alter any index in the database.
DROP ANY INDEX
Drop any index in the database.
PRIVILEGE
 
GRANT ANY PRIVILEGE
Grant any system privilege (not object privilege)
PROCEDURE
 
CREATE PROCEDURE
Create procedure in own schema.
CREATE ANY PROCEDURE
Create procedure in any schema.
DROP ANY PROCEDURE
Drop any procedure in any schema.
EXECUTE ANY PROCEDURE
Execute any procedure in any schema.
ROLE
 
GRANT ANY ROLE
Grant any role in database.
TABLE
 
CREATE TABLE
Create tables in own schema.
CREATE ANY TABLE
Create a table in any schema.
ALTER ANY TABLE
Alter any table in any schema.
DROP ANY TABLE
Drop any table in any schema.
SELECT ANY TABLE
Query any object in any schema.
INSERT ANY TABLE
Insert rows into any object in any schema.
UPDATE ANY TABLE
Update rows into any object in any schema.
DELETE ANY TABLE
Delete rows into any object in any schema and truncate any object.
VIEW
When validating permissions for accessing views, only permissions on the View, and not the base tables, are checked.
CREATE VIEW
Create a view in own schema
CREATE ANY VIEW
Create a view in any schema
DROP ANY VIEW
Drop any view in any schema
USER
 
CREATE USER
Create a user in own schema
CREATE ANY USER
Create a user in any schema
DROP ANY USER
Drop any user in any schema
ROLE
 
CREATE ROLE
Create a role in own schema
CREATE ANY ROLE
Create a role in any schema
DROP ANY ROLE
Drop any role in any schema
Each type of object has different privileges associated with it. The Xs in the following table indicate the privilege/object combinations supported.
 
Table 19: Object privileges 
Privilege Name
Table
View
Procedure
ALTER
X
 
 
DELETE
X
 
 
EXECUTE
 
 
X
INDEX
X
 
 
INSERT
X
 
 
SELECT
X
X
 
UPDATE
X
 
 

Syntax 

GRANT {object_priv | ALL [PRIVILEGES],...} ON {[qualifier.][owner.]object}
TO {user | role | PUBLIC,...}