通过DBCA 配置数据库组件VAULT,再通过以下步骤实现数据管控功能
1.创建realm
exec dvsys.dbms_macadm.create_realm('TOPUP_REALM','Limit unauthorized users access to data','Y', 0);
exec dvsys.dbms_macadm.add_object_to_realm('TOPUP_REALM', 'TOPUP', '%', '%');

2.删除realm
EXEC DVSYS.DBMS_MACADM.DELETE_REALM('TOPUP_REALM');

注：Realm name是区分大小写的，所以操作的时候要注意。

3.将system用户加入到新创建的realm
begin
DVSYS.DBMS_MACADM.ADD_AUTH_TO_REALM(
realm_name => 'TOPUP_REALM',
grantee => 'SYSTEM',
rule_set_name => null,
auth_options => 1);
end;
/

4.将system用户从新创建的realm移除
begin
DVSYS.DBMS_MACADM.DELETE_AUTH_FROM_REALM(
realm_name => 'TOPUP_REALM',
grantee => 'SYSTEM');
end;
/

SYS和DVSYS二者各司其职,SYS没有创建用户/赋予CONNECT角色的权限,可以ALTER USER
而DVSYS则没有权限授予UNLIMITED TABLESPACE和CREATE TABLE等系统权限

5.创建TOPUP_REALM领域（Realm）内保护的对象

begin
DVSYS.DBMS_MACADM.ADD_OBJECT_TO_REALM(
realm_name => 'TOPUP_REALM',
object_owner => 'TOPUP',
object_name => '%',
object_type => 'FUNCTION');

DVSYS.DBMS_MACADM.ADD_OBJECT_TO_REALM(
realm_name => 'TOPUP_REALM',
object_owner => 'TOPUP',
object_name => '%',
object_type => 'PROCEDURE');

DVSYS.DBMS_MACADM.ADD_OBJECT_TO_REALM(
realm_name => 'TOPUP_REALM',
object_owner => 'TOPUP',
object_name => '%',
object_type => 'SEQUENCE');

DVSYS.DBMS_MACADM.ADD_OBJECT_TO_REALM(
realm_name => 'TOPUP_REALM',
object_owner => 'TOPUP',
object_name => '%',
object_type => 'VIEW');

DVSYS.DBMS_MACADM.ADD_OBJECT_TO_REALM(
realm_name => 'TOPUP_REALM',
object_owner => 'TOPUP',
object_name => 'EMP',
object_type => 'TABLE');
end;
/

6. 移除TOPUP_REALM领域（Realm）内保护的对象

begin
DVSYS.DBMS_MACADM.DELETE_OBJECT_FROM_REALM(
realm_name => 'TOPUP_REALM',
object_owner => 'TOPUP',
object_name => '%',
object_type => 'FUNCTION');

DVSYS.DBMS_MACADM.DELETE_OBJECT_FROM_REALM(
realm_name => 'TOPUP_REALM',
object_owner => 'TOPUP',
object_name => '%',
object_type => 'PROCEDURE');

DVSYS.DBMS_MACADM.DELETE_OBJECT_FROM_REALM(
realm_name => 'TOPUP_REALM',
object_owner => 'TOPUP',
object_name => '%',
object_type => 'SEQUENCE');

DVSYS.DBMS_MACADM.DELETE_OBJECT_FROM_REALM(
realm_name => 'TOPUP_REALM',
object_owner => 'TOPUP',
object_name => '%',
object_type => 'VIEW');

DVSYS.DBMS_MACADM.DELETE_OBJECT_FROM_REALM(
realm_name => 'TOPUP_REALM',
object_owner => 'TOPUP',
object_name => 'EMP',
object_type => 'TABLE');
end;
/

7. 验证权限

SQL> show user;
USER is "SYS"
SQL> select * from topup.tab_base_group;
select * from topup.tab_base_group
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> conn system/system#test;
Connected.
SQL> select * from topup.tab_base_group;
select * from topup.tab_base_group
*
ERROR at line 1:
ORA-01031: insufficient privileges

8.给 TOPUP_REALM 创建授权,允许别人看到应该看到的东西

1.In SQL*Plus or EM, connect as the Database Vault Account Manager, who has the DV_ACCTMGR role, and create user topup_ap.
For example:
SQL> conn dvsys/dvsys
已连接。
SQL> create user topup_ap identified by topup_ap;
用户已创建。
SQL>

2.Connect as SYSTEM privilege, and then grant topup_ap following additional privileges.
SQL> conn system/admin
已连接。
SQL> grant select any table to topup_ap;
授权成功。

SQL> grant create session to topup_ap;
授权成功。

2.加入Realm 内的授权
begin
DVSYS.DBMS_MACADM.ADD_AUTH_TO_REALM(
realm_name => 'TOPUP_REALM',
grantee => 'TOPUP_AP',
rule_set_name => null,
auth_options => 1);
end;
/

相关的动作：
begin
DVSYS.DBMS_MACADM.DELETE_AUTH_FROM_REALM(
realm_name => 'TOPUP_REALM',
grantee => 'TOPUP_AP');
end;
/

9.测试 Realm 功能

SQL> conn / as sysdba
Connected.
SQL> show user;
USER is "SYS"
SQL> select * from topup.tab_base_group where rownum <5;
select * from topup.tab_base_group where rownum <5
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> conn TOPUP_AP/TOPUP_AP;
Connected.
SQL> select * from topup.tab_base_group where rownum <5;

CTYPE                                  ID ACCOUNTID
------------------------------ ---------- ---------------
Agent                                1844 xinyuan
Agent                                1886 chenjy
Agent                                1888 tycz
Agent                                1890 ganlp

10.Run a Report
SELECT   id#,
         username,
         action_name,
         action_command,
         comment_text,
         sessionid,
         create_date
FROM   AUDIT_TRAIL$
WHERE   action_object_name = 'HR_REALM' AND action_name = '领域违规审计';

11.卸载相关设置
1)Remove the SYSTEM account from the Data Dictionary Realm.
begin
DVSYS.DBMS_MACADM.DELETE_AUTH_FROM_REALM(
realm_name => 'TOPUP_REALM',
grantee => 'SYSTEM');
end;
/
2)Delete the TOPUP_REALM Realm.
SQL> EXEC DVSYS.DBMS_MACADM.DELETE_REALM('TOPUP_REALM');
3)Drop user TOPUP_AP.

4)Drop user TOPUP.

注：应用VAULT后,exp/imp 同样受到保护

ITPub博客

oracle_database_vault 配置

最新文章