人大金仓数据库KingbaseES 系统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;