浅谈YashanDB三权分立
什么是三权分立？
三权分立，便是对DBA的责任进行区分，界说不同办理职位具有并行使不同人物，相互约束和监督，从机制上尽可能地避免因误操作删去或修正不属于责任范围内的数据或目标，保障体系全体安全，
内置人物
YashanDB内置了不同办理权限的人物，便利用户进行不同责任的办理员界说，办理人物列表如下：
| 人物称号 | 权限描绘 |
| DBA | 未敞开三权分立：具有简直一切权限（除SHUTDOWN）。 敞开三权分立：不具有审计、安全办理相关权限，只对当时用户schema下的表、索引、自界说视图具有DML权限，但仍具有体系表与体系视图的检查权限，以及数据库的DDL权限。 |
| AUDIT_ADMIN | 具有创立、删去、使能、修正、去使能审计战略等与审计相关的权限。 |
| SECURITY_ADMIN | 具有办理用户、人物与权限等安全相关的权限。 |
| SYSDBA | 具有履行SHUTDOWN、备份、BUILD（包含yasrman、yasbak备份东西）的权限。 |
| SYSOPER | 只具有履行SHUTDOWN的权限。 |
| SYSBACKUP | 只具有履行备份、BUILD（包含yasrman、yasbak备份东西）权限。 |
怎么运用？
数据库参数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.
总结
-
三权分立约束了用户对数据库的拜访和操作，能够准确操控用户对数据库的操作，提升了数据库的安全性。
-
敞开三权分立影响的是各办理用户的体系特权，普通用户以及用户下的目标特权不会受到影响
