Modify user privileges

Modifying user privileges includes granting and revoking user privileges.

Grant user privileges

Prerequisites

• When granting an object privilege, you must have the GRANT OPTION or GRANT ANY OBJECT PRIVILEGE privilege, and you must be the owner of the object or have the privilege to be granted. For example, if the test1 user wants to grant the SELECT privilege on table t1 to the test2 user, the test1 user must have the GRANT OPTION or GRANT ANY OBJECT PRIVILEGE privilege and have the SELECT privilege on table t1.

• When granting a system privilege or a role, you must have the GRANT OPTION, GRANT ANY PRIVILEGE, or GRANT ANY ROLE privilege and have the privilege or role to be granted.

For information about how to view your privileges, see View user privileges. If you do not have the required privileges, contact the administrator to obtain the privileges. For information about how to view your roles, see View roles.

Considerations

When you grant privileges, note the following:

• When granting multiple privileges to a user at the same time, separate the privileges with commas (,).

• If a user has been granted a privilege, the privilege takes effect only after the user is reconnected to OceanBase Database.

Syntax for granting privileges

The syntax for granting object privileges is as follows:

GRANT obj_with_col_priv_list
    ON obj_clause TO grant_user_list [WITH GRANT OPTION];
  
obj_with_col_priv_list:
  obj_with_col_priv
  | obj_with_col_priv_list, obj_with_col_priv
  
obj_with_col_priv:
  obj_privilege [column_list]

obj_clause:
  relation_name
  | relation_name '.' relation_name
  | DIRECTORY relation_name

grant_user_list:
    grant_user [, grant_user ...]

The syntax for revoking system privileges is as follows:

GRANT {system_privilege_list | ALL PRIVILEGES}
    TO grantee_user [IDENTIFIED BY password];

system_privilege_list:
  system_privilege [, system_privilege ...]

where

• obj_privilege specifies the object privileges to be granted. When you grant multiple privileges to a user at the same time, separate the privileges with commas (,).

  For more information about the object privilege types supported in Oracle mode, see Privilege types in Oracle mode.

• obj_clause specifies the objects involved in granting object privileges.

• system_privilege specifies the system privileges to be granted. When you grant multiple privileges to a user at the same time, separate the privileges with commas (,).

  For more information about the system privilege types supported in Oracle mode, see Privilege types in Oracle mode.

• WITH GRANT OPTION specifies whether to enable privilege delegation. When privilege delegation is enabled, revoking privileges will also revoke delegated privileges.

Examples

• Grant system privileges

  To grant the CREATE SEQUENCE privilege to the test user, execute the following statement:

  obclient> GRANT CREATE SEQUENCE TO test;
  

• Grant object privileges

  To grant the SELECT and UPDATE privileges on the emp_view view to the test user, execute the following statement:

  obclient> GRANT SELECT, UPDATE ON emp_view TO test;
  

For more information about the GRANT statement, see GRANT.

Revoke user privileges

Prerequisites

• When revoking an object privilege, you must have the GRANT OPTION or GRANT ANY OBJECT PRIVILEGE privilege and the privilege to be revoked. For example, if the test1 user wants to revoke the SELECT privilege on table t1 from the test2 user, the test1 user must have the GRANT OPTION or GRANT ANY OBJECT PRIVILEGE privilege and the SELECT privilege on table t1.

• When revoking a system privilege or a role, you must have the GRANT OPTION, GRANT ANY PRIVILEGE, or GRANT ANY ROLE privilege and the privilege or role to be revoked.

• When revoking the ALL PRIVILEGES and GRANT OPTION privileges, you must have the global GRANT OPTION privilege or have the UPDATE and DELETE privileges on the table.

For information about how to view your privileges, see View user privileges. If you do not have the required privileges, contact the administrator to obtain the privileges.

Considerations

• When you revoke multiple privileges from a user at the same time, separate the privileges with commas (,).

• When you revoke privileges from multiple users at the same time, separate the usernames with commas (,).

• When granting a privilege to a user without specifying GRANT OPTION, the revocation operation will not cascade. For example, if the test1 user has granted some privileges to the test2 user, revoking test1's privileges will not also revoke test2's corresponding privileges.

Syntax for revoking privileges

The syntax for revoking object privileges is as follows:

REVOKE obj_privileges
  ON obj_clause FROM user_list;

user_list:
  user [, user ...]

obj_privileges:
  obj_privilege [, obj_privilege ...]

obj_clause:
  relation_name
  | relation_name '.' relation_name
  | DIRECTORY relation_name

relation_name:
  STR_VALUE

The syntax for revoking system privileges is as follows:

REVOKE {system_privilege_list | ALL PRIVILEGES}
  FROM user_list;
REVOKE ALL [PRIVILEGES], GRANT_OPTION FROM user_list;

system_privilege_list:
  system_privilege [, system_privilege ...]

where

• obj_privilege specifies the object privileges to be revoked. When you revoke multiple privileges, separate them with commas (,).

  For more information about the object privilege types supported in Oracle mode, see Privilege types in Oracle mode.

• obj_clause specifies the objects involved in revoking object privileges.

• system_privilege specifies the system privileges to be revoked. When you revoke multiple privileges, separate them with commas (,).

  For more information about the system privilege types supported in Oracle mode, see Privilege types in Oracle mode.

Examples

• Revoke system privileges

  To revoke the CREATE SEQUENCE privilege from the test user, execute the following statement:

  obclient> REVOKE CREATE SEQUENCE FROM test;
  

• Revoke object privileges

  To revoke the SELECT and UPDATE privileges on the emp_view view from the test user, execute the following statement:

  obclient> REVOKE ALL PRIVILEGES FROM user_name;
  

For more information about the REVOKE statement, see REVOKE.