GRANT

Use the GRANT command to assign system and object permissions to roles. System permissions control the commands that can be run in the ObjectServer. Object permissions control access to individual objects, such as tables.

Syntax for granting system permissions

GRANT system_permission,...
 TO ROLE 'role_name',...
 [ WITH GRANT OPTION ];

The value of system_permission can be any of the following subcommands: 

ISQL
ISQL WRITE
ALTER SYSTEM DROP CONNECTION
ALTER SYSTEM SHUTDOWN
ALTER SYSTEM BACKUP
ALTER SYSTEM SET PROPERTY
CREATE DATABASE
CREATE FILE
CREATE RESTRICTION FILTER
CREATE SQL PROCEDURE
CREATE EXTERNAL PROCEDURE
CREATE SIGNAL
CREATE TRIGGER GROUP
CREATE USER
CREATE GROUP
CREATE ROLE
ALTER USER
ALTER GROUP
ALTER ROLE
DROP USER
DROP GROUP
DROP ROLE
GRANT ROLE
REVOKE ROLE

The role_name is a text string containing the unique name of the role or roles to which you are assigning permissions.

The WITH GRANT OPTION option enables the roles to whom the permission is granted to grant the permission to other roles.

Tip: You can query the catalog.security_permissions table to view information about permissions. For example, to view each system permission, use the following SQL command: SELECT * FROM catalog.security_permissions WHERE Object = 'SYSTEM' ORDER BY Permission;

Example for granting system permissions

grant create database to role 'DDL_Admin';

Syntax for granting object permissions

GRANT object_permission,... ON permission_object object_name
 TO ROLE 'role_name',...
 [ WITH GRANT OPTION ];

You can assign one or more permissions to ObjectServer objects. Use object_permission to define the SQL commands that authorized users can run on an ObjectServer object of type permission_object. The object_name is a text string containing the unique name of the object.

The owner of the object (its creator) automatically has the grant and revoke permissions associated with that object, and can grant and revoke those permissions to other roles. The following table lists the permissions that the owner has for each object type. The owner can also grant these permissions to other users.

Table 1. Objects and associated permissions
Objects (permission_object) Permissions (object_permission)
DATABASE DROP

CREATE TABLE

CREATE VIEW
TABLE DROP

ALTER

SELECT

INSERT

UPDATE

DELETE

CREATE INDEX

DROP INDEX
VIEW DROP

ALTER

SELECT

UPDATE

DELETE
TRIGGER GROUP DROP

ALTER

CREATE TRIGGER
TRIGGER DROP

ALTER
FILE DROP

ALTER

WRITE
SQL PROCEDURE

EXTERNAL PROCEDURE

 DROP

ALTER

EXECUTE
SIGNAL DROP

ALTER

RAISE
RESTRICTION FILTER DROP

ALTER

The role_name is a text string containing the unique name of the role or roles to which the permissions are being assigned.

The WITH GRANT OPTION option enables the roles to whom the permission is granted to grant the permission to other roles.

Tip: In commands where you can replace an existing object by using the CREATE OR REPLACE syntax, you need ALTER permission to replace an existing object. Some objects can be altered only by using the CREATE OR REPLACE syntax; for example, there is no ALTER VIEW command, but you can replace an existing view if you have ALTER permission on the view.

Example for granting object permissions

grant drop on database testdb to role 'DDL_Admin';