View user privileges

After you create a user, you can query the privileges of the user.

Prerequisites

• If you view user privileges by using the SHOW GRANTS statement, you do not need any privileges to view your own privileges, but you need the global SELECT privilege to view the privileges of other users.

• If you view user privileges by using a view, you need the SELECT privilege on the view.

Procedure

1. Log in to a MySQL tenant as an administrator.

2. View user privileges by using the following methods:

   • Use the SHOW GRANTS statement.

     You can view your privileges by executing any of the following statements:

     obclient> SHOW GRANTS;
     

     obclient> SHOW GRANTS FOR CURRENT_USER;
     

     obclient> SHOW GRANTS FOR CURRENT_USER();
     

     To view the privileges granted to a user, use the FOR clause to specify the user name. Here is an example:

     obclient> SHOW GRANTS FOR test;
     

     A sample query result is as follows:

     +----------------------------------------+
     | Grants for test@%                      |
     +----------------------------------------+
     | GRANT INSERT, SELECT ON *.* TO 'test'  |
     | GRANT SELECT ON `db1`.* TO 'test'      |
     +----------------------------------------+
     2 rows in set
     

   • View the global privileges of a user.

     You can view the global privileges of a user by querying the mysql.user view.

     Here is an example:

     obclient> SELECT * FROM mysql.user WHERE user='test'\G
     *************************** 1. row ***************************
                       host: %
                       user: test
                   password: *6691484ea6b50ddde1926a220da01fa9e575c18a
                select_priv: Y
                insert_priv: Y
                update_priv: N
                delete_priv: N
                create_priv: N
                  drop_priv: N
                reload_priv: N
              shutdown_priv: N
               process_priv: N
                  file_priv: N
                 grant_priv: N
             reference_priv: N
                 index_priv: N
                 alter_priv: N
               show_db_priv: N
                 super_priv: N
      create_tmp_table_priv: N
           lock_tables_priv: N
               execute_priv: N
            repl_slave_priv: N
           repl_client_priv: N
           create_view_priv: N
             show_view_priv: N
        create_routine_priv: N
         alter_routine_priv: N
           create_user_priv: N
                 event_priv: N
               trigger_priv: N
     create_tablespace_priv: N
                   ssl_type:
                 ssl_cipher:
                x509_issuer:
               x509_subject:
              max_questions: 0
                max_updates: 0
            max_connections: 0
       max_user_connections: 0
                     plugin: ob_native_password
      authentication_string:
           password_expired:
     1 row in set
     

     For more information about fields in the mysql.user view, see mysql.user.

   • View the database-level privileges of a user.

     You can view the database-level privileges of a user by querying the mysql.db view.

     obclient> SELECT * FROM mysql.db WHERE user='test'\G
     *************************** 1. row ***************************
                      host: %
                        db: test
                      user: test
               select_priv: Y
               insert_priv: Y
               update_priv: Y
               delete_priv: N
               create_priv: N
                 drop_priv: N
                grant_priv: N
            reference_priv: N
                index_priv: Y
                alter_priv: Y
     create_tmp_table_priv: N
          lock_tables_priv: N
          create_view_priv: N
            show_view_priv: Y
       create_routine_priv: N
        alter_routine_priv: N
              execute_priv: N
                event_priv: N
              trigger_priv: N
     1 row in set
     

     For more information about the fields in the mysql.db view, see mysql.db.

   • View the column-level privileges granted to a user.

     You can view the column-level privileges granted to a user by querying the mysql.columns_priv or information_schema.COLUMN_PRIVILEGES view.

     • Query the mysql.columns_priv view

       obclient [mysql]> SELECT * FROM mysql.columns_priv;
       

       The query result is as follows:

       +------+-------+-------+------------+-------------+-------------+---------------------+
       | Host | Db    | User  | Table_name | Column_name | Column_priv | Timestamp           |
       +------+-------+-------+------------+-------------+-------------+---------------------+
       | %    | test1 | user2 | tbl1       | c1          | Insert      | 2024-03-27 15:02:57 |
       | %    | test1 | user1 | tbl1       | c1          | Select      | 2024-03-27 11:05:21 |
       +------+-------+-------+------------+-------------+-------------+---------------------+
       2 rows in set
       

     • Query the information_schema.COLUMN_PRIVILEGES view

       obclient [information_schema]> SELECT * FROM information_schema.COLUMN_PRIVILEGES;
       

       The query result is as follows:

       +-------------+---------------+--------------+------------+-------------+----------------+--------------+
       | GRANTEE     | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | PRIVILEGE_TYPE | IS_GRANTABLE |
       +-------------+---------------+--------------+------------+-------------+----------------+--------------+
       | 'user2'@'%' | def           | test1        | tbl1       | c1          | INSERT         | YES          |
       | 'user1'@'%' | def           | test1        | tbl1       | c1          | SELECT         | NO           |
       +-------------+---------------+--------------+------------+-------------+----------------+--------------+
       2 rows in set
       

       For more information about the fields in the information_schema.COLUMN_PRIVILEGES view, see information_schema.COLUMN_PRIVILEGES.

References

For more information about user privileges, see the following topics:

• Overview

• Grant direct privileges