4.5 权限管理

权限模型

HybridDB的权限管理是参照MySQL的权限实现,控制登录的用户是否具备操作数据库某些行为的权限。

mysql文档
https://dev.mysql.com/doc/refman/5.7/en/privilege-system.html

支持的权限粒度

HybridDB支持下面四个粒度的权限控制:

既如果希望创建一个user只能select某一个表记录,就可以只赋予这个user对于这个table的select权限。

  • global
  • db
  • table
  • column

HibridDB数据操作主要命令权限映射关系

Command Need Priv 权限是否支持DB级别 权限是否支持Table级别 权限是否支持Column级别 备注
Select Select
Insert Insert
Insert … select …. Insert + Select
Update Update
Delete Delete ×
Truncate Table Drop ×
Alter Table Alter + Insert+Create ×
Create Database Create × × ×
Create Table Create ×
Show Create Table Select ×
Drop Database Drop × ×
Drop Table Drop ×
Create View Create_View+Select × 当执行Create View Replace命令时,额外需要Drop权限
Drop View Drop ×
Show Create View Show_View+Select ×
CREATE_PROCEDURE CREATE_ROUTINE × × ×
DROP_PROCEDURE ALTER_ROUTINE × × ×
CREATE_EVENT EVENT × × ×
DROP_EVENT EVENT × × ×
Create User/Drop User/Rename User Create_User × × × 创建/删除/修改用户
Set Password Super × × × 设置密码命令
Grant/Revoke Grant × × × 授权命令

权限相关命令

注意:

  • Create User Account_Name命令中,Account_Name格式为:'user_name'@'host_name' ,当前版本的host_name仅允许传入为'%'通配符。既所有client主机都能匹配成功,目前client主机拦截使用白名单功能。

create user

语法:

CREATE USER
    [if not exists] user [auth_option] [, [if not exists] user [auth_option]] ...

user:
    (see Section 6.2.3, “Specifying Account Names”)

auth_option: {
    IDENTIFIED BY 'auth_string'
  | IDENTIFIED BY PASSWORD 'hash_string'
}

MySQL CREATE USER命令文档:https://dev.mysql.com/doc/refman/5.6/en/create-user.html

相比较MySQL Create User:

  • auth_option中不支持
    • IDENTIFIED WITH auth_plugin
    • IDENTIFIED WITH auth_plugin AS 'hash_string'
  • 增加了 if not exists 的支持

示范:

create user if not exists 'test'@'%' IDENTIFIED BY 'passwd';
create user 'test2'@'%' IDENTIFIED BY 'passwd';

执行需要具备权限

CREATE USER privilege

rename user

修改用户名

语法

RENAME USER old_user TO new_user
    [, old_user TO new_user] ...

drop user

语法

DROP USER [if exists] user [, [if exists] user] ...

示范

drop user if exists 'test'@'%', if exists 'test2'

执行需要具备权限

  • CREATE USER privilege

grant

语法

GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON priv_level
    TO user [auth_option]
    [WITH {GRANT OPTION}]

priv_level: {
    *
  | *.*
  | db_name.*
  | db_name.tbl_name
  | tbl_name
}

user:
    (see Section 6.2.3, “Specifying Account Names”)

auth_option: {
    IDENTIFIED BY 'auth_string'
  | IDENTIFIED BY PASSWORD 'hash_string'
}

示范

创建全局dml帐号 grant insert,select,update,delete on *.* to 'test'@'%' identified by 'testpassword';
创建db级别dml帐号 grant insert,select,update,delete on dbName.* to 'test'@'%' identified by 'testpassword';

执行需要具备权限

  • GRANT OPTION privilege
  • 对赋予的权限,执行grant语句的用户必须包含

revoke

语法1

REVOKE
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    FROM user

示范

回收只读权限
revoke select on db.table from 'test'@'%'

执行需要具备权限

  • GRANT OPTION privilege
  • 对回收的权限,执行revoke语句的用户必须包含

语法2

REVOKE ALL [PRIVILEGES], GRANT OPTION
    FROM user

回收用户的所有权限

执行需要具备权限

  • CREATE USER privilege

查看登录用户的权限

show grants

results matching ""

    No results matching ""