MySQL user management and permissions settings

Tag:io   os    use    ar   for    data    sp   div   on   

User management mysql>use MySQL; view mysql> select Host,user,password from user ; creating mysql> Create user  zx_ Root   identified by ' xxxxx ';  //identified by will use plaintext password encryption as a hash value to store modifications mysql>rename   user  feng  to   newuser;//mysql 5 You need to remove Mysql>drop user newuser by updating the user table with update.  //MYSQL5 Before you remove a user, you must first use revoke to remove the user right, and then delete the user, mysql5 after the drop command can remove the user's associated permissions for the user to change the password mysql> set password  For zx_root =password (' xxxxxx '); mysql> update  mysql.user  set  password=password (' xxxx ')  where user= ' otheruser ' View User rights 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;  //If the permission does not exist error   The above command can also be granted and reclaimed using multiple permissions, and the permissions are separated by commas mysql> grant select,update,delete  ,insert   on dmc_db.*  to  zx_root; If you want to see results immediately using flush  privileges; command Update    Set permissions must be givenFor information 1, to grant permission 2, to be granted access to the database or table 3, the user name grant and revoke can control access at several levels of 1, the entire server, using the grant all   and revoke  ALL2, the entire database, using the on  database.*3, feature table, using on  DATABASE.TABLE4, specific column 5, specific stored procedure  user the meaning of the value of the host column in the 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, Representing 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
create  us Er	 
create view
delete	 
drop	 
execute	use call and stored procedures

Use SELECT INTO outfile   and load data infile

file
grant option
index
insert	 
lock tables	lock table
process	use show full processlist

reload	using flush
replication client	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