How to Use DVSYS.DBMS_MACADM procedures to Prohibit Data Access to Users

A realm is created on HR schema objects : how to prohibit the access to HR objects to the accounts SYSTEM and SYS without using the Database Vault Administrator GUI tool (dva) ?

1. Realm creation and schema objects selection :

SQL> execute dvsys.dbms_macadm.CREATE_REALM('Test HR','Protects data from DBA access','Y', 1);

SQL>  execute DVSYS.DBMS_MACADM.ADD_OBJECT_TO_REALM ( 'Test HR', 'HR', '%', '%');

or

begin
dvsys.dbms_macadm.add_object_to_realm ( realm_name => 'Test HR'  , object_owner => 'HR'  ,
object_name => '%'  ,
object_type => '%');
end;
/

2. Tests : connect as SYSTEM user to check that SYSTEM user has no access to HR data :

SQL> conn system/manager
Connected.

SQL> select * from HR.dept;

DEPTNO DNAME LOC
----------   -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO

Connect as SYS user to check that SYS user has no access to HR data :

SQL> conn sys/xxx as sysdba
Connected

SQL> select * from HR.dept;

select * from HR.dept
*
ERROR at line 1:
ORA-01031: insufficient privileges

Solution

1. Check  if the user SYSTEM user has been granted the object privilege SELECT on HR.DEPT  table.

Yes , it had been granted. Hence the realm does not prohibit the SYSTEM user from selecting data from HR table as Realm only protects data from being accessed with system privileges.

2. Revoke the object privilege on HR schema tables from SYSTEM user.

SQL> conn sys/xxx as sysdba
Connected.

SQL> revoke select on hr.dept from system;
Revoke succeeded.

SQL> conn dvowner/xxx
Connected.

SQL> execute dvsys.dbms_macadm.CREATE_REALM('Test HR','Protects data from DBA access','Y', 1);
PL/SQL procedure successfully completed.

SQL> execute DVSYS.DBMS_MACADM.ADD_OBJECT_TO_REALM ('Test HR', 'HR', '%', '%');
PL/SQL procedure successfully completed.

SQL> conn system/xxx
Connected.

SQL> select * from HR.dept;
select * from HR.dept
*
ERROR at line 1:
ORA-01031: insufficient privileges

user_has_auth_in_realm

How to Drop and Recreate the DB Control Repository

Information in this article applies to:
Oracle Enterprise Manager 10g Release 1 (10.1)
Oracle Enterprise Manager 10g Release 1 (10.2)
So:

Goal:
Provide detailed instructions on how to remove and recreate the DB Control Repository in a 10g Database

1. Stop the DB Console if not already stoped: $ORACLE_HOME/bin/emctl stop dbconsole

On Unix Systems:
1.  Run 10g DB $OH/sysman/admin/emdrep/bin/RepManager
NOTE: Type RepManager by itself and you will get a list of options available for the tool

2.  After you delete the Repository, you will need to remove the configuration files.  To do this, run emca -x

3.  Finally, to created both the schema objects and configuration files run emca and answer the prompts

On Windows Systems:
NOTE: The RepManager script in 10.1.0.2.0 does not work correctly on Windows.  For this reason, you will have to manually drop the sysman schema and management objects.  The RepManager script should be fully functional in the 10.1.0.3.0 release.

1.  Logon SQLPLUS as user SYS or SYSTEM, and drop the sysman account and mangement objects:

•    SHUTDOWN IMMEDIATE;
•
•    STARTUP RESTRICT;
•
•    EXEC sysman.emd_maintenance.remove_em_dbms_jobs;
•
•    EXEC sysman.setEMUserContext(”,5);
•
•    REVOKE dba FROM sysman;
•
•    DECLARE
•        CURSOR c1 IS
•          SELECT owner, synonym_name name
•          FROM   dba_synonyms
•          WHERE  table_owner = ‘SYSMAN’
•        ;
•      BEGIN
•        FOR r1 IN c1 LOOP
•            IF r1.owner = ‘PUBLIC’ THEN
•               EXECUTE IMMEDIATE ‘DROP PUBLIC SYNONYM ‘||r1.name;
•            ELSE
•               EXECUTE IMMEDIATE ‘DROP SYNONYM ‘||r1.owner||’.'||r1.name;
•            END IF;
•        END LOOP;
•      END;
•
•    DROP USER mgmt_view CASCADE;
•
•    DROP ROLE mgmt_user;
•
•    DROP USER sysman CASCADE;
•
•    ALTER SYSTEM DISABLE RESTRICTED SESSION;
•
2.  At a command Prompt, run emca -x sid to remove the existing configuration files and then recreate the schema and configuration files with just emca.  For example:
emca -x <SID>
emca

For additional information on emca commandline options, please see the Oracle Enterprise Manager 10g Advanced Configuration Guide.  The Guide is available from OTN.  http://download-west.oracle.com/docs/pdf/B12013_01.pdf

1.    Logon SQLPLUS as user SYS or SYSTEM, and drop the sysman account and mangement objects:

SHUTDOWN IMMEDIATE;
STARTUP RESTRICT;
A. Drop the repository manually using following procedure.

Stop the DB Console if not already stoped: $ORACLE_HOME/bin/emctl stop dbconsole
Log on in sqlplus as sys user and run the following commands:

a. drop user sysman cascade;
b. drop role MGMT_USER;
c. drop user MGMT_VIEW cascade;
d. drop public synonym MGMT_TARGET_BLACKOUTS;
e. drop public synonym SETEMVIEWUSERCONTEXT;
(Note If any of the above statement failed and saying object doesn’t exist just skip that
statement)

B. Drop the DB Control configuration files.

Remove the following directories:
$rm -rf $ORACLE_HOME/<HOSTNAME_SID>
$rm -rf $ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_<HOSTNAME_SID>

On Windows systems just delete the folders above from the Explorer.

Remove the entries for the old dbconsole information from the $ORACLE_HOME/install/portlist.ini file

On Windows, use delsrv.exe (available for free download from Microsoft) or the ‘sc’ command to remove the ORACLEDBCONSOLE<SID> service if it still remains.

1.    Remove the current Database Control configuration files and repository

1.1 set ORACLE_HOME and ORACLE_SID environment variable
1.2 <ORACLE_HOME>/bin/emca -x <SID>
1.3 <ORACLE_HOME>/sysman/admin/emdrep/bin/RepManager <hostname> <actual_listener_port> <sid> -action drop
1.4 Manually delete any <hostname>_<sid> directories in <ORACLE_HOME> and <ORACLE_HOME>/oc4j/j2ee
1.5 On Windows, use delsrv.exe (available for free download from Microsoft) or the ‘sc’ command to remove the ORACLEDBCONSOLE<SID> service if it still remains.

2. Recreate the Database Control configuration files and repository

2.1 <ORACLE_HOME>/bin/emca

Exploring the Oracle Database Architecture

Explain the Memory Structures

• System Global Area (SGA) - The SGA is a group of shared memory structures, known as SGA components, that contain data and control information for one Oracle Database instance. The SGA is shared by all server and background processes. Examples of data stored in the SGA include cached data blocks and shared SQL areas.


• Program Global Area (PGA) - A PGA is a nonshared memory region that contains data and control information exclusively for use by an Orcle process. The PGA is created by the Oracle Database when an Oracle process is started.
  
  One PGA exists for each server process and background process. The collection of individual PGAs is the total instance PGA, or instance PGA. Database initialization parameters set the size of the instance PGA, not individual PGAs.


• User Global Area (UGA) - The UGA is memory associated with a user session.


• Software Code Areas - Software code areas are portions of memory used to store code that is being run or can be run. Oracle Database code is stored in a software area that is typically at a different location from user programs-a more exclusive or protected location.

Describe the Process Structures

• Client processes run the application or Oracle tool code


• Oracle Processes run the Oracle Database code. Oracle processes include the following subtypes:
  
  
  
  • Background Processes start with the database instance and perform maintenance tasks such as performing instance recovery, cleaning up processes, writing redo buffers to disk, and so on.
  
  
  • Server Processes perform work based on a client request.
    
    For example, these processes parse SQL queries, place them in the shared pool, create and execute a query plan for each query, and read buffers from the database buffer cache or from disk.
  
  
  • Slave Processes perform additional tasks for a background or server process.
  
  
  
  

Overview of Storage Structures

• Physical
  
  
  
  • Data Files and Temp Files - A data file is a physical file on disk that was created by Oracle Database and contains data structures such as tables and indexes. A temp file is a data file that belongs to a temporary tablespace. The data is written to these files in an Oracle proprietary format that cannot by read by other programs.
  
  
  • Control Files - is a root file that tracks the physical components of the database.
  
  
  • Online Redo Log - is a set of file containing records of changes made to data.
  
  
  
  


• Logical
  
  
  
  • Data Blocks - Oracle Database manages the logical storage space in the data files of a database in units called data blocks, also called logical blocks, Oracle blocks, or pages. An Oracle data block is the minimum unit of database I/O.
  
  
  • Extents - is a logical unit of database storage space allocation made up of contiguous data blocks. Data blocks in an extent are logically contiguous but can be physically spread out on disk because of RAID striping and file system implementations.
  
  
  • Segments - is a seg of extents that contains all the data for a logical storage structure within a tablespace. For example, Oracle Database allocates one or more extents to form the data segment for a table. The database also allocates one or more extents to form the index segment for a table.
  
  
  • Tablespaces - is a logical storage container for segments. Segments are database objects, such as tables and indexes, that consume storage space. At the physical level, a tablespace stores data in one or more data files or temp files. A database must have the SYSTEM and SYSAUX tablespaces.