Grants various privileges to the specified users of the database.
There are two forms of the GRANT statement:
Grant database-wide privileges, such as system administration (DBA), general creation (RESOURCE), audit administration (AUDIT_ADMIN), audit archive (AUDIT_ARCHIVE), or audit insert (AUDIT_INSERT).
Grant various privileges on specific tables and views. Privilege definitions are stored in the system tables SYSDBAUTH, SYSTABAUTH, and SYSCOLAUTH for the database, tables, and columns, respectively.
Note: You must use separate commands to grant DBA or RESOURCE privileges with any of the AUDIT privileges. Using the same command to grant a user with DBA or RESOURCE privileges and any of the AUDIT privileges results in an error.
You must use the fully qualified username to grant privileges to tenant data in a multi-tenant table.
Syntax
GRANT { RESOURCE, DBA, AUDIT_ADMIN, AUDIT_ARCHIVE, AUDIT_INSERT } TO user_identifier[ , user_identifier] , ... [ WITH GRANT OPTION ];
Note: The GRANT DBA and GRANT RESOURCE statements do not support the WITH GRANT OPTION syntax.
Parameters
RESOURCE
Allows the specified users to issue CREATE statements.
DBA
Allows the specified users to create, access, modify, or delete any database object, and to grant other users any privileges.
TO username [ , username] , ...
Grants the specified privileges on the table or view to the specified list of users.
WITH GRANT OPTION
Allows the specified users to grant their privileges or a subset of their privileges to other users. The WITH GRANT OPTION syntax is not supported by GRANT DBA and GRANT RESOURCE statements.
Syntax for granting privileges to specific tables and views
This is the syntax to grant privileges on specific tables and views:
GRANT {privilege[, privilege] , ...| ALL [ PRIVILEGES]} ON table_name TO {username[, username] , ...| PUBLIC } [ WITH GRANT OPTION ] ;
Use the following syntax to assign sequence privileges:
GRANT [SELECT | UPDATE] ON SEQUENCE schema.sequence
TO user_name[,user_name]...
Parameters
SELECT
Allows specified user to read data from the sequence.
UPDATE
Allows specified user to modify data for the sequence.
Syntax to execute stored Java procedures
The following syntax is a variation on the GRANT statement that enables the user to execute stored Java procedures:
GRANT EXECUTE ON StoredJavaProcedureName() TO {username[, username] , ...| PUBLIC } [ WITH GRANT OPTION ] ;
Parameters
RESOURCE
Allows the specified users to issue CREATE statements.
DBA
Allows the specified users to create, access, modify, or delete any database object, and to grant other users any privileges.
user_identifier
Identifies a username. For a tenant user, you must mention the fully qualified user name, username@domain_name, to grant access to a user.
Uses the following syntax:
username | username@domain_name
Note: You must not use commands, such as:
GRANT DBA to 'user' WITH GRANT OPTION
or:
GRANT RESOURCE to 'user' WITH GRANT OPTION
The above commands return syntax errors.
TO username [ , username] , ...
Grants the specified privileges on the table or view to the specified list of users.
SELECT
Allows the specified users to read data from the table or view.
INSERT
Allows the specified users to add new rows to the table or view.
DELETE
Allows the specified users to delete rows from the table or view.
INDEX
Allows the specified users to create an index on the table or view.
UPDATE [ ( column , column , ... ) ]
Allows the specified users to modify existing rows in the table or view. If followed by a column list, the users can modify values only in the columns named.
REFERENCES [ ( column , column , ... ) ]
Allows the specified users to refer to the table from other tables' constraint definitions. If followed by a column list, constraint definitions can refer only to the columns named.
For more detail on constraint definitions, see the Column constraints and Table constraints entries of this section.
ALL
Grants all privileges for the table or view including the ALTER privilege. The ALTER privilege gives the user the ability to ALTERTABLE statements for the given table.
Note: The ALTER privilege cannot currently be granted by itself.
ALTER
Grants the ALTER privilege for the table or view. This privilege is granted as part of the ALL privilege and cannot be granted by itslef.
TO PUBLIC
Grants the specified privileges on the table or view to any user with access to the system.
WITH GRANT OPTION
Allows the specified users to grant their privileges or a subset of their privileges to other users.
Examples
GRANT statement
The following example illustrates the GRANT statement:
GRANT DELETE ON cust_view TO dbuser1 ;
GRANT SELECT ON newcustomers TO dbuser2 ;
If the username specified in a RESOURCE or DBA GRANT operation does not already exist, the GRANT statement creates a row in the SYSDBAUTH system table for the new username. This row is not deleted by a subsequent REVOKE operation.
Granting privileges to a user associated with a multi-tenant table
In the following example, a DBA grants DELETE and SELECT privileges to the user dbuser1 associated with domuser1 of a multi-tenant table:
GRANT DELETE ON cust_view TO dbuser1@domuser1 ;
GRANT SELECT ON newcustomers TO dbuser2@domuser1 ;
The user dbuser1, dbuser2 must be associated with the domain name domuser1 in the multi-tenant table.
For more information on SQL database user authentication and GRANT statement, see OpenEdge Getting Started: Identity Management.
Granting audit administration and audit archive privileges
In this example, audit administration and audit archive privileges are granted to bsmith:
GRANT AUDIT_ADMIN, AUDIT_ARCHIVE TO bsmith WITH GRANT OPTION;
Because these privileges are granted to bsmith WITH GRANT OPTION, bsmith may now grant these two privileges to other users.
Granting privileges to modify a sequence
In this example, the sequence generator grants user slsadmin the ability to modify the customer number sequence:
GRANT UPDATE
ON SEQUENCE pub.customer_sequenceTO slsadmin;
Grant database-wide privileges, such as system administration (DBA), general creation (RESOURCE), audit administration (AUDIT_ADMIN), audit archive (AUDIT_ARCHIVE), or audit insert (AUDIT_INSERT).
