关键字：
安全；系统 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 安全指南》
