MySQL user management and permissions settings

User management mysql>use MySQL; View mysql> select Host,user,password fromuser;Creating mysql> Create User Zx_rootidentified by ' xxxxx '; Identified by will store plaintext password encryption as a hash valueAfter modifying Mysql>rename user Feng to Newuser;//mysql 5, you can use the Update user table before you delete mysql>drop user NewUser; MySQL5 before you delete a user, you must first use revoke to remove the user right, and then delete the user, mysql5 the drop command can delete the user's associated permissions at the same timeChange Password mysql> Set passwordFor zx_root =password (' xxxxxx ');mysql> Update Mysql.user set Password=password (' xxxx ') where user= ' otheruser ' View User rights mysql> show grants for Zx_roo t; Grant permission mysql> GrantSelect on dmc_db.* to zx_root; Reclaim Permissions mysql> Revoke select on dmc_db.* from Zx_root; If the permission does not exist, the above command can also be given and reclaimed using multiple permissions, and the permissions are separated by commas using a comma mysql> grantselect,update,delete, insert on dmc_db.* to zx_root; Use flush privileges If you want to see results immediately; command updateyou must give a message when setting permissions1, to grant permission 2, the database or table 3 that is granted access, the user nameGrant and revoke can control access at several levels1, entire server, using grant all and revoke ALL2, entire database, using on DATABASE.*3, feature table, using on DATABASE.TABLE4, specific column 5, specific stored proceduremeaning of the value of the host column in the user table% match all hosts localhost localhost will not be resolved to an IP address, directly via Unixsocket connection 127.0.0.1 will be connected via TCP/IP protocol and can only be accessed natively;:: 1 :: 1 is compatible with support IPv6, indicating the same as IPv4 127.0.0.1

Grant normal data user, the right to query, insert, UPDATE, delete all table data in the database.

Grant Select on testdb.* to [email protected] '% '

Grant insert on testdb.* to [email protected] '% '

Grant update on testdb.* to [email protected] '% '

Grant Delete on testdb.* to [email protected] '% '

Alternatively, replace it with a MySQL command:

Grant SELECT, INSERT, UPDATE, delete on testdb.* to [email protected] '% '

9>.grant database developers, creating tables, indexes, views, stored procedures, functions ... and other permissions.

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

Grant create on testdb.* to [email protected] ' 192.168.0.% ';

Grant ALTER on testdb.* to [email protected] ' 192.168.0.% ';

Grant drop on testdb.* to [email protected] ' 192.168.0.% ';

Grant operates MySQL foreign key permissions.

Grant references on testdb.* to [email protected] ' 192.168.0.% ';

Grant operates MySQL temp table permissions.

Grant create temporary tables on testdb.* to [email protected] ' 192.168.0.% ';

Grant operates MySQL index permissions.

Grant index on testdb.* to [email protected] ' 192.168.0.% ';

Grant operates the MySQL view, viewing the view source code permissions.

Grant CREATE view on testdb.* to [email protected] ' 192.168.0.% ';

Grant Show view on testdb.* to [email protected] ' 192.168.0.% ';

Grant operates MySQL stored procedures, function permissions.

Grant create routine on testdb.* to [email protected] ' 192.168.0.% '; --now, can show procedure status

Grant alter routine on TESTDB.* to [email protected] ' 192.168.0.% '; --now, can drop a procedure

Grant execute on testdb.* to [email protected] ' 192.168.0.% ';

10>.grant the normal DBA to manage permissions for a MySQL database.

Grant all privileges on TestDB to [email protected] ' localhost '

Where the keyword "privileges" can be omitted.

The 11>.grant advanced DBA manages permissions for all databases in MySQL.

Grant all on * * to [email protected] ' localhost '

12>. MySQL grant permissions can be used on multiple levels, respectively.

1. Grant acts on the entire MySQL server:

Grant SELECT On *. * to [email protected]; --DBAs can query tables in all databases in MySQL.

Grant all on * * to [email protected]; --DBA can manage all databases in MySQL

2. Grant acts on a single database:

Grant Select on testdb.* to [email protected]; --DBAs can query the tables in TestDB.

3. Grant acts on a single data table:

Grant SELECT, INSERT, UPDATE, delete on testdb.orders to [email protected];

4. Grant acts on the columns in the table:

Grant Select (ID, SE, rank) on testdb.apache_log to [email protected];

5. Grant acts on stored procedures, functions:

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

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

Note: Be sure to refresh the service after modifying the permissions, or restart the service, refresh the service by: Flush privileges. Permission Table

rights	description
all
alter	 
alter Routine
create	 
create routine

create temporary tables	use create temporary table
create  user	 
create view	 
delete	 
drop	 
execute	using call and stored procedure

file
grant option	can use GRANT and revoke
index
insert
lock tables	lock table
proce SS

reload	use flush
replication Clie NT	server location access
replocation slave
select	 
show databases	 
show view	 

Shutdown	Use mysqladmin shutdown to turn off MySQL
Super
Update
Usage	No access rights

MySQL user management and permissions settings