人大金仓数据库KingbaseES 系统ANY权限经验分享

关键字：

安全；系统 ANY 权限；；人大金仓；KingbaseES

概述

系统 ANY 权限是 KingbaseES 的一种管理特权，通过授予用户 ANY 权限，允许用户操作所有的某种类型的数据库对象的某种操作，不包括系统对象。

ANY 权限包含了四种数据库对象和八种操作类型。

数据库对象包括：TABLE，VIEW，SEQUENCE，PROCEDURE。

操作类型包括：CREATE，ALTER，DROP，INSERT，UPDATE，DELETE，DROP，EXECUTE。

ANY 权限还为每种权限设置了 ADMIN 选项，标志是否为当前权限的 ADMIN 用户，如果是 ADMIN 用户，那么允许当前用户授权 ANY 权限给其他用户。

特性实际操作

修改 kingbase.conf 文件中 shared_preload_libraries 参数后重启数据库。 shared_preload_libraries = 'sysprivilege' 如何赋予和收回 ANY 权限用例。以表举例:. CREATE USER SYS WITH SUPERUSER; CREATE USER U1; CREATE USER U2; CREATE SCHEMA S1; CREATE TABLE TEST(ID INT); CREATE TABLE S1.ST(ID INT); -- ALL SHOULD BE ERROR, BECAUSE NO ANY PRIVILEGE SET SESSION AUTHORIZATION U1; CREATE TABLE S1.UT(ID INT); ERROR: permission denied for schema s1 LINE 1: CREATE TABLE S1.UT(ID INT); ^ SELECT * FROM S1.ST; ERROR: permission denied for schema s1 LINE 1: SELECT * FROM S1.ST; ^ INSERT INTO S1.ST VALUES(1); ERROR: permission denied for schema s1 LINE 1: INSERT INTO S1.ST VALUES(1); ^ DELETE FROM S1.ST WHERE ID = 1; ERROR: permission denied for schema s1 LINE 1: DELETE FROM S1.ST WHERE ID = 1; ^ UPDATE S1.ST SET ID = 2 WHERE ID = 1; ERROR: permission denied for schema s1 LINE 1: UPDATE S1.ST SET ID = 2 WHERE ID = 1; ^ ALTER TABLE S1.ST RENAME TO STT; ERROR: permission denied for schema s1 ALTER TABLE S1.ST ADD COLUMN NAME VARCHAR(10); ERROR: permission denied for schema s1 DROP TABLE S1.ST; ERROR: permission denied for schema s1 -- GIVEN ALL TABLE SYSTEM ANY PRIVILEGE SET SESSION AUTHORIZATION SYS; GRANT CREATE ANY TABLE TO U1; GRANT SELECT ANY TABLE TO U1; GRANT ALTER ANY TABLE TO U1; GRANT INSERT ANY TABLE TO U1; GRANT DELETE ANY TABLE TO U1; GRANT UPDATE ANY TABLE TO U1; GRANT DROP ANY TABLE TO U1; SELECT * FROM SYS_SYSPRIVILEGE; grantee | privilege | admin_option ---------+-----------+-------------- 16386 | 1 | f 16386 | 4 | f 16386 | 2 | f 16386 | 5 | f 16386 | 6 | f 16386 | 7 | f 16386 | 3 | f (7 rows) -- ALL SHOULD BE RIGHT, BECAUSE HAVE ANY PRIVILEGE SET SESSION AUTHORIZATION U1; CREATE TABLE S1.UT(ID INT); SELECT * FROM S1.ST; id ---- (0 rows) INSERT INTO S1.ST VALUES(1); DELETE FROM S1.ST WHERE ID = 1; UPDATE S1.ST SET ID = 2 WHERE ID = 1; ALTER TABLE S1.ST RENAME TO STT; ALTER TABLE S1.STT ADD COLUMN NAME VARCHAR(10); DROP TABLE S1.STT CASCADE; NOTICE: drop cascades to view s1.sv -- CLEAN SET SESSION AUTHORIZATION SYS; REVOKE CREATE ANY TABLE FROM U1; REVOKE SELECT ANY TABLE FROM U1; REVOKE ALTER ANY TABLE FROM U1; REVOKE INSERT ANY TABLE FROM U1; REVOKE DELETE ANY TABLE FROM U1; REVOKE UPDATE ANY TABLE FROM U1; REVOKE DROP ANY TABLE FROM U1; SELECT * FROM SYS_SYSPRIVILEGE; grantee | privilege | admin_option ---------+-----------+-------------- (0 rows) DROP TABLE S1.UT;

使用经验及注意事项

无特殊事项，注意插件的加载并分清权限即可。

价值评价

通过此特性，可以方便的控制用户权限，方便管理员的管理。

参考资料

《KingbaseES 安全指南》