GRANT

Purpose

This statement is used by system administrators to grant users object privileges, system privileges, and roles.

Privilege requirements

When executing the GRANT statement, the current user must have the privileges being granted and the privilege to grant those privileges. For example, if user user1 wants to grant the SELECT privilege on table tbl1 to user user2, user user1 must have the SELECT privilege on table tbl1 and the privilege to grant the SELECT privilege on table tbl1.

Syntax

/* Grant object privileges */
GRANT {obj_all_col_priv [, obj_all_col_priv...]}
    ON obj_clause
    TO {grant_user [, grant_user...]}
    [WITH GRANT OPTION]

/* Grant system privileges or roles */
GRANT obj_all_col_priv [, obj_all_col_priv...]
    TO grantee_clause
    [WITH ADMIN OPTION]

grantee_clause:
      grant_user [, grant_user...]
    | grant_user IDENTIFIED BY password

obj_all_col_priv:
      role
    | sys_and_obj_priv [(column_list)]
    | ALL [PRIVILEGES] [(column_list)]
    | READ
    | WRITE

obj_clause:
    schema_name.relation_name
    | LOCATION location_name
    | SENSITIVE RULE relation_name

Parameters

obj_clause

• schema_name.relation_name: specifies that the privileges are granted to database objects such as tables, views, and sequences.

• LOCATION location_name: specifies that the privileges are granted to a Location object. The following privileges can be granted to a user for a Location object:

  • READ: specifies the read privilege for a Location object.
  • WRITE: specifies the write privilege for a Location object.

  Note

  For OceanBase Database V4.4.x, the GRANT statement supports the LOCATION parameter and the corresponding READ and WRITE privileges starting from V4.4.1.

• SENSITIVE RULE relation_name: specifies that the privileges are granted to a sensitive data protection rule. relation_name specifies the name of the sensitive data protection rule. For more information about the rule semantics, see CREATE SENSITIVE RULE.

System privileges related to sensitive data protection rules

In addition to object-level privileges, you can also grant system privileges related to sensitive data protection rules. Examples:

• CREATE SENSITIVE RULE: allows you to create a sensitive data protection rule.
• PLAINACCESS ANY SENSITIVE RULE: allows you to access columns protected by a sensitive data protection rule in plaintext (used in maintenance scenarios where data desensitization needs to be bypassed; use this privilege with caution).

To grant system privileges, use the GRANT ... TO grantee_clause syntax. The ON clause is not required.

Examples

• Grant the CREATE VIEW privilege to user user1 and allow the privilege to be granted to other users.

  obclient> GRANT CREATE VIEW TO user1 WITH ADMIN OPTION;
  

• Grant the CONNECT role to user user1 and modify the password of user1.

  obclient> GRANT CONNECT TO user1 IDENTIFIED by '********';
  

  After execution, check the password of user user1 in the dba_users table. The password will be updated to the latest setting.

• Grant the COMMENT ANY TABLE privilege to role role1.

  obclient> GRANT COMMENT ANY TABLE TO role1;
  

• Grant the read privilege on the object local_t of the Location to user user006.

  obclient> GRANT READ ON LOCATION local_t TO user006;
  

• Grant the system privilege to user user1 to create sensitive data protection rules.

  obclient> GRANT CREATE SENSITIVE RULE TO user1;
  

• Grant the system privilege to user user1 to access sensitive rules in plaintext. (Use this privilege with caution.)

  obclient> GRANT PLAINACCESS ANY SENSITIVE RULE TO user1;
  

References

• For information about how to view user privileges, see View user privileges.

• For information about how to view roles and the privileges in roles, see View roles.

• You can view the information about the created users in the dba_users table. For more information about the dba_users table, see DBA_USERS.