GaussDB(DWS)实践系列-两级用户权限管理实践

华为云博客 技术博客 23748 10

GaussDB(DWS)两级用户权限管理实践

一、      场景描述

       在项目交付中，经常会用到两级或者多级用户权限管理，例如系统用户分为省和市两级，省级用户包括1名省级管理员和N名省级普通用户，市级用户包括多名市级管理员（每个地市设置1名市级管理员）和N名普通用户。省级管理员对接管理省级用户和市级管理员用户，市级管理员对接管理市级用户，并进行分级用户和权限管理。

例如针对如下场景：结合用户场景需求，梳理用户组织架构。

【说明】

（1）系统管理员是数据库自带的用户，作为一个技术用户和数据库日常维护用户，一般不在日常应用开发中使用。

（2）省级管理员设置1名，可以作为应用开发和管理的全局用户，由省级管理员创建其他地市管理员或者省级普通用户。通常来说，省级管理员只负责创建地市的管理员，之后，由地市管理员管理地市的普通用户，省级管理员也可以创建省级的普通用户。

（3）地市管理员，作为某地市的管理员，可以管理本地市的所有用户。

（4）普通用户，日常应用开发或者查询的用户。

二、      两级用户权限管理实践

由于GaussDB(DWS) 8.0版本暂不支持with grant option（后续版本已支持，整体方案实现步骤会相对简单），结合用户场景需求和组织架构，使用常规赋权语句设计两级用户权限管理方案如下：

  • 省级范围由省级管理员用户统一管理，分解工作如下：
  • 省级管理员创建和管理省级用户组和省级普通用户。
  • 省级管理员将省级schema权限授予省级用户组。
  • 省级管理员创建和管理省级schema。
  • 省级管理员创建市级schema，并将市级schema的owner修改为市级管理员。
  • 省级管理员将省级schema权限授予市级用户组（部分市级用户需要查询使用省级schema相关表信息）。
  • 省级管理员创建市级管理员用户，并赋予create role权限。
  • 市级范围由市级管理员用户统一管理，分解工作如下：
  • 市级管理员创建和管理市级用户组和市级普通用户。
  • 市级管理员将市级schema权限授予市级用户组。
  • 市级管理员管理市级schema。

三、      授权方案验证

1、         用户场景假设

角色假设

角色名称

系统管理员

Ruby

省级管理员

Province_admin

A地市管理员

City_A_admin

B地市管理员

City_B_admin

省级普通用户

Province_01

A地市普通用户01

City_A_user01

A地市普通用户02

City_A_user02

B地市普通用户01

City_B_user01

省级用户组

Province_role01

市级用户组

CityA_role

2、         用户与schema的关系

schema

用户

schema与用户的关系

province_schema

省级管理员

schema的owner

省级普通用户01

普通用户

city_a_schema

地市A管理员

schema的owner

地市A普通用户01

普通用户

地市A普通用户02

普通用户

city_b_schema

地市B管理员

schema的owner

地市B普通用户01

普通用户

 

3、         权限赋予方法

步骤1：首先通过系统管理员（Ruby用户），创建省级管理员province_admin，省级管理员拥有sysadmin的权限。

--i. 以系统管理员的用户登录(数据库后台)

gsql -d postgres -p 8000 -ar

--ii. 创建省级管理员

create user province_admin with sysadmin identified by 'test@123';

 

步骤2：省级管理员相关账号和权限配置命令。

--i.  以省级管理员用户登录（或通过DataStudio工具前台登录）

gsql -d postgres -p 8000 -ar -U province_admin -W test@123

--ii. 创建地市A的管理员用户

create user city_a_admin identified by 'test@123';

--iii. 创建地市B的管理员用户

create user city_b_admin identified by 'test@123';

--iv. 赋予地市A管理员创建用户的权限

alter user city_a_admin createrole;

--v. 赋予地市B管理员创建用户的权限

alter user city_b_admin createrole;

--vi. 创建省级普通用户（注意，省级普通用户不需要createrole的权限）

create user province_user01 identified by 'test@123';

--vii 创建省级用户组，并将省级用户加入用户组；

create role province_role01 identified by 'Bigdata123@';

grant province_role01 to province_user01;

 

步骤3：地市管理员相关账号和权限配置命令。注意，地市A管理员创建地市A的普通用户，不需要给普通用户赋予createrole的权限。

--i. 以地市A的管理员用户登录（或通过DataStudio工具前台登录）

gsql -d postgres -p 8000 -ar -U city_a_admin -W test@123

--ii. 创建地市A的普通用户

create user city_a_user01 identified by 'test@123';

create user city_a_user02 identified by 'test@123';

--iii 创建市级用户组，并将市级用户加入用户组；

create role citya_role identified by 'Bigdata123@';

grant citya_role to city_a_user01;

grant citya_role to city_a_user02;

地市B管理员创建地市B的普通用户（不需要给普通用户赋予createrole的权限）。

--i. 以地市B的管理员用户登录（或通过DataStudio工具前台登录）

gsql -d postgres -p 8000 -ar -U city_b_admin -W test@123

--ii. 创建地市B的普通用户

create user city_b_user01 identified by 'test@123';

--iii 创建市级用户组，并将市级用户加入用户组；

create role cityb_role identified by 'Bigdata123@';

grant cityb_role to city_b_user01;

 

步骤4：由省级管理员创建三个schema，分别为province_schema，city_a_schema，city_b_schema。并将city_a_schema和city_b_schema的owner分别赋予city_a_admin，city_b_admin。

--i. 以省级管理员用户登录（或通过DataStudio工具前台登录）

gsql -d postgres -p 8000 -ar -U province_admin -W test@123

--ii. 创建省级的schema并赋予其拥有者为省级管理员

create schema province_schema authorization province_admin;

--iii. 创建地市A的schema并赋予其拥有者为地市A管理员

create schema city_a_schema;

alter schema city_a_schema owner to city_a_admin;

--iv. 创建地市B的schema并赋予其拥有者为地市B管理员

create schema city_b_schema;

alter schema city_b_schema owner to city_b_admin;

 

步骤5：以省级管理员用户province_admin登录，赋予地市A用户组使用province_schema的权限。

--i. 以省级管理员登录（或通过DataStudio工具前台登录）

gsql -d postgres -p 8000 -ar -U province_admin -W test@123

--ii 设置search_path= province_schema

set search_path= province_schema;

--iii. 赋予地市A用户组使用省级province_schema的权限

grant usage on schema province_schema to citya_role ;

grant select on all tables in schema province_schema to citya_role;

 

步骤6：以地市A管理员用户city_a_admin登录，赋予地市A用户组使用city_a_schema的权限。

--i. 以地市A管理员登录（或通过DataStudio工具前台登录）

gsql -d postgres -p 8000 -ar -U city_a_admin -W test@123

--ii 设置search_path=city_a_schema

set search_path=city_a_schema;

--iii. 赋予地市A用户组使用市级city_a_schema的权限

grant usage on schema city_a_schema to citya_role;

grant select on all tables in schema city_a_schema to citya_role;

 

步骤7：以市级普通用户city_a_user01用户登录，验证是否拥有查询province_schema 和 city_a_schema的权限。

--i. 以city_a_user01的用户登录（或通过DataStudio工具前台登录）

gsql -d postgres -p 8000 -ar -U city_a_user01 -W test@123

--ii. 设置搜索路径为省级schema。

set search_path= province_schema;

--iii. 确认是否有省级schema的查询权限

select * from tbl_a;

--iv. 设置搜索路径为市级schema；

set search_path=city_a_schema;

--v. 确认是否有市级schema的查询权限

select * from tbl_a;

4、         权限回收方法

--i 将省级用户从省级用户组剔除；

revoke province_role01 from province_user01;

--ii 将市级用户从市级用户组剔除；

revoke citya_role from city_a_user01;

 

（完）