Database permission Assignment Operations

1. telnet to MySQL
Mysql-h ip-u root-p Password
2. Create a user
Format: Grant permissions on database. * To User name @ login host identified by "password";
Example 1: Add a test1 user, password 123456, can log on any host, and all databases have query, add, modify and delete functions. Need to be done under the root user of MySQL
Mysql>grant select,insert,update,delete On * * to [e-mail protected] "%" identified by "123456″;
Mysql>flush privileges;
Example 2: Add a test2 user, password is 123456, can only log on 192.168.2.12, and the database student have query, add, modify and delete functions. Need to be done under the root user of MySQL
Mysql>grant Select,insert,update,delete on student.* to [e-mail protected] identified by "123456″;
Mysql>flush privileges;
Example 3: Authorized user Test3 has all permissions for database student
Mysql>grant all privileges in student.* to [e-mail protected] identified by ' 123456′;
Mysql>flush privileges;
3. Modify User Password
Mysql>update Mysql.user Set Password=password (' 123456′) where user= ' Test1′and host= ' localhost ';
Mysql>flush privileges;
4. Delete a user
Mysql>delete from user where user= ' test2′and host= ' localhost ';
Mysql>flush privileges;
5. Deleting a database and deleting a table
Mysql>drop database name;
Mysql>drop table name;
6. Delete accounts and permissions
Drop user username @ '% '
Drop user Username @localhost
**************************************************************************************
Grant detailed parsing is as follows:
**************************************************************************************
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;
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 [e-mail protected]% identified by "Yeelion" allows connections from any host note: the "%" word character wildcard action, which matches the meaning of 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 [e-mail 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 [e-mail 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.
Instance:
1. Create a database
CREATE DATABASE ' fypay ' DEFAULT CHARACTER SET UTF8 COLLATE utf8_general_ci;
2. Add user Fypay to the created database
Grant Create,select,insert,update,delete,drop,alter on fypay.* to [e-mail protected] "%" identified by "Testfpay";
3. Delete Fypay Users
Delete from user where user= "Fypay"
drop user [email protected]
4. Refreshing the database
Flush privileges;

This article transferred from: http://blog.csdn.net/xuxile/article/details/53161908

Database permission Assignment Operations