1.Oracle Database Vault - Realm

1.  REALM的作用

Oracle Database Vault通过建立REALM可以防止未授权的DBA用户访问REALM内的业务数据。

在Oracle中，如果要看到其他用户下的数据，有两种方法，一个是被直接授权这个对象的查询权限，而另一个是被授权SELECT ANY TABLE系统权限。REALM对于用户直接授权是允许的，而对于SELECT ANY TABLE系统权限是禁止的。

 

Realms: Concepts

A realm is a collection of roles anddatabase objects that are, as a group, protected from access by users on thebasis of a participant list. Even though certain users may have been grantedthe SELECT ANY TABLE system privilege, they have to be listed as realmparticipants in order to select data from a table that is protected by therealm.

 

Realm可以使用Rule Set来定义一些访问Realm内业务对象的条件，如只能在工作时间访问、限制IP访问等。Rule Set在本次测试不进行设置。

2.  测试用户

SQL> conndvacct/qawsedrf_1

Connected.

SQL> create user scottidentified by tiger default tablespace users;

User created.

SQL> grant connect toscott;

Grant succeeded.

SQL> alter user scottquota 0 on system;

User altered.

SQL> alter user scottquota unlimited on users;

User altered.

dvacct用户无法赋权限resource，因为resource没有赋给DV_ACCTMGR角色

SQL> conn / as sysdba

Connected.

SQL> grant resource toscott;

Grant succeeded.

3.  创建和删除REALM

# dvowner用户

创建realm

begin

  dbms_macadm.CREATE_REALM(realm_name    => 'SCOTT_REALM',

              description   => 'Protect SCOTT data from DBA access',

              enabled       => DVSYS.DBMS_MACUTL.G_YES,

              audit_options =>DVSYS.DBMS_MACUTL.G_REALM_AUDIT_FAIL);

  commit;

end;

/

 

删除realm

# 删除relam

execdbms_macadm.delete_realm(realm_name => 'SCOTT_REALM');

4.  向REALM中添加和删除对象

添加对象，添加完对象后，默认这些对象无法被有SELECT ANY TABLE权限的用户所访问，DatabaseVault就是为了防止系统权限

begin

  DVSYS.DBMS_MACADM.ADD_OBJECT_TO_REALM(

realm_name   => 'SCOTT_REALM',

                                       object_owner => 'SCOTT',

                                       object_name  => '%',

                                       object_type  => 'TABLE');

end;

/

 

删除对象

begin

  DVSYS.DBMS_MACADM.delete_object_from_realm(

realm_name   => 'SCOTT_REALM',

                                      object_owner => 'SCOTT',

                                      object_name  => '%',

                                      object_type  => 'TABLE');

end;

/

 

# 测试System用户访问scott用户的表

SQL>conn system/system

Connected.

SQL>select * from scott.emp;

select* from scott.emp

                    *

ERRORat line 1:

ORA-01031:insufficient privileges

 

5.  向REALM添加和删除AUTH

添加auth

begin

  DVSYS.DBMS_MACADM.add_auth_to_realm(

realm_name    => 'SCOTT_REALM',

              grantee       => 'SCOTT',

              rule_set_name => null,

              auth_options  => DVSYS.DBMS_MACUTL.G_REALM_AUTH_OWNER);

end;

/

 

begin

  DVSYS.DBMS_MACADM.add_auth_to_realm(

realm_name    => 'SCOTT_REALM',

      grantee       => 'SYSTEM',

      rule_set_name => null,

      auth_options  =>DVSYS.DBMS_MACUTL.g_realm_auth_participant);

end;

/

 

这样system用户就可以访问scott的表

 

删除auth

begin

  DVSYS.DBMS_MACADM.delete_auth_from_realm(

realm_name=> 'SCOTT_REALM',

                           grantee    => 'SCOTT');

end;

/

所有者与参与者的区别：

l  Participant: Thegrantee is able to access the realm-secured objects.
l  Owner: Thegrantee has all the access rights that a participant has, and can also grantprivileges to others on any of the objects in the realm. This is comparable tothe WITH ADMIN option of the GRANT statement.

 

6.  相关视图

DBA_DV_REALM
DBA_DV_REALM_OBJECT
DBA_DV_REALM_AUTH

7.  REALM相关API

DBMS_MACADM包：

•         Create realms:
-         CREATE_REALM

•         Modify realms:
-         ADD_AUTH_TO_REALM
-         ADD_OBJECT_TO_REALM
-         DELETE_AUTH_FROM_REALM
-         DELETE_OBJECT_FROM_REALM
-         RENAME_REALM
-         UPDATE_REALM
-         UPDATE_REALM_AUTH

•         Delete realms:
-         DELETE_REALM
-         DELETE_REALM_CASCADE