Mariadb 权限管理，Mariadb权限管理

Mariadb 权限管理，Mariadb权限管理

Mariadb用户和权限管理

权限类别

库级别
表级别
字段级别
管理类
程序类

管理类：

CREATE TEMPORARY TABLES 创建临时表
CREATE USER 创建用户
FILE
SUPER
SHOW DATABASES
RELOAD
SHUTDOWN
REPLACTION SLAVE
REPLACTION CLIENT
LOCK TABLES
PRECESS

程序类：

FUNCTION
PROCEDURE
TRIGGER

CREATE,ALTER,DROP,EXCUTE

库和表级别：

ALTER
CREATE
CREATE VIEW
UPDATE
INDEX
DROP
SHOW VIEW
GRANT OPTION: 能否将自己的权限转送给另一个用户

数据操作

SELECT
DELETE
UPDATE
INSERT

字段级别

SELECT(COLL1,COLL2...)
UPDATE(COLL1,COLL2...)
...

所有权限为ALL PROVILEGES
mysql的元数据库 mysql

mysql库内有授权表
db,host,user
其中user表上可以查看用户具有哪些权限,以及用户的密码（加密或者明文）
DESC user;
-->
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field                  | Type                              | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host                   | char(60)                          | NO   | PRI |         |       |
| User                   | char(16)                          | NO   | PRI |         |       |
| Password               | char(41)                          | NO   |     |         |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Drop_priv              | enum('N','Y')                     | NO   |     | N       |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Process_priv           | enum('N','Y')                     | NO   |     | N       |       |
| File_priv              | enum('N','Y')                     | NO   |     | N       |       |
| Grant_priv             | enum('N','Y')                     | NO   |     | N       |       |
| References_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Index_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Alter_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Show_db_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Super_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N       |       |
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Execute_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Repl_client_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Create_view_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Show_view_priv         | enum('N','Y')                     | NO   |     | N       |       |
| Create_routine_priv    | enum('N','Y')                     | NO   |     | N       |       |
| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N       |       |
| Create_user_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N       |       |
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       |
| ssl_cipher             | blob                              | NO   |     | NULL    |       |
| x509_issuer            | blob                              | NO   |     | NULL    |       |
| x509_subject           | blob                              | NO   |     | NULL    |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0       |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0       |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0       |       |
| max_user_connections   | int(11)                           | NO   |     | 0       |       |
| plugin                 | char(64)                          | NO   |     |         |       |
| authentication_string  | text                              | NO   |     | NULL    |       |
+------------------------+-----------------------------------+------+-----+---------+-------+

用户账号

'USERNAME'@'HOST'
	@'HOST'可以使用如下格式
	@'192.168.1.1'
	@'%.%.%.%'
	@'192.168.%.%'
	@'localhost'

创建用户

CREATE USER 'USERNAME'@'HOST' [IDENTIFIED BY 'PASS'];
查看用户所有的权限
SHOW GRANT FOR 'user'@'host';

用户重命名

RENAME USER old_name TO new_name;

删除用户

DROP USER 'username'@'host';

修改密码

SET PASSWORD FOR username = PASSWORD('password');
	SET PASSWORD FOR slackware = PASSWORD('openstack');
修改mysql.user表
	UPDATE mysql.user SET Password=PASSWORD('passwoard') WHERE User='xxx';
mysqladmin password命令

忘记管理员密码解决办法

在/etc/my.cnf中的mysqld段添加
skip_grant_tables
skip_networking
systemctl stop mariadb
systemctl start mariadb
可以忽略授权表登录并且使用root用户登录mysql数据库
然后可以修改root的密码
修改完退出以后，删除my.cnf中的两个参数

授权

GRANT prvi_type[,...] ON [table|function|procedure] db.{table|routine} TO 
'username'@'HOST' [IDENTIFIED BY 'password'] [REQUIRE SSL] [WITH with_options]
with_options:
	MAX_QUERIES_PER_HOUR count(数值)
	MAX_UPDATES_PER_HOUR count
	MAX_CONNECTIONS_PER_HOUR count
	MAX_USER_CONNECTIONS count

取消授权

REVOKE prvi_type [[(column_list)] [,(column_list)]] ... ON prvi_level FROM user