MySQL User management and permission management _ MySQL

MySQL User management and permission management show grants for wzhao;
Grant ALL on *. * to 'wzhao' @ '% ';

----------------------------------------------------
GRANT SELECT , INSERT , UPDATE , DELETE , CREATE , DROP , RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES , INDEX , ALTER , SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE , REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'shsadmin' @ '%' IDENTIFIED BY 'f,d,r5080' WITH GRANT OPTION ;


GRANT SELECT , INSERT , UPDATE , DELETE , CREATE , DROP , RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES , INDEX , ALTER , SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE , REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'shsadmin' @ 'localhost' IDENTIFIED BY 'f,d,r5080' WITH GRANT OPTION ;

User management mysql> use mysql; view mysql> select host, user, password from user; create mysql> create user zx_root IDENTIFIED by 'xxxxx '; // identified by encrypts the plain text password as a hash value storage and modifies mysql> rename user feng to newuser; // mysql 5 can be used, before using update to update the user table to delete mysql> drop user newuser, you must use revoke to delete the user permission before using update to update the user table, after mysql5, the drop command can delete the user and delete the user's related permissions. change the password mysql> set password for zx_root = password ('xxxxxx ');
Mysql> update mysql. user set password = password ('xxxx') where user = 'otheruser'
View user permissions mysql> show grants for zx_root; grant permissions mysql> grant select on dmc_db. * to zx_root; revoke permission mysql> revoke select on dmc_db. * from zx_root; // if the permission does not exist, an error is returned. the preceding command can also be used to grant and revoke multiple permissions. separate the permissions by commas (,) from mysql> grant select, update, delete, insert on dmc_db. * to zx_root; if you want to see the result immediately, use flush privileges. Information 1 must be provided when the command is used to update the configuration permission. for permission 2 to be granted, the database or table 3 to be granted the access permission, the user name grant and revoke can control access permission 1 in several layers. the whole server uses grant ALL and revoke ALL2, the whole database uses on database. * 3. feature table, on database. table4, specific column 5, meaning of the value of the host column in the specific stored procedure user table % matching all hosts localhost will not be resolved to an IP address, directly connected to 127.0.0.1 through UNIXsocket will be connected through TCP/IP protocol, and can only be accessed on the local machine;: 1: 1 is compatible with ipv6, indicating 127.0.0.1 of the same ipv4

Grant normal 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 @ '%'

Alternatively, replace the following with a MySQL command:

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

9>. grant database developers to create tables, indexes, views, stored procedures, and functions... .

Grant permissions to create, modify, and delete MySQL data table structures.

Grant create on testdb. * to developer @ '192. 192.% ';

Grant alter on testdb. * to developer @ '192. 192.% ';

Grant drop on testdb. * to developer @ '192. 192.% ';

Grant the MySQL foreign key operation permission.

Grant references on testdb. * to developer @ '192. 192.% ';

Grant the permission to operate MySQL temporary tables.

Grant create temporary tables on testdb. * to developer @ '2017. 192.% ';

Grant the permission to operate MySQL indexes.

Grant index on testdb. * to developer @ '192. 192.% ';

Grant permissions to operate the MySQL view and view the source code.

Grant create view on testdb. * to developer @ '192. 192.% ';

Grant show view on testdb. * to developer @ '192. 192.% ';

Grant permissions to operate MySQL stored procedures and functions.

Grant create routine on testdb. * to developer @ '192. 192.% '; -- now, can show procedure status

Grant alter routine on testdb. * to developer @ '192. 192.% '; -- now, you can drop a procedure

Grant execute on testdb. * to developer @ '192. 192.% ';

10>. grant common DBA permission to manage a MySQL database.

Grant all privileges on testdb to dba @ 'localhost'

The keyword "privileges" can be omitted.

11>. grant Senior DBA permission to manage all databases in MySQL.

Grant all on *. * to dba @ 'localhost'

12>. MySQL grant permissions can be applied to multiple levels.

1. grant applies to 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 applies to a single database:

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

3. grant applies to a single data table:

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

4. grant applies to columns in the table:

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

5. grant applies to stored procedures and functions:

Grant execute on procedure testdb. pr_add to 'dba '@ 'localhost'

Grant execute on function testdb. fn_add to 'dba '@ 'localhost'

Note: after modifying the permissions, you must refresh the service or restart the service to use flush privileges.