MySQL user management and permissions settings

User Management

Mysql>use MySQL;

View

Mysql> select Host,user,password from user;

Create

Mysql> create user Zx_root identified by ' xxxxx '; Identified by will store plaintext password encryption as a hash value

Modify

Mysql>rename User Feng to Newuser;//mysql 5 can be used after updating the user table with update

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 time

Change 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;

granting permissions

Mysql> Grant Select on dmc_db.* to Zx_root;

Reclaim Permissions

Mysql> Revoke select on dmc_db.* from Zx_root; Error if no permissions exist

The above command can also be granted and reclaimed using multiple permissions, separated by commas between permissions

Mysql> Grant Select,update,delete, insert on dmc_db.* to Zx_root;

If you want to see the results immediately, use

Flush privileges;

Command update

You must give a message when setting permissions

1, permissions to grant

2, the database or table that is granted access

3, User name

Grant and revoke can control access at several levels

1, entire server, using grant all and revoke all

2, entire database, using on database.*

3, Feature table, using on database.table

4, a specific column

5, a specific stored procedure

Meaning of the value of the host column in the user table

% matches all hosts

localhost localhost will not be parsed into an IP address and connected directly via Unixsocket

The 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 127.0.0.1 with IPv4

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

Permissions	Description
All
Alter
Alter routine	Use ALTER procedure and drop procedure
Create
Create routine	Using the CREATE PROCEDURE
Create temporary tables	Using the Create temporary table
Create user
CREATE view
Delete
Drop
Execute	Using call and stored procedures

File	Using SELECT INTO outfile and load data infile
Grant option	You can use GRANT and revoke
Index	You can use the CREATE INDEX and drop index
Insert
Lock tables	Lock table
Process	Use Show full processlist
Reload	Using flush
Replication Client	Server location Access
Replocation slave	Used by replication slaves
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