View user privileges

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

View user privileges by using SQL statements

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

2. Use the following methods to view user privileges:

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

     For example:

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

   • View the user-level privileges of a user.

     You can query the mysql.user view to view the user-level privileges of a user as an administrator. A regular user can query the mysql.user view to view its own user-level privileges.

     For example:

     obclient> SELECT * FROM mysql.user WHERE user='user1'\G;
     *************************** 1. row ***************************
                       host: %
                       user: user1
                   password: ******
                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 query the mysql.db view to view the database-level privileges of a user as an administrator. A regular user can query the mysql.db view to view its own database-level privileges.

     obclient> SELECT * FROM mysql.db WHERE user='user1'\G;
     *************************** 1. row ***************************
                      host: %
                        db: test
                      user: user1
               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 fields in the mysql.db view, see mysql.db.

View user privileges in the OCP console

1. Log on to the OceanBase Cloud Platform (OCP) console.

2. In the left-side navigation pane, click Tenants to go to the Tenants page.

3. In the tenant list, select a tenant whose Tenant Mode is MySQL to go to the Overview page.

4. In the left-side navigation pane, click User Management.

5. In the user list, find the target user and move the pointer over the System Permissions column to view the privileges of the user, or over the Roles column to view the roles assigned to the user.

   

More information

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

• Overview of user privileges

• Modify user privileges