|
目录
一、管理用户
Ⅰ、新建用户
Ⅱ、删除用户
Ⅲ、用户的重命名
Ⅳ、修改密码
Ⅴ、忘记root密码（8.0以下版本）
二、授权控制
Ⅰ、授予权限
Ⅱ、查看权限
Ⅲ、撤销权限
一、管理用户
Ⅰ、新建用户
登录mysql，创建用户的SQL
MySQL>create user 'username'@'localhost' identified by password 'password';
#'创建的用户名'@'创建的用户可以在哪些主机上登录（可使用IP地址、网段、主机名）'
创建用户时，可以选择使用或不使用[password]关键字，下面是使用PASSWORD关键字，使用密文作为密码
MySQL [(none)]> select password('root');
+-------------------------------------------+
| password('root') |
+-------------------------------------------+
| *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)
MySQL [(none)]> create user 'ljp'@'%' identified by
password'*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B';
创建后的用户是保存在mysql数据库表里，使用查询语句查看用户
MySQL [(none)]> use mysql;
Database changed
MySQL [mysql]> select user, authentication_string,host from user;
+---------------+-------------------------------------------+-----------+
| user | authentication_string | host |
+---------------+-------------------------------------------+-----------+
| root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | % |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | 127.0.0.1 |
| ljp | *C7FCE122423528D127FC47C9B711A5B16CB07158 | localhost |
+---------------+-------------------------------------------+-----------+
5 rows in set (0.00 sec)
使用新用户登录
[root@test111 opt]# mysql -uljp -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.38-log Source distribution
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
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.
Ⅱ、删除用户
删除用户命令使用语句
drop 'username'@'host';
删除之前创建的ljp用户
MySQL [(none)]> drop user 'ljp'@'localhost';
Query OK, 0 rows affected (0.00 sec)
MySQL [(none)]> use mysql;
Database changed
MySQL [mysql]> select user,authentication_string,host from user;
+---------------+-------------------------------------------+-----------+
| user | authentication_string | host |
+---------------+-------------------------------------------+-----------+
| root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | % |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | 127.0.0.1 |
+---------------+-------------------------------------------+-----------+
4 rows in set (0.00 sec)
也可以使用delete语句进行删除，但和drop有区别，drop会把用户和相关权限都删除，而delete只会删除用户，权限依然存在。
Ⅲ、用户的重命名
用户重命名使用如下SQL语句
MySQL [mysql]> rename user 'old name'@'host' to 'new name'@'host';
例：
MySQL [mysql]> rename user 'ljp'@'localhost' to 'paul'@'localhost';
Query OK, 0 rows affected (0.00 sec)
MySQL [mysql]> select user,host,authentication_string from user;
+---------------+-----------+-------------------------------------------+
| user | host | authentication_string |
+---------------+-----------+-------------------------------------------+
| root | % | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| root | 127.0.0.1 | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| paul | localhost | *C7FCE122423528D127FC47C9B711A5B16CB07158 |
+---------------+-----------+-------------------------------------------+
5 rows in set (0.00 sec)
Ⅳ、修改密码
修改用户的密码可以使用以下SQL语句
#给目前登录的用户修改密码
set password = password('password');
#给其他用户的用户修改密码
set password for 'user'@'host' = password('password');
?例：
MySQL [mysql]> set password =password('222333');
MySQL [mysql]> set password for 'paul'@'localhost' = password('241567');
Ⅴ、忘记root密码（8.0以下版本）
进入配置文件修改
vim /etc/my.cnf
#在[mysqld]下面增加一句
......省略部分内容
[mysqld]
skip-grant-tables
......省略部分内容
---wq
#重启mysql服务
systemctl restart mysqld
进入mysql命令行设置root密码
[root@test111 ~]# mysql
#更改密码
mysql>update mysql.user set authentication_string=password('新的root密码')
where user='root';
#保存
mysql>flush privileges;
回到配置配件删去所做的修改并重启服务，之后就可以使用新的密码登录了。
二、授权控制
| 权限 | 说明 | | ALL | 设置GRANT OPTION之外的所有权限 | | ALTER | 允许使用ALTER TABLE | | CREATE | 允许使用CREATE TTABLE | | CREATE USER | 允许使用CREATE USER | | DELETE | 允许使用DELETE | | INDEX | 允许使用INDEX | | INSERT | 允许使用INSERT | | SELECT | 允许使用SELECT | | UPDATE | 允许使用UPDATE | | DROP | 允许使用DROP TABLE | | REPLICATION SLAVE | 允许从主服务器读取二进制日志文件 | | SHOW DATABASES | 允许显示所有库 |
Ⅰ、授予权限
grant 权限列表 on 库名.表名 to 'user'@'host' identified by ‘password’;
需要注意的是，当用户和密码在数据库中不存在或存在但不相同时，执行授予权限操作可以起到创建和修改的作用
Ⅱ、查看权限
show grants for 'user'@'host';
MySQL [mysql]> show grants for 'paul'@'localhost';
+------------------------------------------+
| Grants for paul@localhost |
+------------------------------------------+
| GRANT USAGE ON *.* TO 'paul'@'localhost' |
+------------------------------------------+
1 row in set (0.00 sec)
Ⅲ、撤销权限
revoke 权限列表 on 库名.表名 from 'user'@'host';
先给paul用户给予 select，update权限
MySQL [mysql]> grant select,update on test.class to 'paul'@'localhost' identified by '241567';
Query OK, 0 rows affected, 1 warning (0.00 sec)
MySQL [mysql]> show grants for 'paul'@'localhost';
+--------------------------------------------------------------+
| Grants for paul@localhost |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'paul'@'localhost' |
| GRANT SELECT, UPDATE ON `test`.`class` TO 'paul'@'localhost' |
+--------------------------------------------------------------+
2 rows in set (0.00 sec)
对paul的select权限进行撤销
MySQL [mysql]> revoke update on test.class from 'paul'@'localhost';
Query OK, 0 rows affected (0.00 sec)
MySQL [mysql]> show grants for 'paul'@'localhost';
+------------------------------------------------------+
| Grants for paul@localhost |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO 'paul'@'localhost' |
| GRANT SELECT ON `test`.`class` TO 'paul'@'localhost' |
+------------------------------------------------------+
2 rows in set (0.00 sec)
撤销Paul用户的所有权限
MySQL [mysql]> revoke all on test.class from 'paul'@'localhost';
|