MySQL user management and permissions settings

Source: Internet
Author: User
Tags dba mysql index mysql view

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.