Shulou(Shulou.com)06/01 Report--

Editor to share with you what mysql permissions have, I hope you will gain a lot after reading this article, let's discuss it together!

Various permissions for mysql:

1.usage: connect (login) permission. If a user is established, he or she will be automatically granted usage permission (default grant). This permission can only be used for database login and cannot perform any operations. Moreover, usage permission cannot be recovered, that is, REVOKE users cannot delete users.

2.select: you must have select permission to use select table

3.create: you must have permission to create before you can use create table

4.create routine: you must have create routine permission to use {create | alter | drop} {procedure | function}. When create routine is granted, EXECUTE is automatically granted, and ALTER ROUTINE permission is given to its creator:

5.create temporary tables: (note that this is tables, not table) you must have permission to use create temporary tables before you can use create temporary tables

6.create view must have the permission of create view to use create view

7.create user: to use CREATE USER, you must have global CREATE USER permissions or INSERT permissions for the mysql database.

8.insert: you must have permission to use insert to use insert into. .. Values... .

9.alter: you must have permission to alter before you can use alter table

10. Alter routine: you must have permission to alter routine before you can use {alter | drop} {procedure | function}

Undefinedupdateundefined must have the permission of update to use update table

12. Delete: you must have the permission of delete to use delete from … .where... . (delete records in the table)

13. Drop: you must have the permission of drop to use drop database db_name; drop table tab_name

14. Show database: you can only see databases with certain permissions you have through show database, unless you have global SHOW DATABASES permissions.

15:show view: you must have show view permission to execute show create view. 16. Index: you must have index permission to execute [create | drop] index

17.execute: execute the existing Functions,Procedures

18.lock tables: you must have lock tables permission to use lock tables

19. References: with REFERENCES permission, users can use a field of another table as a foreign key constraint of a table.

20. Reload: you must have reload permission before you can execute flush [tables | logs | privileges]

21. Replication client: with this permission, you can query the status of master server and slave server.

22.replication slave has this permission to view the slave server and read the binary logs from the master server.

23. File: only with file permission can you execute select.. into outfile and load data infile... Operation, but do not grant file, process, super permissions to accounts other than the administrator, which has serious security risks.

24. Super: this permission allows the user to terminate any query; modify SET statements for global variables; and use CHANGE MASTER,PURGE MASTER LOGS.

25. Process: with this permission, users can execute SHOW PROCESSLIST and KILL commands. By default, each user can execute the SHOW PROCESSLIST command, but only this user's process can be queried

PS: administrative permissions (such as super, process, file, etc.) cannot specify a database. On must be followed by *. *

Let's first take a look at mysql two-phase verification:

Phase 1: the server will first check to see if you allow the connection. Because you add host restrictions when you create a user, you can limit the cost, a certain IP, a certain IP segment, and anywhere, and only allow you to log in from the specified place of the configuration.

Phase 2: if you can connect, Mysql will check every request you make to see if you have sufficient permissions to implement it. For example, if you want to update a table or query a table, Mysql will check to see if you have permissions on which table or column.

I. authorization

Mysql > grant all privileges on *. * to 'myuser'@'%' identified by' 123456 'with grant option

All privileges: means to grant all permissions to the user, or specify specific permissions, such as select,update,delete, etc.

On: indicates that these permissions take effect on those databases and tables. Format: dbname.tablename, all tables under all databases are written here.

To: Grant permissions to that user

'myuser'@'%':@ is followed by a restricted host, which can be IP, IP segment, domain name, and%, which means anywhere. Note: some versions here do not include local. You have previously set% for a user to log in anywhere, but you can't log in locally. This has something to do with the version. If you encounter this problem, just add a localhost user.

Identified by: specifies the login password of the user

With grant option: this option means that the user can delegate the permissions he has to others. Note: there are often people who create an operating user without specifying the WITH GRANT OPTION option so that the user cannot later use the GRANT command to create a user or authorize another user.

PS: you can add permissions to the user repeatedly using GRANT. For example, if you first add a select permission to the user, and then add an insert permission to the user, then the user will have both select and insert permissions.

1. Create a superuser: create a superuser U01 that only allows you to log in locally and allow permissions to be granted to other users

GRANT ALL PRIVILEGES ON *. * TO u01 localhost 'IDENTIFIED BY' 111111 'WITH GRANT OPTION

two。 Create a website user: (program user)

To create a general program user, this user may only need SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES and other permissions. If there is a stored procedure, you need to add EXECUTE permission, which usually specifies the 192.168.10 network segment of the private network.

GRANT USAGE,SELECT, INSERT, UPDATE, DELETE, SHOW VIEW, CREATE TEMPORARY TABLES,EXECUTE ON `test`.* TO webuser@'192.168.10.%' IDENTIFIED BY '123456'

3. Create a normal user: (query permissions only)

GRANT USAGE,SELECT ON `test`. * TO public@'192.168.10.%' IDENTIFIED BY '123456'

II. Authority management

1. Refresh permissions:

Use this command to make permissions effective, especially if you make update or delete updates to those permission tables such as user, db, host, etc. In the past, when the permissions were not updated after using grant, we can get into the habit of using the FLUSH PRIVILEGES command to refresh permissions as long as we make changes to the permissions.

FLUSH PRIVILEGES

two。 View permissions:

Use the following command to easily view the permissions of a user:

SHOW GRANTS FOR 'webuser'@'192.168.10.%'

3. Reclaim permissions:

Reclaim the DELETE permissions of the previously created webuser user, using the following command

REVOKE DELETE ON test.* FROM 'webuser'@'192.168.10.%'

4. Delete user

1. Delete user

DROP USER 'webuser'@'192.168.10.%'

Drop user deletes all the user's information, while delete only clears the user table, and other information, such as the db table, still exists. If a user with minimum privileges is created after delete, then he will reuse the previous permissions.

After reading this article, I believe you have a certain understanding of mysql permissions, want to know more related knowledge, welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.