MySQL/MariaDB数据库的用户和权限管理
作者：尹正杰
版权声明：原创作品，谢绝转载！否则将追究法律责任。
一.元数据数据库(mysql)
系统授权表(均在mysql数据库中):
db
host
user:
存放用户的表。
columns_priv
tables_priv
procs_priv
proxies_priv
MariaDB [yinzhengjie]> SELECT user,host FROM mysql.user; #查看MySQL实例安全初始化后的默认用户 +------+-----------+ | user | host | +------+-----------+ | root | 127.0.0.1 | | root | ::1 | | root | localhost | +------+-----------+ 3 rows in set (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]>
二.用户账号
Mysql账号由两部分组成，即用户名加主机名。具体格式为:'USERNAME'@'HOST' 说明: USERNAME表示用户名称; @表示默认的分隔符; HOST表示主机名称;
三.用户管理
1>.创建用户
MariaDB [yinzhengjie]> HELP CREATE USER Name: 'CREATE USER' Description: Syntax: CREATE USER user_specification [, user_specification] ... user_specification: user [ IDENTIFIED BY [PASSWORD] 'password' | IDENTIFIED WITH auth_plugin [AS 'auth_string'] ] The CREATE USER statement creates new MySQL accounts. To use it, you must have the global CREATE USER privilege or the INSERT privilege for the mysql database. For each account, CREATE USER creates a new row in the mysql.user table and assigns the account no privileges. An error occurs if the account already exists. Each account name uses the format described in https://mariadb.com/kb/en/create-user#account-names. For example: CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass'; If you specify only the user name part of the account name, a host name part of '%' is used. The user specification may indicate how the user should authenticate when connecting to the server: o To enable the user to connect with no password (which is insecure), include no IDENTIFIED BY clause: CREATE USER 'jeffrey'@'localhost'; In this case, the account uses built-in authentication and clients must provide no password. o To assign a password, use IDENTIFIED BY with the literal plaintext password value: CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass'; The account uses built-in authentication and clients must match the given password. o To avoid specifying the plaintext password if you know its hash value (the value that PASSWORD() would return for the password), specify the hash value preceded by the keyword PASSWORD: CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY PASSWORD '*90E462C37378CED12064BB3388827D2BA3A9B689'; The account uses built-in authentication and clients must match the given password. o To authenticate the account using a specific authentication plugin, use IDENTIFIED WITH, where auth_plugin is the plugin name. It can be an unquoted name or a quoted string literal. 'auth_string' is an optional quoted string literal to pass to the plugin. The plugin interprets the meaning of the string, so its format is plugin specific. Consult the documentation for a given plugin for information about the authentication string values it accepts. CREATE USER 'jeffrey'@'localhost' IDENTIFIED WITH my_auth_plugin; For connections that use this account, the server invokes the named plugin and clients must provide credentials as required for the authentication method that the plugin implements. If the server cannot find the plugin, either at account-creation time or connect time, an error occurs. IDENTIFIED WITH can be used as of MySQL 5.5.7. The IDENTIFIED BY and IDENTIFIED WITH clauses are mutually exclusive, so at most one of them can be specified for a given user. For additional information about setting passwords, see https://mariadb.com/kb/en/create-user/. URL: https://mariadb.com/kb/en/create-user/ MariaDB [yinzhengjie]> MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT user,host FROM mysql.user; +------+-----------+ | user | host | +------+-----------+ | root | 127.0.0.1 | | root | ::1 | | root | localhost | +------+-----------+ 3 rows in set (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> CREATE USER jason@'172.30.1.%' IDENTIFIED BY 'yinzhengjie'; #创建一个默认权限为"USAGE"权限用户，即该用户仅有登录连接数据库权限。 Query OK, 0 rows affected (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> SELECT user,host FROM mysql.user; +-------+------------+ | user | host | +-------+------------+ | root | 127.0.0.1 | | jason | 172.30.1.% | | root | ::1 | | root | localhost | +-------+------------+ 4 rows in set (0.00 sec) MariaDB [yinzhengjie]>
[root@node101.yinzhengjie.org.cn ~]# mysql -ujason -pyinzhengjie -h 172.30.1.105 #测试用户是否可以正常登录 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 9 Server version: 5.5.5-10.2.19-MariaDB MariaDB Server Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> mysql> STATUS -------------- mysql Ver 8.0.18 for Linux on x86_64 (MySQL Community Server - GPL) Connection id: 9 Current database: Current user: jason@172.30.1.101 SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.5.5-10.2.19-MariaDB MariaDB Server Protocol version: 10 Connection: 172.30.1.105 via TCP/IP Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: utf8mb4 Conn. characterset: utf8mb4 TCP port: 3306 Uptime: 19 min 19 sec Threads: 8 Questions: 19 Slow queries: 0 Opens: 21 Flush tables: 1 Open tables: 15 Queries per second avg: 0.016 -------------- mysql> SELECT USER(); +--------------------+ | USER() | +--------------------+ | jason@172.30.1.101 | +--------------------+ 1 row in set (0.00 sec) mysql> mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | +--------------------+ 1 row in set (0.00 sec) mysql>
2>.用户重命名
MariaDB [yinzhengjie]> HELP RENAME USER Name: 'RENAME USER' Description: Syntax: RENAME USER old_user TO new_user [, old_user TO new_user] ... The RENAME USER statement renames existing MySQL accounts. To use it, you must have the global CREATE USER privilege or the UPDATE privilege for the mysql database. An error occurs if any old account does not exist or any new account exists. Each account name uses the format described in https://mariadb.com/kb/en/create-user/#account-names. For example: RENAME USER 'jeffrey'@'localhost' TO 'jeff'@'127.0.0.1'; If you specify only the user name part of the account name, a host name part of '%' is used. RENAME USER causes the privileges held by the old user to be those held by the new user. However, RENAME USER does not automatically drop or invalidate databases or objects within them that the old user created. This includes stored programs or views for which the DEFINER attribute names the old user. Attempts to access such objects may produce an error if they execute in definer security context. (For information about security context, see https://mariadb.com/kb/en/stored-routine-privileges/.) The privilege changes take effect as indicated in http://dev.mysql.com/doc/refman/5.5/en/privilege-changes.html. URL: https://mariadb.com/kb/en/rename-user/ MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT user,host FROM mysql.user; +-------+------------+ | user | host | +-------+------------+ | root | 127.0.0.1 | | jason | 172.30.1.% | | root | ::1 | | root | localhost | +-------+------------+ 4 rows in set (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> RENAME USER jason@'172.30.1.%' TO jason@'192.168.100.105'; #将用户重新命名 Query OK, 0 rows affected (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> SELECT user,host FROM mysql.user; +-------+-----------------+ | user | host | +-------+-----------------+ | root | 127.0.0.1 | | jason | 192.168.100.105 | | root | ::1 | | root | localhost | +-------+-----------------+ 4 rows in set (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]>
3>.删除用户
MariaDB [yinzhengjie]> HELP DROP USER Name: 'DROP USER' Description: Syntax: DROP USER user [, user] ... The DROP USER statement removes one or more MySQL accounts and their privileges. It removes privilege rows for the account from all grant tables. To use this statement, you must have the global CREATE USER privilege or the DELETE privilege for the mysql database. Each account name uses the format described in https://mariadb.com/kb/en/create-user#account-names. For example: DROP USER 'jeffrey'@'localhost'; If you specify only the user name part of the account name, a host name part of '%' is used. URL: https://mariadb.com/kb/en/drop-user/ MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT user,host FROM mysql.user; +-------+-----------------+ | user | host | +-------+-----------------+ | root | 127.0.0.1 | | jason | 192.168.100.105 | | root | ::1 | | root | localhost | +-------+-----------------+ 4 rows in set (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> DROP USER jason@'192.168.100.105'; #删除'jason'@'192.168.100.105'用户 Query OK, 0 rows affected (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> SELECT user,host FROM mysql.user; +------+-----------+ | user | host | +------+-----------+ | root | 127.0.0.1 | | root | ::1 | | root | localhost | +------+-----------+ 3 rows in set (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]>
4>.修改密码
MariaDB [yinzhengjie]> HELP SET PASSWORD Name: 'SET PASSWORD' Description: Syntax: SET PASSWORD [FOR user] = { PASSWORD('cleartext password') | OLD_PASSWORD('cleartext password') | 'encrypted password' } The SET PASSWORD statement assigns a password to an existing MySQL user account. When the read_only system variable is enabled, the SUPER privilege is required to use SET PASSWORD, in addition to whatever other privileges might be required. If the password is specified using the PASSWORD() or OLD_PASSWORD() function, the cleartext (unencrypted) password should be given as the argument to the function, which hashes the password and returns the encrypted password string. If the password is specified without using either function, it should be the already encrypted password value as a literal string. In all cases, the encrypted password string must be in the format required by the authentication method used for the account. The old_passwords system variable value determines the hashing method used by PASSWORD(). If you specify the password using that function and SET PASSWORD rejects the password as not being in the correct format, it may be necessary to set old_passwords to change the hashing method. For descriptions of the permitted values, see https://mariadb.com/kb/en/server-system-variables#old_passwords. With no FOR user clause, this statement sets the password for the current user. (To see which account the server authenticated you as, invoke the CURRENT_USER() function.) Any client who successfully connects to the server using a nonanonymous account can change the password for that account. With a FOR user clause, this statement sets the password for the named user. You must have the UPDATE privilege for the mysql database to do this. The user account name uses the format described in https://mariadb.com/kb/en/create-user#account-names. The user value should be given as 'user_name'@'host_name', where 'user_name' and 'host_name' are exactly as listed in the User and Host columns of the mysql.user table row. (If you specify only a user name, a host name of '%' is used.) For example, to set the password for an account with User and Host column values of 'bob' and '%.example.org', write the statement like this: SET PASSWORD FOR 'bob'@'%.example.org' = PASSWORD('cleartext password'); URL: https://mariadb.com/kb/en/set-password/ MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT user,host,password FROM mysql.user; +-------+------------+-------------------------------------------+ | user | host | password | +-------+------------+-------------------------------------------+ | root | localhost | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | | jason | 172.30.1.% | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | | root | 127.0.0.1 | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | | root | ::1 | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | +-------+------------+-------------------------------------------+ rows in set (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> SET PASSWORD FOR jason@'172.30.1.%' = PASSWORD('centos8'); #为jason@'172.30.1.%'用户修改密码为"centos8"。 Query OK, 0 rows affected (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> SELECT user,host,password FROM mysql.user; +-------+------------+-------------------------------------------+ | user | host | password | +-------+------------+-------------------------------------------+ | root | localhost | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | | jason | 172.30.1.% | *515CE264AF5A9076EC5876689206AB3C06E20340 | | root | 127.0.0.1 | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | | root | ::1 | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | +-------+------------+-------------------------------------------+ rows in set (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]>
[root@node101.yinzhengjie.org.cn ~]# mysql -ujason -pyinzhengjie -h 172.30.1.105 #使用之前的密码无法登录 mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'jason'@'172.30.1.101' (using password: YES) [root@node101.yinzhengjie.org.cn ~]# [root@node101.yinzhengjie.org.cn ~]# mysql -ujason -pcentos8 -h 172.30.1.105 #使用修改后的密码登录成功 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 13 Server version: 5.5.5-10.2.19-MariaDB MariaDB Server Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> STATUS -------------- mysql Ver 8.0.18 for Linux on x86_64 (MySQL Community Server - GPL) Connection id: 13 Current database: Current user: jason@172.30.1.101 SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.5.5-10.2.19-MariaDB MariaDB Server Protocol version: 10 Connection: 172.30.1.105 via TCP/IP Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: utf8mb4 Conn. characterset: utf8mb4 TCP port: 3306 Uptime: 42 min 52 sec Threads: 8 Questions: 57 Slow queries: 0 Opens: 21 Flush tables: 1 Open tables: 15 Queries per second avg: 0.022 -------------- mysql> mysql>
MariaDB [yinzhengjie]> SELECT user,host,password FROM mysql.user; +-------+------------+-------------------------------------------+ | user | host | password | +-------+------------+-------------------------------------------+ | root | localhost | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | | jason | 172.30.1.% | *515CE264AF5A9076EC5876689206AB3C06E20340 | | root | 127.0.0.1 | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | | root | ::1 | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | +-------+------------+-------------------------------------------+ 4 rows in set (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> UPDATE mysql.user SET password=PASSWORD('yinzhengjie') WHERE user = 'jason'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> SELECT user,host,password FROM mysql.user; +-------+------------+-------------------------------------------+ | user | host | password | +-------+------------+-------------------------------------------+ | root | localhost | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | | jason | 172.30.1.% | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | | root | 127.0.0.1 | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | | root | ::1 | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | +-------+------------+-------------------------------------------+ 4 rows in set (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> FLUSH PRIVILEGES; #若使用上面的方式修改密码需要执行该条指令才能生效。 Query OK, 0 rows affected (0.00 sec) MariaDB [yinzhengjie]>
5>.忘记管理员密码的解决办法
启动mysqld进程时，为其使用如下选项： skip-grant-tables
skip-networking
使用UPDATE命令修改管理员密码
关闭mysqld进程，移除上述两个选项，重启mysqld
[root@node105.yinzhengjie.org.cn ~]# cat /mysql/3306/etc/my.cnf #修改该配置文件记得重启MySQL实例 [mysqld] character-set-server=utf8mb4 skip-grant-tables #跳过授权表验证 skip-networking #不启用网络服务，即仅能使用套接字方式连接MySQL数据库 port = 3306 datadir = /mysql/3306/data socket = /mysql/3306/socket/mysql.sock [mysqld_safe] log-error = /mysql/3306/log/mariadb.log pid-file = /mysql/3306/pid/mariadb.pid [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/mysql/3306/etc/my.cnf &> /dev/null & #手动启动MySQL [1] 4485 [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# ps -ef | grep mysql #发现进程存在就正常啦 root 4485 3428 0 07:10 pts/0 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/mysql/3306/etc/my.cnf mysql 4573 4485 2 07:10 pts/0 00:00:00 /usr/local/mysql/bin/mysqld --defaults-file=/mysql/3306/etc/my.cnf --basedir=/usr/local/mysql --d atadir=/mysql/3306/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/mysql/3306/log/mariadb.log --pid-file=/mysql/3306/pid/mariadb.pid --socket=/mysql/3306/socket/mysql.sock --port=3306root 4605 3428 0 07:10 pts/0 00:00:00 grep --color=auto mysql [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# ss -ntl #由于我们开启了"skip-networking"参数，因此不会对外暴漏默认的3306端口，外界就无法连接咱们正在维护的数据库啦。 State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 128 *:22 *:* LISTEN 0 128 :::22 :::* [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# ll /mysql/3306/pid/ #MySQL的pid文件 total 4 -rw-rw---- 1 mysql mysql 5 Oct 29 07:10 mariadb.pid [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# cat /mysql/3306/pid/mariadb.pid #该文件保存的id就是mariadb的进程id号，发现和上面ps命令的查询结果一致 4573 [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# ll /mysql/3306/socket/ #虽然说我们无法通过端口连接了，但是由于咱们在维护数据库，可以直接使用socket套接字的方式连接数据库哟~ total 0 srwxrwxrwx 1 mysql mysql 0 Oct 29 07:10 mysql.sock [root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysql -S /mysql/3306/socket/mysql.sock Welcome to the MariaDB monitor. Commands end with ; or g. Your MariaDB connection id is 8 Server version: 10.2.19-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. MariaDB [(none)]> MariaDB [(none)]> UPDATE mysql.user SET password=PASSWORD('yinzhengjie') WHERE user = 'root'; #修改root密码后理论上我们得刷新权限，但考虑后面我们仍要重启MySQL实例，因此此处通过修改表的方式修改管理员root密码后直接修改MySQL服务的配置文件并重启服务即可。 Query OK, 0 rows affected (0.00 sec) Rows matched: 3 Changed: 0 Warnings: 0 MariaDB [(none)]> MariaDB [(none)]> QUIT Bye [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# pkill mysql #密码修改成功后我们杀死Mysql进程 [root@node105.yinzhengjie.org.cn ~]# [1]+ Done /usr/local/mysql/bin/mysqld_safe --defaults-file=/mysql/3306/etc/my.cnf &>/dev/null [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# vim /mysql/3306/etc/my.cnf [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# cat /mysql/3306/etc/my.cnf #修改配置文件，将之前的配置删除掉。 [mysqld] character-set-server=utf8mb4 port = 3306 datadir = /mysql/3306/data socket = /mysql/3306/socket/mysql.sock [mysqld_safe] log-error = /mysql/3306/log/mariadb.log pid-file = /mysql/3306/pid/mariadb.pid [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/mysql/3306/etc/my.cnf &> /dev/null & #再次手动启动MySQL，当然也可以使用脚本启动哟~ [1] 4749 [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# mysql -S /mysql/3306/socket/mysql.sock #我们发现此时若没有密码则无法登录MySQL数据库啦 ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie -S /mysql/3306/socket/mysql.sock #使用我们修改后的root密码登录数据库即可。 Welcome to the MariaDB monitor. Commands end with ; or g. Your MariaDB connection id is 10 Server version: 10.2.19-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. MariaDB [(none)]> MariaDB [(none)]> STATUS -------------- mysql Ver 15.1 Distrib 10.2.19-MariaDB, for Linux (x86_64) using readline 5.1 Connection id: 10 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server: MariaDB Server version: 10.2.19-MariaDB MariaDB Server Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: utf8mb4 Conn. characterset: utf8mb4 UNIX socket: /mysql/3306/socket/mysql.sock Uptime: 45 sec Threads: 7 Questions: 6 Slow queries: 0 Opens: 17 Flush tables: 1 Open tables: 11 Queries per second avg: 0.133 -------------- MariaDB [(none)]>
四.MySQL权限管理
1>.权限类别
管理类： CREATE TEMPORARY TABLES CREATE USER FILE SUPER SHOW DATABASES RELOAD SHUTDOWN REPLICATION SLAVE REPLICATION CLIENT LOCK TABLES PROCESS
程序类:FUNCTION(函数),PROCEDURE(存储过程),TRIGGER（触发器） CREATE ALTER DROP EXCUTE 数据库和级别:DATABASE(数据库),TABLE(表) ALTER CREATE CREATE VIEW DROP INDEX SHOW VIEW GRANT OPTION：能将自己获得的权限转赠给其他用户 数据操作： SELECT INSERT DELETE UPDATE 字段级别： SELECT(col1,col2,...) UPDATE(col1,col2,...) INSERT(col1,col2,...) 所有权限： ALL PRIVILEGES 或者 ALL
2>.授权案例
MariaDB [(none)]> HELP GRANT Name: 'GRANT' Description: 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 ...] GRANT PROXY ON user_specification TO user_specification [, user_specification] ... [WITH GRANT 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' | IDENTIFIED WITH auth_plugin [AS 'auth_string'] ] 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 The GRANT statement grants privileges to MySQL user accounts. GRANT also serves to specify other account characteristics such as use of secure connections and limits on access to server resources. To use GRANT, you must have the GRANT OPTION privilege, and you must have the privileges that you are granting. Normally, a database administrator first uses CREATE USER to create an account, then GRANT to define its privileges and characteristics. For example: CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass'; GRANT ALL ON db1.* TO 'jeffrey'@'localhost'; GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost'; GRANT USAGE ON *.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90; However, if an account named in a GRANT statement does not already exist, GRANT may create it under the conditions described later in the discussion of the NO_AUTO_CREATE_USER SQL mode. The REVOKE statement is related to GRANT and enables administrators to remove account privileges. See [HELP REVOKE]. When successfully executed from the mysql program, GRANT responds with Query OK, 0 rows affected. To determine what privileges result from the operation, use SHOW GRANTS. See [HELP SHOW GRANTS]. URL: https://mariadb.com/kb/en/grant/ MariaDB [(none)]>
MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | yinzhengjie | +--------------------+ 4 rows in set (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> GRANT ALL ON yinzhengjie.* TO jason@'172.30.1.%'; #将yinzhengjie数据库下的所有表的所有权限授权给jason@'172.30.1.%'用户。 Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]>
[root@node101.yinzhengjie.org.cn ~]# mysql -ujason -pcentos8 -h 172.30.1.105 #客户端测试权限是否生效 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 11 Server version: 5.5.5-10.2.19-MariaDB MariaDB Server Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | yinzhengjie | +--------------------+ 2 rows in set (0.01 sec) mysql> use yinzhengjie Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> mysql> SHOW TABLES; +-----------------------+ | Tables_in_yinzhengjie | +-----------------------+ | classes | | coc | | courses | | scores | | students | | teachers | | toc | +-----------------------+ 7 rows in set (0.00 sec) mysql> mysql> DROP TABLE toc; Query OK, 0 rows affected (0.01 sec) mysql> mysql> SHOW TABLES; +-----------------------+ | Tables_in_yinzhengjie | +-----------------------+ | classes | | coc | | courses | | scores | | students | | teachers | +-----------------------+ 6 rows in set (0.00 sec) mysql>
MariaDB [yinzhengjie]> SELECT * FROM students; #查看待测试表的数据 +-------+---------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+---------------+-----+--------+---------+-----------+ | 1 | Shi Zhongyu | 22 | M | 2 | 3 | | 2 | Shi Potian | 22 | M | 1 | 7 | | 3 | Xie Yanke | 53 | M | 2 | 16 | | 4 | Ding Dian | 32 | M | 4 | 4 | | 5 | Yu Yutong | 26 | M | 3 | 1 | | 6 | Shi Qing | 46 | M | 5 | NULL | | 7 | Xi Ren | 19 | F | 3 | NULL | | 8 | Lin Daiyu | 17 | F | 7 | NULL | | 9 | Ren Yingying | 20 | F | 6 | NULL | | 10 | Yue Lingshan | 19 | F | 3 | NULL | | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | | 12 | Wen Qingqing | 19 | F | 1 | NULL | | 13 | Tian Boguang | 33 | M | 2 | NULL | | 14 | Lu Wushuang | 17 | F | 3 | NULL | | 15 | Duan Yu | 19 | M | 4 | NULL | | 16 | Xu Zhu | 21 | M | 1 | NULL | | 17 | Lin Chong | 25 | M | 4 | NULL | | 18 | Hua Rong | 23 | M | 7 | NULL | | 19 | Xue Baochai | 18 | F | 6 | NULL | | 20 | Diao Chan | 19 | F | 7 | NULL | | 21 | Huang Yueying | 22 | F | 6 | NULL | | 22 | Xiao Qiao | 20 | F | 1 | NULL | | 23 | Ma Chao | 23 | M | 4 | NULL | | 24 | Xu Xian | 27 | M | NULL | NULL | | 25 | Sun Dasheng | 100 | M | NULL | NULL | +-------+---------------+-----+--------+---------+-----------+ 25 rows in set (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> SHOW TABLES; +-----------------------+ | Tables_in_yinzhengjie | +-----------------------+ | classes | | coc | | courses | | scores | | students | | teachers | +-----------------------+ 6 rows in set (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> DESC students; +-----------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------------------+------+-----+---------+----------------+ | StuID | int(10) unsigned | NO | PRI | NULL | auto_increment | | Name | varchar(50) | NO | | NULL | | | Age | tinyint(3) unsigned | NO | | NULL | | | Gender | enum('F','M') | NO | | NULL | | | ClassID | tinyint(3) unsigned | YES | | NULL | | | TeacherID | int(10) unsigned | YES | | NULL | | +-----------+---------------------+------+-----+---------+----------------+ 6 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT user,host,password FROM mysql.user; +-------+------------+-------------------------------------------+ | user | host | password | +-------+------------+-------------------------------------------+ | root | localhost | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | | jason | 172.30.1.% | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | | root | 127.0.0.1 | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | | root | ::1 | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | +-------+------------+-------------------------------------------+ 4 rows in set (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> GRANT SELECT(Name,Age),UPDATE(Name) ON yinzhengjie.students TO centos@'172.30.1.%' IDENTIFIED BY 'yinzhengjie'; #将yinzhengjie.students表的部分权限授权给centos@'172.30.1.%'用户，若该用户不存在则自动创建。 Query OK, 0 rows affected (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> SELECT user,host,password FROM mysql.user; +--------+------------+-------------------------------------------+ | user | host | password | +--------+------------+-------------------------------------------+ | root | localhost | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | | jason | 172.30.1.% | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | | root | 127.0.0.1 | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | | root | ::1 | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | | centos | 172.30.1.% | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | +--------+------------+-------------------------------------------+ 5 rows in set (0.00 sec) MariaDB [yinzhengjie]>
[root@node101.yinzhengjie.org.cn ~]# mysql -ucentos -pyinzhengjie -h 172.30.1.105 #验证SELECT和UPDATE授权是否生效 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 14 Server version: 5.5.5-10.2.19-MariaDB MariaDB Server Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | yinzhengjie | +--------------------+ rows in set (0.00 sec) mysql> mysql> USE yinzhengjie Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> mysql> SHOW TABLES; +-----------------------+ | Tables_in_yinzhengjie | +-----------------------+ | students | +-----------------------+ row in set (0.00 sec) mysql> mysql> SELECT * FROM students; #由于我们没有授权整张表的查询权限，因此只能看到部分字段哟 ERROR 1143 (42000): SELECT command denied to user 'centos'@'172.30.1.101' for column 'StuID' in table 'students' mysql> mysql> SELECT name,age FROM students; #只有查询name和age列的权限 +---------------+-----+ | name | age | +---------------+-----+ | Shi Zhongyu | 22 | | Shi Potian | 22 | | Xie Yanke | 53 | | Ding Dian | 32 | | Yu Yutong | 26 | | Shi Qing | 46 | | Xi Ren | 19 | | Lin Daiyu | 17 | | Ren Yingying | 20 | | Yue Lingshan | 19 | | Yuan Chengzhi | 23 | | Wen Qingqing | 19 | | Tian Boguang | 33 | | Lu Wushuang | 17 | | Duan Yu | 19 | | Xu Zhu | 21 | | Lin Chong | 25 | | Hua Rong | 23 | | Xue Baochai | 18 | | Diao Chan | 19 | | Huang Yueying | 22 | | Xiao Qiao | 20 | | Ma Chao | 23 | | Xu Xian | 27 | | Sun Dasheng | 100 | +---------------+-----+ rows in set (0.00 sec) mysql> mysql> UPDATE students SET name = '齐天大圣孙悟空' WHERE name = 'Sun Dasheng'; #更新name列的某行 Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> mysql> SELECT name,age FROM students; #查询后发现的确更新啦！ +-----------------------+-----+ | name | age | +-----------------------+-----+ | Shi Zhongyu | 22 | | Shi Potian | 22 | | Xie Yanke | 53 | | Ding Dian | 32 | | Yu Yutong | 26 | | Shi Qing | 46 | | Xi Ren | 19 | | Lin Daiyu | 17 | | Ren Yingying | 20 | | Yue Lingshan | 19 | | Yuan Chengzhi | 23 | | Wen Qingqing | 19 | | Tian Boguang | 33 | | Lu Wushuang | 17 | | Duan Yu | 19 | | Xu Zhu | 21 | | Lin Chong | 25 | | Hua Rong | 23 | | Xue Baochai | 18 | | Diao Chan | 19 | | Huang Yueying | 22 | | Xiao Qiao | 20 | | Ma Chao | 23 | | Xu Xian | 27 | | 齐天大圣孙悟空 | 100 | +-----------------------+-----+ rows in set (0.00 sec) mysql> mysql> UPDATE students SET age = '500' WHERE name = '齐天大圣孙悟空'; #由于我们没有将age列的修改的权限授权给该用户，因此无法修改。 ERROR 1143 (42000): UPDATE command denied to user 'centos'@'172.30.1.101' for column 'age' in table 'students' mysql>
3>.查看指定用户获得的授权
MariaDB [yinzhengjie]> HELP SHOW GRANTS Name: 'SHOW GRANTS' Description: Syntax: SHOW GRANTS [FOR user] This statement lists the GRANT statement or statements that must be issued to duplicate the privileges that are granted to a MySQL user account. The account is named using the same format as for the GRANT statement; for example, 'jeffrey'@'localhost'. If you specify only the user name part of the account name, a host name part of '%' is used. For additional information about specifying account names, see [HELP GRANT]. MariaDB> SHOW GRANTS FOR 'root'@'localhost'; +---------------------------------------------------------------------+ | Grants for root@localhost | +---------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION | +---------------------------------------------------------------------+ To list the privileges granted to the account that you are using to connect to the server, you can use any of the following statements: SHOW GRANTS; SHOW GRANTS FOR CURRENT_USER; SHOW GRANTS FOR CURRENT_USER(); If SHOW GRANTS FOR CURRENT_USER (or any of the equivalent syntaxes) is used in DEFINER context, such as within a stored procedure that is defined with SQL SECURITY DEFINER), the grants displayed are those of the definer and not the invoker. URL: https://mariadb.com/kb/en/show-grants/ MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT user,host,password FROM mysql.user; +--------+------------+-------------------------------------------+ | user | host | password | +--------+------------+-------------------------------------------+ | root | localhost | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | | jason | 172.30.1.% | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | | root | 127.0.0.1 | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | | root | ::1 | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | | centos | 172.30.1.% | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | +--------+------------+-------------------------------------------+ 5 rows in set (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> SHOW GRANTS FOR jason@'172.30.1.%'; +---------------------------------------------------------------------------------------------------------------+ | Grants for jason@172.30.1.% | +---------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'jason'@'172.30.1.%' IDENTIFIED BY PASSWORD '*515CE264AF5A9076EC5876689206AB3C06E20340' | | GRANT ALL PRIVILEGES ON `yinzhengjie`.* TO 'jason'@'172.30.1.%' | +---------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> SHOW GRANTS FOR centos@'172.30.1.%'; +----------------------------------------------------------------------------------------------------------------+ | Grants for centos@172.30.1.% | +----------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'centos'@'172.30.1.%' IDENTIFIED BY PASSWORD '*BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7' | | GRANT SELECT (Age, Name), UPDATE (Name) ON `yinzhengjie`.`students` TO 'centos'@'172.30.1.%' | +----------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) MariaDB [yinzhengjie]>
温馨提示：MariaDB服务进程启动时会读取mysql库中所有授权表至内存 1.GRANT或REVOKE等执行权限操作会保存于系统表中，MariaDB的服务进程通常会自动重读授权表，使之生效; 2.对于不能够或不能及时重读授权表的命令，可手动让MariaDB的服务进程重读授权表,执行"FLUSH PRIVILEGES;"即可;
MariaDB [yinzhengjie]> SELECT USER(); +----------------+ | USER() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> SHOW GRANTS FOR CURRENT_USER(); #查看当前用户拥有权限 +----------------------------------------------------------------------------------------------------------------------------------------+ | Grants for root@localhost | +----------------------------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7' WITH GRANT OPTION | | GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION | +----------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]>
4>.回收权限
MariaDB [yinzhengjie]> HELP REVOKE Name: 'REVOKE' Description: 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] ... REVOKE PROXY ON user FROM user [, user] ... The REVOKE statement enables system administrators to revoke privileges from MySQL accounts. Each account name uses the format described in https://mariadb.com/kb/en/create-user#account-names. For example: REVOKE INSERT ON *.* FROM 'jeffrey'@'localhost'; If you specify only the user name part of the account name, a host name part of '%' is used. For details on the levels at which privileges exist, the permissible priv_type and priv_level values, and the syntax for specifying users and passwords, see [HELP GRANT] To use the first REVOKE syntax, you must have the GRANT OPTION privilege, and you must have the privileges that you are revoking. To revoke all privileges, use the second syntax, which drops all global, database, table, column, and routine privileges for the named user or users: REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ... To use this REVOKE syntax, you must have the global CREATE USER privilege or the UPDATE privilege for the mysql database. URL: https://mariadb.com/kb/en/revoke/ MariaDB [yinzhengjie]> MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW GRANTS FOR centos@'172.30.1.%'; +----------------------------------------------------------------------------------------------------------------+ | Grants for centos@172.30.1.% | +----------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'centos'@'172.30.1.%' IDENTIFIED BY PASSWORD '*BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7' | | GRANT SELECT (Age, Name), UPDATE (Name) ON `yinzhengjie`.`students` TO 'centos'@'172.30.1.%' | +----------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> REVOKE UPDATE ON yinzhengjie.students FROM centos@'172.30.1.%'; #回收centos@'172.30.1.%'用户的UPDATE权限。 Query OK, 0 rows affected (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> SHOW GRANTS FOR centos@'172.30.1.%'; +----------------------------------------------------------------------------------------------------------------+ | Grants for centos@172.30.1.% | +----------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'centos'@'172.30.1.%' IDENTIFIED BY PASSWORD '*BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7' | | GRANT SELECT (Age, Name) ON `yinzhengjie`.`students` TO 'centos'@'172.30.1.%' | +----------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]>
[root@node101.yinzhengjie.org.cn ~]# mysql -ucentos -pyinzhengjie -h 172.30.1.105 #验证UPDATE权限是否回收成功 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 15 Server version: 5.5.5-10.2.19-MariaDB MariaDB Server Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | yinzhengjie | +--------------------+ 2 rows in set (0.00 sec) mysql> mysql> USE yinzhengjie Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> mysql> SHOW TABLES; #该库有多张表，只不过当前用户权限仅能看到这一张表 +-----------------------+ | Tables_in_yinzhengjie | +-----------------------+ | students | +-----------------------+ 1 row in set (0.00 sec) mysql> mysql> DESC students; #我们直到该表有多个字段，只不过当前用户权限仅能看到2个字段 +-------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------------+------+-----+---------+-------+ | Name | varchar(50) | NO | | NULL | | | Age | tinyint(3) unsigned | NO | | NULL | | +-------+---------------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> mysql> SELECT name,age FROM students; #查询权限正常 +-----------------------+-----+ | name | age | +-----------------------+-----+ | Shi Zhongyu | 22 | | Shi Potian | 22 | | Xie Yanke | 53 | | Ding Dian | 32 | | Yu Yutong | 26 | | Shi Qing | 46 | | Xi Ren | 19 | | Lin Daiyu | 17 | | Ren Yingying | 20 | | Yue Lingshan | 19 | | Yuan Chengzhi | 23 | | Wen Qingqing | 19 | | Tian Boguang | 33 | | Lu Wushuang | 17 | | Duan Yu | 19 | | Xu Zhu | 21 | | Lin Chong | 25 | | Hua Rong | 23 | | Xue Baochai | 18 | | Diao Chan | 19 | | Huang Yueying | 22 | | Xiao Qiao | 20 | | Ma Chao | 23 | | Xu Xian | 27 | | 齐天大圣孙悟空 | 100 | +-----------------------+-----+ 25 rows in set (0.00 sec) mysql> mysql> UPDATE students SET name = '美猴王' WHERE name = '齐天大圣孙悟空'; #无法进行更新操作，因为该权限已经被咱们回收了 ERROR 1142 (42000): UPDATE command denied to user 'centos'@'172.30.1.101' for table 'students' mysql> mysql>