Modify user privileges

Modifying user privileges includes granting and revoking user privileges.

Grant privileges

Prerequisites

• When granting an object privilege, the current user must be the owner of the object or have the privilege to be granted. For example, if test1 grants the SELECT privilege on table t1 to test2, then test1 must have the SELECT privilege on table t1. In addition, the user must have the GRANT OPTION or GRANT ANY OBJECT PRIVILEGE privilege.

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

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

Considerations

When granting privileges, pay attention to the following:

• When granting multiple privileges to a user at a 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_privilege:
    ALTER
  | DELETE
  | INDEX
  | INSERT
  | SELECT
  | UPDATE
  | REFERENCES
  | EXECUTE

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

grant_user_list:
    grant_user [, grant_user ...]

The syntax for granting system privileges is as follows:

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

system_privilege_list:
  system_privilege [, system_privilege ...]

system_privilege:
    CREATE SESSION
  | EXEMPT REDACTION POLICY
  | SYSDBA
  | SYSOPER
  | SYSBACKUP
  | CREATE TABLE
  | CREATE ANY TABLE
  | ALTER ANY TABLE
  | BACKUP ANY TABLE
  | DROP ANY TABLE
  | LOCK ANY TABLE
  | COMMENT ANY TABLE
  | SELECT ANY TABLE
  | INSERT ANY TABLE
  | UPDATE ANY TABLE
  | DELETE ANY TABLE
  | FLASHBACK ANY TABLE
  | CREATE ROLE
  | DROP ANY ROLE
  | GRANT ANY ROLE
  | ALTER ANY ROLE
  | AUDIT ANY
  | GRANT ANY PRIVILEGE
  | GRANT ANY OBJECT PRIVILEGE
  | CREATE ANY INDEX
  | ALTER ANY INDEX
  | DROP ANY INDEX
  | CREATE ANY VIEW
  | DROP ANY VIEW
  | CREATE VIEW
  | SELECT ANY DICTIONARY
  | CREATE PROCEDURE
  | CREATE ANY PROCEDURE
  | ALTER ANY PROCEDURE
  | DROP ANY PROCEDURE
  | EXECUTE ANY PROCEDURE
  | CREATE SYNONYM
  | CREATE ANY SYNONYM
  | DROP ANY SYNONYM
  | CREATE PUBLIC SYNONYM
  | DROP PUBLIC SYNONYM
  | CREATE SEQUENCE
  | CREATE ANY SEQUENCE
  | ALTER ANY SEQUENCE
  | DROP ANY SEQUENCE
  | SELECT ANY SEQUENCE
  | CREATE TRIGGER
  | CREATE ANY TRIGGER
  | ALTER ANY TRIGGER
  | DROP ANY TRIGGER
  | CREATE PROFILE
  | ALTER PROFILE
  | DROP PROFILE
  | CREATE USER
  | ALTER USER
  | DROP USER
  | CREATE TYPE
  | CREATE ANY TYPE
  | ALTER ANY TYPE
  | DROP ANY TYPE
  | EXECUTE ANY TYPE
  | UNDER ANY TYPE
  | PURGE DBA_RECYCLEBIN
  | CREATE ANY OUTLINE
  | ALTER ANY OUTLINE
  | DROP ANY OUTLINE
  | SYSKM
  | CREATE TABLESPACE
  | ALTER TABLESPACE
  | DROP TABLESPACE
  | SHOW PROCESS
  | ALTER SYSTEM
  | CREATE DATABASE LINK
  | CREATE PUBLIC DATABASE LINK
  | DROP DATABASE LINK
  | ALTER SESSION
  | ALTER DATABASE

where

• obj_privilege: the object privileges to be granted. When granting multiple privileges to a user at a time, separate the privileges with commas (,).

• obj_clause: the objects involved in granting object privileges.

• system_privilege: the system privileges to be granted. When granting multiple privileges to a user at a time, separate the privileges with commas (,).

• WITH GRANT OPTION: indicates whether the currently granted privilege can be further granted to others and whether it will be revoked cascadingly when the grant is revoked.

Examples

• Grant system privileges

  Grant the CREATE SEQUENCE privilege to the test user.

  obclient> GRANT CREATE SEQUENCE TO test;
  

• Grant object privileges

  Grant the SELECT and UPDATE privileges on the emp_view view to the test user.

  obclient> GRANT SELECT, UPDATE ON emp_view TO test;
  

For more information about the GRANT statement, see GRANT.

Revoke privileges

Prerequisites

• When revoking an object privilege, the current user must have the GRANT OPTION or GRANT ANY OBJECT PRIVILEGE privilege and the privilege to be revoked. For example, if the test1 user revokes 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, the current user must have the privilege or role to be revoked and have the GRANT OPTION, GRANT ANY PRIVILEGE, or GRANT ANY ROLE privilege.

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

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

Considerations

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

• When revoking a privilege 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 granted some privileges to the test2 user, revoking the privileges from test1 will not revoke the corresponding privileges from test2.

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_privilege:
    ALTER
  | DELETE
  | INDEX
  | INSERT
  | SELECT
  | UPDATE
  | REFERENCES
  | EXECUTE

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 ...]

system_privilege:
  CREATE SESSION
  | EXEMPT REDACTION POLICY
  | SYSDBA
  | SYSOPER
  | SYSBACKUP
  | CREATE TABLE
  | CREATE ANY TABLE
  | ALTER ANY TABLE
  | BACKUP ANY TABLE
  | DROP ANY TABLE
  | LOCK ANY TABLE
  | COMMENT ANY TABLE
  | SELECT ANY TABLE
  | INSERT ANY TABLE
  | UPDATE ANY TABLE
  | DELETE ANY TABLE
  | FLASHBACK ANY TABLE
  | CREATE ROLE
  | DROP ANY ROLE
  | GRANT ANY ROLE
  | ALTER ANY ROLE
  | AUDIT ANY
  | GRANT ANY PRIVILEGE
  | GRANT ANY OBJECT PRIVILEGE
  | CREATE ANY INDEX
  | ALTER ANY INDEX
  | DROP ANY INDEX
  | CREATE ANY VIEW
  | DROP ANY VIEW
  | CREATE VIEW
  | SELECT ANY DICTIONARY
  | CREATE PROCEDURE
  | CREATE ANY PROCEDURE
  | ALTER ANY PROCEDURE
  | DROP ANY PROCEDURE
  | EXECUTE ANY PROCEDURE
  | CREATE SYNONYM
  | CREATE ANY SYNONYM
  | DROP ANY SYNONYM
  | CREATE PUBLIC SYNONYM
  | DROP PUBLIC SYNONYM
  | CREATE SEQUENCE
  | CREATE ANY SEQUENCE
  | ALTER ANY SEQUENCE
  | DROP ANY SEQUENCE
  | SELECT ANY SEQUENCE
  | CREATE TRIGGER
  | CREATE ANY TRIGGER
  | ALTER ANY TRIGGER
  | DROP ANY TRIGGER
  | CREATE PROFILE
  | ALTER PROFILE
  | DROP PROFILE
  | CREATE USER
  | ALTER USER
  | DROP USER
  | CREATE TYPE
  | CREATE ANY TYPE
  | ALTER ANY TYPE
  | DROP ANY TYPE
  | EXECUTE ANY TYPE
  | UNDER ANY TYPE
  | PURGE DBA_RECYCLEBIN
  | CREATE ANY OUTLINE
  | ALTER ANY OUTLINE
  | DROP ANY OUTLINE
  | SYSKM
  | CREATE TABLESPACE
  | ALTER TABLESPACE
  | DROP TABLESPACE
  | SHOW PROCESS
  | ALTER SYSTEM
  | CREATE DATABASE LINK
  | CREATE PUBLIC DATABASE LINK
  | DROP DATABASE LINK
  | ALTER SESSION
  | ALTER DATABASE

where

• obj_privilege: the object privileges to be revoked. When revoking multiple privileges, separate the privileges with commas (,).

• obj_clause: the objects involved in revoking object privileges.

• system_privilege: the system privileges to be revoked. When revoking multiple privileges, separate the privileges with commas (,).

Examples

• Revoke system privileges

  Revoke the CREATE SEQUENCE privilege from the test user.

  obclient> REVOKE CREATE SEQUENCE FROM test;
  

• Revoke object privileges

  Revoke the SELECT and UPDATE privileges on the emp_view view from the test user.

  obclient> REVOKE ALL PRIVILEGES FROM user_name;
  

For more information about the REVOKE statement, see REVOKE.