Share user management methods under mysql Command Line

After mysql is installed, 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 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:
Copy codeThe Code is as follows:
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:
Copy codeThe Code is as follows:
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:
Copy codeThe Code is as follows:
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:
Copy codeThe Code is as follows:
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:
Copy codeThe Code is 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 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.
Copy codeThe Code is as follows:
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: sea bottom e