Oracle 10g


Oracle DBA role granted privileges per version

Below you can find the Oracle defaults
DBA role privileges per database version since Oracle 10.2. It is based on Enterprise Edition with Java and XML installed (on 12.1 you cannot anymore choose what to install).
On Oracle
12.1 there are 3 new roles : CAPTURE_ADMIN, EM_EXPRESS_ALL and
OPTIMIZER_PROCESSING_RATE. Comments about these new roles/privileges can be found below.

admin_option
comment
CAPTURE_ADMIN
yes
>12.1
Provides
the privileges necessary to create and manage privilege analysis policies.
DATAPUMP_EXP_FULL_DATABASE
>11.1
DATAPUMP_IMP_FULL_DATABASE
>11.1
DELETE_CATALOG_ROLE
yes
EM_EXPRESS_ALL
>12.1
Enables
users to connect to Oracle Enterprise Manager (EM) Express and use all the
functionality provided by EM Express (read and write access to all EM Express
features). The EM_EXPRESS_ALL role includes the EM_EXPRESS_BASIC role
EXECUTE_CATALOG_ROLE
yes
EXP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
IMP_FULL_DATABASE
JAVA_ADMIN
JAVA_DEPLOY
OPTIMIZER_PROCESSING_RATE
>12.1
Provides privileges to execute the
GATHER_PROCESSING_RATE, SET_PROCESSING_RATE, and DELETE_PROCESSING_RATE
procedures in the DBMS_STATS package. These procedures manage the
processing rate of a system for automatic degree of parallelism (Auto DOP).
Auto DOP uses these processing rates to determine the optimal degree of
parallelism for a SQL statement.
SCHEDULER_ADMIN
yes
SELECT_CATALOG_ROLE
yes
WM_ADMIN_ROLE
XDBADMIN
XDB_SET_INVOKER
>11.1
XDBWEBSERVICES
<10.2

The query used is:
set pages 20 lines 200
col grantee for a10
col granted_role for a40
select * from dba_role_privs where grantee=’DBA’ order by 1,2;

Datapump, remap_schema and passwords 2

Something that is quite logical after you think about, but surprises you at first.

When you do expdp/impdp with the remap_schema parameter, the password of the created user on the target database will not be a valid one.

This because on Oracle 10g the password hash is calculated using the “username” as a seed. When you import a user with impdp, the password hash is not recalculated. So when the user changes (by the remap_schema parameter), the new schema password will not work.

Clean environment:

SQL> select username, password
from dba_users 
where username='T1';
no rows selected

Create user T1 with simple password…

SQL> grant create session to t1 identified by t1;
Grant succeeded.

Check password hash_values

SQL> select username, password 
from dba_users 
where username='T1';
USERNAME                       PASSWORD
------------------------------ ------------------------------
T1                             2A6EC3E5F234DF52

We can connect:

SQL> connect t1/t1
Connected.

Run the export…

$expdp schemas=t1
Export: Release 10.2.0.4.0 - 64bit Production on Thursday, 19 April, 2012 11:16:30
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Username: / as sysdba

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA schemas=t1
...

Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 11:17:00

And import with remap_schema:

$impdp remap_schema=t1:t2
Import: Release 10.2.0.4.0 - 64bit Production on Thursday, 19 April, 2012 11:18:30
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  /******** AS SYSDBA remap_schema=t1:t2
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at 11:18:35

We see now that the password hash of the two users is the same:

SQL> select username, password from dba_users where username in ('T1','T2');
USERNAME                       PASSWORD
------------------------------ ------------------------------
T1                             2A6EC3E5F234DF52
T2                             2A6EC3E5F234DF52

But connecting with the original password does not work:

SQL> connect t2/t1
ERROR:
ORA-01017: invalid username/password; logon denied

Ideas I would like to develop

– testing and documenting the apply of Oracle PSU by:
. clone a master patched Oracle Home
. attach new home to inventory
. move existing DBs to use the new Oracle Home
. run post-PSU operations on each DB (catpsu.sql, etc)

Advantages:
– minimize downtime
– automatize patching


GoldenGate and invalid dates

GoldenGate is a powerful tool bought by Oracle to replace the “Oracle Streams”. It allows heterogeneous replication of data and works just fine almost out-of-the-box.

My client uses it to make a cache copy of data a kind of active standby while running on Oracle 10.2.

Today I had to fight for a problem that comes from the application, which seldom writes to the source database dates in a invalid format, with an year 0 (Oracle should not accept, but the year got in, we are still investigating how).

This makes the GoldenGate replication process to abend, as the write on the target database fails with an “ORA-01841: (full) year must be between -4713 and +9999, and not be 0”.

For the workaround, using GoldenGate MAP function, I managed to transform the dates like the following:
Source: 0000-12-09:08:11:20
Target: 0001-12-09:08:11:20

MAP NESSOFT.NEXT_ORD, TARGET NESSOFT_NL.NEXT_ORD , &
COLMAP ( USEDEFAULTS,
EXPIRE = @IF ( @STRCMP(@STREXT(EXPIRE,1,4),"0000") = 0, @STRCAT("0001-",@STREXT(EXPIRE,6,18)),EXPIRE)
);

This allowed to me also to discover the discard file (.dsc) on the dirrtp folder of GoldenGate, where you can see the output of the MAP function, useful for the debug.


Oracle auditing – answer to auditors

Queries for audit information in Oracle 10gR2. The auditors are there…

They want to know that audit exists and what is audited:

select * from v$spparameter where name like ‘%audit%’; — equivalent to SQL> show parameter audit

select sys_context(‘USERENV’, ‘DB_NAME’) DBNAME , a.* from dba_STMT_AUDIT_OPTS a;
select sys_context(‘USERENV’, ‘DB_NAME’) DBNAME , a.* from DBA_OBJ_AUDIT_OPTS a;
select sys_context(‘USERENV’, ‘DB_NAME’) DBNAME , a.* from DBA_PRIV_AUDIT_OPTS a;
And they want to know who has access to what:
Roles per user:
set pages 1000
break on grantee;
select grantee, granted_role 
  from dba_role_privs, dba_users 
  where grantee=username
  order by 1,2; 
User per role:
set pages 0
break on granted_role
select granted_role,grantee username 
  from dba_role_privs, dba_users
  where grantee=username
  order by 1,2; 
Table privileges per role:
set lines 150
set pages 0
break on privilege on role_name
col role_name for a30
col privilege for a20
col table_name for a60
select grantee role_name,privilege, a.owner||’.’||a.table_name table_name
  from dba_tab_privs a, dba_tables b, dba_roles c
  where a.owner not in (‘ORDSYS’,’EXFSYS’,’MDSYS’,’SYSMAN’,’OUTLN’)
    and a.table_name=b.table_name
    and a.grantee=c.role
  order by 1,2,3;
Privileges given directly to users:
set pages 0
set lines 150
break on username
col username for a30
col privilege for a20
col table_name for a60
select grantee username,privilege, a.owner||’.’||a.table_name table_name
  from dba_tab_privs a, dba_tables b
  where a.owner not in (‘ORDSYS’,’EXFSYS’,’MDSYS’,’SYSMAN’,’OUTLN’,’SYS’,’SYSTEM’,’DBSNMP’,’WMSYS’)
    and a.table_name=b.table_name
    and a.grantee not in (select role from dba_roles)

  order by 1,2,3;