一、权限授予操作
1. 基础权限赋予
1.1 创建测试环境
-- 1.创建测试用户
test=# CREATE USER schema_user WITH PASSWORD 'Schema@123';
CREATE ROLE-- 2.创建测试Schema
test=# CREATE SCHEMA test_schema AUTHORIZATION current_user;
CREATE SCHEMA-- 3. 创建测试表
test=# CREATE TABLE test_schema.employees (
test(# id SERIAL PRIMARY KEY,
test(# name VARCHAR(100),
test(# salary NUMERIC
test(# );
CREATE TABLE-- 4. 插入测试数据
test=# INSERT INTO test_schema.employees (name, salary) VALUES
test-# ('张三', 8000),
test-# ('李四', 9500),
test-# ('王五', 12000);
INSERT 0 3
1.2 基本权限授予
-- 1. 授予Schema USAGE权限（允许用户使用该schema）
test=# GRANT USAGE ON SCHEMA test_schema TO test_user;
GRANT-- 授予Schema内所有表的查询权限
test=# GRANT SELECT ON ALL TABLES IN SCHEMA test_schema TO test_user;
GRANT-- 授予Schema内所有序列的使用权限
test=# GRANT USAGE ON ALL SEQUENCES IN SCHEMA test_schema TO test_user;
GRANT
2. 高级权限配置
2.1 为未来对象设置默认权限
-- 设置未来创建的表自动有SELECT权限
test=# ALTER DEFAULT PRIVILEGES IN SCHEMA test_schema
test-# GRANT SELECT ON TABLES TO schema_user;
ALTER DEFAULT PRIVILEGES-- 设置未来创建的序列自动有USAGE权限
test=# ALTER DEFAULT PRIVILEGES IN SCHEMA test_schema
test-# GRANT USAGE ON SEQUENCES TO schema_user;
ALTER DEFAULT PRIVILEGES
2.2 组合权限授予
-- 授予多种操作权限
test=# GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA test_schema TO schema_user;
GRANT-- 授予函数执行权限
test=# CREATE OR REPLACE FUNCTION test_schema.calculate_bonus(salary NUMERIC)
test-# RETURNS NUMERIC AS $$
test$# BEGIN
test$# RETURN salary * 0.1;
test$# END;
test$# $$ LANGUAGE plpgsql;
CREATE FUNCTIONtest=# GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA test_schema TO schema_user;
GRANT
3. 权限精细控制
-- 1. 仅授予特定列权限
test=# GRANT SELECT (id, name) ON test_schema.employees TO schema_user;
GRANT
test=# CREATE ROLE schema_reader;
CREATE ROLE
test=# GRANT USAGE ON SCHEMA test_schema TO schema_reader;
GRANT
test=# GRANT SELECT ON ALL TABLES IN SCHEMA test_schema TO schema_reader;
GRANT
test=# GRANT schema_reader TO schema_user;
GRANT ROLE
二、权限测试步骤
1. 连接测试
[kingbase@node2 ~]$ ksql -U schema_user -d test
授权类型: 企业版.
输入 "help" 来获取帮助信息.test=>
2. 基础权限测试
-- 1. 测试Schema使用权限
test=> SET search_path TO test_schema;
SET
test=> SHOW search_path;search_path
-------------test_schema
(1 行记录)-- 2. 测试表查询权限
test=> SELECT * FROM employees;id | name | salary
----+------+--------1 | 张三 | 80002 | 李四 | 95003 | 王五 | 12000
(3 行记录)-- 3. 测试无权限操作（预期失败）
test=> DELETE FROM employees WHERE id = 1;
-- 预期错误： ERROR: 对表 employees 权限不够
3. 高级权限测试
3.1 DML操作测试
-- 如果授予了INSERT权限
test=> INSERT INTO employees (name, salary) VALUES ('赵六', 8800);
INSERT 0 1
test=> UPDATE employees SET salary = salary * 1.1 WHERE name = '张三';
UPDATE 1
test=> TRUNCATE TABLE employees;
-- 预期无权限错误 : ERROR: 对表 employees 权限不够
3.2 函数权限测试
-- 测试函数执行
test=> SELECT id, name, salary, test_schema.calculate_bonus(salary) AS bonus
test-> FROM employees;id | name | salary | bonus
----+------+--------+--------2 | 李四 | 9500 | 950.03 | 王五 | 12000 | 1200.04 | 赵六 | 8800 | 880.01 | 张三 | 8800.0 | 880.00
(4 行记录)
4. 默认权限测试
-- 管理员创建新表
test=# CREATE TABLE test_schema.departments (
test(# id SERIAL PRIMARY KEY,
test(# dept_name VARCHAR(100)
test(# );
CREATE TABLE-- schema_user测试对新表的访问权限
test=# \c test schema_user
您现在以用户名"schema_user"连接到数据库"test"。-- 如有默认权限设置应能成功查询
test=> SELECT * FROM test_schema.departments;id | dept_name
----+-----------
(0 行记录)
三、权限验证与管理
1. 权限查询方法
-- 1. 查询Schema权限
test=# SELECT grantee, privilege_type
test-# FROM information_schema.role_usage_grants
test-# WHERE object_schema = 'test_schema';grantee | privilege_type
-------------+----------------system | USAGEschema_user | USAGEsystem | USAGEschema_user | USAGE
(4 行记录)test=# SELECT grantee, table_name, privilege_type
test-# FROM information_schema.table_privileges
test-# WHERE table_schema = 'test_schema';grantee | table_name | privilege_type
---------------+-------------+----------------system | employees | INSERTsystem | employees | SELECTsystem | employees | UPDATEsystem | employees | DELETEsystem | employees | TRUNCATEsystem | employees | REFERENCESsystem | employees | TRIGGERschema_user | employees | INSERTschema_user | employees | SELECTschema_user | employees | UPDATEschema_reader | employees | SELECTsystem | departments | INSERTsystem | departments | SELECTsystem | departments | UPDATEsystem | departments | DELETEsystem | departments | TRUNCATEsystem | departments | REFERENCESsystem | departments | TRIGGERschema_user | departments | SELECT
(19 行记录)test=# SELECT grantee, table_name, column_name, privilege_type
test-# FROM information_schema.column_privileges
test-# WHERE table_schema = 'test_schema';grantee | table_name | column_name | privilege_type
---------------+-------------+-------------+----------------schema_reader | employees | salary | SELECTschema_user | departments | dept_name | SELECTsystem | employees | id | INSERTsystem | departments | dept_name | SELECTsystem | employees | name | UPDATEsystem | employees | id | UPDATEschema_user | employees | salary | UPDATEsystem | employees | name | INSERTschema_user | employees | salary | SELECTsystem | departments | id | SELECTsystem | employees | name | REFERENCESsystem | departments | dept_name | REFERENCESsystem | employees | id | SELECTschema_user | employees | salary | INSERTschema_user | employees | name | INSERTsystem | employees | name | SELECTsystem | employees | salary | SELECTsystem | departments | id | INSERTsystem | departments | id | REFERENCESschema_reader | employees | id | SELECTschema_user | employees | name | UPDATEsystem | departments | dept_name | INSERTsystem | employees | salary | REFERENCESsystem | departments | id | UPDATEschema_reader | employees | name | SELECTsystem | departments | dept_name | UPDATEschema_user | departments | id | SELECTsystem | employees | salary | INSERTsystem | employees | id | REFERENCESschema_user | employees | name | SELECTschema_user | employees | id | INSERTsystem | employees | salary | UPDATEschema_user | employees | id | SELECTschema_user | employees | id | UPDATE
(34 行记录)
本次测试KingbaseESV009R001C010 Oracle兼容模式。