Granting a system privilege (SQL)

Grant a system privilege to authorize a user or role to perform an operation on the database.

Prerequisites

You must have administrative rights on the system privilege you want to grant.

Task

Connect to the database.

Execute a statement similar to the following:

Option

Statement

Grant a privilege, with no administrative rights.

GRANT privilege-name 
TO userid;

Grant a privilege, with administrative rights.

GRANT privilege-name 
TO userid WITH ADMIN OPTION;

Grant administrative rights only on a privilege.

GRANT privilege-name 
TO userid WITH ADMIN ONLY OPTION;

Results

The user or role is granted the system privilege. If you granted a system privilege to a role, then any user or role that is granted that role inherits the new system privilege.

Example

To grant the CREATE ANY OBJECT system privilege to the role RoleA without giving RoleA administrative rights, execute the following statement:

GRANT CREATE ANY OBJECT TO RoleA

To grant RoleA the CREATE ANY OBJECT system privilege along with the ability to grant or revoke the system privilege to and from other users and roles, execute the following statement:

GRANT CREATE ANY OBJECT TO RoleA WITH ADMIN OPTION

To give RoleA administrative rights to the BACKUP DATABASE system privilege, but not the ability to use the BACKUP DATABASE privilege, execute the following statement:

GRANT BACKUP DATABASE TO RoleA WITH ADMIN ONLY OPTION

To grant RoleA administrative rights (only) for the BACKUP DATABASE and CHECKPOINT system privileges, execute the following statement:

GRANT BACKUP DATABASE, CHECKPOINT TO RoleA WITH ADMIN ONLY OPTION;

Granting a system privilege (SQL)

Task

See also