User Privilege Tables in MySQL

Q

Where Are User Privileges Stored on the Server in MySQL?

✍: FYIcenter.com

A

MySQL server has a system database, which hosts a number of system tables to system related information like user privileges. Depending on the scope levels, granted user privileges are stored in different tables:

  • "mysql.user" - Stores privileges granted at the global level.
  • "mysql.db" - Stores privileges granted at the database level.
  • "mysql.table_priv" - Stores privileges granted at the table level.
  • "mysql.columns_priv" - Stores privileges granted at the column level.
  • "mysql.procs_priv" - Stores privileges granted at the routine level.

The following tutorial exercise shows you an example of granted privileges at the table level:

>cd \mysql\bin
>mysql -u root -pretneciyf

mysql> SELECT db, user, create_priv, select_priv 
    -> FROM mysql.db;
+---------+-------+-------------+-------------+
| db      | user  | create_priv | select_priv |
+---------+-------+-------------+-------------+
| test    |       | Y           | Y           |
| test\_% |       | Y           | Y           |
| fyi     | guest | N           | Y           |
| faq     | qa    | Y           | N           |
+---------+-------+-------------+-------------+
4 rows in set (0.00 sec)

 

Managing User Accounts and Access Privileges in MySQL

⇒⇒MySQL Database Tutorials

2016-10-17, 179👍, 0💬