oracle 授权clear权限,MySQL 数据库用户和权限管理

最新推荐文章于 2022-04-18 19:28:17 发布

我真真

最新推荐文章于 2022-04-18 19:28:17 发布

阅读量191

点赞数

文章标签： oracle 授权clear权限

MySQL 数据库用户和权限管理

技能目标

掌握MySQL 用户管理

添加管理用户

修改密码及忘记密码修改

用户授权

数据库是信息系统中非常重要的环节，合理高效的对它进行管理是很重要的工作。通常是由拥有最高权限的管理员创建不同的管理账户，然后分配不同的操作权限，把这些账户交给相应的管理人员使用

用户管理

1: 新建用户

新建用户的命令格式如下

CREATE USER 'username'@'host' [IDENTIFIED BY [PASSWORD]'password'] #大写是固定格式大括弧是一个整体再写命令的时候没有

username 将创建的用户名

host 指定用户允许那些主机终端可以登录，可以是IP地址、网段、指定本地用户localhost、如果让该用户可以从任意远程主机登录可以用通配符%

password 设置登录的密码

下面是MySQL安装之后创建的用户密码,在数据库中显示的密码是以密文的形式保存的大大的增强了安全性

mysql> select User,authentication_string,Host from user;

+-----------+-------------------------------------------+-----------+

| User | authentication_string | Host |

+-----------+-------------------------------------------+-----------+

| root | *0DB339632B48910F8F0BEF61BD7EAD4441267E6E | localhost |

| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |

+-----------+-------------------------------------------+-----------+

2 rows in set (0.01 sec)

创建新用户

mysql> create user 'accp'@'localhost' identified by '123123';

Query OK, 0 rows affected (0.01 sec)

mysql> select User,authentication_string,Host from user;

+-----------+-------------------------------------------+-----------+

| User | authentication_string | Host |

+-----------+-------------------------------------------+-----------+

| root | *0DB339632B48910F8F0BEF61BD7EAD4441267E6E | localhost |

| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |

| accp | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 | localhost |

+-----------+-------------------------------------------+-----------+

3 rows in set (0.00 sec)

删除用户命令格式如下

DROP USER 'username'@'host'

mysql> drop user 'accp'@'localhost'; #删除accp

Query OK, 0 rows affected (0.00 sec)

mysql> select User,authentication_string,Host from user;

+-----------+-------------------------------------------+-----------+

| User | authentication_string | Host |

+-----------+-------------------------------------------+-----------+

| root | *0DB339632B48910F8F0BEF61BD7EAD4441267E6E | localhost |

| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |

| bent | *437F1809645E0A92DAB553503D2FE21DB91270FD | localhost |

+-----------+-------------------------------------------+-----------+

3 rows in set (0.00 sec)

用户重命名，格式如下

RENAME USER 'old_user'@'host' TO 'new_user' @ 'host'

mysql> select User,authentication_string,Host from user; #这边我们把bent重命名为accp

+-----------+-------------------------------------------+-----------+

| User | authentication_string | Host |

+-----------+-------------------------------------------+-----------+

| root | *0DB339632B48910F8F0BEF61BD7EAD4441267E6E | localhost |

| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |

| bent | *437F1809645E0A92DAB553503D2FE21DB91270FD | localhost |

+-----------+-------------------------------------------+-----------+

3 rows in set (0.00 sec)

mysql> rename user 'bent'@'localhost' to 'accp'@'localhost' ;

Query OK, 0 rows affected (0.00 sec)

mysql> select User,authentication_string,Host from user;

+-----------+-------------------------------------------+-----------+

| User | authentication_string | Host |

+-----------+-------------------------------------------+-----------+

| root | *0DB339632B48910F8F0BEF61BD7EAD4441267E6E | localhost |

| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |

| accp | *437F1809645E0A92DAB553503D2FE21DB91270FD | localhost |

+-----------+-------------------------------------------+-----------+

3 rows in set (0.00 sec)

给用户设置密码

1：给当前用户设置密码

SET PASSWORD=PASSWORD('password')

mysql> select User,authentication_string,Host from user;

+-----------+-------------------------------------------+-----------+

| User | authentication_string | Host |

+-----------+-------------------------------------------+-----------+

| root | *0DB339632B48910F8F0BEF61BD7EAD4441267E6E | localhost |

| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |

| accp | *437F1809645E0A92DAB553503D2FE21DB91270FD | localhost |

+-----------+-------------------------------------------+-----------+

3 rows in set (0.00 sec)

mysql> set password=password('123123'); #当前用户是root我把root用户密码改为了"123123"与上面的root密码对比一下秘闻的区别

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select User,authentication_string,Host from user;

+-----------+-------------------------------------------+-----------+

| User | authentication_string | Host |

+-----------+-------------------------------------------+-----------+

| root | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 | localhost |

| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |

| accp | *437F1809645E0A92DAB553503D2FE21DB91270FD | localhost |

+-----------+-------------------------------------------+-----------+

3 rows in set (0.00 sec)

2：使用超级管理员root修改其他用户密码，格式如下

SET PASSWORD FOR 'username'@'host'=PASSWORD('password');

mysql> select User,authentication_string,Host from user;

+-----------+-------------------------------------------+-----------+

| User | authentication_string | Host |

+-----------+-------------------------------------------+-----------+

| root | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 | localhost |

| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |

| accp | *437F1809645E0A92DAB553503D2FE21DB91270FD | localhost |

+-----------+-------------------------------------------+-----------+

3 rows in set (0.00 sec)

mysql> set password for 'accp'@'localhost'=password('951116'); #同样对比一下密文密码的区别

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select User,authentication_string,Host from user;

+-----------+-------------------------------------------+-----------+

| User | authentication_string | Host |

+-----------+-------------------------------------------+-----------+

| root | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 | localhost |

| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |

| accp | *0DB339632B48910F8F0BEF61BD7EAD4441267E6E | localhost |

+-----------+-------------------------------------------+-----------+

3 rows in set (0.00 sec)

忘记root密码解决方法

[root@localhost ~] systemctl stop mysqld.service #关闭服务

[root@localhost ~] netstat -ntap | grep 3306 #查看端口有没有关闭

[root@localhost ~] mysql --skip-grant-tables #会出现以下代码不要去动它重新开一个终端

2018-06-28T02:16:16.399381Z 0 [Note] - '::' resolves to '::';

2018-06-28T02:16:16.399402Z 0 [Note] Server socket created on IP: '::'.

2018-06-28T02:16:16.400217Z 0 [Note] InnoDB: Loading buffer pool(s) from /usr/local/mysql/data/ib_buffer_pool

2018-06-28T02:16:16.401959Z 0 [Note] InnoDB: Buffer pool(s) load completed at 180628 10:16:16

2018-06-28T02:16:16.410638Z 0 [Note] Executing 'SELECT * FROM INFORMATION_SCHEMA.TABLES;' to get a list of tables using the deprecated partition engine. You may use the startup option '--disable-partition-engine-check' to skip this check.

2018-06-28T02:16:16.410661Z 0 [Note] Beginning of list of non-natively partitioned tables

2018-06-28T02:16:16.423678Z 0 [Note] End of list of non-natively partitioned tables

2018-06-28T02:16:16.423748Z 0 [Note] mysqld: ready for connections.

Version: '5.7.17' socket: '/usr/local/mysql/mysql.sock' port: 3306 Source distribution

[root@localhost ~] mysql -u root #直接这样登录跳过密码选项

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.7.17 Source distribution

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> update mysql.user set authentication_string=password('123123')where user='root'; #修改root密码

Query OK, 1 row affected, 1 warning (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 1

mysql> flush privileges; #刷新数据库

Query OK, 0 rows affected (0.01 sec)

[root@localhost ~]# mysql -u root -p123123

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 5

Server version: 5.7.17 Source distribution

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中，权限设置非常重要，分配权限可以清晰的划分责任。管理人员只需要关注完成自己的任务即可，最重要的是保证系统数据的安全

1：授予权限

(1):权限控制主要出于安全因素，需要遵循以下原则

1)：只授予能满足需要的最小权限，防止误操作和做坏事

2)：创建用户的时候限制用户的登录主机，一般限制指定IP或者内网IP网段

3)：初始化数据库时删除没有密码的用户，MySQL安装完成是会自动创建没有密码的用户

4)：为每个用户设置满足要求的密码

5)：定期清理不需要的用户

(2)：授予权限使用GRANT命令，命令格式如下

GRANT 权限列表 ON 库名.表明 TO 用户@主机地址[IDENTIFIED BY'密码']

命令个是很明确，是指定用户允许它操作某些表，对这些表拥有相应的操作权限

下面演示GRANT的使用方法

mysql> grant select on ×××表.×××信息 to 'accp'@'localhost' identified by '123123';

Query OK, 0 rows affected, 1 warning (0.00 sec)

上面命令的意思是使用户accp可以在主机localhost登录，连接密码是123123，它拥有对数据库(×××表.×××信息)的select权限

登录accp用户验证以下

[root@localhost ~]# mysql -u accp -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 9

Server version: 5.7.17 Source distribution

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> insert into imployee_英航客户表.×××信息 values (2,'张三','广州珠海','18888888');

ERROR 1142 (42000): INSERT command denied to user 'accp'@'localhost' for table '×××信息'

上图显示select语句可以正常使用，但执行insert语句是没有足够权限

当当用户和主机名在列表中不存在时，用户和主机名会被自动创建，如果限制用户密码与原用密码不同时会自动覆盖原密码

mysql> select User,authentication_string,Host from user;

+-----------+-------------------------------------------+-----------+

| User | authentication_string | Host |

+-----------+-------------------------------------------+-----------+

| root | *0DB339632B48910F8F0BEF61BD7EAD4441267E6E | localhost |

| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |

| accp | *0DB339632B48910F8F0BEF61BD7EAD4441267E6E | localhost |

+-----------+-------------------------------------------+-----------+

3 rows in set (0.00 sec)

#用户列表中只有三个用户此时，做一个用户列表中不存在用户权限

mysql> grant select on ×××表.×××信息 to 'benet'@'localhost' identified by '1223123';

Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> select User,authentication_string,Host from user;

+-----------+-------------------------------------------+-----------+

| User | authentication_string | Host |

+-----------+-------------------------------------------+-----------+

| root | *0DB339632B48910F8F0BEF61BD7EAD4441267E6E | localhost |

| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |

| benet | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 | localhost |

| accp | *0DB339632B48910F8F0BEF61BD7EAD4441267E6E | localhost |

+-----------+-------------------------------------------+-----------+

4 rows in set (0.00 sec)

#上面自动创建了benet用户登陆密码为‘123123’

下面设置benet用户限制原密码为123123，我把限制密码改以新密码‘321321’然后看一下用原密码能不能登录

mysql> grant insert on ×××表.×××信息 to 'benet'@'localhost' identified by '3221321';

Query OK, 0 rows affected, 1 warning (0.00 sec)

[root@localhost ~]# mysql -u benet -p123123

mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 1045 (28000): Access denied for user 'benet'@'localhost' (using password: YES)

#提示你输入正确的登陆密码

查看用户权限

SHOW GRANTS FOR 'username'@'主机地址'

mysql> show grants for 'accp'@'localhost';

+------------------------------------------------------------------------------+

| Grants for accp@localhost |

+------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'accp'@'localhost' |

| GRANT SELECT ON "×××表"."×××信息" TO 'accp'@'localhost' |

+------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

撤销用户权限

REVOKE 权限列表 ON 数据库名.表名 FROM 用户@主机地址

mysql> revoke select on ×××表.×××信息 from 'accp'@'localhost';

Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'accp'@'localhost';

+------------------------------------------+

| Grants for accp@localhost |

+------------------------------------------+

| GRANT USAGE ON *.* TO 'accp'@'localhost' |

+------------------------------------------+

1 row in set (0.00 sec)

撤销用户所有权限

REVOKE ALL ON 数据库名.表名 FROM 用户@主机地址

我真真

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle 授权clear权限,MySQL 数据库用户和权限管理

MySQL 数据库用户和权限管理技能目标掌握MySQL 用户管理添加管理用户修改密码及忘记密码修改用户授权数据库是信息系统中非常重要的环节，合理高效的对它进行管理是很重要的工作。通常是由拥有最高权限的管理员创建不同的管理账户，然后分配不同的操作权限，把这些账户交给相应的管理人员使用用户管理1: 新建用户新建用户的命令格式如下CREATE USER 'username'@'host' [IDENTI...
复制链接

扫一扫