Modify user privileges

This topic describes how to modify privileges for an OceanBase database user.

Precautions

For privilege grants:

• Before you grant privileges to a user, make sure that the user that you have logged on to has the privileges to be granted and the GRANT OPTION privilege.

• The user to which the privileges are granted needs to reconnect to the OceanBase database for the newly granted privileges to take effect.

For privilege revocation:

• The user that you have logged on to must have the privileges to be revoked and the GRANT OPTION privilege.

• To revoke the ALL PRIVILEGES and GRANT OPTION privilege, the user that you have logged on to must have the global GRANT OPTION privilege or the UPDATE and DELETE privileges on the table.

• Privilege revocation does not extend to other users. For example, if user1 has granted some privileges to user2, when you revoke the privileges of user1, the privileges granted by user1 to user2 are not revoked.

Grant or revoke privileges using SQL statements

• MySQL mode

  The user privilege management syntax in MySQL mode is as follows:

  grant_stmt:
      GRANT privilege_type_list ON privilege_level TO user_option_list [WITH GRANT OPTION];
  
  revoke_stmt:
      REVOKE privilege_type_list ON privilege_level FROM user_name_list;
  
  show_grants_stmt:
      SHOW GRANTS [FOR user_name];
  
  privilege_type_list:
      {ALL [PRIVILEGES] | privilege_type [, privilege_type ...]}
  
  privilege_type:
        ALTER
      | CREATE
      | CREATE USER
      | CREATE VIEW
      | DELETE
      | DROP
      | GRANT OPTION
      | INDEX
      | INSERT
      | PROCESS
      | SELECT
      | SHOW DATABASES
      | SHOW VIEW
      | SUPER
      | UPDATE
      | USAGE
  
  privilege_level:
        *
      | *.*
      | database_name.*
      | database_name.table_name
      | table_name
  
  user_option_list:
      user_option [, user_option ...]
  
  user_option:
      user_name [IDENTIFIED BY 'password']
  
  password:
      STR_VALUE
  
  user_name_list:
      user_name [, user_name ...]
  

  Examples:

  • Grant privileges

    obclient> GRANT ALL PRIVILEGES ON *.* TO demo WITH GRANT OPTION;
    

  • Revoke privileges

    obclient> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'demo';
    Query OK, 0 rows affected (0.03 sec)
    

• Oracle mode

  The user privilege management syntax in Oracle mode is as follows:

  grant_stmt:
      /*Grant object privileges*/
      GRANT obj_with_col_priv_list
        ON obj_clause TO grant_user_list [WITH GRANT OPTION];
      /*Grant system privileges*/
      GRANT {system_privilege_list | ALL PRIVILEGES}
        TO grantee_user [IDENTIFIED BY password] [WITH {GRANT | ADMID} OPTION];
      /*Grant roles*/
      GRANT role_list TO grantee_user [IDENTIFIED BY password] [WITH {GRANT | ADMID} OPTION];
  
  revoke_stmt:
        /*Revoke object privileges*/
      REVOKE obj_privileges
        ON obj_clause FROM user_list;
      /*Revoke system privileges*/
      REVOKE {system_privilege_list | ALL PRIVILEGES}
        FROM user_list;
      REVOKE ALL [PRIVILEGES], GRANT_OPTION FROM user_list;
      /*Revoke roles*/
      REVOKE role_list FROM user;
  
  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_privileges:
    obj_privilege [, obj_privilege ...]
  
  obj_privilege:
      ALTER
    | AUDIT
    | COMMENT
    | DELETE
    | GRANT
    | INDEX
    | INSERT
    | LOCK
    | RENAME
    | SELECT 
    | UPDATE
    | REFERENCES
    | EXECUTE
    | CREATE
    | FLASHBACK
    | READ
    | WRITE
    | DEBUG
  
  obj_clause:
    relation_name
    | relation_name '.' relation_name
    | DIRECTORY relation_name
  
  grant_user_list:
    grant_user [, grant_user ...]
  
  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
  
  role_list:
  role [, role ...]
  

  Examples:

  • Grant privileges

    obclient> GRANT SELECT ON demo.table_test TO demo;
    

  • Grant all system privileges to a user

    obclient> GRANT ALL PRIVILEGES TO user_name;
    

  • Revoke privileges

    obclient> REVOKE SELECT ON demo.table_test FROM demo; 
    Query OK, 0 rows affected (0.03 sec)
    

  • Revoke all system privileges from the user

    obclient> REVOKE ALL PRIVILEGES FROM user_name;