Mysql 用户权限管理

最新推荐文章于 2024-06-14 08:00:00 发布

xpisme

最新推荐文章于 2024-06-14 08:00:00 发布

阅读量429

点赞数

分类专栏： mysql 文章标签： mysql 用户权限创建删除

本文链接：https://blog.csdn.net/u013303402/article/details/53331682

版权

mysql 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

版本：mysql5.6

一：存放位置
mysql用户的存放位置在mysql库下的user表中

mysql> use mysql;
Database changed
mysql> select Host, User, Password from user;
+-----------+------------+-------------------------------------------+
| Host      | User       | Password                                  |
+-----------+------------+-------------------------------------------+
| localhost | root       | *85E26B8AB29FEE8453201A3511DAE24A24059109 |
| 127.0.0.1 | root       | *85E26B8AB29FEE8453201A3511DAE24A24059109 |
| %         | outside    | *4A8E00D6E06B539C7E50A9A5010ACFB1861FCACF |
| localhost | program    | *85E26B8AB29FEE8453201A3511DAE24A24059109 |
+-----------+------------+-------------------------------------------+
4 rows in set (0.00 sec)

二：创建一个用户

mysql> CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
Query OK, 0 rows affected (0.00 sec)

mysql> select Host, User, Password from user where User = 'jeffrey';
+-----------+---------+-------------------------------------------+
| Host      | User    | Password                                  |
+-----------+---------+-------------------------------------------+
| localhost | jeffrey | *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 |
+-----------+---------+-------------------------------------------+
1 row in set (0.00 sec)

# 内网可访问 只需把Host设置为192.168.1.%即可
# 外网可访问 只需把Host设置为%即可

三：赋予权限

mysql> grant all on dbname.* to 'jeffrey'@'localhost';
Query OK, 0 rows affected (0.00 sec)
# 必须冲刷一下权限
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

全局权限所有表

GRANT ALL ON *.* TO 'someuser'@'somehost';
GRANT SELECT, INSERT ON *.* TO 'someuser'@'somehost';

数据库权限

GRANT ALL ON mydb.* TO 'someuser'@'somehost';
GRANT SELECT, INSERT ON mydb.* TO 'someuser'@'somehost';

表权限

GRANT ALL ON mydb.mytbl TO 'someuser'@'somehost';
GRANT SELECT, INSERT ON mydb.mytbl TO 'someuser'@'somehost';

字段权限

GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO 'someuser'@'somehost';

四：删除用户

mysql> DROP USER 'jeffrey'@'localhost';
Query OK, 0 rows affected (0.00 sec)

五：修改密码

mysql> update user set Password = password('hehe') where user = 'x' limit 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)