Skip to main content

Directly grant privileges

You can use the GRANT statement to directly grant privileges to a user.

Prerequisites​

The current user must have the privileges to be granted (for example, test1 grants the SELECT privilege on table t1 to test2, then test1 must have the SELECT privilege on table t1) and the GRANT OPTION privilege to successfully grant privileges.

To view the privileges that the current user has, see View user privileges.

Considerations​

Before granting privileges, note the following:

  • When granting privileges to a specific user, if the user does not exist, you can directly create the user. If sql_mode='no_auto_create_user' and the IDENTIFIED BY clause is not specified in the statement, you cannot directly create the user.

  • When granting multiple privileges to a user, separate the privilege types with commas (,).

  • When granting privileges to multiple users, separate the usernames with commas (,).

  • After a user is granted privileges, the privileges take effect only after the user reconnects to seekdb.

Syntax​

The syntax for granting privileges is as follows:

GRANT priv_type 
    ON priv_level 
    TO user_specification [, user_specification]... 
     [WITH GRANT OPTION];
  
priv_level: 
      *
    | *.*
    | database_name.* 
    | database_name.table_name
    | table_name
    | database_name.routine_name

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

The statement is used as follows:

  • priv_type: specifies the type of privilege to be granted. When granting multiple privileges to a user, separate the privilege types with commas (,).

    If you need to grant column-level privileges, specify the column name after the corresponding privilege type (in the format of priv(col_list)). You can specify multiple columns, separated by commas (,). For example, to grant the SELECT privilege on column c1, use SELECT(c1).

    When granting privileges to columns of a table:

    • If you need to execute the UPDATE statement on certain columns, you must grant the UPDATE privilege on these columns and the SELECT privilege on other columns that need to be accessed.

      For example, consider the following statement:

      UPDATE tb1 SET c1=c3, c2=1+3 WHERE c4=1;

      Since the columns to be updated are c1 and c2, you must grant the UPDATE privilege on these columns to the user. Additionally, since the user needs to access columns c3 and c4, you must also grant the SELECT privilege on these columns to the user.

    • If you need to execute the INSERT or REPLACE statement on certain columns, you must grant the INSERT privilege on these columns and the SELECT privilege on other columns that need to be accessed.

    • For other statements, you only need to grant the SELECT privilege on the columns that need to be accessed.

    For more information about all privilege types supported in MySQL mode, see Privilege types.

  • priv_level: specifies the level at which privileges are granted. Privileges are mainly divided into the following levels:

    • Global: applies to all databases. Use GRANT ... ON *.* to grant global privileges.

    • Database-level: applies to all objects in a specified database. Use GRANT ... ON db_name.* to grant database-level privileges.

    • Table-level: applies to all columns in a specified table. Use GRANT ... ON database_name.table_name to grant table-level privileges.

      When granting column privileges, also use GRANT ... ON database_name.table_name to specify the table.

  • user_specification: specifies the user to whom privileges are to be granted. If the user does not exist, the user is directly created.

    When granting privileges to multiple users, separate the usernames with commas (,).

  • user_name IDENTIFIED BY 'password' and user_name IDENTIFIED BY PASSWORD 'password': the password in the user_name IDENTIFIED BY 'password' clause is plaintext; the password in the user_name IDENTIFIED BY PASSWORD 'password' clause is ciphertext.

  • WITH GRANT OPTION: specifies whether the privilege can be delegated or revoked.

Examples​

  • Grant global privileges

    Global privileges apply to all databases. Typically, use GRANT ... ON *.* to grant global privileges.

    • Grant all privileges on all objects in all databases to the user test.

      GRANT ALL ON *.* TO test;

    • Grant the INSERT and SELECT privileges on all tables in all databases to the user test.

      GRANT SELECT, INSERT ON *.* TO test;

  • Grant database-level privileges

    Database-level privileges refer to the operation privileges on all objects in a specified database. Typically, use GRANT ... ON db_name.* to grant database-level privileges.

    • Grant all operation privileges on all objects in database db1 to the user test.

      GRANT ALL ON db1.* TO test;

    • Grant the INSERT and SELECT privileges on all tables in database db1 to the user test.

      GRANT SELECT, INSERT ON db1.* TO test;

  • Grant table-level privileges

    Table-level privileges refer to the operation privileges on a specified table in a specified database. Typically, use GRANT ... ON db_name.tb1_name to grant table-level privileges.

    • Grant the INSERT and SELECT privileges on table tb1_name in database db1 to the user test.

      GRANT SELECT, INSERT ON db1.tb1_name TO test;

    • Grant all operation privileges on table tb1_name in database db1 to the user test.

      GRANT ALL ON db1.tb1_name TO test;

  • Grant column privileges in a table

    Column privileges refer to the operation privileges on a specified column in a specified table.

    In the following example, grant the SELECT privilege on column c1 and the INSERT privilege on columns c1 and c2 in table tb1_name of database db1 to the user test.

    GRANT SELECT(c1), INSERT (c1, c2) ON db1.tb1_name TO test;

For more information about the GRANT statement, see GRANT.

Contents

Edit this page