GaussDB用户权限管理详解
GaussDB用户权限管理:构建安全可靠的数据防线
在当今数据驱动的时代,数据库作为企业核心信息的载体,其安全性至关重要。华为GaussDB作为一款领先的企业级分布式数据库,提供了一套完善、精细且灵活的权限管理体系。有效的权限管理不仅能防止数据泄露和恶意篡改,还能确保数据库的稳定运行,满足合规性要求。本文将深入探讨GaussDB的权限管理机制,并提供实践指导。
一、 核心概念:用户、角色与权限
在深入操作之前,理解以下几个核心概念是至关重要的:
- 用户 (USER):
- 代表一个可以登录数据库的账户。每个用户都拥有一个唯一的用户名。
- 例如:
CREATE USER data_analyst WITH PASSWORD ‘********’;
- 角色 (ROLE):
- GaussDB中,“用户”和“角色”在概念上是通用的。一个用户本质上就是一个可以登录的角色,而一个角色可以被授予给其他用户/角色。
- 这种设计极大地增强了权限管理的灵活性。我们可以创建不用于登录的“角色”作为权限的集合,然后将其“授予”给具体的“用户”。
- 示例:创建一个
readonly_role
角色,然后将它授予给多个只读用户。
- 权限 (PRIVILEGE):
- 定义了用户/角色可以对数据库对象执行的操作。主要分为两大类:
- 系统权限:针对数据库全局性操作的权限,与具体对象无关。
CREATEDB
:创建数据库的权限。CREATEROLE
:创建角色的权限。SYSADMIN
:系统管理员权限,拥有最高权限(在GaussDB中尤为重要)。LOGIN
:允许登录的权限。
- 对象权限:针对特定数据库对象(如表、视图、模式、函数等)的权限。
SELECT
、INSERT
、UPDATE
、DELETE
、REFERENCES
、TRIGGER
等(针对表/视图)。CREATE
、USAGE
(针对模式)。EXECUTE
(针对函数)。
- 系统权限:针对数据库全局性操作的权限,与具体对象无关。
- 定义了用户/角色可以对数据库对象执行的操作。主要分为两大类:
- 模式 (SCHEMA):
- 是数据库对象的命名空间和容器。它好比一个文件夹,里面可以包含表、视图、索引等对象。
- 权限可以授予到模式级别(例如,拥有某个模式的
USAGE
权限才能访问其中的对象),这为实现多租户或逻辑隔离提供了基础。
二、 权限管理实践操作
我们将通过一个典型的场景来演示如何进行权限管理:为一个新来的数据分析师配置只读权限。
步骤1:创建角色/用户
首先,我们创建一个专门用于只读操作的角色。
sql
-- 创建一个无法登录的角色,作为权限集合
CREATE ROLE readonly_role NOLOGIN;
-- 创建一个可以登录的用户,并立即设置密码过期以强制修改
CREATE USER data_analyst WITH PASSWORD ‘YourSecurePassword123’ PASSWORD EXPIRY;
步骤2:授予系统权限
对于数据分析师,我们通常只需要其能登录,不需要创建数据库或角色的能力。因此,CREATEROLE
和CREATEDB
权限通常不授予。
sql
-- 默认情况下,CREATE USER已经包含了LOGIN权限。如果需要,可以显式授予
-- GRANT LOGIN TO data_analyst;
步骤3:授予对象权限
假设我们有一个名为sales
的模式,里面存放着所有销售数据表。
-
授予模式权限:用户必须先有模式的
USAGE
权限,才能看到和访问其中的对象。sql
GRANT USAGE ON SCHEMA sales TO readonly_role;
-
授予表权限:将模式中所有现有表和未来表的
SELECT
权限授予只读角色。sql
-- 授予现有表的选择权限 GRANT SELECT ON ALL TABLES IN SCHEMA sales TO readonly_role; -- 设置默认权限,确保未来新建的表也会自动继承此权限(非常重要!) ALTER DEFAULT PRIVILEGES IN SCHEMA sales GRANT SELECT ON TABLES TO readonly_role;
步骤4:将角色授予用户
现在,将我们精心配置的readonly_role
授予给数据分析师用户data_analyst
。
sql
GRANT readonly_role TO data_analyst;
完成以上步骤后,用户data_analyst
登录后,就可以对sales
模式下的所有表(包括未来新建的表)进行查询操作,但无法进行任何修改。
三、 高级权限管理与最佳实践
-
遵循最小权限原则
- 这是权限管理的黄金法则。只授予用户完成其工作所必需的最小权限。永远不要轻易授予
SYSADMIN
或对象的ALL PRIVILEGES
。
- 这是权限管理的黄金法则。只授予用户完成其工作所必需的最小权限。永远不要轻易授予
-
使用角色进行分组授权
- 不要直接将权限授予单个用户。根据 job function 创建不同的角色(如
dev_role
,readonly_role
,app_user_role
),将权限授予角色,再将角色授予用户。这极大地简化了管理。
- 不要直接将权限授予单个用户。根据 job function 创建不同的角色(如
-
利用
ALTER DEFAULT PRIVILEGES
- 如上例所示,此命令可以确保新创建的数据库对象自动继承预设的权限规则,避免了手动管理的疏漏。
-
定期审查与回收权限
-
使用系统视图定期审计权限分配情况。
\du
(在gsql中) 或SELECT * FROM pg_roles;
查看角色。\dp
或SELECT * FROM information_schema.table_privileges;
查看表权限。
-
当员工岗位变动或离职时,及时使用
REVOKE
命令回收权限。sql
REVOKE readonly_role FROM data_analyst; REVOKE SELECT ON ALL TABLES IN SCHEMA sales FROM readonly_role;
-
-
安全配置
- 密码策略:强制使用复杂密码并定期更换。
- 网络加密:使用SSL/TLS加密客户端与服务器之间的连接。
- 审计日志:开启GaussDB的审计功能,记录所有敏感操作(如权限变更、数据删除等),便于事后追溯。
四、实例
实例1
-- 创建用户sjh111
create user sjh111 password 'Huawei@1234';
-- 将表sjh_test表的读取,删除权限授权给sjh111用户
grant usage on schema root to sjh111;
grant select,delete on sjh_test to sjh111;
-- 为用户sjh111授权在sjh_test表的a、b列上的查询、添加和更新权限
grant select(a,b),insert(a,b),update(a,b) on sjh_test to sjh111;
-- 将用户sjh111授权在sjh_test表的a列上的查询、添加和更新权限收回
revoke select(a),insert(a),update(a) on sjh_test from sjh111;
-- 创建角色sjh_audit,此角色拥有审计权限
create role sjh_audit with auditadmin password 'Huawei@1234';
-- 将sjh112角色权限授予给用户sjh111
grant sjh112 to sjh111;
-- 创建新用户sjh113,设置使用有效期”2023-01-28”至”2026-01-01”
create user sjh113 password 'Huawei@1234' valid begin '2023-01-28' valid until '2026-01-01';
实例2
-- 创建用户user1
create user user1 password 'Huawei@1234';
-- 查看用户user1和数据库的相关权限,要求显示数据库名、用户名、数据库的权限
select a.datname,b.rolname,string_agg(a.pri_t,',') from
(select datname
,(aclexplode(COALESCE(datacl, acldefault('d'::"char",datdba)))).grantee as grantee
,(aclexplode(COALESCE(datacl, acldefault('d'::"char",datdba)))).privilege_type as pri_t
from pg_database where datname not like 'template%') a,pg_roles b where (a.grantee=b.oid or a.grantee=0) and b.rolname='user1' group by a.datname,b.rolname;
-- 不确定
select datname
,(aclexplode(datacl)).grantee as grantee
,(aclexplode(datacl)).privilege_type as pri_t
from pg_database where datname not like 'template%';
-- 将表table1的select和alter权限赋给user1;
grant select,alter on table1 to user1;
-- 查询table1的owner,要求显示表名和owner
select tablename,tableowner from pg_tables where tablename='table1';
select c.relname,r.rolname as owner from pg_class c join pg_roles r on c.relowner=r.oid where relname='table1';
-- 查询table1的表权限,要求显示表名、schema名、用户名
select table_name,table_schema,grantee,privilege_type from information_schema.table_privileges where table_name='table1';
-- 查询对表table1有操作权限的用户,要求显示2列:用户名、操作权限
select grantee as user ,privilege_type from information_schema.table_privileges where table_name='table1';
实例3
#### 使用两个查询语句,查看“postgres”数据库的最大连接数和已使用连接数
--数据库的最大连接数
select datname,datconnlimit from pg_database where datname='postgres';
--数据库已使用连接数
select datname,count(*) from pg_stat_activity where datname='postgres';
```
#### (2)创建用户user_test,指定该用户具有创建数据库和创建角色的权限
create user user_test createdb createrole password 'Test@123';
```
#### (3)创建表table_test,此表中包含一个名为col_test的列,为用户user_test授权在table_test表的col_test列上的查询、更新权限
create table table_test(col_test int);
grant select(col_test),update(col_test) on table_test to user_test;
```
#### (4)收回用户user_test在table_test表在col_test列上的更新权限
revoke update(col_test) on table_test from user_test;
```
#### (5)创建角色role_test,此角色拥有审计权限
create role role_test with auditamin password 'Huawei@123';
```
#### (6)将角色role_test的权限授权给用户user_test,并允许用户将此权限再授权给其他用户或角色
grant role_test to user_test with admin option;
```
#### (7)用户user_test账号被盗,请手动锁定此账号
alter user user account lock;
```
#### (8)级联删除用户user_test,并重新创建,将账号设置为在2023年国庆节期间有效
--级联删除用户
drop user user_test cascade;
--创建用户
create user user_test createdb createrole password 'Test@123' valid begin '2023-10-01 00:00:00' valid until '2023-10-07 00:00:00';
```
实例4
#### (1)查看全局最大连接数
show max_connections;
```
#### (2)创建数据库指定最大连接数,指定最大连接为100000,并使用SQL查看展示数据库名称,最大连接数
-- 创建数据库,connection limit指定最大连接数
create database test_db connection limit 100000;
-- 查看数据库、最大连接数
select datname,datconnlimit from pg_database where datname='test_db';
```
#### (3)创建用户并指定最大连接数,指定最大连接为20000,并使用SQL查看展示用户名称,最大连接数
-- 创建用户,connection limit指定最大连接数
create user sjh_max password 'Huawei@123' connection limit 20000;
-- 对已存在用户设置也可以设置最大连接数的指定
alter user sjh_max connection limit 2000;
-- 查看用户名、最大连接数
select rolname,rolconnlimit from pg_roles where rolname='sjh_max';
```
#### (4)修改数据库的最大连接数,将最大连接数修改为200000
alter database test_db connection limit 200000;
```
实例5
#### (1)查看全局最大连接数
show max_connections;
```
#### (2)创建用户并指定最大连接数,指定最大连接数为20000
create user sjh_max password 'Huawei@123' connection limit 20000;
```
#### (3)查看用户的连接数,展示用户、最大连接数
select rolname,rolconnlimit from pg_roles where rolname='sjh_max';
```
#### (4)修改用户最大连接数,将最大连接数修改为1000
alter user sjh_max connection limit 10000;
```
#### (5)创建数据库指定最大连接数,指定最大连接数为100000
create database test_db connection limit 100000;
```
#### (6)查看数据库最大连接数,展示数据库名称,最大连接数
select datname,datconnlimit from pg_database where datname='test_db';
```
#### (7)修改数据库的最大连接数,将最大连接数改为200000
alter database test_db connection limit 200000;
```
实例6
#### (1)创建user2用户,user2用户需要具备创建数据库的权限
create user user2 createdb password 'Huawei@123';
```
#### (2)查看用户的连接数上限
select rolname,rolconnlimit from pg_roles where rolname='user2';
```
#### (3)设置user2用户连接数为100
alter user user2 connection limit 100;
```
#### (4)查询postgres用户连接数上限,显示库,上限数量
select datname,datconnlimit from pg_database where datname='postgres';
```
#### (5)查询postgres用户已使用的会话连接数
select count(datname) from pg_stat_activity where datname = 'postgres';
-- 如果查询已结束的会话数量,筛选state
select count(datname) from pg_stat_activity where datname = 'postgres' and state = 'idle';
```
#### (6)查看所有用户已使用的会话连接数
Select count(datname) from pg_stat_activity;
```
#### (7)查询库最大连接数
show max_connections;
```
#### (8)查询会话状态,显示datid、pid、state
select datid,pid,state from pg_stat_activity;
```
-- create row level security policy row_pol1 on row_test using(name=current_user and id >30);
五、 权限操作命令速查
-
创建用户/角色:
CREATE USER/ROLE name [WITH option ...];
-
授权:
GRANT { { SYSTEM | OBJECT } privilege_name [, ...] | role_name } TO grantee [, ...];
-
收回权限:
REVOKE { { SYSTEM | OBJECT } privilege_name [, ...] | role_name } FROM grantee [, ...];
-
查看权限:
-- 查看所有角色 SELECT rolname FROM pg_roles; -- 查看当前用户的权限 \du -- 查看特定表的权限 \dp table_name
六、 总结
GaussDB的权限管理体系,深度融合了PostgreSQL的成熟理念并进行了企业级增强,提供了从宏观的系统权限到微观的对象权限的全面控制。通过理解用户-角色-权限这一核心模型,并遵循基于角色的访问控制(RBAC) 和最小权限原则,数据库管理员可以高效、安全地构建起一道坚固的数据安全防线,确保GaussDB数据库在支撑业务创新的同时,也能从容应对各种安全挑战。
- 点赞
- 收藏
- 关注作者
评论(0)