通过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', '%', '%');
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');
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;
/
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等系统权限
而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;
/
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;
/
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
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;
授权成功。
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.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;
/
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
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;
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
------------------------------ ---------- ---------------
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.
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博客 ” ,链接:http://blog.itpub.net/25198367/viewspace-742532/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25198367/viewspace-742532/