目录
一、数据库用户管理
1.1 新建用户
CREATE USER '用户名'@'来源地址' [IDENTIFIED BY [PASSWORD] '密码'];
-------------------------------------------------------------------------------
'用户名':指定将创建的用户名
'来源地址':指定新创建的用户可在哪些主机上登录,可使用p地址、网段、主机名的形式,本地用户可用localhost,允许任意主机登录可用通配符%
'密码':
若使用明文密码,直接输入'密码',插入到数据库时由Mysq1自动加密;
若使用加密密码,需要先使用SELECT PASSWORD('密码');获取密文,再在语句中添加PASSWORD'密文';
若省略"IDENTIEIEDBY"部分,则用户的密码将为空(不建议使用)
1.2 查看用户
用户数据位于mysql库->user表中
mysql> USE mysql;
mysql> select user,host,authentication_string from user;
+---------------+-----------+-------------------------------------------+
| user | host | authentication_string |
+---------------+-----------+-------------------------------------------+
| root | localhost | *032197AE5731D4664921A6CCAC7CFCE6A0698693 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| zhangsan | localhost | *032197AE5731D4664921A6CCAC7CFCE6A0698693 |
| lisi | localhost | *032197AE5731D4664921A6CCAC7CFCE6A0698693 |
+---------------+-----------+-------------------------------------------+
5 rows in set (0.00 sec)
1.3 重命名用户rename
用户完整名称’zhangsan’@localhost
mysql> rename user 'zhangsan'@'localhost' to 'stevelu'@'localhost';
mysql> select user,host,authentication_string from user;
+---------------+-----------+-------------------------------------------+
| user | host | authentication_string |
+---------------+-----------+-------------------------------------------+
| root | localhost | *032197AE5731D4664921A6CCAC7CFCE6A0698693 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| stevelu | localhost | *032197AE5731D4664921A6CCAC7CFCE6A0698693 |
| lisi | localhost | *032197AE5731D4664921A6CCAC7CFCE6A0698693 |
+---------------+-----------+-------------------------------------------+
5 rows in set (0.00 sec)
1.4 删除用户drop
mysql> drop user 'lisi'@'localhost';
mysql> select user,host,authentication_string from user;
+---------------+-----------+-------------------------------------------+
| user | host | authentication_string |
+---------------+-----------+-------------------------------------------+
| root | localhost | *032197AE5731D4664921A6CCAC7CFCE6A0698693 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| stevelu | localhost | *032197AE5731D4664921A6CCAC7CFCE6A0698693 |
+---------------+-----------+-------------------------------------------+
4 rows in set (0.00 sec)
1.5 修改当前用户登录密码
mysql> set password = password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select user,host,authentication_string from user;
+---------------+-----------+-------------------------------------------+
| user | host | authentication_string |
+---------------+-----------+-------------------------------------------+
| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| stevelu | localhost | *032197AE5731D4664921A6CCAC7CFCE6A0698693 |
+---------------+-----------+-------------------------------------------+
4 rows in set (0.00 sec)
1.6 修改其他用户密码
mysql> set password for 'stevelu'@'localhost'= password('123456');
1.7 忘记root密码
(1)修改/etc/my.cnf配置文件,不使用密码直接登录到mysql
vim /etc/my.cnf
[mysqld]
skip-grant-tables #添加,使登录mysq1不使用授权表
systemctl restart mysqld
(2)使用update 修改root密码,刷新数据库
UPDATE mysql.user SET AUTHENTICATION_STRING=PASSWORD('000000')where user='root';
FLUSH PRIVILEGES;
quit
mysql -u root -p000000
注意:最后再把/etc/my.cnf 配置文件里的skip-grant-tables删除,并重启mysql服务。
1.8 小结
create user ' username'@' address' identified by ' password';
rename user old user to new user;
drop user select user, host, authentication string from mysql. user;
set password=password(' XXXX');
set password for
二、数据库用户授权
all privilege 权限如下:
- insert(插入数据)
- select(查询数据)
- update(更新表的数据)
- delete(删除表中数据)
- create(创建库,表)
- drop(删除库,表)
- refemces
- index(建立索引)
- alter(更改表属性)
- create temp orary tables
- lock tables(锁表)
- execute
- create view(创建视图)
- show view(显示视图)
- create routine(创建存储过程)
- alter routine(修改存储过程)e
- vent(手件)
- tigger on(创建触发器)
2.1 授予权限
GRANT语句:专门用来设置数据库用户的访问权限。
- 当指定的用户名不存在时,GRANT语句将会创建新的用户;
- 当指定的用户名存在时,GRANT 语句用于修改用户信息。
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'来源地址' [IDENTIFIED BY '密码'];
-
权限列表:用于列出授权使用的各种数据库操作,以逗号进行分隔如"select, insert,update"。使用"all"表示所有权限(实际上部分权限仍无法使用,只包括大部分权限),可授权执行任何操作。
-
数据库名.表名:用于指定授权操作的数据库和表的名称,其中可以使用通配符*。如,使用"mysql.*" 表示授权操作的对象为mysql数据库中的所有表。
-
用户名’@‘来源地址’:用于指定用户名称和允许访问的客户机地址,即谁能连接、能从哪里连接。来源地址可以是域名、IP地址,还可以使用“%”通配符,表示某个区域或网段内的所有地址,如“%.stevelu.com"、 “192.168.109.%"等。
-
IDENTIFIED BY: 用于设置用户连接数据库时所使用的密码字符串。在新建用户时,若省略"IDENTIFIED BY"部分,则用户的密码将为空。
2.2.1 示例1
授权zhangsan用户只能查看mysql库中的user表
#授权前查看用户
mysql> use mysql;
mysql> select user,host,authentication_string from user;
+------+-----------------------+-----------------------+
| user | host | authentication_string |
+------+-----------------------+-----------------------+
| root | localhost | |
| root | localhost.localdomain | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | NULL |
| | localhost.localdomain | NULL |
+------+-----------------------+-----------------------+
6 rows in set (0.00 sec)
#对zhangsan用户授权
mysql> grant select on mysql.user to 'zhangsan'@'localhost' identified by '000000';
#退出数据库
quit
#使用该用户登录mysql
[root@localhost ~]# mysql -uzhangsan -p000000
#切换数据库
mysql> use mysql;
Database changed
#查看表
mysql> show tables;
+-----------------+
| Tables_in_mysql |
+-----------------+
| user |
+-----------------+
1 row in set (0.00 sec)
#此时只能查看到user一个表
#创建stevelu库,student表
mysql> create database stevelu;
mysql> create table student;
mysql> create table student(id int,name char);
#对zhangsan用户授权
mysql> grant select on stevelu.student to 'zhangsan'@'localhost' ;
#用zhangsan用户登录后可以查看steveulu库中的student表
mysql> use stevelu;
Database changed
mysql> show tables;
+-------------------+
| Tables_in_stevelu |
+-------------------+
| student |
+-------------------+
1 row in set (0.00 sec)
mysql> select * from student;
Empty set (0.00 sec)
对zhangsan用户授权
mysql> grant select on stevelu.student to ‘zhangsan’@‘localhost’ ;
2.2.2 示例2
授权用户 lisi在192.168.109.131终端远程连接mysql,并拥有查看mysql库中的host表权限。使用密码“000000”进行登录。
#授权
mysql> grant select on mysql.host to 'lisi'@'192.168.109.132' identified by '000000';
#打开192.168.109.132主机进行测试
mysql -ulisi -p000000 -h 192.168.109.136 -P3306
#-p000000,小写p指定登录密码
#-P3306,大写P指定端口号
#-h 192.168.109.136,指定目标IP/主机名
一定要把目标主机的防火墙关掉,否则登不上
2.2 查看用户权限
#查看当前用户(自己)的权限
SHOW GRANTS;
#查看其他用户的权限
SHOW GRANTS FOR 用户名@来源地址;
#查看root的权限
mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
#查看lisi用户的权限
mysql> show grants for 'lisi'@'192.168.109.132'\G;
*************************** 1. row ***************************
Grants for lisi@192.168.109.132: GRANT USAGE ON *.* TO 'lisi'@'192.168.109.132' IDENTIFIED BY PASSWORD '*032197AE5731D4664921A6CCAC7CFCE6A0698693'
*************************** 2. row ***************************
Grants for lisi@192.168.109.132: GRANT ALL PRIVILEGES ON `mysql`.`host` TO 'lisi'@'192.168.109.132'
2 rows in set (0.00 sec)
###################################################################################################
USAGE特权说明符表示“无特权”。它与GRANT一起在全局级别上使用,以在不影响现有帐户特权的情况下修改帐户属性(例如资源限制或SSL特征)。
USAGE是一种在没有授予任何真实特权的情况下告诉MySQL帐户存在的方法。因此,他们仅具有使用 MySQL服务器的权限USAGE。它对应于`mysql`.`user`表中未设置特权的行。
可以把USAGE理解为就是一个登录权限
2.3 撤销用户权限
revoke 权限列表/ALL on 库名.表名 from '用户名'@'来源地址';
#删除zhangsan用户权限
mysql> revoke all on *.* from 'zhangsan'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GRANTS FOR 'zhangsan'@'localhost';
+-----------------------------------------------------------------------------------------------------------------+
| Grants for zhangsan@localhost |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zhangsan'@'localhost' IDENTIFIED BY PASSWORD '*032197AE5731D4664921A6CCAC7CFCE6A0698693' |
| GRANT SELECT ON `mysql`.`user` TO 'zhangsan'@'localhost' |
| GRANT SELECT ON `stevelu`.`student` TO 'zhangsan'@'localhost' |
+-----------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
mysql> revoke select on mysql.user from 'zhangsan'@'localhost';
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW GRANTS FOR 'zhangsan'@'localhost';
+-----------------------------------------------------------------------------------------------------------------+
| Grants for zhangsan@localhost |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zhangsan'@'localhost' IDENTIFIED BY PASSWORD '*032197AE5731D4664921A6CCAC7CFCE6A0698693' |
| GRANT SELECT ON `stevelu`.`student` TO 'zhangsan'@'localhost' |
+-----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> revoke select on stevelu.student from 'zhangsan'@'localhost';
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW GRANTS FOR 'zhangsan'@'localhost';
+-----------------------------------------------------------------------------------------------------------------+
| Grants for zhangsan@localhost |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zhangsan'@'localhost' IDENTIFIED BY PASSWORD '*032197AE5731D4664921A6CCAC7CFCE6A0698693' |
+-----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
必须要精准匹配删除,如果不是所有权限,不能用所有权限删除
会剩下一个USAGE登录权限,若要彻底删除用户使用drop user
2.4 小结
#授权
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'来源地址' [IDENTIFIED BY '密码'];
*表示所有
#远程登录
mysql -ulisi -p000000 -h 192.168.109.136 -P3306
#-u和用户之间空格可有可无,-p和密码之间不能有空格
#-p000000,小写p指定登录密码
#-P3306,大写P指定端口号
#-h 192.168.109.136,指定目标IP/主机名
#查看当前用户(自己)的权限
SHOW GRANTS;
#查看其他用户的权限
SHOW GRANTS FOR 用户名@来源地址;
#撤销用户的权限
revoke 权限列表/ALL on 库名.表名 from 'username'@'address';