Hologres的权限模型

一、简单权限模型

1、开启函数调用

create extension spm;

2、开启简单权限模型

调用spm_enable()函数后，系统将会自动创建{db}_admin、{db}_developer、{db}_writer和{db}_viewer 4个用户组。

call spm_enable();  // 开启当前DB的简单权限模型。

--查看是否开启SPM
SHOW hg_experimental_enable_spm;

--查看是否开启SLPM
SHOW hg_enable_slpm;

3、专家权限模型迁移

如果DB是专家权限模型,且DB中有表,视图或外表, 必须执行; 否则会出现表权限缺失

call spm_migrate();  
--将DB中已有的对象change owner到developer，使用SPM管理。当遇到DONE BUT NOT COMPLETED提示，说明全部对象还没有迁移完毕，您需要继续执行spm_migrate()迁移。

4、创建用户

创建的用户仅具有登录权限，您需要授予其具体的权限才能进行开发。

call spm_create_user ('云账号UID/云邮箱/RAM账号'); // 创建用户，使用云邮箱时还需要加双引号
call spm_create_user ('云账号UID/云邮箱/RAM账号', '<dbname>_[admin|developer|writer|viewer]'); // 创建用户的同时把用户加入对应的用户组

示例:

call spm_create_user ('xxx.onaliyun.com', 'testdb_developer');
create user "BASIC$<user_name>" with password '<password>';

5、授权新用户加入用户组

--语法: 仅Superuser和{db}_admin可以调用该函数。
call spm_grant('{dbname}_[admin|developer|writer|viewer]', '云账号id/云邮箱/RAM账号'); // 将某个用户加入某个用户组。

示例:

// 加入某个DB的admin用户组。
call spm_grant('mydb_admin', 'p4_564306222995xxx'); // 将564306222995xxx（RAM用户）加入数据库mydb的admin用户组。
call spm_grant('mydb_admin', '197006222995xxx'); // 将197006222995xxx（云账号）加入数据库mydb的admin用户组。
call spm_grant('mydb_admin', 'ALIYUN$xxx'); // 将xxx@aliyun.com加入数据库mydb的admin用户组。

// 加入某个DB的developer用户组。
call spm_grant('mydb_developer', 'p4_564306222995xxx'); // 将564306222995xxx（RAM用户）加入数据库mydb的developer用户组。
call spm_grant('mydb_developer', '197006222995xxx'); // 将197006222995xxx（云账号）加入数据库mydb的developer用户组。
call spm_grant('mydb_developer', 'RAM$mainaccount:subuser');// 将云账号mainaccount的RAM用户subuser加入数据库mydb的developer用户组。

// 加入某个DB的viewer用户组。
call spm_grant('"MYDB_viewer"', 'p4_564306222995xxx'); // 将564306222995xxx（RAM用户）加入数据库"MYDB"的viewer用户组。
call spm_grant('"MYDB_viewer"', '197006222995xxx'); // 将197006222995xxx（云账号）加入数据库"MYDB"的viewer用户组。
call spm_grant('mydb_viewer', '"xxx@aliyun.com"'); // 将账号xxx@aliyun.com加入数据库mydb的viewer用户组。

6、移除用户组

--语法: 仅Superuser和{db}_admin可以调用该函数。
call spm_revoke('<dbname>_[admin|developer|writer|viewer]', '云账号id/云账号'); // 移除某用户的权限。

示例:

CALL spm_revoke('mydb_developer', 'p4_202338382183xxx');//将RAM用户从mydb的developer用户组移除。
CALL spm_revoke('mydb_admin', 'RAM$my_test:xxx');//将RAM用户从mydb的admin用户组移除。
CALL spm_revoke('otherdb_admin', '13532313103042xxx'); // 错误，移出其他DB的用户组请连接到其他DB执行spm_revoke。

7、删除用户

DROP ROLE "云账号ID/云邮箱/RAM账号"; // 直接将该用户从实例中删除。

二、专家权限模型

1、关闭简单权限模型

call spm_disable();

注意事项:

只能由Superuser执行关闭操作。

Public拥有Public Schema的USAGE及CREATE权限。

Public拥有DB的CONNECT及TEMPORARY权限。

Public拥有functions及procedures的EXECUTE权限。

Public拥有language, data types (include domains)的USAGE权限。

Public不拥有其他对象如table、view、materialized view、table
column、sequence、foreign data wrapper、foreign server及schema（除public
schema）等的权限。

关闭简单权限模型之后，用户组的权限如下：

admin：保留对当前已有对象的权限，但对新建数据库对象不生效。

developer用户组：保留对当前已有对象的权限，但对新建数据库对象不生效。

writer用户组：保留对当前已有对象的权限，但对新建数据库对象不生效。

viewer用户组：保留对当前已有对象的权限，但对新建数据库对象不生效。

2、清除用户组(可选)

通常情况下，为了方便管理不建议删除用户组。

--场景1：删除用户组保留DB。
call spm_cleanup('{dbname}');

--场景2：先删除DB再执行下面语句删除用户组。
call spm_cleanup('mydb');

3、创建用户

--使用阿里云账号ID创建用户。
CREATE USER "11822780xxx";
--授予RAM用户Superuser权限。
CREATE USER "p4_1822780xxx" SUPERUSER; 

4、授予权限

专家权限模型的授权方式

--授予Schema访问权限(必须授予Schema的访问权限才能有表的查询权限。)
GRANT USAGE ON SCHEMA schema_name  TO "云账号/云邮箱";

--授予用户public schema中所有表的SELECT权限(这里注意是现有表)
GRANT SELECT ON ALL TABLES IN SCHEMA public TO "云账号/云邮箱";

--设置当前授权人在publicSchema下创建的未来表对所有人可读。
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO PUBLIC;
--默认让USER1 在publicSchema下创建的未来表对所有人可读。
ALTER DEFAULT PRIVILEGES FOR ROLE USER1 IN SCHEMA public GRANT SELECT ON TABLES TO PUBLIC;

--修改普通用户为Superuser
ALTER USER "云账号/云邮箱" SUPERUSER;

--授予其他用户表的Owner权限
ALTER TABLE <tablename> OWNER TO "云账号/云邮箱";

--授予某个用户某个表的某些列的查询权限
GRANT SELECT (<column1>,<column2>,<column3>,...) ON TABLE <tablename> TO "云账号/云邮箱" ;

照以下示例给一个新用户授予某张表的查询权限。

CREATE USER "云账号/云邮箱";
GRANT USAGE ON SCHEMA <schema_name>  TO "云账号/云邮箱";
GRANT SELECT ON TABLE <tablename> TO "云账号/云邮箱";

5、查看授权

SELECT ROLNAME FROM pg_roles;
SELECT user_display_name(ROLNAME) FROM pg_roles;

6、撤销授权

REVOKE SELECT ON TABLE tablename FROM "云账号ID/云邮箱" ; --如果是RAM用户，账号格式请使用RAM用户的表达格式。

7、再次开启简单权限模型

call spm_enable('t');  // 开启当前DB的简单权限模型。
call spm_migrate();  // 将DB中已有的对象，change owner到developer，使用SPM管理。

三、其他相关命令

1、账号ID转换为阿里云账号

SELECT USER_DISPLAY_NAME (user_name);
示例:
SELECT USER_DISPLAY_NAME ('4356345645622343');

2、阿里云账号转换为账号ID

SELECT HG_DISPLAY_NAME_TO_ID (aliyun_id);
示例:
SELECT HG_DISPLAY_NAME_TO_ID ('RAM$main:subuser');