MySQL使用者管理

1. MySQL使用者管理

'user'@'host';
 host:IP、主機名、NETWORK、%(任意長字元),_(任意單個字元)    
skip_name_resolve={ON|OFF} 跳過主機名解析
 [root@node2 ~]# vim /etc/my.cnf
 skip_name_resolve=ON
      

2.檢視使用者

示例：

MariaDB [mysql]> SELECT User,Host,Password FROM user;
      

3.建立使用者

CREATE USER  'user'@'host' [IDENTIFIED BY [PASSWORD] 'password'] [,'user'@'host' [IDENTIFIED BY [PASSWORD] 'password']...]

MariaDB [(none)]> CREATE USER 'tom'@'127.0.0.1' IDENTIFIED BY 'liumanlin' , 'jerry'@'%' IDENTIFIED BY 'liumanlin';
      

4.重命名：RENAME USER

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

MariaDB [mysql]> RENAME USER 'tom'@'127.0.0.1' TO 'jerry'@'172.18.%.%';
      

5.删除使用者

DROP USER 'user'@'host' [, 'user'@'host'] ...

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

6.讓MySQL重新加載授權清單

FLUSH PRIVILEGES;

MariaDB [mysql]> FLUSH PRIVILEGES;
      

7.修改使用者密碼

(1) SET PASSWORD [FOR 'user'@'host'] = PASSWORD('cleartext password'); PASSWORD是MySQL内建加密函數

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';

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'

[root@node2 ~]# mysqladmin -h127.0.0.1 -uroot -p password 'liumanlin';
      

8.忘記管理者密碼的解決辦法

(1) 啟動mysqld程序時，使用--skip-grant-tables和--skip-networking選項

CentOS 7：

[root@node2 ~]# vim /usr/lib/systemd/system/mariadb.service
 ExecStart=/usr/bin/mysqld_safe --basedir=/usr --skip-grant-tables --skip-networking
[root@node2 ~]# systemctl daemon-reload
[root@node2 ~]# systemctl restart mariadb.service
      

CentOS 6：

[root@node2 ~]# vim /etc/init.d/mysqld 同理
      

(2) 通過UPDATE指令修改管理者密碼

MariaDB [mysql]> UPDATE user SET Password=PASSWORD('liumanlin') WHERE User='root' AND Host='127.0.0.1';
[root@node2 ~]# vim /usr/lib/systemd/system/mariadb.service
ExecStart=/usr/bin/mysqld_safe --basedir=/usr
      

(3) 以正常方式啟動mysqld程序；

[root@node2 ~]# systemctl daemon-reload
[root@node2 ~]# systemctl restart mariadb.service
      

9.授權：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(對象類型):

 TABLE

 | FUNCTION

 | PROCEDURE

priv_level:

 *

 | *.*(所有庫的所有表)

 | db_name.*(指定庫的所有表)

 | db_name.tbl_name(指定庫的指定表)

 | tbl_name(指定表)

 | db_name.routine_name(指定庫的指定函數)

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

示例1：

MariaDB [mysql]> GRANT CREATE ON hidb.* TO 'jerry'@'172.18.%.%';
[root@node2 ~]# mysql -ujerry -h172.18.67.12 -p
MariaDB [(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 user 'jerry'@'172.18.67.12' for table 'tbl1' (無權建立索引，用以下方法)
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); (授權成功)
      

示例2：

MariaDB [mysql]> CREATE USER 'tom'@'172.18.%.%' IDENTIFIED BY 'liumanlin';
[root@node2 ~]# mysql -utom -h172.18.67.12 -p (可正常登入)
MariaDB [mysql]> GRANT ALL ON hidb.* TO 'tom'@'172.18.%.%' REQUIRE SSL; (使用ssl授權登入)
MariaDB [mysql]> SHOW GRANTS FOR 'tom'@'172.18.%.%';
MariaDB [mysql]> FLUSH PRIVILEGES;
[root@node2 ~]# mysql -utom -h172.18.67.12 -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'tom'@'172.18.67.12' (using password: YES) (無法連接配接，需指明ssl證書)
      

示例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.檢視授權：SHOW GRANTS

SHOW GRANTS [FOR 'user'@'host']

MariaDB [mysql]> SHOW GRANTS FOR 'tom'@'172.18.%.%';
      

11.取消授權：REVOKE

REVOKE  priv_type [(column_list)][, priv_type [(column_list)]] ...
ON [object_type] priv_level
FROM  'user'@'host' [,  'user'@'host'] ...
REVOKE ALL PRIVILEGES, GRANT OPTION
FROM user [, user] ...
      

MariaDB [mysql]> REVOKE CREATE VIEW ON hidb.* FROM 'tom'@'172.18.%.%';
MariaDB [mysql]> SHOW GRANTS FOR 'tom'@'172.18.%.%';
MariaDB [mysql]> FLUSH PRIVILEGES;