About table privileges

2023-10-24 09:23:03  Updated

This topic describes how to grant and revoke table privileges.

Grant privileges

Overview

You can use the GRANT statement to grant privileges to other users as the system administrator.

Usage notes

  • You can grant only the privileges that you have. For example, user1 attempts to grant the SELECT privilege on table tbl1 to user2. In this case, user1 must have the SELECT privilege on table tbl1 and the GRANT OPTION privilege.

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

Syntax

GRANT priv_type
    ON priv_level 
    TO user_specification [, user_specification]... 
     [WITH with_option ...]

priv_type:
      ALTER
    | CREATE
    | CREATE USER
    | CREATE VIEW
    | DELETE
    | DROP
    | GRANT OPTION
    | INDEX
    | INSERT
    | PROCESS
    | SELECT
    | SHOW DATABASES
    | SHOW VIEW
    | SUPER
    | UPDATE
    | USAGE
    
priv_level: 
      *
    | *.*
    | database_name.* 
    | database_name.table_name
    | table_name
    | database_name.routine_name

user_specification: 
user [IDENTIFIED BY [PASSWORD] 'password'] 

with_option:
 GRANT OPTION

Parameters

Parameter Description
priv_type The type of the privilege to be granted. For information about the specific privilege types and their description, see the following table. To grant multiple privileges to a user, separate the types with commas (,).
priv_level The level of the privilege to be granted. Privileges can be divided into the following levels:
  • Global: Privileges at this level apply to all databases. You can use the GRANT ALL ON *.* statement to grant global privileges.
  • Database level: Privileges at this level apply to all objects in a specified database. You can use the GRANT ALL ON db_name.* statement to grant database privileges.
  • Table level: Privileges at this level apply to all columns in a specified table. You can use the GRANT ALL ON database_name.table_name statement to grant table privileges.
If you replace table_name with an asterisk (*), privileges on all tables in the database will be granted.
user_specification Grants privileges to a specific user. If the user does not exist, you can directly create the user. When you grant privileges to multiple users, the usernames must be separated with commas (,).
user IDENTIFIED BY 'password' Displays the password in plaintext.
user IDENTIFIED BY PASSWORD 'password' Displays the password in ciphertext.
with_option Specifies whether to enable privilege delegation.

The following table lists the types of privileges that can be granted.

Privilege Description
ALL PRIVILEGES All privileges except GRANT OPTION.
ALTER The ALTER TABLE privilege.
CREATE The CREATE TABLE privilege.
CREATE USER The CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES privileges.
CREATE TABLEGROUP The global CREATE TABLEGROUP privilege.
DELETE The DELETE privilege.
DROP The DROP privilege.
GRANT OPTION The GRANT OPTION privilege.
INSERT The INSERT privilege.
SELECT The SELECT privilege.
UPDATE The UPDATE privilege.
SUPER The SET GLOBAL privilege for modifying global system parameters.
SHOW DATABASES The global SHOW DATABASES privilege.
INDEX The CREATE INDEX and DROP INDEX privileges.
CREATE VIEW The CREATE VIEW and DROP VIEW privileges.
SHOW VIEW The SHOW CREATE VIEW privilege.

Examples

  1. Grant all privileges to a user.

    1. Grant all privileges to the ny user.

      obclient> GRANT ALL PRIVILEGES ON *.* TO ny WITH GRANT OPTION;
Query OK, 0 rows affected

    2. View the privileges of the ny user.

      obclient> SHOW GRANTS FOR ny;
+-------------------------------------------------------+
| Grants for ny@%                                       |
+-------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'ny' WITH GRANT OPTION |
+-------------------------------------------------------+
1 row in set

  2. Grant a user all the privileges on a database.

    1. Grant the ny user all the privileges on the data_ny database.

      obclient> GRANT ALL PRIVILEGES ON data_ny.* to ny with GRANT OPTION;
Query OK, 0 rows affected (0.02 sec)

    2. View the privileges of the ny user on the data_ny database.

      obclient> SHOW GRANTS FOR ny;
+---------------------------------------------------------------+
| Grants for ny@%      |
+---------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'ny'                                    |
| GRANT ALL PRIVILEGES ON `data_ny`.* TO 'ny' WITH GRANT OPTION |
+---------------------------------------------------------------+
2 rows in set

  3. Grant a user all the privileges on a table.

    1. Grant the ny user all the privileges on the dws_ny table in the data_ny database.

      obclient> GRANT ALL PRIVILEGES ON data_ny.dws_ny to ny with GRANT OPTION;
Query OK, 0 rows affected

    2. View the privileges of the ny user on the dws_ny table in the data_ny database.

      obclient> SELECT * FROM information_schema.TABLE_PRIVILEGES where TABLE_NAME='dws_ny';
+----------+---------------+--------------+------------+----------------+--------------+
| GRANTEE  | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | PRIVILEGE_TYPE | IS_GRANTABLE |
+----------+---------------+--------------+------------+----------------+--------------+
| 'ny'@'%' | def           | data_ny      | dws_ny     | ALTER          | YES          |
| 'ny'@'%' | def           | data_ny      | dws_ny     | CREATE         | YES          |
| 'ny'@'%' | def           | data_ny      | dws_ny     | DELETE         | YES          |
| 'ny'@'%' | def           | data_ny      | dws_ny     | DROP           | YES          |
| 'ny'@'%' | def           | data_ny      | dws_ny     | INSERT         | YES          |
| 'ny'@'%' | def           | data_ny      | dws_ny     | UPDATE         | YES          |
| 'ny'@'%' | def           | data_ny      | dws_ny     | SELECT         | YES          |
| 'ny'@'%' | def           | data_ny      | dws_ny     | INDEX          | YES          |
| 'ny'@'%' | def           | data_ny      | dws_ny     | CREATE VIEW    | YES          |
| 'ny'@'%' | def           | data_ny      | dws_ny     | SHOW VIEW      | YES          |
+----------+---------------+--------------+------------+----------------+--------------+
10 rows in set

      Note

      Both the root user and ny user can query the privileges on the dws_ny table in the TABLE_PRIVILEGES table.

Revoke privileges

Overview

You can use the REVOKE statement as the system administrator to revoke privileges from a user.

Usage notes:

  • The user must be granted with the privileges to be revoked and the GRANT OPTION privilege. For example, if user1 wants to revoke the SELECT privilege on table t1 from user2, user1 must have the SELECT privilege on table t1.

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

  • The revocation does not extend to dependent users. For example, if user1 has granted some privileges to user2, when the privileges of user1 are revoked, the privileges granted to user2 are not revoked.

Syntax

REVOKE priv_type 
     ON database.table_name 
     FROM 'user_name';

priv_type:
      ALTER
    | CREATE
    | CREATE USER
    | CREATE VIEW
    | DELETE
    | DROP
    | GRANT OPTION
    | INDEX
    | INSERT
    | PROCESS
    | SELECT
    | SHOW DATABASES
    | SHOW VIEW
    | SUPER
    | UPDATE
    | USAGE

Parameters

Parameter Description
priv_type The type of the privilege to be revoked. For information about the specific privilege types and their description, see the following table. Multiple privileges must be separated with commas (,).
database.table_name The name of the table in the database on which the privilege is to be revoked. If you replace database or table_name with an asterisk (*), operation privileges on all tables in the database will be revoked.
user_name The name of the user from which the privilege is to be revoked. Multiple usernames must be separated with commas (,).

The following table lists the privileges that can be revoked.

Privilege Description
ALL PRIVILEGES All privileges except GRANT OPTION.
ALTER The ALTER TABLE privilege.
CREATE The CREATE TABLE privilege.
CREATE USER The CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES privileges.
CREATE TABLEGROUP The global CREATE TABLEGROUP privilege.
DELETE The DELETE privilege.
DROP The DROP privilege.
GRANT OPTION The GRANT OPTION privilege.
INSERT The INSERT privilege.
SELECT The SELECT privilege.
UPDATE The UPDATE privilege.
SUPER The SET GLOBAL privilege for modifying global system parameters.
SHOW DATABASES The global SHOW DATABASES privilege.
INDEX The CREATE INDEX and DROP INDEX privileges.
CREATE VIEW The CREATE VIEW and DROP VIEW privileges.
SHOW VIEW The SHOW CREATE VIEW privilege.

Note

Currently, all users in the SYS tenant can execute the CHANGE EFFECTIVE TENANT statement to revoke privileges of specified users in business tenants.

Examples

  1. Revoke the UPDATE privilege from a user.

    1. Revoke the UPDATE privilege on the dws_ny table in the data_ny database from the ny user.

      obclient> revoke update on data_ny.dws_ny from ny;
Query OK, 0 rows affected

    2. View the current privileges of the ny user on the dws_ny table in the data_ny database.

      obclient> select * from information_schema.TABLE_PRIVILEGES where TABLE_NAME='dws_ny';
+----------+---------------+--------------+------------+----------------+--------------+
| GRANTEE  | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | PRIVILEGE_TYPE | IS_GRANTABLE |
+----------+---------------+--------------+------------+----------------+--------------+
| 'ny'@'%' | def           | data_ny      | dws_ny     | ALTER          | YES          |
| 'ny'@'%' | def           | data_ny      | dws_ny     | CREATE         | YES          |
| 'ny'@'%' | def           | data_ny      | dws_ny     | DELETE         | YES          |
| 'ny'@'%' | def           | data_ny      | dws_ny     | DROP           | YES          |
| 'ny'@'%' | def           | data_ny      | dws_ny     | INSERT         | YES          |
| 'ny'@'%' | def           | data_ny      | dws_ny     | SELECT         | YES          |
| 'ny'@'%' | def           | data_ny      | dws_ny     | INDEX          | YES          |
| 'ny'@'%' | def           | data_ny      | dws_ny     | CREATE VIEW    | YES          |
| 'ny'@'%' | def           | data_ny      | dws_ny     | SHOW VIEW      | YES          |
+----------+---------------+--------------+------------+----------------+--------------+
9 rows in set

      Note

      Both the root user and ny user can query the privileges on the dws_ny table in the TABLE_PRIVILEGES table.

  2. Revoke the UPDATE privilege on a database from a user.

    1. Revoke the UPDATE privilege on the dws_ny table in the data_ny database from the ny user.

      obclient> revoke update on data_ny.* from ny;
Query OK, 0 rows affected

    2. View the current privileges of the ny user on the data_ny database.

      obclient> SHOW GRANTS FOR ny;
+----------------------------------------------------------------------------------------------------------------------------------+
| Grants for ny@%                                        |
+----------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'ny'                             |
| GRANT ALTER, CREATE, DELETE, DROP, INSERT, SELECT, INDEX, CREATE VIEW, SHOW VIEW ON `data_ny`.* TO 'ny' WITH GRANT OPTION        |
| GRANT ALTER, CREATE, DELETE, DROP, INSERT, SELECT, INDEX, CREATE VIEW, SHOW VIEW ON `data_ny`.`dws_ny` TO 'ny' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------+
3 rows in set

  3. Revoke the UPDATE privilege from a user.

    1. Revoke the UPDATE privilege from the ny user.

      obclient> revoke UPDATE on *.* from ny;
Query OK, 0 rows affected

    2. View the current privileges of the ny user.

      obclient> SHOW GRANTS FOR ny;
+----------------------------------------------------------------------------------------------------------------------------------+
| Grants for ny@%                                        |
+----------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'ny'                             |
| GRANT ALTER, CREATE, DELETE, DROP, INSERT, SELECT, INDEX, CREATE VIEW, SHOW VIEW ON `data_ny`.* TO 'ny' WITH GRANT OPTION        |
| GRANT ALTER, CREATE, DELETE, DROP, INSERT, SELECT, INDEX, CREATE VIEW, SHOW VIEW ON `data_ny`.`dws_ny` TO 'ny' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------+
3 rows in set
Last
Next

OceanBaseOceanBase

OceanBase, A Highly Scalable Database for Transactional, Analytical, and AI Workloads.

Company

About OceanBaseContact UsPartner

Product

OceanBase CloudOceanBase EnterpriseQuick StartDownloadPricing

Resources

DocsBlogWhite PaperTrust Center

Follow us

LinkedInLinkedInDiscordDiscordTwitterTwitterYoutubeYoutubeForumForumGitHubGitHubStack OverflowStack Overflow

© OceanBase 2024. All rights reserved | Cloud Service Agreement | Privacy Policy | Legal | Security

Contact Us