元数据数据库：mysql 系统授权表： db, host, user columns_priv, tables_priv, procs_priv, proxies_priv 用户账号： ‘USERNAME‘@‘HOST‘： @‘HOST‘: 主机名； IP地址或Network; 通配符： % _ 示例：172.16.%.%
创建用户：CREATE USER CREATE USER ‘USERNAME‘@‘HOST‘ [IDENTIFIED BY ‘password‘]； 默认权限：USAGE create user 创建完用户之后它的权限很有限、创建完用户之后是立即生效的用户重命名：RENAME USER RENAME USER old_user_name TO new_user_name删除用户： DROP USER ‘USERNAME‘@‘HOST‘ 示例：删除默认的空用户 DROP USER ‘‘@‘localhost‘;修改密码： mysql>SET PASSWORD FOR ‘user‘@‘host‘ = PASSWORD(‘password‘); mysql>UPDATE mysql.user SET password=PASSWORD(‘password‘) WHERE clause; 此方法需要执行下面指令才能生效： mysql> FLUSH PRIVILEGES; #mysqladmin -u root -poldpass password ‘newpass‘忘记管理员密码的解决办法： 启动mysqld进程时，为其使用如下选项： --skip-grant-tables --skip-networking 使用UPDATE命令修改管理员密码 关闭mysqld进程，移除上述两个选项，重启mysqld查看系统表里面的用户： MariaDB [db1]> select user,host,password from mysql.user; +---------+---------------+-------------------------------------------+ | user | host | password | +---------+---------------+-------------------------------------------+ | root | localhost | *A498955BAB852BEF5B0C78584202F0326BB117A7 | | root | 127.0.0.1 | *A498955BAB852BEF5B0C78584202F0326BB117A7 | | cobbler | 192.168.137.% | *128977E278358FF80A246B5046F51043A2B1FCED | | mage | 192.168.137.% | *128977E278358FF80A246B5046F51043A2B1FCED | +---------+---------------+-------------------------------------------+创建一个用户只允许单台远程主机IP能连接 MariaDB [db1]> create user test@‘192.168.137.56‘ identified by ‘centos‘; Query OK, 0 rows affected (0.01 sec)查看创建好的用户 MariaDB [db1]> select user,host,password from mysql.user; +---------+----------------+-------------------------------------------+ | user | host | password | +---------+----------------+-------------------------------------------+ | root | localhost | *A498955BAB852BEF5B0C78584202F0326BB117A7 | | root | 127.0.0.1 | *A498955BAB852BEF5B0C78584202F0326BB117A7 | | test | 192.168.137.56 | *128977E278358FF80A246B5046F51043A2B1FCED | | cobbler | 192.168.137.% | *128977E278358FF80A246B5046F51043A2B1FCED | | mage | 192.168.137.% | *128977E278358FF80A246B5046F51043A2B1FCED | +---------+----------------+-------------------------------------------+ 5 rows in set (0.00 sec)客户端验证此用户 [root@node6 ~mysql -utest -p -h192.168.137.57 Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 12 Server version: 10.2.15-MariaDB-log MariaDB Server Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. MariaDB [(none)]> select user(); +---------------------+ | user() | +---------------------+ | test@192.168.137.56 | +---------------------+ 1 row in set (0.00 sec)删除用户： MariaDB [db1]> drop user mage@‘192.168.137.%‘; Query OK, 0 rows affected (0.00 sec) MariaDB [db1]> select user,host,password from mysql.user; +---------+----------------+-------------------------------------------+ | user | host | password | +---------+----------------+-------------------------------------------+ | root | localhost | *A498955BAB852BEF5B0C78584202F0326BB117A7 | | root | 127.0.0.1 | *A498955BAB852BEF5B0C78584202F0326BB117A7 | | test | 192.168.137.56 | *128977E278358FF80A246B5046F51043A2B1FCED | | cobbler | 192.168.137.% | *128977E278358FF80A246B5046F51043A2B1FCED | +---------+----------------+-------------------------------------------+ 4 rows in set (0.00 sec)修改用户密码： # set 修改用户密码： MariaDB [db1]> set password for test@‘192.168.137.56‘=password(‘123456‘); Query OK, 0 rows affected (0.00 sec) # update 修改表的方式来修改密码，不过需要手动来刷新 MariaDB [db1]> update mysql.user set password=password(‘centos‘) where user=‘test‘; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [db1]> flush privileges; Query OK, 0 rows affected (0.00 sec)权限类别： 管理类 程序类 数据库级别 表级别 字段级别管理类： 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参考：https://dev.mysql.com/doc/refman/5.7/en/grant.htmlGRANT priv_type [(column_list)],... ON [object_type] priv_level TO ‘user‘@‘host‘ [IDENTIFIED BY ‘password‘] [WITH GRANT OPTION]; priv_type: ALL [PRIVILEGES] object_type:TABLE | FUNCTION | PROCEDURE priv_level: *(所有库) | *.* | db_name.* | db_name.tbl_name | tbl_name(当前库的表) | db_name.routine_name(指定库的函数,存储过程,触发器) with_option: GRANT OPTION | MAX_QUERIES_PER_HOUR count # 限定在每个小时最多查询多少次 | MAX_UPDATES_PER_HOUR count # 限定每个小时最多更新多少次 | MAX_CONNECTIONS_PER_HOUR count # 限定每个小时最多连接多少次 | MAX_USER_CONNECTIONS count # 限定每个小时用户连接多少次回收授权： REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ... ON [object_type] priv_level FROM user [, user] ...查看指定用户获得的授权： Help SHOW GRANTS SHOW GRANTS FOR ‘user‘@‘host‘; SHOW GRANTS FOR CURRENT_USER[()];注意： MariaDB服务进程启动时会读取mysql库中所有授权表至内存 (1) GRANT或REVOKE等执行权限操作会保存于系统表中，MariaDB的服务进程通常会自动重读授权表，使之生效 (2) 对于不能够或不能及时重读授权表的命令，可手动让MariaDB的服务进程重读授权表：mysql> FLUSH PRIVILEGES;创建test用户MariaDB [db1]> create user test@‘192.168.137.56‘ identified by ‘centos‘;只授权select权限给test用户MariaDB [hellodb]> grant select(stuid,name) on hellodb.students to ‘test‘@‘192.168.137.56‘;Query OK, 0 rows affected (0.00 sec)在客户端验证： MariaDB [hellodb]> show databases; +--------------------+ | Database | +--------------------+ | hellodb | | information_schema | +--------------------+ 2 rows in set (0.00 sec) MariaDB [hellodb]> desc students; +-------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+----------------+ | StuID | int(10) unsigned | NO | PRI | NULL | auto_increment | | Name | varchar(50) | NO | | NULL | | +-------+------------------+------+-----+------。，---+----------------+ 2 rows in set (0.01 sec) MariaDB [hellodb]> select stuid,name from students;取消权限： MariaDB [hellodb]> revoke SELECT (name,stuid) ON `hellodb`.`students` TO ‘test‘@‘192.168.137.56‘;
