MySQL permission control detailed explanation

mysql permission control

As a DBA, you must be familiar with permissions in MySQL. The control of permissions in MySQL is divided into three levels:

• Global management permissions, applicable to the entire MySQL instance
• Database-level permissions apply to a specific database or all databases.
• Database object-level permissions apply to specified database objects (tables, views, etc.) or all database objects.
  

Here, we list all the permissions in MySQL, and finally give a special case to reflect a small bug in MySQL permission control. First, let's look at the permission list. The permissions are arranged in alphabetical order:

•All/All Privileges
This permission represents all permissions at the global or database-wide object level.
•Alter
This permission allows you to modify the table structure, but it must be accompanied by the create and insert permissions.
If you want to rename a table, you must have the permissions to alter and drop the original table, and create and insert a new table.
•Alter routine
This permission allows you to modify or delete stored procedures and functions.
•Create
This permission allows you to create new databases and tables.
•Create routine
This permission allows you to create stored procedures and functions.
• Create tablespace
This permission allows you to create, modify, and delete tablespaces and log groups.
•Create temporary tables
This permission allows you to create a temporary table.
•Create user
This permission allows you to create, modify, delete, and rename users.
•Create view
This permission allows you to create a view.
•Delete
This permission allows you to delete row data.
•Drop
This permission allows you to delete databases, tables, and views, including the truncate table command.
•Event
This permission allows you to query, create, modify, and delete MySQL events.
•Execute
This permission allows the execution of stored procedures and functions.
•File
This permission allows reading and writing disk files in directories accessible to MySQL.
Available commands include load data infile, select ... into outfile, load file() function
• Grant option
This permission indicates whether to allow this user to authorize or revoke the permissions you have granted to other users.
•Index
This permission indicates whether to allow the creation and deletion of indexes.
•Insert
This permission indicates whether data can be inserted into the table. The insert permission is also required when executing analyze table, optimize table, and repair table statements.
•Lock
This permission allows you to lock a table with select permission to prevent other links from reading or writing to this table.
•Process
This permission allows you to view process information in MySQL, such as executing showprocesslist,
•Reference
This permission was introduced after version 5.7.6 and indicates whether to allow the creation of foreign keys.
•Reload
This permission allows the flush command to be executed, indicating that the permission table is reloaded into the system memory. The refresh command means closing and reopening the log file and refreshing all tables.
•Replication client
This permission allows the execution of show master status, show slave status, show binary logs commands.
•Replication slave
This permission allows the slave host to connect to the master through this user in order to establish a master-slave replication relationship.
•Select
This permission allows you to view data from the table, and the select permission is also required when executing update/delete statements that contain where conditions.
•Show databases
This permission means that you can view all database names by executing the show databases command.
•Show view
This permission means that you can view the statements created by the view by executing the show create view command, mysqladmin processlist, show engine, and other commands.
•Shutdown
This permission allows you to shut down the database instance. Execute statements including mysqladmin shutdown
•Super
This permission allows you to execute a series of database management commands, including the kill command to forcibly close a connection, the change master to command to create a replication relationship, and the create/alter/drop server command.
•Trigger
This permission allows you to create, delete, execute, and display triggers.
•Update
This permission allows you to modify the data in the table.
•Usage
This permission is the default permission after creating a user, which itself represents the connection login permission

Permission system table

The permissions are stored in the mysql database user, db, tables_priv, columns_priv, and procs_priv system tables, which are loaded into memory after the MySQL instance is started.

• User table:

Stores user account information and global level (all database) permissions, which determines which users from which hosts can access the database instance. If there is a global permission, it means that all databases have this permission
• Db table:

Stores database-level permissions, which determine which users from which hosts can access this database
• Tables_priv table:

Stores table-level permissions, which determine which users from which hosts can access this table in the database
• Columns_priv table:

Stores column-level permissions, which determine which users from which hosts can access this field in the database table
• Procs_priv table:

Stores stored procedure and function level permissions

How to make MySQL permissions take effect after modification

• After executing Grant, revoke, setpassword, or renameuser commands to modify permissions, MySQL will automatically load the modified permission information into the system memory.
• If you perform insert/update/delete operations on the system privilege table above, you must execute the refresh privilege command to synchronize it to the system memory. The refresh privilege commands include: flush privileges/mysqladmin flush-privileges/mysqladmin reload
• If you modify the permissions at the tables and columns level, the new permissions will take effect the next time the client operates.
• If you modify database-level permissions, the new permissions take effect after the client executes the use database command.
• If you modify the global level permissions, you need to re-create the connection for the new permissions to take effect
• --skip-grant-tables can skip all system permission tables and allow all users to log in. It is only used temporarily in special circumstances.

Granting and Revoking Permissions

When granting permissions, the grant statement is usually used. Take query as an example, the details are as follows:

grant select on db_name.table_name to user@host;

If you grant permissions to all tables in a database to an account, you need to change table_name in the above statement to *. If you grant permissions to all tables in all databases in the database, you need to change both db_name and table_name to *.

The revoke syntax is usually used to revoke permissions. Its usage is as follows, taking query as an example:

revoke select on db_name.table_name from user@host;

It should be noted that the difference between it and grant is that the last part of the statement is changed from to to from.

A special case

Today, when I was processing online business, there was a need to open permissions. I wanted to view the stored procedure from the workbench client. My first reaction was to directly grant execute permissions. As a result, after granting execute permissions, the business party still could not view the stored procedure. I went over to check and after connecting to the database, it was displayed as follows:

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
|AAA|
+--------------------+
2 rows in set (0.00 sec)
mysql> use AAA
Database changed
mysql> show procedure status\G
*************************** 1. row ***************************
     Db: CVS
    Name: ba_get_grow
    Type: PROCEDURE
    Definer: [email protected]
   Modified: 2019-02-26 ::
    Created: 2019-02-26 ::
  Security_type: DEFINER
    Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
 Database Collation: utf8_general_ci

mysql> show create procedure ba_get_grow\G
*************************** 1. row ***************************
   Procedure: ba_get_grow
   sql_mode:
 Create Procedure: NULL
character_set_client: utf8
collation_connection: utf8_general_ci
 Database Collation: utf8_general_ci
 row in set (0.00 sec)

It can be seen that the value of create procedure in line 29 is NULL, and the content of the procedure is not displayed. Of course, the procedure cannot be viewed. What should we do in this case?

I thought that the mysql.proc table contains information about stored procedures, so I gave the mysql.proc table a read-only permission using the following method:

[email protected]:(none) 22:35:07>>grant select on `mysql`.proc to 'user'@'host';
Query OK, 0 rows affected (0.00 sec)

Log in to the client again, and you can find that the contents of the stored procedure are already visible, as follows:

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
|AAA|
+--------------------+
2 rows in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
|AAA|
|mysql |
+--------------------+
3 rows in set (0.00 sec)

mysql> show create procedure ba_get_grow\G
*************************** 1. row ***************************
   Procedure: ba_get_grow
   sql_mode:
 Create Procedure: CREATE DEFINER=`dba_admin`@`127.0.0.1` PROCEDURE `ba_get_grow`(
 in p_table_info varchar(),
 out out_retmsg varchar()
)
BEGIN
 XXXXXXXXX
 XXXXXXXXX
 XXXXXXXXX
 XXXXXXXXX
END
character_set_client: utf8
collation_connection: utf8_general_ci
 Database Collation: utf8_general_ci
 row in set (0.00 sec)

The above is the detailed content of MySQL permission control. For more information about MySQL permission control, please pay attention to other related articles on 123WORDPRESS.COM!