磐维数据库用户与权限管理篇（三）

引言

在磐维数据库用户与权限管理篇（一）的“授权场景”中提及了四种常见的授权形式：

• 系统权限授权
• 对象权限授权
• 用户与用户授权
• 级联授权

在生产实践中，面对海量的数据库对象和不断新增（或删除）的数据库用户，用户的权限管理成为了一个比较繁杂的事情。本文将围绕第三点进行展开，旨在帮助降低数据库用户、运维人员在授权方面的繁杂程度。

三种角色

前文中虽然强调过用户和角色不会刻意区分，但在这里我们把用户（user）作为业务连接数据库的标识，角色（role）作为对象权限的集合，通过把三种不同权限范围的角色授权给业务用户，实现通过角色来管理业务用户的权限：

• cmit_roleol：可读可写高运维角色，拥有schema下所有表、序列的所有权，可自建表和序列
• cmit_rolea：可读可写普通运维角色，拥有schema下所有表的只读权限，可自建表和序列
• cmit_rolero：仅读角色，拥有schema下所有表的只读权限，不可自建表和序列

角色权限管理

1. cmit_roleol角色

# 首先创建三种角色、一个示例schema：tt
# create role cmit_roleol password disable;
# create role cmit_rolea password disable;
# create role cmit_rolero password disable;
grant create,usage on schema tt to cmit_roleol;
grant all privileges on all tables in schema tt to cmit_roleol;
grant all privileges on all sequences in schema tt to cmit_roleol;


2. cmit_rolea角色

grant create,usage on schema tt to to cmit_rolea;
grant select on all tables in schema tt to cmit_rolea;
grant select on all sequences in schema tt to cmit_rolea;


3. cmit_rolero角色

grant usage on schema tt to cmit_rolero;
grant select on all tables in schema tt to cmit_rolero;
grant select on all sequences in schema tt to cmit_rolero;


以上便是三种角色在tt模式下的权限规范，如果现在有三个业务用户u1,u2,u3，他们都需要tt模式下的高运维权限：

grant cmit_roleol to u1,u2,u3;


都只需要普通运维权限

grant cmit_rolea to u1,u2,u3;


都只需要仅读权限

grant cmit_rolero to u1,u2,u3;


但是上面的方式并不能实现组内用户之前新增对象的自动赋权，为了实现这一点，需要进行如下操作进一步授权：

--cmit_roleol
alter default privileges for user [cmit_roleol组内用户][,cmit_rolea组内用户] in schema tt grant all privileges on tables to {cmit_roleol组内用户};
alter default privileges for user [cmit_roleol组内用户][,cmit_rolea组内用户] in schema tt grant all privileges on sequences to {cmit_roleol组内用户};

--cmit_rolea
alter default privileges for user [cmit_roleol组内用户][,cmit_rolea组内用户] in schema tt grant select on tables to {cmit_rolea组内用户};
alter default privileges for user [cmit_roleol组内用户][,cmit_rolea组内用户] in schema tt grant select on sequences to {cmit_rolea组内用户};

--cmit_rolero
alter default privileges for user [cmit_roleol组内用户][,cmit_rolea组内用户] in schema tt grant select on tables to {cmit_rolero组内用户};
alter default privileges for user [cmit_roleol组内用户][,cmit_rolea组内用户] in schema tt grant select on sequences to {cmit_rolero组内用户};


组成员管理

随着业务侧运维侧相关人员的入职、离职等事件的发生，业务账号也需要不定期得进行维护，如遇新增用户应该如何快捷得授权、如需删除用户又需如何安全地处理?

1. 新增某一角色下对应的用户

• cmit_roleol类

grant cmit_roleol to new_user;
alter default privileges for user [cmit_roleol组内用户][,cmit_rolea组内用户] in schema tt grant all privileges on tables|sequences to [cmit_roleol组内用户][,cmit_rolea组内用户];


• cmit_rolea类

grant cmit_rolea to new_user;
alter default privileges for user [cmit_roleol组内用户][,cmit_rolea组内用户] in schema tt grant select on tables|sequences to [cmit_roleol组内用户][,cmit_rolea组内用户];


• cmit_rolero类

grant cmit_rolero to new_user;
alter default privileges for user [cmit_roleol组内用户][,cmit_rolea组内用户] in schema tt grant select on tables|sequences to new_user;


2. 删除某一角色下对应的用户

# 1.转让owner是被删除角色的对象，参考磐维数据库用户与权限管理篇（二）
# 2.回收角色权限
revoke cmit_roleol|cmit_rolea|cmit_rolero from deleted_user;
# 3.回收自动赋予权限
alter default privileges for user [cmit_roleol组内用户][,cmit_rolea组内用户] in schema tt revoke on tables|sequences from deleted_user;
# 4.安全删除用户，参考磐维数据库用户与权限管理篇（二）


总结

按需设置多种角色来管理权限集合，可以实现更加方便地对业务账号进行权限管理。