The user management method in the mysql command line shares bitsCN. after installing commysql, there will be a database named mysql. the table for storing users is user, and the user management of mysql database is centered on this table. of course, there are also some tables, such: tables_priv, procs_priv, clumns_priv, and USER_PRIVILEGES in the information_schema database.
If there is a mysql management tool phpmyadmin, we can manage users through a graphical interface, but what if there is no such management tool as phpmyadmin? At this time, we can execute SQL statements through the command line to manage mysql users.
1. add a user
1, create user
Syntax:
Create user user_specification
[, User_specification]...
User_specification:
User [identified by [PASSWORD] 'password']
Instance:
Mysql> create user '44' @ '192. 0.0.1 '; // create a 44 user
Query OK, 0 rows affected (0.00 sec)
Mysql> create user '33' @ 'localhost' identified by 'AAA'; // create a 33 user with the password aaaa
Query OK, 0 rows affected (0.00 sec)
Mysql> select * from mysql. user where user = '33' or user = '44'/G; // Check the user in mysql.
Although the create user can be used to create a user, it only creates a user and does not assign sub-configuration permissions to the user. Therefore, it is generally replaced by the grant command.
2, grant
Syntax:
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 certificate required
| CIPHER 'cipher' # Encryption Algorithm
| ISSUER 'issuer' # certificate issuer
| SUBJECT 'subobject' # Topic
With_option:
GRANT OPTION
| MAX_QUERIES_PER_HOUR count # maximum number of SQL statements per hour
| MAX_UPDATES_PER_HOUR count # maximum number of data updates per hour
| MAX_CONNECTIONS_PER_HOUR count # maximum number of connections per hour
| MAX_USER_CONNECTIONS count # maximum number of user connections
Do not be intimidated by the above syntax. it is easy to understand what it means. I understand it in this way.
Grant permission on application scope (database tables, methods, etc.) to users (separated by @, followed by the username 'username' @ 'hostname ') identified by password require requires something with the number of SQL statements executed by the user.
I personally think that as long as I remember the red keywords above, basically this command will be mastered.
| Privilege |
Meaning |
ALL [PRIVILEGES] |
All permissions |
ALTER |
You can use alter table |
ALTER ROUTINE |
YesUse alter routine |
CREATE |
You can create databases and tables. |
CREATE ROUTINE |
You can use create routine |
CREATE TEMPORARY TABLES |
Temporary tables can be used. |
CREATE USER |
Users can be added, deleted, renamed, and revoked. |
CREATE VIEW |
You can create and modify views. |
DELETE |
Data can be deleted. |
DROP |
May delete databases, tables, views, etc. |
EVENT |
You can use the event height generator. |
EXECUTE |
You can execute routine |
FILE |
You can read and write files on the server. |
GRANT OPTION |
You have the right to authorize your clothes |
INDEX |
You can create or delete indexes. |
INSERT |
Yes |
LOCK TABLES |
You can lock a table. |
PROCESS |
AvailableShow processlist to view the SQL statement executed by the current mysql User |
REFERENCES |
Not implemented |
RELOAD |
You can use the refresh function. |
REPLICATION CLIENT |
You can perform master-slave synchronization. |
REPLICATION SLAVE |
During master-slave synchronization, the slave server can read binary logs from the master server. |
SELECT |
Available |
SHOW DATABASES |
You can use show databases to view all databases. |
SHOW VIEW |
You can use show view to view the view. |
SHUTDOWN |
You can use the shutdown parameter in mysqladmin. |
SUPER |
Enable use of other adminstrative operations suchCHANGE MASTER TO,KILL,PURGE BINARY LOGS,SET GLOBAL, AndMysqladmin debugCommand |
TRIGGER |
Trigger available |
UPDATE |
Allows you to ignore new operations. |
USAGE |
No privilege |
Instance:
Grant all ON test. * TO 'test' @ 'localhost'; // The test user has all the operations in the test database.
Grant select, update on test. user to 'test' @ 'localhost'; // You can search for and update the user table in the test database.
// The password of the test user is 111111. you have the permission to read the name field in the user table and to update the id and name fields.
Grant select (name), update (id, name) on test. user to 'test' @ 'localhost' identified by '123 ';
// The test user has all rights to all databases and requires ssl encryption
Grant all privileges on *. * to 'test' @ '%' identified by '000000' require ssl
After adding a user, do not forget flush privileges;
2. delete a user
Syntax:
Drop user user [, user]...
Instance:
Drop user 'test2' @ 'localhost'; // when you use drop to delete a user, data in tables such as tables_priv and procs_priv will also be deleted.
Here, why use 'test2' @ 'localhost' as the user name instead of test2, because mysql. the root user name and host name determine a user. you can check the table structure.
Show create table mysql. user/G; you will find this item primary key ('host', 'User'), indicating Union PRIMARY KEY
3. modify a user
Syntax:
Rename user old_user TO new_user
[, Old_user TO new_user]...
Instance:
Rename user 'test2' @ 'localhost' to 'test' @ '% ';
4. modify permissions
Syntax:
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 is used to assign permissions to the user. The revoke removes the permissions from the user.
Instance:
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 the update function of the tank @ localhost user. this is to remove a permission. what if I want to remove all the permissions? it is too troublesome to write one by one. let's look at the example below.
Mysql> revoke all privileges, grant option from '33' @ 'localhost ';
Query OK, 0 rows affected (0.00 sec)
Mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
Use all permissions of the user 33 @ localhost
Author: submarine cangying bitsCN.com
