MySQL之用户管理
#前言：我们知道，无论是登陆MySQL数据库还是登陆Linux系统，都需要有用户来登陆。默认情况下，root用户是享有最高权限的超级用户，可以使用包括create,drop,insert等操作，但是我们也需要一些普通用户来进行管理，接下来就让我们对号入座，来进行如何创建用户，授权用户，和删除用户等操作
#1.通过grant命令创建用户并授权
#grant命令语法：
#grant all privileges on dbname.* to username@localhost identified by 'passwd';
#说明：上述命令使授权localhost主机上通过用户username管理dbname数据库的所有权限，密码为passwd
其中username,dbname,passwd可根据情况修改
#语法解释
grant：授权命令
all privileges：对应权限
on dbname.*：目标：库和表
to username@localhhost ：用户名和客户端主机
identified by 'passwd'：用户密码
#例子：
#创建test用户，对db库具备所有权限，允许从localhost主机登陆管理数据库，密码使用guoke123 mysql> create database db; #创建数据库 Query OK, 1 row affected (0.00 sec) mysql> grant all privileges on db.* to 'test'@'localhost' identified by 'guoke123'; #创建用户并授权 Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> select user,host from mysql.user; #查看用户 +---------------+-----------+ | user | host | +---------------+-----------+ | root | localhost | | test | localhost | +---------------+-----------+ 5 rows in set (0.00 sec) mysql> flush privileges; #刷新权限 Query OK, 0 rows affected (0.00 sec) mysql> show grants for 'test'@'localhost'; #查看用户权限 +------------------------------------------------------+ | Grants for test@localhost | +------------------------------------------------------+ | GRANT USAGE ON *.* TO 'test'@'localhost' | | GRANT ALL PRIVILEGES ON `db`.* TO 'test'@'localhost' | +------------------------------------------------------+ 2 rows in set (0.00 sec)
#2.使用create和grant配合创建用户
#查看帮助：help grant
mysql> help grant Name: 'GRANT' .......... Each account name uses the format described in https://dev.mysql.com/doc/refman/5.7/en/account-names.html. For example: GRANT ALL ON db1.* TO 'jeffrey'@'localhost'; The host name part of the account, if omitted, defaults to '%'. Normally, a database administrator first uses CREATE USER to create an account and define its nonprivilege characteristics such as its password, whether it uses secure connections, and limits on access to server resources, then uses GRANT to define its privileges. ALTER USER may be used to change the nonprivilege characteristics of existing accounts. For example: CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password'; GRANT ALL ON db1.* TO 'jeffrey'@'localhost'; GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost'; ALTER USER 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;
View Code
#例子：
#使用create创建用户，再使用grant授权 mysql> create user 'demo'@'localhost' identified by 'guoke123'; #创建用户 Query OK, 0 rows affected (0.00 sec) mysql> grant all on db.* to 'demo'@'localhost'; #授权 Query OK, 0 rows affected (0.01 sec) mysql> select user,host from mysql.user; #查看用户 +---------------+-----------+ | user | host | +---------------+-----------+ | root | localhost | | demo | localhost | +---------------+-----------+ 6 rows in set (0.01 sec)
#查看当前用户
mysql> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec)
#3.使用Revoke收回权限
#查看命令帮助：help revoke
mysql> 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] ... ........ https://dev.mysql.com/doc/refman/5.7/en/account-names.html. For example: REVOKE INSERT ON *.* FROM 'jeffrey'@'localhost';
View Code
#例子：收回test用户的插入权限
#1.首先查看test用户拥有什么权限，查看到是all所有权限 mysql> show grants for 'test'@'localhost'; +------------------------------------------------------+ | Grants for test@localhost | +------------------------------------------------------+ | GRANT USAGE ON *.* TO 'test'@'localhost' | | GRANT ALL PRIVILEGES ON `db`.* TO 'test'@'localhost' | +------------------------------------------------------+ 2 rows in set (0.00 sec) #2.收回insert权限 mysql> revoke insert on db.* from 'test'@'localhost'; Query OK, 0 rows affected (0.00 sec) #3.再次查看，就没有insert权限了 mysql> show grants for 'test'@localhost; +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for test@localhost | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'test'@'localhost' | | GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `db`.* TO 'test'@'localhost' | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) #提示：如果不生效的话 # 查看有没有对上用户管理的数据库名字 mysql> show grants for 'test'@'localhost'; +------------------------------------------------------+ | Grants for test@localhost | +------------------------------------------------------+ | GRANT USAGE ON *.* TO 'test'@'localhost' | | GRANT ALL PRIVILEGES ON `db`.* TO 'test'@'localhost' | +------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> revoke insert on *.* from 'test'@'localhost'; #这里*.*就会不生效，改成db.* Query OK, 0 rows affected (0.00 sec) mysql> show grants for 'test'@'localhost'; +------------------------------------------------------+ | Grants for test@localhost | +------------------------------------------------------+ | GRANT USAGE ON *.* TO 'test'@'localhost' | | GRANT ALL PRIVILEGES ON `db`.* TO 'test'@'localhost' | +------------------------------------------------------+ 2 rows in set (0.00 sec)
#在创建用户的时候将指定想要的权限，使用，分隔
#例子:创建guoke用户的时候给insert,select,create权限 mysql> create user 'guoke'@'localhost' identified by 'guoke123'; #创建用户 Query OK, 0 rows affected (0.00 sec) mysql> grant insert,select,create on db.* to 'guoke'@'localhost'; #授权 Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
#4.企业生成环境的用户授权
mysql> grant select,insert,update,delete on db.* to 'li'@'localhost' identified by 'guoke123'; Query OK, 0 rows affected, 1 warning (0.00 sec) #一般情况下，授权select,insert,update,delete 4个权限即可，有些可能需要create,drop等比较危险的权限，可以再创建数据库后再将危险权限收回 mysql> revoke create on *.* from 'test'@'localhost'; mysql> revoke drop on *.* from 'test'@'localhost';
#查看all里面包含着什么权限
[root@cots3 ~]# mysql -uroot -p -e "show grants for 'test'@localhost" | grep -i grant | tail -1 | tr ',' '\n' Enter password: GRANT SELECT INSERT UPDATE DELETE CREATE DROP REFERENCES INDEX ALTER CREATE TEMPORARY TABLES LOCK TABLES EXECUTE CREATE VIEW SHOW VIEW CREATE ROUTINE ALTER ROUTINE EVENT #提示：之前test是有所有权限，然后使用revoke将insert权限收回了，就可以查看其他的权限，上面也将INSERT权限写进去了
View Code
#5.本地及远程授权
#1.百分号授权法
# 允许所有主机连接% mysql> grant all privileges on db.* to test1@'%' identified by 'guoke123'; Query OK, 0 rows affected, 1 warning (0.00 sec) # 允许一个网段 mysql> grant all privileges on db.* to test2@'1.1.1.%' identified by 'guoke123'; Query OK, 0 rows affected, 1 warning (0.00 sec) #允许一个IP mysql> grant all privileges on db.* to test3@'1.1.1.1' identified by 'guoke123'; Query OK, 0 rows affected, 1 warning (0.00 sec) #2.子网掩码配置法 mysql> grant all privileges on db.* to test4@'1.1.1.0/255.255.255.0' identified by 'guoke123'; Query OK, 0 rows affected, 1 warning (0.00 sec) #查看 mysql> select user,host from mysql.user; +---------------+-----------------------+ | user | host | +---------------+-----------------------+ | root | localhost | | test1 | % | | test2 | 1.1.1.% | | test4 | 1.1.1.0/255.255.255.0 | | test3 | 1.1.1.1 |
#6.mysql客户端连接远程MySQL方法
#语法：mysql -u用户名 -p密码 -h主机
#例子：mysql -uroot -p”guoke123″ -h192.168.226.146
#7.删除MySQL用户
#语法：drop user “user”@”主机”
#查看帮助：help drop user
mysql> help drop user #查看帮助 Name: 'DROP USER' Description: Syntax: DROP USER [IF EXISTS] user [, user] ... https://dev.mysql.com/doc/refman/5.7/en/account-names.html. For example: DROP USER 'jeffrey'@'localhost';
#例子：
mysql> select user,host from mysql.user; #查看用户 +---------------+-----------------------+ | user | host | +---------------+-----------------------+ | root | localhost | | test1 | % | | test2 | 1.1.1.% | | test4 | 1.1.1.0/255.255.255.0 | +---------------+-----------------------+ 12 rows in set (0.00 sec) mysql> drop user 'test1'@'%'; #删除test1用户 Query OK, 0 rows affected (0.00 sec) mysql> select user,host from mysql.user; +---------------+-----------------------+ | user | host | +---------------+-----------------------+ | root | localhost | | test2 | 1.1.1.% | | test4 | 1.1.1.0/255.255.255.0 | +---------------+-----------------------+ 11 rows in set (0.00 sec)
版权声明：本文为guoke-boy原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。