浅谈 YashanDB 三权分立
作者：YashanDB
- 2024-12-02 广东
本文字数：2494 字
阅读完需：约 8 分钟
什么是三权分立？
三权分立，即是对 DBA 的职责进行划分，定义不同管理职位具备并行使不同角色，互相限制和监督，从机制上尽可能地防止因误操作删除或修改不属于职责范围内的数据或对象，保障系统整体安全，
内置角色
YashanDB 内置了不同管理权限的角色，方便用户进行不同职责的管理员定义，管理角色列表如下：
如何使用？
数据库参数 ENABLE_SEPARATE_DUTY 控制三权分立开关，默认关闭，重启生效
--数据库参数修改方式ALTER SYSTEM SET ENABLE_SEPARATE_DUTY=true SCOPE=SPFILE;
复制代码
使用示例
1.确认数据库没有打开三权分立开关
SQL> show parameter ENABLE_SEPARATE_DUTY
NAME VALUE ---------------------------------------------------------------- ---------------------------------------------------------------- ENABLE_SEPARATE_DUTY FALSE
1 row fetched.
复制代码
2.创建测试用户并分别赋予内置权限
SQL> CREATE USER user1 IDENTIFIED BY "user1";
Succeed.
SQL> CREATE USER user2 IDENTIFIED BY "user2";
Succeed.
SQL> CREATE USER user3 IDENTIFIED BY "user3";
Succeed.
--user1用户赋dba角色权限SQL> GRANT dba TO user1;
Succeed.
--user2用户赋审计相关权限SQL> GRANT AUDIT_ADMIN TO user2;
Succeed.
--user3用户赋安全相关权限SQL> GRANT SECURITY_ADMIN TO user3;
Succeed.
SQL> GRANT RESOURCE TO user3;
Succeed.
复制代码
3.查看视图
--查看视图SQL> select * from DBA_ROLE_PRIVS;
GRANTEE GRANTED_ROLE ADMIN_OPTION ---------------------------------------------------------------- ---------------------------------------------------------------- ------------ USER1 DBA N USER2 AUDIT_ADMIN N USER3 SECURITY_ADMIN N USER3 RESOURCE N
3 rows fetched.
复制代码
4.测试 user1 用户具有权限相关、审计相关的权限操作
SQL> conn user1/user1
Connected to:YashanDB Server Enterprise Edition Release 23.2.1.100 x86_64 - X86 64bit Linux
SQL> grant select any table to user2;
Succeed.
SQL> revoke select any table from user2;
Succeed.
SQL> create AUDIT POLICY p1 PRIVILEGES SELECT ANY TABLE, DELETE ANY TABLE;
Succeed.
SQL> drop audit policy p1;
Succeed.
复制代码
5.测试 user2 用户仅具有审计相关权限，无安全相关权限
SQL> conn user2/user2
Connected to:YashanDB Server Enterprise Edition Release 23.2.1.100 x86_64 - X86 64bit Linux
SQL> create AUDIT POLICY p1 PRIVILEGES SELECT ANY TABLE, DELETE ANY TABLE;
Succeed.
SQL> drop audit policy p1;
Succeed.
--user2无法授权权限SQL> grant select any table to user3;
YAS-02213 insufficient privileges
复制代码
6.测试 user3 用户仅安全相关权限，无审计相关权限
SQL> conn user3/user3
Connected to:YashanDB Server Enterprise Edition Release 23.2.1.100 x86_64 - X86 64bit Linux
SQL> grant select any table to user2;
Succeed.
SQL> revoke select any table from user2;
Succeed.
SQL> create AUDIT POLICY p1 PRIVILEGES SELECT ANY TABLE, DELETE ANY TABLE;
YAS-02213 insufficient privileges
复制代码
7.开启三权分立开关
SQL> conn / as sysdba
Connected to:YashanDB Server Enterprise Edition Release 23.2.1.100 x86_64 - X86 64bit Linux
SQL> ALTER SYSTEM SET ENABLE_SEPARATE_DUTY=true SCOPE=SPFILE;
Succeed.
SQL> shutdown immediate;
Succeed.
复制代码
8.测试 user1 用户无权限相关和审计相关的权限操作
SQL> conn user1/user1
Connected to:YashanDB Server Enterprise Edition Release 23.2.1.100 x86_64 - X86 64bit Linux
SQL> grant select any table to user2;
YAS-02213 insufficient privileges
SQL> create AUDIT POLICY p1 PRIVILEGES SELECT ANY TABLE, DELETE ANY TABLE;
YAS-02213 insufficient privileges
复制代码
9.测试 user2、user3 用户权限无变化
SQL> conn user2/user2
Connected to:YashanDB Server Enterprise Edition Release 23.2.1.100 x86_64 - X86 64bit Linux
SQL> create AUDIT POLICY p2 PRIVILEGES SELECT ANY TABLE;
Succeed.
SQL> drop audit policy p2;
Succeed.
--user2无安全相关权限SQL> grant select any table to user1;
YAS-02213 insufficient privileges
SQL> conn user3/user3
Connected to:YashanDB Server Enterprise Edition Release 23.2.1.100 x86_64 - X86 64bit Linux
SQL> grant select any table to user1;
Succeed.
SQL> revoke select any table from user1;
Succeed.
--user3无审计相关权限SQL> create AUDIT POLICY p3 PRIVILEGES DELETE ANY TABLE;
YAS-02213 insufficient privileges
--user3用户正常创建表，普通用户权限无变化SQL> create table t (c1 int);
Succeed.
复制代码
总结
三权分立限制了用户对数据库的访问和操作，可以精确控制用户对数据库的操作，提升了数据库的安全性。
开启三权分立影响的是各管理用户的系统特权，普通用户以及用户下的对象特权不会受到影响
划线
评论
复制
发布于: 刚刚阅读数: 3
YashanDB
关注
全自研国产新型大数据管理系统 2022-02-15 加入
还未添加个人简介
评论