MySQL Rights Management

MySQL change user permissions

This entry is posted by admin Monday, April, 2010

1. "Grant all on * * to [E- Mail protected]'% ' identified by ' YourPassword ';" --This can also be set by passing the password.
2. "Flush privileges; "--refresh for permission to take effect.
Some other MySQL management can be used with the mysqladmin command. Can be used to set a password or something.

More information on Grant can be seen in the following reprint:
This example runs on MySQL version 5.0 and above.

The simple format that MySQL gives to user rights commands can be summarized as:

Grant permissions on database object to user

A grant general 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]'% '

II. Grant Database Developer, 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, you can drop a procedure
Grant execute on testdb.* to [email protected]' 192.168.0.% ';

Grant General DBA manages permissions for a MySQL database.

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

Where the keyword "privileges" can be omitted.
Grant Advanced DBA manages permissions for all databases in MySQL.

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

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

1. Grant acts on the entire MySQL server:

Grant SELECT On *. * to [email protected]; -DBA 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]; -DBA can query the table 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 '

Vi. Viewing MySQL user rights

To view the current user (own) permissions:

Show grants;

To view additional MySQL user rights:

Show grants for [email protected];

Vii. revoke permissions that have been given to MySQL users.

Revoke is similar to Grant's syntax, just replace the keyword "to" with "from":

Grant all on * * to [email protected];
Revoke all on * * from [email protected];

Viii. MySQL Grant, REVOKE user rights considerations

1. Grant, after revoke user rights, the user has to reconnect to the MySQL database for the permission to take effect.

2. If you want to allow authorized users, you can also grant these permissions to other users, you need the option "grant option"

Grant SELECT on testdb.* to [e-mail protected] with GRANT option;

This feature is generally not available. In practice, database permissions are best managed centrally by DBAs.

Category:post
You can follow any responses to this entry via RSS.
Comments is currently closed, but can trackback from your own site.

=========================================================================

1. Create a user and authorize

Syntax for the GRANT statement:

Grant privileges (columns) on the What to the user identified by ' Password ' with GRANT option
To use this sentence pattern, you need to make sure that the fields are:

Privileges permission specifier permissions allowed actions
Alter Modify tables and indexes
Create DATABASE and tables
Delete Deletes existing records in the table
Drop Discard (delete) database and table
index Create or Discard indexes
Insert inserts a new row into the table
Reference not used
Select to retrieve records from a table
Update modifies existing table records
File reads or writes files on the server
Process to view thread information or kill threads that are executing on the server
Reload overloads the authorization table or empties the log, host cache, or table cache.
Shutdown shutting down the server
All privileges synonyms
Usage Special "No permissions" permission

The above permissions are divided into three groups:

First group: Applies to databases, tables, and columns such as: Alter create delete DROP INDEX Insert Select Update

Second group: Number of administrative permissions they allow users to influence the operation of the server to be strictly authorized such as: File process reload shut*

Third group: Privilege all means "All permissions" Uasge means no permissions, that is, create users, but do not grant permissions

Columns

Permissions apply the column (optional) and you can only set column-specific permissions. If the command has more than one column, you should separate them with commas.

What

The level at which permissions are applied. Permissions can be global, fixed database, or a specific table.

User

Permissions granted by a user, consisting of a user name and host name, two users of the same name are connected from different places. Default: MySQL user password

The password that is given to the user (optional), if you do not specify the identified by clause for the user, the user password does not change.

With identified by, the password string uses the literal meaning of the password instead, and grant will code the password for you.

Note: Set password uses the password () function
With GRANT option

User can grant permissions to other users via Grant statement (optional)

Example Explanation:

Grant all on db_book.* to [e-mail protected] identified by "yeelion" can only be connected locally

Grant all on db_book.* to [e-mail protected] identified by "Yeeliong" allows connections from this domain

Grant all on db_book.* to [email protected]% identified by "Yeelion" allows connections from any host

Note: the "%" character character wildcard function, the same meaning as the like pattern.

Grant all on db_book.* to [e-mail protected]%.koowo.com identified by "yeelion";

Allow huaying to connect from any host in the koowo.com domain

Grant all on db_book.* to [e-mail protected] identified by "Yeelion"

Grant all on db_book.* to [email protected]% identified by "Yeelion"

Grant all on db_book.* to [e-mail protected]/17 identified by "Yeelion"

Allow login from single IP segment IP or one subnet IP

Note: Sometimes users @ip need quotes such as "[email protected]/17"

Grant all on * * to [E- Mail protected] identified by ' yeelion ' with GRANT option

Add Superuser huaying can do anything on the local login.

Grant Reload on * * to [E- Mail protected] identified by "yeelion" gives only reload permissions

Grant all on Db_book to [e-mail protected] indetified by ' yeelion ' all rights

Grant SELECT on Db_book to [email protected]% indetified by "yeelion" read-only permission

Grant Select,insert,delete,update on Db_book to [e-mail protected] indetified by "Yeelion"

Only the permissions of Select,insert,delete,update

Grant SELECT on Db_book.storybook to [e-mail protected] indetified by "yeelion" only to the table

Grant update (name) on Db_book.storybook to [email protected] does not change the Name column password for the table

Grant Update (Id,name,author) on Db_book.storybook to [email protected] only for multiple columns of the table

Grant all on book.* to "" @koowo. com allows all users in the koowo.com domain to use library book

Grant all on book.* to [e-mail protected]%.koowo.com indetified by ' yeelion ' with GRANT option

Allows huaying to administer authorization to all tables of the library book.

2. Withdraw the rights and delete the user

Revoke syntax is similar to the GRANT statement

To is substituted by and without the indetifed by and with GRANT OPTION clauses. As follows:

Revoke privileges (columns) on how from user

User: You must match the user portion of the original grant statement that you want to withdraw.

Privileges: No match is required, authorization is granted with the grant statement, and only partial permissions are revoked with the REVOKE statement.

Revoke statement only delete the permissions do not delete the user, revoke all permissions after the user record is retained in the user table, users can still connect to the server.

To completely remove a user, the user record must be explicitly removed from the users table with a DELETE statement:

Delete from user where user= "huaying"

Flush privileges; Overloaded Authorization table

Note: When using the GRANT and REVOKE statements, the table is automatically overloaded and not when you modify the authorization table directly.

Http://www.cnblogs.com/llsun/archive/2013/08/06/3240963.html

　　　　

MySQL Rights Management