Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to analyze Mysql rights management. The article is rich in content and analyzed and described from a professional point of view. I hope you can get something after reading this article.

The Mysql account permission information is stored in the mysql database user, db, host, table_priv, colunms_priv, and procs_ privtables, and the server reads the contents of these database tables into memory when Mysql starts.

Mysql permission levels are mainly divided into: server, database, table, column. The use environment according to permissions is mainly divided into: ordinary permissions, administrator permissions, special permissions, in which ordinary permissions are mainly applied to the application link database, while administrator permissions are mainly used for server management.

Mysql permission list details:

Classification permissions describe general permissions at the application level

(application) CREATE allows users to create databases or tables, tables or indexes DROP allows users to delete databases or tables database or tables GRANT OPTION allows users to grant permissions to databases, tables or saved programs ALTER allows users to change table structure tables DELETE allows users to delete rows of existing tables INDEX allows users to create, Modify table index table INSERT allows user to insert new record table SELECT allows user to view table record table UPDATE allows user to modify existing record table in table CREATE VIEW allows user to create view view SHOW VIEW allows user to view view create statement view ALTER ROUTINE allows user to modify stored procedure, function saved program CREATE ROUTINE allows user to create stored procedure, The program EXECUTE saved by the function allows the user to allow the program administrator FILE saved with the created subroutine to allow the user to use select. Into outfile, load data infile read data from a file into a table or read from a table into a file access CREATE TEMPORARY TABLES on a file server host allows users to create temporary table servers manage LOCK TABLES allows users to use LOCK TABLES server management CREATE USER allows users to use CREATE USER,DORP USER,RENAME USER REVOKE ALL PRIVILEGES server management PROCESS allows users to use show processlist to view thread server management RELOAD allows users to use flush, overload authorization table, empty authorization, host, Server management REPLICATION CLIENT such as logs allows users to ask the slave server or host server address server management REPLICATION SLAVE for master-slave replication slave server (read binary log files from the master server) server management SHOW DATABASES allows you to view a list of all databases using show databases Without this permission, the user can only see that the database server administration SHUTDOWN with permissions allows the use of mysqladmin shutdown to shut down the mysql server administration SUPER allows the use of change master,kill,purge master logs and set global statements, the mysqladmin debug command, when the database reaches max_connections allows one connection server management special all\ all perivileges grants all permissions server administration usage only allows users to log in, but does not grant permissions to server administration

The simple format of the command granted by MySQL to the user can be summarized as follows:

Grant permissions on database objects to users, grant ordinary data users, the right to query, insert, update and delete all table data in the database. Grant select on testdb.* to common_user@'%'grant insert on testdb.* to common_user@'%'grant update on testdb.* to common_user@'%'grant delete on testdb.* to common_user@'%'

Or, replace it with a MySQL command:

Grant select, insert, update, delete on testdb.* to common_user@'%'

Second, grant database developers, create tables, indexes, views, stored procedures, functions. Wait for permission.

Grant creates, modifies, and deletes MySQL data table structure permissions.

Grant create on testdb.* to developer@'192.168.0.%';grant alter on testdb.* to developer@'192.168.0.%';grant drop on testdb.* to developer@'192.168.0.%'

Grant manipulates MySQL foreign key permissions.

Grant references on testdb.* to developer@'192.168.0.%';grant manipulates MySQL temporary table permissions. Grant create temporary tables on testdb.* to developer@'192.168.0.%';grant manipulates MySQL index permissions. Grant index on testdb.* to developer@'192.168.0.%';grant manipulates MySQL view and view view source code permissions. Grant create view on testdb.* to developer@'192.168.0.%';grant show view on testdb.* to developer@'192.168.0.%'

Grant manipulates MySQL stored procedures and function permissions.

Grant create routine on testdb.* to developer@'192.168.0.%';-- now, can show procedure statusgrant alter routine on testdb.* to developer@'192.168.0.%';-- now, you can drop a proceduregrant execute on testdb.* to developer@'192.168.0.%'; III, grant the permission of an ordinary DBA to manage a MySQL database. Grant all privileges on testdb to dba@'localhost'

The keyword "privileges" can be omitted.

4. Grant Advanced DBA manages the permissions of all databases in MySQL. Grant all on *. * to dba@'localhost' V and MySQL grant permissions can be used at multiple levels, respectively.

1. Grant acts on the entire MySQL server:

Grant select on *. * to dba@localhost;-- dba can query tables in all databases in MySQL. Grant all on *. * to dba@localhost;-- dba can manage all databases in MySQL

2. Grant acts on a single database:

Grant select on testdb.* to dba@localhost;-- dba can query tables in testdb.

3. Grant acts on a single data table:

Grant select, insert, update, delete on testdb.orders to dba@localhost

4. Grant acts on the columns in the table:

Grant select (id, se, rank) on testdb.apache_log to dba@localhost

5. Grant acts on stored procedures and functions:

Grant execute on procedure testdb.pr_add to 'dba'@'localhost'grant execute on function testdb.fn_add to' dba'@'localhost' VI. View MySQL user permissions

View current user (own) permissions:

Show grants

View additional MySQL user rights:

Show grants for dba@localhost; 7. Revoke the permissions that have been granted to MySQL users.

The syntax of revoke is similar to that of grant, except that you can replace the keyword "to" with "from":

Grant all on *. * to dba@localhost;revoke all on *. * from dba@localhost; VIII, MySQL grant, revoke user rights considerations

1. After the grant, revoke user permission, the user will not take effect until the user reconnects to the MySQL database.

two。 If you want authorized users, you can also grant these permissions to other users. You need the option "grant option".

Grant select on testdb.* to dba@localhost with grant option

This feature is generally not needed. In practice, database permissions are best managed by DBA.

-

The syntax of the authorization command GRANT statement is as follows:

GRANT privileges (columns)

ON what

TO user IDENTIFIEDBY "password"

WITH GRANT OPTION

Authorize users

Mysql > grant rights on database.* to user@host identified by "pass"

Example 1:

Add a user's test1 password to abc, so that he can log in on any host and have the authority to query, insert, modify and delete all databases.

Grant select,insert,update,delete on *. * to test1@ "%" Identified by "abc"

The *. * specifier in the ON clause means "all databases, all tables"

Example 2:

Add a user test2 password to abc, so that he can only log in on localhost, and can query, insert, modify and delete the database mydb.

Grant select,insert,update,delete on mydb.* to test2@localhost identified by "abc"

Example 3

Add a user custom who can connect from the host localhost, server.domain and whitehouse.gov. He only wants to access the bankaccount database from localhost, the expenses database from whitehouse.gov and the customer database from all three hosts. He wants to use the password stupid from all three hosts.

To set the permissions of an individual user using the Grant statement, run these commands:

Shell > mysql-- user=root mysql

Mysql > GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP

ON bankaccount.* TO custom@localhost IDENTIFIED BY 'stupid'

Mysql > GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP

ON expenses.* TO custom@whitehouse.gov IDENTIFIED BY 'stupid'

Mysql > GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP

ON customer.* TO custom@'%' IDENTIFIED BY 'stupid'

=

Permission information is stored in the mysql database using the user, db, host, tables_priv, and columns_priv tables (that is, in a database named mysql).

Permission column Context

Select Select_priv table

Insert Insert_priv table

Update Update_priv table

Delete Delete_priv table

Index Index_priv table

Alter Alter_priv table

Create Create_priv database, table, or index

Drop Drop_priv database or table

Grant Grant_priv database or table

References References_priv database or table

Reload Reload_priv server management

Shutdown Shutdown_priv server management

Process Process_priv server management

File access of file File_priv on the server

1.select, insert, update, and delete permissions allow you to perform operations on an existing table in a database, which are basic permissions

2.alter permission allows you to use ALTER TABLE

3.create and drop permissions allow you to create new databases and tables, or to discard (delete) existing databases and tables. If you grant drop permission to a mysql database to a user, the user can discard the database that stores MySQL access!

4.grant permissions allow you to grant those permissions you own to other users.

You cannot explicitly specify that a given user should be denied access. That is, you can't obviously match a user and then refuse to connect. You cannot specify that a user has the right to create or discard tables in a database, nor can you create or discard the database itself. Many individual permissions that are granted can be listed at the same time.

For example, if you want users to read and modify the contents of an existing table, but do not allow you to create a new table or delete a table, you can authorize it as follows:

GRANT SELECT,INSERT,DELETE,UPDATE ON samp_db.* TO 'user'@'%' IDENTIFIEDBY "pass"

The above is the text that I copied from other places and modified slightly. Write some things that need to be paid attention to below.

The above is the editor for you to share how to analyze Mysql rights management, if you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.