Mysql command line user management method sharing _ MySQL

Source: Internet
Author: User
Tags mysql command line
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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.