mysql command line user management method sharing

After the installation of mysql, there will be a database named mysql, and the user table is user. The user management of mysql database is based on this table. Of course, there are also some tables, such as tables_priv, procs_priv, clumns_priv, information_schema, USER_PRIVILEGES and so on.

If we have mysql management tool phpmyadmin, we can manage users through the graphical interface, but what if we don't have phpmyadmin management tool? At this point, we can manage mysql users by executing the sql statement from the command line.

1. Add users

1, create user

Grammar:

CREATE USER user_specification
[, user_specification] ...

user_specification:
user [IDENTIFIED BY [PASSWORD] 'password']
Example:

 
mysql> create user '44'@'127.0.0.1'; // create 1 a 44 The user  
Query OK, 0 rows affected (0.00 sec) 

mysql> create user '33'@'localhost' identified by 'aaaa'; // create 1 a 33 User, password is aaaa 
Query OK, 0 rows affected (0.00 sec) 

mysql> select * from mysql.user where user='33' or user='44'\G; // To view 1 Under the mysql Under the user 

Although create user can create users, it only creates users and does not assign user permissions, so the grant command has replaced 1.

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 # requires an x509 certificate
| CIPHER 'cipher' # encryption algorithm
| ISSUER 'issuer' # certificate issuer
| SUBJECT 'subject' # theme

with_option:
GRANT OPTION
| MAX_QUERIES_PER_HOUR count # how many sql can be executed at most per hour
| MAX_UPDATES_PER_HOUR count # maximum number of data updates per hour
What is the maximum number of joins per hour for | MAX_CONNECTIONS_PER_HOUR count #
| MAX_USER_CONNECTIONS count # maximum number of user joins
Don't be frightened by the above grammar, in fact, it is easy to grasp what you understand. I understand it this way.

grant permissions on scope of application (database tables, methods, etc.)

Personally, as long as you remember a few red keywords above, basically this command is mastered.
Privilege Meaning ALL [PRIVILEGES] All permissions ALTER can be used with alter table ALTER ROUTINE You can use alter routine CREATE You can create databases and tables CREATE ROUTINE You can use create routine CREATE TEMPORARY TABLES You can use temporary tables CREATE USER Users can add, delete, rename, and revoke permissions CREATE VIEW You can create and modify views DELETE can delete data DROP May delete databases, tables, views, etc EVENT You can use the event scheduler EXECUTE You can execute routine FILE You can read and write files on the server GRANT OPTION Users have the right to authorize the clothes they add INDEX Indexes can be created and dropped INSERT You can insert LOCK TABLES You can lock the table PROCESS You can use SHOW PROCESSLIST to see the current mysql user performing sql REFERENCES Not implemented RELOAD You can use the refresh feature REPLICATION CLIENT Users can synchronize master and slave REPLICATION SLAVE When master and slave are synchronized, the slave server can read binary log from the master server SELECT can be found SHOW DATABASES You can use show databases to view all databases SHOW VIEW can be viewed using show view SHUTDOWN You can use the parameter shutdown in mysqladmin SUPER Enable use of other adminstrative operations such as CHANGE MASTER TO, KILL, PURGE BINARY LOGS, SET GLOBAL, and mysqladmin debug command TRIGGER You can use triggers UPDATE New operations can be performed USAGE No privilege
Example:

 
grant all ON test.* TO 'test'@'localhost'; //test A user has test All operations under the database  
grant select,update on test.user to 'test'@'localhost'; //test Users can test Under the database user Table for lookup and update operations  
//test The user's password is 111111 for user In the table name The field has read permissions, right id,name Have update rights  
grant select(name),update(id,name) on test.user to 'test'@'localhost' identified by '111111'; 
//test The user has full authority over all databases and requires it ssl encryption  
grant all privileges on *.* to 'test'@'%' identified by '123456' require ssl 

When you're done adding users, don't forget flush privileges;

2. Delete users

Grammar:

DROP USER user [, user] ...
Example:

 
drop user 'test2'@'localhost'; // When using drop Delete the user into, tables_priv,procs_priv The data in the wait table will also be deleted  

Why use 'test2'@'localhost' as the user name here, instead of test2 directly, because mysql.user is a table where the root user name and host name determine the user.

show create table mysql. user \ G; You'll notice that there's this thing called PRIMARY KEY (' Host ', 'User'), which is the union primary key

3. Modify the user

Grammar:

RENAME USER old_user TO new_user
[, old_user TO new_user] ...
Example:

 
rename user 'test2'@'localhost' to 'test'@'%'; 

4. Modify 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 users, and revoke takes permissions away from users.

Example:

 
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) 

Get rid of tank @localhost user update function, this is to get rid of 1 permissions, what if I want to get rid of all of them, it's too much trouble to write 1 by 1, see the following example

 
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 privileges of the user 33 @localhost

Author: sea eagle