Summary: In this tutorial you are getting to know MySQL Access Control System and various privileges tables in MySQL.
MySQL implemented a sophisticated access control and privilege system to allow you to create comprehensive access rules for handling client operations and prevent unauthorized clients from accessing the database system.
The MySQL access control has two stages when a client connects to the server:
- Connection verification: A client, which wants to connect to the MySQL database server, needs to have a valid username and password. In addition, the host, which the client connects from, has to be match with the host in the MySQL grant table.
- Request verification: Once a connection established successfully, MySQL checks the privileges to verify whether the client has sufficient privileges. It is possible to check privilege at database, table, and even field level.
There is a database called mysql is created automatically by MySQL installer. The mysql database contains five main grant tables:
- user: contains user account and global privileges columns. MySQL uses this table to either accept or reject connection from a host. A privilege granted in user table is effective to all databases on the server.
- db: contains database level privileges. MySQL uses this db table to determine which database user can access from which host. A privilege granted at database level in db table applies to the database and all objects belong to that database such as table, view, stored procedures.
- table_priv and columns_priv: contains table and column level privileges. A privilege granted in table_priv table applies to the table and all its columns while a privilege granted in columns_priv table applies only to a specific column.
- procs_priv: contains function and stored procedure privileges
All of privileges of MySQL are controlled by those grant tables. And from those grant tables you can create flexible access rules to meet your requirements.