MySQL User management

1. mysql User management

' User ' @ ' host '; HOST:IP, hostname, NETWORK,% (any long character), _ (any single character) skip_name_resolve={on| OFF} Skip host name resolution [[email protected] ~]# vim/etc/my.cnf Skip_name_resolve=on

2. View the user
Example:

MariaDB [mysql]> SELECT User,host,password from User;

3. Create a user
CREATE user ' user ' @ ' host ' [identified by [PASSWORD] ' PASSWORD '] [, ' User ' @ ' host ' [identified by [PASSWORD] ' PASSWORD '] ...]
Example:

MariaDB [(None)]> CREATE USER ' tom ' @ ' 127.0.0.1 ' identified by ' Liumanlin ', ' Jerry ' @ ' percent ' identified by ' Liumanlin ';

4. Renaming: RENAME USER
RENAME USER Old_user to new_user[, Old_user to New_user] ...
Example:

MariaDB [mysql]> RENAME USER ' tom ' @ ' 127.0.0.1 ' to ' Jerry ' @ ' 172.18.%.% ';

5. Delete a user
DROP user ' user ' @ ' host ' [, ' User ' @ ' host '] ...
Example:

MariaDB [mysql]> DROP USER ' Jerry ' @ '% '; MariaDB [mysql]> DROP USER ' @ ' localhost ';

6. Let MySQL reload the authorization list
FLUSH privileges;
Example:

MariaDB [mysql]> FLUSH privileges;

7. Modify User Password
(1) SET PASSWORD [for ' user ' @ ' host '] = PASSWORD (' cleartext PASSWORD '); Password is a MySQL built-in cryptographic function
Example:

MariaDB [mysql]> SET PASSWORD for ' root ' @ ' localhost ' = PASSWORD (' Liumanlin '); MariaDB [mysql]> FLUSH privileges;

(2) UPDATE mysql.user SET password=password (' cleartext Password ') WHERE user= ' USERNAME ' and host= ' Host ';
Example:

MariaDB [mysql]> UPDATE user SET Password=password (' Liumanlin ') WHERE user= ' root ' and host= ' 127.0.0.1 '; MariaDB [mysql]> FLUSH privileges;

(3) mysqladmin-uusername-hhost-p password ' new_pass '
Example:

[Email protected] ~]# mysqladmin-h127.0.0.1-uroot-p password ' Liumanlin ';

8. Forget the Administrator password solution
(1) When starting the mysqld process, use the--skip-grant-tables and--skip-networking options
Example:
CentOS 7:

[Email protected] ~]# vim/usr/lib/systemd/system/mariadb.service execstart=/usr/bin/mysqld_safe--basedir=/usr-- Skip-grant-tables--skip-networking[[email protected] ~]# systemctl daemon-reload[[email protected] ~]# Systemctl Restart Mariadb.service

CentOS 6:

[[email protected] ~]# Vim/etc/init.d/mysqld

(2) Modify the administrator password with the update command
Example:

MariaDB [mysql]> UPDATE user SET Password=password (' Liumanlin ') WHERE user= ' root ' and host= ' 127.0.0.1 '; [Email protected] ~]# Vim/usr/lib/systemd/system/mariadb.serviceexecstart=/usr/bin/mysqld_safe--BASEDIR=/USR

(3) Start the mysqld process in a normal manner;
Example:

[Email protected] ~]# systemctl daemon-reload[[email protected] ~]# systemctl restart Mariadb.service

9. Authorization: Grant

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 (object type):
 table
 | FUNCTION
 | PROCEDURE
Priv_level:
 *
 | * * (All tables for all libraries)
 | db_name.* (all tables for the specified library)
 | Db_name.tbl_ Name (specify table for the specified library)
 | tbl_name (Specify table)
 | db_name.routine_name (Specify function for the specified library)
Ssl_option:
 ssl
 | X509
 | CIPHER ' CIPHER '
 | ISSUER ' ISSUER '
 | SUBJECT ' SUBJECT '     
with_option:
 grant option
 | Max_queries_per_hour Count
 | Max_updates_per_hour Count
 | Max_connections_per_hour Count
 | Max_user_connections Count
Example 1:

MariaDB [mysql]> GRANT CREATE on hidb.* to ' Jerry ' @ ' 172.18.%.% '; [[email protected] ~]# mysql-ujerry-h172.18.67.12-pmariadb [(none)]> CREATE DATABASE hidb; MariaDB [(None)]> use HIDB; MariaDB [hidb]> CREATE TABLE tbl1 (name CHAR (20)); MariaDB [hidb]> CREATE INDEX test on TBL1 (name);  ERROR 1142 (42000): Index command denied to the user ' Jerry ' @ ' 172.18.67.12 ' for table ' TBL1 ' (no permission to create an index, in the following way) MariaDB [mysql]> GRANT INDEX on hidb.* to ' Jerry ' @ ' 172.18.%.% '; MariaDB [mysql]> SHOW GRANTS for ' Jerry ' @ ' 172.18.%.% '; MariaDB [hidb]> CREATE INDEX test on TBL1 (name); (Authorization succeeded)

Example 2:

MariaDB [mysql]> CREATE USER ' tom ' @ ' 172.18.%.% ' identified by ' Liumanlin '; [[email protected] ~]# mysql-utom-h172.18.67.12-p (normal login) MariaDB [mysql]> GRANT all on hidb.* to ' Tom ' @ ' 172.18.%.% ' REQUIRE SSL; (Login with SSL authorization) MariaDB [mysql]> SHOW GRANTS for ' Tom ' @ ' 172.18.%.% '; MariaDB [mysql]> FLUSH privileges; [Email protected] ~]# mysql-utom-h172.18.67.12-penter password:error 1045 (28000): Access denied for user ' Tom ' @ ' 172. 18.67.12 ' (using Password:yes) (cannot connect, need to indicate SSL certificate)

Example 3:

MariaDB [mysql]> SHOW GLOBAL VARIABLES like '%ssl% '; +---------------+----------+| variable_name | Value |+---------------+----------+| Have_openssl | DISABLED | | Have_ssl | DISABLED | |          Ssl_ca | ||          Ssl_capath | ||          Ssl_cert | ||          Ssl_cipher | ||          Ssl_key | |+---------------+----------+

10. View Authorization: Show GRANTS
SHOW GRANTS [for ' user ' @ ' host ']
Example:

MariaDB [mysql]> SHOW GRANTS for ' Tom ' @ ' 172.18.%.% ';

11. Cancellation of Authorization: REVOKE

REVOKE Priv_type [(column_list)] [, Priv_type [(column_list)]] ... On [object_type] priv_levelfrom ' user ' @ ' host ' [, ' User ' @ ' host '] ... REVOKE all privileges, GRANT optionfrom user [, User] ...

Example:

MariaDB [mysql]> REVOKE CREATE VIEW on hidb.* from ' Tom ' @ ' 172.18.%.% '; MariaDB [mysql]> SHOW GRANTS for ' Tom ' @ ' 172.18.%.% '; MariaDB [mysql]> FLUSH privileges;

MySQL User management