mysql5.7 用户和权限管理（Linux系统下）

最新推荐文章于 2023-04-02 22:39:29 发布

wqs_1994

最新推荐文章于 2023-04-02 22:39:29 发布

阅读量383

点赞数

分类专栏： mysql

原文链接：https://www.cnblogs.com/yangmingxianshen/p/7997725.html

版权

mysql 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

1. 查看用户
	use mysql;
	select user,host,authentication_string from user;

		mysql> use mysql;
		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> select user,host,authentication_string from user;
		+---------------+-----------+-------------------------------------------+
		| user          | host      | authentication_string                     |
		+---------------+-----------+-------------------------------------------+
		| root          | localhost | *49D7A8D9455A990A12052F5C72D569B9EAEC155A |
		| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
		| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
		| myuser        | %         | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
		| root          | %         | *49D7A8D9455A990A12052F5C72D569B9EAEC155A |
		+---------------+-----------+-------------------------------------------+
		5 rows in set (0.00 sec)

2. 创建用户
	语法：create user 用户名@主机 identified by 密码;
	例如：
		create user test@localhost identified by '123456';


		mysql> create user test@localhost identified by '123456';
		Query OK, 0 rows affected (0.00 sec)

		mysql> select user,host,authentication_string from user;
		+---------------+-----------+-------------------------------------------+
		| user          | host      | authentication_string                     |
		+---------------+-----------+-------------------------------------------+
		| root          | localhost | *49D7A8D9455A990A12052F5C72D569B9EAEC155A |
		| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
		| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
		| myuser        | %         | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
		| root          | %         | *49D7A8D9455A990A12052F5C72D569B9EAEC155A |
		| test          | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
		+---------------+-----------+-------------------------------------------+
		6 rows in set (0.01 sec)

3.用户授权
	语法：grant 权限列表 on 库名.表名 to 用户@主机 identified by '密码';
	例如：grant select,delete,update,create,drop on . to test@"192.168.%.%" identified by "123456"; 
		# "%" 表示对所有非本地主机授权，不包括localhos
		# *.*：所有库上的所有表； 
		# 192.168.%.%：192.168内的所有Ip；
		# 123456：密码
	刷新权限：flush privileges;


4. 查看用户权限
	语法：show grants for 用户@主机;
	例如：show grants for test@localhost;

		mysql> show grants for test@localhost;
		+------------------------------------------+
		| Grants for test@localhost                |
		+------------------------------------------+
		| GRANT USAGE ON *.* TO 'test'@'localhost' |
		+------------------------------------------+
		1 row in set (0.00 sec)

5. 回收权限
	回收权限有两种方法：删除用户和revoke

	回收语法：revoke 权限 on 库名.表名 from 用户@主机；
	例如：revoke all on *.* from test@localhost;
	revoke insert,select on *.* from test@localhost;

6. 删除账户及权限
	语法：drop user 用户名@主机;
	例如：drop user 'test'@localhost;