MySQL installed after good, there will be a database named MySQL, the user's table is user,mysql database user management is around this table, of course, there are some tables, such as: Tables_priv,procs_priv,clumns_priv, INFORMATION_SCHEMA database inside the user_privileges and so on.
If there is a MySQL management tool phpMyAdmin, we can use the graphical interface to manage users, but if there is no phpmyadmin such a management tool to do? At this time, we can execute SQL statements from the command line to manage MySQL users.
One, add users
1,create User
Grammar:
CREATE USER user_specification
[, User_specification] ...
User_specification:
user [identified by [PASSWORD] ' PASSWORD ']
Instance:
Copy Code code as follows:
mysql> create user ' 44 ' @ ' 127.0.0.1 '; Create a 44 user
Query OK, 0 rows Affected (0.00 sec)
mysql> create user ' @ ' localhost ' identified by ' AAAA '; Create a 33 user with a password of AAAA
Query OK, 0 rows Affected (0.00 sec)
Mysql> SELECT * from Mysql.user where user= ' or user= ' \g; Check the user under MySQL
Creating user can create users, but it simply creates users and does not assign permissions to users, so it is generally replaced by the grant command.
2,grant
Grammar:
GRANT
Priv_type [(column_list)]
[, Priv_type [(column_list)]] ...
On [object_type] Priv_level
To User_specification [, user_specification] ...
[REQUIRE {NONE | ssl_option [[and] ssl_option] ...}]
[With With_option ...]
Object_type:
TABLE
| FUNCTION
| PROCEDURE
Priv_level:
*
| *.*
| Db_name.*
| Db_name.tbl_name
| Tbl_name
| Db_name.routine_name
User_specification:
user [identified by [PASSWORD] ' PASSWORD ']
Ssl_option:
Ssl
| X509 #要求x509证书
| CIPHER ' CIPHER ' #加密算法
| Issuer ' issuer ' #证书发行商
| SUBJECT ' SUBJECT ' #主题
With_option:
GRANT OPTION
| Max_queries_per_hour Count # The maximum number of SQL executions per hour
| Max_updates_per_hour Count # Update the maximum number of data per hour
| Max_connections_per_hour Count # What is the maximum number of joins per hour?
| Max_user_connections Count # Maximum user join number
Do not be intimidated by the above grammar, in fact, understand what meaning, it is easy to grasp. That's how I understand it.
Grant permission on the scope of application (database tables, methods, etc.) to the user (separated by @, preceded by the user name is the hostname ' username ' @ ' hostname ') identified by password require what is required with the user's executed SQL control.
Personally feel, as long as remember the above a few red keywords, basically this command is mastered.
| Privilege |
meaning |
ALL [PRIVILEGES] |
All permissions |
ALTER |
可以使用alter table |
ALTER ROUTINE |
OK使用alter routine |
CREATE |
You can create databases and tables |
CREATE ROUTINE |
You can use the Create routine |
CREATE TEMPORARY TABLES |
You can use temporary tables |
CREATE USER |
Can add, delete, rename, revoke permissions on user |
CREATE VIEW |
You can create and modify views |
DELETE |
可以删除数据 |
DROP |
May delete database, table, view, etc. |
EVENT |
You can use the event height device |
EXECUTE |
can perform routine |
FILE |
Can read and write files on the server |
GRANT OPTION |
Users have the right to add their own clothing authorization |
INDEX |
You can create, delete indexes |
INSERT |
can be inserted |
LOCK TABLES |
Can lock table |
PROCESS |
can useSHOW PROCESSLIST来查看mysql当前用户的执行sql情况 |
REFERENCES |
Not implemented |
RELOAD |
You can use the Refresh feature |
REPLICATION CLIENT |
User can perform master-slave synchronization |
REPLICATION SLAVE |
From the server, you can read binary log from the master server when master-slave synchronization |
SELECT |
可以查找 |
SHOW DATABASES |
You can use Show databases to view all databases |
SHOW VIEW |
可以使用show view来查看视图 |
SHUTDOWN |
You can use the parameters in Mysqladmin shutdown |
SUPER |
Enable use of the other adminstrative operations such as,,, CHANGE MASTER TO KILL PURGE BINARY LOGS SET GLOBAL , and mysqladmin the Debug command |
TRIGGER |
You can use triggers |
UPDATE |
Can take care of the new operation |
USAGE |
No privileges |
Instance:
Copy Code code as follows:
Grant all on test.* to ' test ' @ ' localhost '; Test user owns all actions under the test database
Grant Select,update on Test.user to ' test ' @ ' localhost '; Test users can find and update the user table under the test database
Test user's password is 111111, has Read permission to the Name field in the user table, has the update permission to Id,name
Grant Select (name), update (id,name) on Test.user to ' test ' @ ' localhost ' identified by ' 111111 ';
Test user has all the rights to all databases and requires SSL encryption
Grant all privileges "*.* to ' test" @ '% ' identified by ' 123456 ' Require SSL
When the user is added, don't forget to flush privileges;
Two, delete user
Grammar:
DROP user user [, user] ...
Instance:
Copy Code code as follows:
Drop user ' test2 ' @ ' localhost '; Data in tables such as Tables_priv,procs_priv will be deleted when the user is deleted with drop
Why use ' test2 ' @ ' localhost ' as username, not direct test2, because mysql.user this table, is the root user name and host name to determine a user, you can look at the structure of the table to know.
Show create table mysql.user\g; you'll find this thing primary key (' Host ', ' user '), representing a federated primary key
Third, modify the user
Grammar:
RENAME USER Old_user to New_user
[, Old_user to New_user] ...
Instance:
Copy Code code as follows:
Rename user ' test2 ' @ ' localhost ' to ' test ' @ '% ';
Four, modify the permissions
Grammar:
REVOKE
Priv_type [(column_list)]
[, Priv_type [(column_list)]] ...
On [object_type] Priv_level
From user [, user] ...
REVOKE all privileges, GRANT OPTION
From user [, user] ...
Grant assigns permissions to the user, and revoke the permissions away from the user.
Instance:
Copy Code code as follows:
mysql> revoke update on *.* from ' tank ' @ ' localhost ';
Query OK, 0 rows Affected (0.00 sec)
Mysql> Select Mysql.user.update_priv from Mysql.user where user= ' tank ' and host= ' localhost ';
+-------------+
| Update_priv |
+-------------+
+-------------+
1 row in Set (0.00 sec)
Remove Tank@localhost This user's update function, this is to remove a permission, if I want to remove all how to do it, one write too troublesome, look at an example below
Copy Code code as follows:
Mysql> Revoke all privileges, grant option from ' @ ' @ ' localhost ';
Query OK, 0 rows Affected (0.00 sec)
mysql> flush Privileges;
Query OK, 0 rows affected (0.01 sec)
With all the privileges of 33@localhost this user
Author: Undersea Eagle
