Finding Oracle Users with Import – Export Privileges – 2

Oracle Users with  IMPORT/EXPORT  role -2  DATAPUMP_IMP_FULL_DATABASE

Export utilities are used for extracting database objects and data from database to a file, and Import utilities are used for importing these extracted files into databases. In order to run IMPORT/EXPORT utilities you would have to have system roles which are given below.

  • IMP_FULL_DATABASE
  • DATAPUMP_IMP_FULL_DATABASE
  • EXP_FULL_DATABASE
  • DATAPUMP_EXP_FULL_DATABASE

These privileges should only be granted to authorized users. Normally database administrators should perform export and import operations. Therefore during our database assessment; we should find that these grants would only be given to DBA users.

If you want to list Oracle users  ( or roles ) which have DATAPUMP_IMP_FULL_DATABASE system role,  we could use the below query.  This query is developed by hierarchical query technique. Same query can be used on Oracle 11g and Oracle 12c versions.

Capture

The text version of the SQL are given below
SELECT
DISTINCT A.GRANTEE,
A.GRANTED_ROLE,
‘DATAPUMP_IMP_FULL_DATABASE’ GRANTED_CRITIC_ROLE
FROM
(
SELECT
DISTINCT LEVEL LEVEL_DEEP,
GRANTEE,
GRANTED_ROLE
FROM
DBA_ROLE_PRIVS
START WITH GRANTED_ROLE = ‘DATAPUMP_IMP_FULL_DATABASE’
CONNECT BY PRIOR GRANTEE = GRANTED_ROLE
) A,
DBA_USERS B
WHERE
A.GRANTEE = B.USERNAME
AND B.USERNAME NOT IN(
‘SYSTEM’,
‘SYS’
)
AND B.ACCOUNT_STATUS = ‘OPEN’;

 

In order to list users with DATAPUMP_IMP_FULL_DATABASE   in the multitenant architecture, we use the below query.

 

Capture

The text version of this query is given below.

SELECT
DISTINCT A.GRANTEE,
A.GRANTED_ROLE,
B.COMMON,
C.NAME,
‘DATAPUMP_IMP_FULL_DATABASE’ GRANTED_CRITIC_ROLE
FROM
(
SELECT
DISTINCT LEVEL LEVEL_DEEP,
GRANTEE,
GRANTED_ROLE,
CON_ID
FROM
CDB_ROLE_PRIVS
START WITH GRANTED_ROLE = ‘DATAPUMP_IMP_FULL_DATABASE’
CONNECT BY PRIOR GRANTEE = GRANTED_ROLE
AND PRIOR CON_ID = CON_ID
) A,
CDB_USERS B,
V$CONTAINERS C
WHERE
A.GRANTEE = B.USERNAME
AND B.USERNAME NOT IN(
‘SYSTEM’,
‘SYS’
)
AND B.ACCOUNT_STATUS = ‘OPEN’
AND A.CON_ID = C.CON_ID
AND B.CON_ID = C.CON_ID ;

 

Finding  users with  EXP_FULL_DATABASE Role;

Capture

And the last one DATAPUMP_EXP_FULL_DATABASE ;

Capture.JPG

 

Now look the the same SQLs in Multitenant Architecture ;

EXP_FULL_DATABASE Role for Multitenant Architecture

Capture

 

And the last one DATAPUMP_EXP_FULL_DATABASE for Multitenant Architecture

Capture

 

If we want to revoke IMPORT/EXPORT privileges from a user;  we could use below commands

 

REVOKE IMP_FULL_DATABASE FROM UserName;

REVOKE DATAPUMP_ FULL_DATABASE FROM UserName;

REVOKE EXP_FULL_DATABASE FROM UserName;

REVOKE DATAPUMP_EXP_FULL_DATABASE FROM UserName;

Advertisements

Oracle DB Vault New Features in Oracle 12c Release 1 – Part 4 : New Realms

Now we continue to work on changes in Oracle Database Vault in 12c Release 1 version. Three new Realms are added in this release . These are;

  • Oracle Default Schema Protection Realm
  • Oracle System Privilege and Role Management Realm
  • Oracle Default Component Protection Realm

Let’s check these new Realms with the query.

SELECT

*

FROM

DVSYS.DBA_DV_REALM;

What objects are protected in these new Realms ? Let’s start with Oracle Default Schema Protection Realm;

 

FROM

dvsys.DBA_DV_REALM_OBJECT

WHERE

REALM_NAME = ‘Oracle Default Schema Protection Realm’;

 

This realm protects roles and schemas in Oracle OLAP, Oracle Spatial, and Oracle Text. (CTXSYS, MDDATA and MDSYS)

SELECT

*

FROM

DVSYS.DBA_DV_REALM_AUTH

WHERE

REALM_NAME = ‘Oracle Default Schema Protection Realm’;

Oracle System Privilege and Role Management realm. This realm protects sensitive roles which are given below. Some import and export roles , Java roles , audit management roles, catalog operation roles..

Oracle Default Component Protection Realm ; This realm protects the SYSTEM and OUTLN schemas.

Oracle DB Vault New Features in Oracle 12c R1 – Part1 : Changes at DB Vault Installation

There are many changes at Oracle DB Vault in Oracle 12c version. At this note I will give you information about  the change at DB  Vault Installation .

  1. DB Vault Installation

At Oracle 11g version you need to  relink Oracle binary before installing Oracle DB Vault. You do not need this operation in Oracle 12c.

At Oracle 11g version you need to relink binary with chopt command like below

$ chopt enable lbac

$ chopt enable dv

And You do not need in Oracl 12c vesion

In order to install and configure DB Vault In Oracle 11g version, you have to use dbca. At Oracle 12c you only need dbca to install Oracle Label Security and Oracle DB vault component . You can use DVSYS.CONFIGURE_DV packet to configure DB vault. Actually still you can do this configuration with dbca but it is optional.

DB Vault component installation is made with this page in  dbca in Oracle 12c version.

Capture

And DB Vault configuration can be made by dbca but It is optional

Capture

This gives us flexibility ; During database installation DBAs can install Oracle DB Vault without making any configuration on it. After the DB Vault installation is completed, you can make DB Vault configuration as a security officer without DBA intervention.

The below  query shows that DB Vault component is installed. But It does not mean that It is enabled.

SQL> select comp_id,status from dba_registry where comp_id in (‘OLS’,’DV’);

COMP_ID STATUS
—————————— ———–
DV VALID
OLS VALID

With DVSYS.CONFIGURE_DV; you can mention which user is DB Vault admin and which user database account manager.

SQL> BEGIN
2 DVSYS.CONFIGURE_DV (
3 dvowner_uname => ‘dvowner’,
4 dvacctmgr_uname => ‘dvacctmngr’);
5 END;
6 /

PL/SQL procedure successfully completed.

This packet is new in Oracle 12c and It gives us flexibility to security officer to configure DB vault alone. After DB Vault component installation tou do not need to rebound the database bu you need to run utlrp.sql to compile all invalid objects.