KingbaseES Schema权限及空间限额
一、权限授予操作
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 FUNCTION
test=# 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 | 张三 | 8000
2 | 李四 | 9500
3 | 王五 | 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.0
3 | 王五 | 12000 | 1200.0
4 | 赵六 | 8800 | 880.0
1 | 张三 | 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 | USAGE
schema_user | USAGE
system | USAGE
schema_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 | INSERT
system | employees | SELECT
system | employees | UPDATE
system | employees | DELETE
system | employees | TRUNCATE
system | employees | REFERENCES
system | employees | TRIGGER
schema_user | employees | INSERT
schema_user | employees | SELECT
schema_user | employees | UPDATE
schema_reader | employees | SELECT
system | departments | INSERT
system | departments | SELECT
system | departments | UPDATE
system | departments | DELETE
system | departments | TRUNCATE
system | departments | REFERENCES
system | departments | TRIGGER
schema_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 | SELECT
schema_user | departments | dept_name | SELECT
system | employees | id | INSERT
system | departments | dept_name | SELECT
system | employees | name | UPDATE
system | employees | id | UPDATE
schema_user | employees | salary | UPDATE
system | employees | name | INSERT
schema_user | employees | salary | SELECT
system | departments | id | SELECT
system | employees | name | REFERENCES
system | departments | dept_name | REFERENCES
system | employees | id | SELECT
schema_user | employees | salary | INSERT
schema_user | employees | name | INSERT
system | employees | name | SELECT
system | employees | salary | SELECT
system | departments | id | INSERT
system | departments | id | REFERENCES
schema_reader | employees | id | SELECT
schema_user | employees | name | UPDATE
system | departments | dept_name | INSERT
system | employees | salary | REFERENCES
system | departments | id | UPDATE
schema_reader | employees | name | SELECT
system | departments | dept_name | UPDATE
schema_user | departments | id | SELECT
system | employees | salary | INSERT
system | employees | id | REFERENCES
schema_user | employees | name | SELECT
schema_user | employees | id | INSERT
system | employees | salary | UPDATE
schema_user | employees | id | SELECT
schema_user | employees | id | UPDATE
(34 行记录)
本次测试KingbaseESV009R001C010 Oracle兼容模式。
浙公网安备 33010602011771号