MySQL - User Privilege Info

Objective: MySQL - Collect Privilege Information for a user.

Excerise:

 
Test User creation:

Command: create user test_grantall identified by 'test_grantall';

mysql> create user test_grantall identified by 'test_grantall';
Query OK, 0 rows affected (0.01 sec)

Command: grant all on *.* to test_grantall with grant option (a global privilege);

mysql>  grant all on *.* to test_grantall with grant option;
Query OK, 0 rows affected (0.00 sec)
mysql>

To list the users created in mysql:

mysql> select user from mysql.user;
+------------------+
| user             |
+------------------+
| test_grantall    |
| debian-sys-maint |
| mysql.session    |
| mysql.sys        |
| root             |
+------------------+
5 rows in set (0.00 sec)
mysql>

Examine the privileges granted:

mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql>

mysql> select * from USER_PRIVILEGES;
+--------------------------------+---------------+-------------------------+--------------+
| GRANTEE                        | TABLE_CATALOG | PRIVILEGE_TYPE          | IS_GRANTABLE |
+--------------------------------+---------------+-------------------------+--------------+
...|
| 'test_grantall'@'%'            | def           | SELECT                  | YES          |
| 'test_grantall'@'%'            | def           | INSERT                  | YES          |
| 'test_grantall'@'%'            | def           | UPDATE                  | YES          |
| 'test_grantall'@'%'            | def           | DELETE                  | YES          |
| 'test_grantall'@'%'            | def           | CREATE                  | YES          |
| 'test_grantall'@'%'            | def           | DROP                    | YES          |
| 'test_grantall'@'%'            | def           | RELOAD                  | YES          |
| 'test_grantall'@'%'            | def           | SHUTDOWN                | YES          |
| 'test_grantall'@'%'            | def           | PROCESS                 | YES          |
| 'test_grantall'@'%'            | def           | FILE                    | YES          |
| 'test_grantall'@'%'            | def           | REFERENCES              | YES          |
| 'test_grantall'@'%'            | def           | INDEX                   | YES          |
| 'test_grantall'@'%'            | def           | ALTER                   | YES          |
| 'test_grantall'@'%'            | def           | SHOW DATABASES          | YES          |
| 'test_grantall'@'%'            | def           | SUPER                   | YES          |
| 'test_grantall'@'%'            | def           | CREATE TEMPORARY TABLES | YES          |
| 'test_grantall'@'%'            | def           | LOCK TABLES             | YES          |
| 'test_grantall'@'%'            | def           | EXECUTE                 | YES          |
| 'test_grantall'@'%'            | def           | REPLICATION SLAVE       | YES          |
| 'test_grantall'@'%'            | def           | REPLICATION CLIENT      | YES          |
| 'test_grantall'@'%'            | def           | CREATE VIEW             | YES          |
| 'test_grantall'@'%'            | def           | SHOW VIEW               | YES          |
| 'test_grantall'@'%'            | def           | CREATE ROUTINE          | YES          |
| 'test_grantall'@'%'            | def           | ALTER ROUTINE           | YES          |
| 'test_grantall'@'%'            | def           | CREATE USER             | YES          |
| 'test_grantall'@'%'            | def           | EVENT                   | YES          |
| 'test_grantall'@'%'            | def           | TRIGGER                 | YES          |
| 'test_grantall'@'%'            | def           | CREATE TABLESPACE       | YES          |
+--------------------------------+---------------+-------------------------+--------------+
86 rows in set (0.00 sec)
mysql>

Since we didnt suffix any hostname, the test_grantall user has all the hosts to connect from.
In summary the grant we made to the user was:

mysql> SHOW GRANTS FOR test_grantall;
+----------------------------------------------------------------------+
| Grants for test_grantall@%                                           |
+----------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'test_grantall'@'%' WITH GRANT OPTION |
+----------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>

Quick check, what happens if we remove a subset of privilege from "ALL".

revoke reload on *.* from test_grantall;

mysql> revoke reload on *.* from test_grantall;
Query OK, 0 rows affected (0.00 sec)
mysql>

So, the grants now breaks the ALL privilege - RELOAD and shows the individual grants you get from ALL as below...

mysql> SHOW GRANTS FOR test_grantall;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for test_grantall@%                                                                                                                                                                                                                                                                                                                                                |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO 'test_grantall'@'%' WITH GRANT OPTION |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>

Regranting the revoked privilege reconstructs the ALL privilege:

mysql> grant reload on *.* to test_grantall;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR test_grantall;
+----------------------------------------------------------------------+
| Grants for test_grantall@%                                           |
+----------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'test_grantall'@'%' WITH GRANT OPTION |
+----------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>

Simillarly granting an object privilege in a schema or database:

mysql> use test20apr23;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

mysql> show tables;
+-----------------------+
| Tables_in_test20apr23 |
+-----------------------+
| test1                 |
+-----------------------+
1 row in set (0.00 sec)

mysql> grant insert,update,select on test1 to test_grantall;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for test_grantall;
+------------------------------------------------------------------------------+
| Grants for test_grantall@%                                                   |
+------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'test_grantall'@'%' WITH GRANT OPTION         |
| GRANT SELECT, INSERT, UPDATE ON `test20apr23`.`test1` TO 'test_grantall'@'%' |
+------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql>

To grant a database level privilege:

mysql> grant create on test20apr23.* to test_grantall;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for test_grantall;
+------------------------------------------------------------------------------+
| Grants for test_grantall@%                                                   |
+------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'test_grantall'@'%' WITH GRANT OPTION         |
| GRANT CREATE ON `test20apr23`.* TO 'test_grantall'@'%'                       |
| GRANT SELECT, INSERT, UPDATE ON `test20apr23`.`test1` TO 'test_grantall'@'%' |
+------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
mysql>

This closes the blog.

Thank you!