View user privileges

After creating a user, you can view the privileges of the user.

Procedure

1. Log on to a MySQL tenant as the administrator.

2. Execute the following statements to view user privileges:

   • View the privileges granted to a user by using the SHOW GRANTS statement.

     Here is an example:

     obclient> SHOW GRANTS FOR test;
     +----------------------------------------+
     | Grants for test@%                     |
     +----------------------------------------+
     | GRANT INSERT, SELECT ON *.* TO 'test' |
     | GRANT SELECT ON `db1`.* TO 'test'     |
     +----------------------------------------+
     2 rows in set
     

   • View the user-level privileges of a user.

     Administrators can view the user-level privileges of a user through the mysql.user view. Regular users can view their own user-level privileges through 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 the mysql.user view, see mysql.user.

   • View the database-level privileges of a user.

     Administrators can view the database-level privileges of a user through the mysql.db view. Regular users can view their own database-level privileges through 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 mysql.db view, see mysql.db.

References

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

• Overview

• Modify user privileges