View user permissions

After a user is created, you can view the permissions of the user as needed.

Prerequisites

• When you use the SHOW GRANTS statement to query the permissions of the current user, you do not need any permissions. If you need to query the permissions of other users, you must have the global SELECT permission.

• When you use a view to view the permissions of a user, you must have the SELECT permission on the view.

Procedure

1. Log in to seekdb as the administrator root.

2. View the permissions of a user.

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

     You can use the following statement to view the permissions granted to the current user:

     SHOW GRANTS;

     SHOW GRANTS FOR CURRENT_USER;

     SHOW GRANTS FOR CURRENT_USER();

     To view the permissions granted to a specified user, you can add the FOR clause to the statement. The statement is as follows:

     SHOW GRANTS FOR test;

     The 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 permissions of a user.

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

     Here is an example:

     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
              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 fields in the mysql.user view, see mysql.user.

   • View the database-level permissions of a user.

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

     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 permissions granted to a user.

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

     • Query the mysql.columns_priv view.

       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      | 2025-12-15 15:02:57 |
       | %    | test1 | user1 | tbl1       | c1          | Select      | 2025-12-15 11:05:21 |
       +------+-------+-------+------------+-------------+-------------+---------------------+
       2 rows in set

     • Query the information_schema.COLUMN_PRIVILEGES view.

       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 permissions and related operations, see the following topics:

• Overview of users and permissions

• Directly grant permissions