MariaDB 用户与权限管理
MariaDB 是一个多用户数据库,具有功能强大的访问控制系统,可以为不同用户指定允许的权限.MariaDB用户可以分为普通用户和ROOT用户.ROOT用户是超级管理员,拥有所有权限,包括创建用户、删除用户和修改用户的密码等管理权限,普通用户只拥有被授予的各种权限.
MariaDB 权限概述
MariaDB 服务器通过权限表来控制用户对数据库的访问,权限表存放在MariaDB的数据库中,由MySQL_install_db脚本初始化，,存储账户权限信息表主要有:user、db、host、table_priv,columns_priv和procs_priv.
◆USER表◆
user表是MariaDB中最重要的一个权限表,记录允许连接到服务器的账号信息,里面的权限是全局的,MariaDB中user表一共有42个字段,用户列可分为4类,分别是用户列,权限列,安全列和资源控制列,下面我们介绍介个常用列的作用.
| 字段名 | 数据类型 | 默认值 |
|---|---|---|
| Host | char | NULL |
| User | char | NULL |
| Password | char | NULL |
| ssl_cipher | blob | NULL |
| x509_issuer | blob | NULL |
| x509_subject | blob | NULL |
| max_questions | int | 0 |
| max_updates | int | 0 |
| max_connections | int | 0 |
| max_user_connections | int | 0 |
用户列:
user表的用户列包括Host、User、Password,分别表示主机名、用户名和密码.其中User和Host为User表的联合主键,当用户与服务器之间建立连接时,输入的账户信息中的用户名称、主机名和密码必须匹配User表中对应的字段,只有3个值都匹配的时候,才允许连接建立.这3个字段的值就是创建账户时保存的账户信息,修改用户密码时,实际就是修改user表的Password字段的值.
权限列:
权限列的字段决定了用户的权限,描述了在全局范围内允许对数据和数据库进行的操作.包括查询权限、修改权限等普通权限,还包括了关闭服务器、超级权限和加载用户等高级权限,普通权限用于操作数据库,高级权限用于数据库管理.
安全列:
安全列只有6个字段,其中两个是SSL相关的,两个是 x509 相关的,另外两个是授权插件相关的,SSL用于加密, x509 标准可用于标识用户,Plugin字段标识可以用于验证用户身份的插件,如果该字段为空,服务器使用内建授权验证机制验证用户身份.
资源控制列:
资源控制列用来限制用户使用的资源,包括以下4个字段,分别为:
max_questions:用户每小时允许执行查询次数.
max_updates:用户每小时允许执行更新次数.
max_connections:用户每小时允许执行的连接次数.
max_user_connection:用户允许同时建立的连接次数.
◆DB权限表◆
DB表和HOST表,在数据库中非常重要的权限表,DB表中存储了用户对某个数据库的操作权限,决定用户能从哪个主机存取哪个数据库.host表中存储了某个主机对数据库的操作权限,配合db权限表对给定主机上数据库级操作权限做更细致的控制.这个权限表不受GRANT 和 REVOKE语句的影响,db表比较常用,host表一般很少使用.db表和host表结构相似,字段大致可以分为两类:用户列和权限列.
select_priv,Insert_priv,update_priv,delete_priv,Create_priv,Drop_priv,Alter_priv,Grant_priv
| 字段名 | 数据类型 | 默认值 |
|---|---|---|
| Host | char | NULL |
| DB | char | NULL |
| USER | char | NULL |
| select_priv | enum(N,Y) | NO |
| insert_priv | enum(N,Y) | NO |
| update_priv | enum(N,Y) | NO |
| delete_priv | enum(N,Y) | NO |
| create_priv | enum(N,Y) | NO |
| drop_priv | enum(N,Y) | NO |
| alter_priv | enum(N,Y) | NO |
| grant_priv | enum(N,Y) | NO |
DB表用户列有3个字段,分别是Host、User、Db标识从某个主机连接某个用户对某个数据库的操作权限,这3个字段的组合构成了db表的主键.host表不存储用户名称,用户列只有2个字段,分别是Host和DB,表示从某个主机连接的用户对某个数据库的操作权限,其主键包括Host和Db两个字段,host很少用到,一般情况下db表就可以满足权限控制需求了.
MariaDB 账户管理
MariaDB提供了许多语句来管理用户账号,这些语句可以用来管理包括登陆和退出MariaDB服务器,创建用户,删除用户,密码管理和权限管理等,MariaDB数据库的安全性,需要通过账户管理来保证.
◆查询在线用户◆
本地查询: 当我们本地登录到数据库时,可以使用本地查询,查询SQL语句如下.
MariaDB [(none)]> show processlist;
+----+---------+-----------+------+---------+------+-------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+---------+-----------+------+---------+------+-------+------------------+----------+
| 2 | root | localhost | NULL | Query | 0 | NULL | show processlist | 0.000 |
| 5 | lyshark | localhost | NULL | Sleep | 4 | | NULL | 0.000 |
+----+---------+-----------+------+---------+------+-------+------------------+----------+
2 rows in set (0.08 sec)
远程查询: 如果在远程终端机上查询远程数据库,前提是数据库开启了远程授权我们可以使用以下SQL语句.
[root@localhost ~]# mysqladmin -uroot -p123 processlist
+----+---------+-----------+----+---------+------+-------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+---------+-----------+----+---------+------+-------+------------------+----------+
| 5 | lyshark | localhost | | Sleep | 154 | | | 0.000 |
| 11 | root | localhost | | Query | 0 | | show processlist | 0.000 |
+----+---------+-----------+----+---------+------+-------+------------------+----------+
[root@localhost ~]#
查全部用户: 我们通过构建Select语句查询指定字段(Host,User,Password),查询mysql.user这个数据表,SQL语句如下.
MariaDB [none]> select Host,User,Password from mysql.user;
+-----------+---------+-------------------------------------------+
| Host | User | Password |
+-----------+---------+-------------------------------------------+
| localhost | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| 127.0.0.1 | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| ::1 | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| localhost | lyshark | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+-----------+---------+-------------------------------------------+
4 rows in set (0.01 sec)
去重查询: 通过使用distinct命令使查询结果不重复,自动过滤重复的记录.
MariaDB [(none)]> select distinct User,Password from mysql.user;
+---------+-------------------------------------------+
| User | Password |
+---------+-------------------------------------------+
| root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| lyshark | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+---------+-------------------------------------------+
2 rows in set (0.00 sec)
◆新建普通用户◆
创建新用户,必须有相应的权限来执行创建操作.在MariaDB数据库中,有两种方式创建新用户:一种是使用CREATE USER或GRANT语句,另一种是直接操作MariaDB授权表,最好的方法是使用GRANT语句,因为这样更精确,如果使用create语句创建用户后用户无权限,需要手动添加权限,而直接使用grant语句可以一步到位.
使用create user语句创建新用户:
1.使用create user创建一个用户,名称为jeffrey,密码是mypass,指定开启%远程权限.
MariaDB [(none)]> create user 'jeffrey'@'%' identified by 'mypass';
Query OK, 0 rows affected (0.05 sec)
2.接着继续创建一个新用户jeffreys,密码是mypass,并指定使用localhost本地权限.
MariaDB [(none)]> select password('mypass');
+-------------------------------------------+
| password('mypass') |
+-------------------------------------------+
| *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 |
+-------------------------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> create user 'jeffreys'@'localhost' identified by password '*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4';
Query OK, 0 rows affected (0.00 sec)
使用grant user语句创建新用户:
使用grant语句创建一个新用户myuser,密码是123123,并授予用户对所有表的select和update权限,SQL语句如下:
MariaDB [(none)]> grant select,update ON *.* TO 'myuser'@'localhost' identified by '123123';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> select Host,User,Select_priv,Update_priv from mysql.user where user='myuser';
+-----------+--------+-------------+-------------+
| Host | User | Select_priv | Update_priv |
+-----------+--------+-------------+-------------+
| localhost | myuser | Y | Y |
+-----------+--------+-------------+-------------+
1 row in set (0.00 sec)
◆删除普通用户◆
使用drop user语句删除用户:
MariaDB [(none)]> select distinct User,Host from mysql.user;
+---------+-----------+
| User | Host |
+---------+-----------+
| root | 127.0.0.1 |
| root | ::1 |
| lyshark | localhost |
| root | localhost |
+---------+-----------+
4 rows in set (0.07 sec)
MariaDB [(none)]> drop user lyshark@"localhost";
Query OK, 0 rows affected (0.37 sec)
MariaDB [(none)]> select distinct User,Host from mysql.user;
+------+-----------+
| User | Host |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1 |
| root | localhost |
+------+-----------+
3 rows in set (0.00 sec)
MariaDB [(none)]>
使用delete语句删除用户:
MariaDB [(none)]> delete from mysql.user where host='localhost' and user='myuser';
Query OK, 1 row affected (0.01 sec)
◆修改用户密码◆
修改自身密码: 修改自己用户的用户名和密码.
MariaDB [(none)]> set password=password("123123");
Query OK, 0 rows affected (0.00 sec)
修改指定用户的密码: 修改lyshark用户授权方式为localhost的密码为123123
MariaDB [(none)]> set password for "lyshark"@"localhost"=password("123123");
Query OK, 0 rows affected (0.00 sec)
◆ROOT密码找回◆
1.关闭MariaDB数据库
[root@localhost ~]# systemctl stop mariadb
[root@localhost ~]# systemctl status mariadb
2.修改MariaDB的主配置文件,在Mysqld区域添加skip-grant-table语句,保存退出
[root@localhost etc]# ll /etc/my.cnf
-rw-r--r--. 1 root root 570 6月 8 2017 /etc/my.cnf
[root@localhost etc]#
[root@localhost etc]# vim /etc/my.cnf
[mysqld]
skip-grant-table #添加我就可以了
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
3.重启MariaDB数据库
[root@localhost ~]# systemctl restart mariadb
[root@localhost ~]# systemctl status mariadb
4.免密码登陆数据库,并执行修改数据库的SQL语句
[root@localhost ~]# mysql -uroot -p
MariaDB [(none)]> update mysql.user set password=password("123") where user="root";
MariaDB [(none)]> Ctrl-C -- exit!
5.修改完成后将主配置文件的skip-grant-table属性去掉,重启数据库即可,下次使用密码登陆即可
[root@localhost ~]# vim /etc/my.cnf
[root@localhost ~]# systemctl restart mariadb
[root@localhost ~]# mysql -uroot -p123
MariaDB 权限管理
权限管理主要是对登录到MariaDB的用户进行权限验证所有用户的权限都存储在MariaDB的权限表中,不合理的权限规划会给MariaDB服务器带来安全隐患,数据库管理员要对所有用户的权限进行合理规划管理.
◆查看权限◆
查询所有权限: 查询数据库中所有用户列表和权限信息.
MariaDB [(none)]> select distinct concat("用户:",user," 权限:",host," : ") as query from mysql.user;
+------------------------------------+
| query |
+------------------------------------+
| 用户:root 权限:localhost : |
| 用户:root 权限:127.0.0.1 : |
| 用户:root 权限:::1 : |
| 用户:lyshark 权限:localhost : |
| 用户:lyshark 权限:% : |
+------------------------------------+
8 rows in set (0.46 sec)
MariaDB [(none)]>
查询指定权限: 查询关于lyshark的所有权限信息
MariaDB [(none)]> show grants for lyshark;
+----------------------------------------------+
| Grants for lyshark@% |
+----------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'lyshark'@'%' |
+----------------------------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]>
查询指定权限: 查询lyshark用户的远程%权限,和本地localhost权限
MariaDB [(none)]> show grants for "lyshark"@"%";
+----------------------------------------------+
| Grants for lyshark@% |
+----------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'lyshark'@'%' |
+----------------------------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> show grants for "lyshark"@"localhost";
+----------------------------------------------------------------------------------------------------------------+
| Grants for lyshark@localhost |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'lyshark'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.37 sec)
MariaDB [(none)]>
◆授予权限◆
创建用户并授权: 创建wang用户并给予%远程登陆的权限,并对所有数据库全部授权
MariaDB [(none)]> grant all on *.* to "wang"@"%" identified by "123";
Query OK, 0 rows affected (0.15 sec)
MariaDB [(none)]> show grants for "wang"@"%";
+--------------------------------------------------------------------------------------------------------------+
| Grants for wang@% |
+--------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'wang'@'%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
创建用户并授权: 创建用户名wang1,并允许localhost本机访问,对mysql库中的所有表具有select查询权限,密码为123
MariaDB [(none)]> grant select on mysql.* to "wang1"@"localhost" identified by "123";
Query OK, 0 rows affected (0.36 sec)
MariaDB [(none)]> show grants for "wang1"@"localhost";
+--------------------------------------------------------------------------------------------------------------+
| Grants for wang1@localhost |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wang1'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
| GRANT SELECT ON `mysql`.* TO 'wang1'@'localhost' |
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
MariaDB [(none)]>
创建用户并授权: 创建用户名wang2,且可在任意主机%登陆,并对所有数据库有(增删改查)权限,密码为123
MariaDB [(none)]> grant insert,delete,update,select on *.* to "wang2"@"%" identified by "123";
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show grants for "wang2"@"%";
+-------------------------------------------------------------------------------------------------------------------------------+
| Grants for wang2@% |
+-------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'wang2'@'%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]>
创建用户并授权: 创建用户名wang3,且只能在192.168.1.59上登陆,并对mysql数据库有(查)权限,密码为123
MariaDB [(none)]> grant select on mysql.* to "wang3"@"192.168.1.59" identified by "123";
Query OK, 0 rows affected (0.15 sec)
MariaDB [(none)]> show grants for "wang3"@"192.168.1.59";
+-----------------------------------------------------------------------------------------------------------------+
| Grants for wang3@192.168.1.59 |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wang3'@'192.168.1.59' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
| GRANT SELECT ON `mysql`.* TO 'wang3'@'192.168.1.59' |
+-----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.50 sec)
MariaDB [(none)]>
创建用户并授权: 创建一个普通用户wang4,且仅有mysql库的(查)权限,密码为123
MariaDB [(none)]> grant usage,select on mysql.* to "wang4"@"localhost" identified by "123";
Query OK, 0 rows affected (0.35 sec)
只授权用户权限: 授权用户wang4,对所有数据库的全部权限,密码123
MariaDB [(none)]> grant all privileges on *.* to "wang4"@"localhost" identified by "123";
Query OK, 0 rows affected (0.36 sec)
只授权用户权限: 授权一个已存在账号允许远程登陆最大权限
MariaDB [(none)]> grant all on *.* to "root"@"%";
Query OK, 0 rows affected (0.07 sec)
◆收回权限◆
收回用户授权: 撤销lyshark用户,对所有数据库的远程%用户权限
MariaDB [(none)]> show grants for lyshark;
+----------------------------------------------+
| Grants for lyshark@% |
+----------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'lyshark'@'%' |
+----------------------------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> revoke all on *.* from "lyshark"@"%";
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show grants for lyshark;
+-------------------------------------+
| Grants for lyshark@% |
+-------------------------------------+
| GRANT USAGE ON *.* TO 'lyshark'@'%' |
+-------------------------------------+
1 row in set (0.00 sec)
收回用户授权: 撤销lyshark用户的远程登陆权限
MariaDB [(none)]> revoke create on *.* from "lyshark"@"%";
Query OK, 0 rows affected (0.01 sec)
刷新权限: 修改完成以后,记得执行权限的刷新操作.
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.01 sec)
本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！
