Getting Started with MySQL Access Control System

MySQL implements a sophisticated access control and permissions system that allows you to create comprehensive access rules for handling client operations and effectively prevents unauthorized clients from accessing the database system.

1. When the client connects to the server, MySQL access control has two phases:

• Connection verification : Clients connecting to the MySQL database server need to have a valid username and password. Additionally, the host to which the client connects must match the host in the MySQL authorization table.

• Request Verification: When the connection is successfully established, for each statement issued by the client, MySQL will check whether the client has sufficient permissions to execute the specific statement. MySQL is able to check database, table and field level permissions

2. MySQL installer automatically creates a database named mysql.

 ​ mysql database contains five main authorization tables. You can indirectly operate these tables through statements such as GRANT and REVOKE

• user table: contains user accounts and global Permissions column. MySQL uses the user table to accept or reject connections from the host. Permissions granted in the user table are valid for all databases on the MySQL server.

• dbTable: Contains database-level permissions. MySQL uses database tables to determine which database and which host a user can access. Privileges granted at the database level in db tables apply to the database to which all objects belong, such as tables, triggers, views, stored procedures , etc.

• table_priv and columns_priv tables: Contains table-level and column-level permissions. Permissions granted in a table_priv table apply to the table and its columns, whereas permissions granted in a columns_priv table apply only to specific columns of the table.

• procs_privTable: Contains permissions for stored functions and stored procedures.

MySQL uses these tables to control the permissions of the MySQL database server. It's important to understand these tables before implementing your own flexible access control system.

Recommended: mysql tutorial

The above is the detailed content of Getting Started with MySQL Access Control System. For more information, please follow other related articles on the PHP Chinese website!