Oracle Audit Part 4 – Unified Auditing Problem

Part 4 in a loose series of blog posts about auditing; the new Oracle 12 feature. Unified auditing.monkey_examine

The intention behind Unified Auditing, as the name suggests, is to pull together all of the audit records from the disparate Oracle auditing locations into a single unified location. It captures standard audit information (including sys records normally written to the O/S),  FGA audit, record from Database Vault, Label Security, rman, etc.

Sounds great, and it is. 12.1 has a significant problem, but it seems a lot better in 12.2 (where I have not yet had time to spot any problems ūüôā )

The first thing that you need to know is that it is on by default.
It is gathering audit information in your 12C database right now, possibly duplicating any traditional auditing you may have switched-on yourself. By default the database is in “Mixed mode”, meaning you can use the old style of audit and unified auditing too. You need to re-link the oracle binary with the database down to enable exclusively-Unified auditing

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk uniaud_on ioracle

(on windows, you need to move the correct DLL into place.)

So what is enabled by default?

1  select * from audit_unified_enabled_policies;

USER_NAME       POLICY_NAME            ENABLED ENABLED_OPTION  ENTITY_NAME     ENTITY_ SUC FAI
--------------- ---------------------- ------- --------------- --------------- ------- --- ---
ALL USERS       ORA_SECURECONFIG       BY      BY USER         ALL USERS       USER    YES YES
ALL USERS       ORA_LOGON_FAILURES     BY      BY USER         ALL USERS       USER    NO  YES

What does that mean?

  1  select POLICY_NAME,AUDIT_OPTION,AUDIT_OPTION_TYPE,OBJECT_SCHEMA,OBJECT_NAME,OBJECT_TYPE
  2    from audit_unified_policies
  3   where policy_name in ('ORA_SECURECONFIG','ORA_LOGON_FAILURES')
  4*  order by 1,2,3,4,5;

POLICY_NAME          AUDIT_OPTION                             AUDIT_OPTION_TYPE  OBJECT_SCHEMA             OBJECT_NAME               OBJECT_TYPE
-------------------- ---------------------------------------- ------------------ ------------------------- ------------------------- --------------------
ORA_LOGON_FAILURES   LOGON                                    STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     ADMINISTER KEY MANAGEMENT                SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     ALTER ANY PROCEDURE                      SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     ALTER ANY SQL TRANSLATION PROFILE        SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     ALTER ANY TABLE                          SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     ALTER DATABASE                           SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     ALTER DATABASE LINK                      STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     ALTER PLUGGABLE DATABASE                 STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     ALTER PROFILE                            STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     ALTER ROLE                               STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     ALTER SYSTEM                             SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     ALTER USER                               STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     AUDIT SYSTEM                             SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     BECOME USER                              SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE ANY JOB                           SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE ANY LIBRARY                       SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE ANY PROCEDURE                     SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE ANY SQL TRANSLATION PROFILE       SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE ANY TABLE                         SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE DATABASE LINK                     STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE DIRECTORY                         STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE EXTERNAL JOB                      SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE PLUGGABLE DATABASE                STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE PROFILE                           STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE PUBLIC SYNONYM                    SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE ROLE                              STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE SQL TRANSLATION PROFILE           SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE USER                              SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     DROP ANY PROCEDURE                       SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     DROP ANY SQL TRANSLATION PROFILE         SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     DROP ANY TABLE                           SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     DROP DATABASE LINK                       STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     DROP DIRECTORY                           STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     DROP PLUGGABLE DATABASE                  STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     DROP PROFILE                             STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     DROP PUBLIC SYNONYM                      SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     DROP ROLE                                STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     DROP USER                                SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     EXECUTE                                  OBJECT ACTION      REMOTE_SCHEDULER_AGENT    ADD_AGENT_CERTIFICATE     PROCEDURE
ORA_SECURECONFIG     EXECUTE                                  OBJECT ACTION      SYS                       DBMS_RLS                  PACKAGE
ORA_SECURECONFIG     EXEMPT ACCESS POLICY                     SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     EXEMPT REDACTION POLICY                  SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     GRANT ANY OBJECT PRIVILEGE               SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     GRANT ANY PRIVILEGE                      SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     GRANT ANY ROLE                           SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     LOGMINING                                SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     PURGE DBA_RECYCLEBIN                     SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     SET ROLE                                 STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     TRANSLATE ANY SQL                        SYSTEM PRIVILEGE   NONE                      NONE                      NONE

49 rows selected.

So that’s quite a lot of auditing on by default.

If you want to disable unified auditing policies, you need to use:

noaudit policy 
e.g. noaudit policy ora_secureconfig

So what’s the problem with Unified Auditing in 12.1?

Performance. It’s dreadful, and simply unusable.¬† Hwo about this simple select asking how much audit in the last 30 minutes from the UNIFIED_AUDIT_TRAIL view?

select count(*) from unified_audit_trail where EVENT_TIMESTAMP > systimestamp - interval '30' minutes;

It effectively blocked itself in a RAC environment, with one parallel query slave blocked by another – really not sure what was going on there but it never completed (well, I killed it after half a day).

I have seen a similar query in a non-RAC environment (or against table v$unified_audit_trail – which you really shouldn’t use as it can give an incomplete picture) run for well over an hour with a remarkably small data set. Not useful if you are trying to scrape content into an external monitor regularly…

MOS Article 2212196.1 explains how to resolve this in 12.1, by transferring all of the data to a table.

In 12.2, this is resolved by all data being stored in a table call AUDSYS.AUD$UNIFIED.
The table uses interval-based partitioning and is partitioned with a monthly interval:

...
PARTITION BY RANGE ("EVENT_TIMESTAMP") INTERVAL (INTERVAL '1' MONTH)
 (PARTITION "AUD_UNIFIED_P0"  VALUES LESS THAN (TIMESTAMP' 2014-07-01 00:00:00')
...

Because it is a table, we can index it.It works like a table. It’s quick.

In my next audit article, I’ll look to explain how unified auditing works in a bit more depth, now that the performance no longer renders it unusable to queries.

Oracle Audit Control part 3 ‚Äď OS files

Just realized that I wrote a follow-up to Part 2 two years ago and never posted it! So here’s the stripped-out key facts to round off auditing control a bit more:

Deleting O/S audit files is, like, totally necessary. In Oracle 12C (12.1), they are created by default as AUDIT_SYS_OPERATIONS defaults to TRUE now, so you are going to be filling up your AUDIT_FILE_DEST whether you realized it or not.

[NOTE: In theory, this could end up being a performance problem as well as a space problem if you have many millions files in the AUDIT_FILE_DEST. In Oracle 11G, when allocating an AUDIT file, it would check to see if a file for that SPID already existed and add an incremental number, The act of calling vx_dirbread and vx_dirscan can get terribly slow if there are lots of files to wade through. This mechanism is different in 12C but I have not traced the kernel calls to see if the vx_ calls have gone away.]

To cleanup the O/S, we use the same commands as we did in Part 2, but changed to use an AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS

You also need to be aware of the RAC_INSTANCE_NUMBER. For database-level audit, it’s irrelevant. For single instance, it is also irrelevant, but with RAC, this parameter tells you which node to clean up as they are all individual installs with their own audit files (assuming a Grid Infrastructure install per node.)

Commands:

-- Show the BEFORE sizes
host ssh server01 du -m -s /u01/app/oracle/admin/ORCL/a*
host ssh server02 du -m -s /u01/app/oracle/admin/ORCL/a*
begin

-- Loop around every instance in the RAC cluster and run the command
for i in (select inst_id from gv$instance)
loop

-- cleanup AUDIT_FILE_DEST (operating system audit files)
-- set cleanup for 60 days
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
LAST_ARCHIVE_TIME => systimestamp-60,
RAC_INSTANCE_NUMBER => i.inst_id );

-- And cleanup based upon that date
DBMS_AUDIT_MGMT.clean_audit_trail(
audit_trail_type        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
use_last_arch_timestamp => TRUE);

end loop;

end;
/

-- And check the sizes AFTER
host ssh server01 du -m -s /u01/app/oracle/admin/ORCL/a*
host ssh server02 du -m -s /u01/app/oracle/admin/ORCL/a*

 


 

Output:

15:58:56 SYS @ ORCL1 > host ssh server01 du -m -s /u01/app/oracle/admin/ORCL/a*
776 /u01/app/oracle/admin/ORCL/adump
15:58:56 SYS @ ORCL1 > host ssh server02 du -m -s /u01/app/oracle/admin/ORCL/a*
694 /u01/app/oracle/admin/ORCL/adump
15:58:56 SYS @ ORCL1 >
15:58:56 SYS @ ORCL1 >
15:58:56 SYS @ ORCL1 > begin
15:58:56   2
15:58:56   3   for i in (select inst_id from gv$instance)
15:58:56   4   loop
15:58:56   5
15:58:56   6      -- cleanup AUDIT_FILE_DEST (operating system audit files)
15:58:56   7      -- set cleanup for 60 days
15:58:56   8      DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
15:58:56   9      AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
15:58:56  10      LAST_ARCHIVE_TIME => systimestamp-50,
15:58:56  11      RAC_INSTANCE_NUMBER => i.inst_id );
15:58:56  12
15:58:56  13      -- And cleanup based upon that date
15:58:56  14      DBMS_AUDIT_MGMT.clean_audit_trail(
15:58:56  15        audit_trail_type        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
15:58:56  16        use_last_arch_timestamp => TRUE);
15:58:56  17
15:58:56  18   end loop;
15:58:56  19
15:58:56  20  end;
15:58:56  21  /
PL/SQL procedure successfully completed.

15:58:56 SYS @ ORCL1 >
15:58:56 SYS @ ORCL1 >
15:58:56 SYS @ ORCL1 > host ssh server01 du -m -s /u01/app/oracle/admin/ORCL/a*
354 /u01/app/oracle/admin/ORCL/adump
15:58:57 SYS @ ORCL1 > host ssh server02 du -m -s /u01/app/oracle/admin/ORCL/a*
364 /u01/app/oracle/admin/ORCL/adump
15:58:57 SYS @ ORCL1 >
 


 

 
And just one final bit. If you change the default

AUDIT_TRAIL=DB to
AUDIT_TRAIL=DB, EXTENDED

the audit writes the first 2,000 characters of the SQL command to DBA_AUDIT_TRAIL.SQL_TEXT, so you get the full text of the SQL command audited, rather than just the action. Very handy!

Accessing a user when you don’t know the password

There are times that you may need to logon to a database user, probably a schema owner to do a release, but you don’t know the password. You may not be able to (easily) change the password as it could be embedded in application connect strings or worse.

If may not be possible simply to change your session using alter session set current_schema=<schema-to-be-changed>; to auto-prefix all of your selects with the schema, especiually if the release references “USER_” views, which is unaffected by the session setting.

You need to become the account.

So, what you need to do is record the current password encryption, change the password, logon and do your maintenance, logoff and change the password back!

And this is how you do it:
Create an account:

04:38:35 SYS @ ORCL01 > create user hackme identified by password1;

User created.

04:38:35 SYS @ ORCL01 > grant connect,resource to hackme;

Grant succeeded.

Grab the encryption.This is stored in SYS.USER$.SPARE4 plus SYS.USER$.PASSWORD:

04:38:35 SYS @ ORCL01 > select name,'alter user '||name||' identified by values '''||spare4||';'||password||''';' command from sys.user$ where name = 'HACKME'
04:38:35   2  /

NAME       COMMAND
---------- ------------------------------------------------------------------------------------------------------------------------
HACKME     alter user HACKME identified by values 'S:59F38E64D3914BB9396C5D4B968380676333EA7CB34F2471A85C4770A7BA;H:2D3693D1357CF012D9A11EFE3D792C0C;T:B2261F70475F3BD6173867C68427E346C53216E3EC305121DDAF4E13E72E6889DF1E314934F3C5F46E5F12B82D8AC144955C937413FD192904A2762D66B31A872429AB78E72AFC2BC4101E68DB5903A6;4345E749C3EBB34A';

Now we can change the password, logon with the new password, logoff back to a DBA and change it back using the previously captured command

04:38:35 SYS @ ORCL01 > alter user hackme identified by hacker;

User altered.

04:38:35 SYS @ ORCL01 > connect hackme/hacker;
Connected.

04:38:35 HACKME @ ORCL01 > show user
USER is "HACKME"

04:38:35 HACKME @ ORCL01 > connect sys/oracle as sysdba
Connected.

04:38:35 SYS @ ORCL01 > alter user HACKME identified by values 'S:59F38E64D3914BB9396C5D4B968380676333EA7CB34F2471A85C4770A7BA;H:2D3693D1357CF012D9A11EFE3D792C0C;T:B2261F70475F3BD6173867C68427E346C53216E3EC305121DDAF4E13E72E6889DF1E314934F3C5F46E5F12B82D8AC144955C937413FD192904A2762D66B31A872429AB78E72AFC2BC4101E68DB5903A6;4345E749C3EBB34A';
User altered.

04:38:57 SYS @ ORCL01 > conn hackme/password1
Connected.

Magic!

You can also use DBMS_METADATA to get the encryption;

04:39:08 SYS @ ORCL01 >  set long 10000

04:39:08 SYS @ ORCL01 >  select dbms_metadata.get_ddl('USER','HACKME') command from dual;

COMMAND
--------------------------------------------------------------------------------

CREATE USER "HACKME" IDENTIFIED BY VALUES 'S:F299C40420DD341AF9AC4AC89C59A2BB1DFCEF01DB5E3C2B5AD837100117;H:2D3693D1357CF012D9A11EFE3D792C0C;T:101F2A697CA5F77B089C4ECA8EE2DDB82E340D46FE60712445699C5715C3C71BA06532F52CFA987076B51254E5E5A565C44E9F7479018F924707F30874A0BF958D1B8935B7434CF993D3346FF53F28B4;4345E749C3EBB34A'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"

Please read the COMMENTS to learn about Proxy Accounts – an (admin) alternative from 10G onwards!

When did I update that row?

I had a requirement the other day to understand when some data had been changed, but there was no column on the table which showed this.

So how do I find out? Well I could go off mining redo and lots of other time consuming and exotic things, but you can use the Oracle Pseudocolumn ORA_ROWSCN. This gives the SCN assocaited with each row. Well, actually it usually doesn’t. It does not show when the individual row of data was changed but, by default, gives the last changed SCN for the block where the row of data lives.

If you want to know with accuracy the SCN for the row change, you need to create your table with the extension “rowdependencies”. This adds a hidden column to each row, taking 6 bytes and storing the SCN on a row-by-row basis with your data. NOTE: This is a CREATE TABLE option only. You can’t modify a table to add rowdependencies, and there are a few restrictions for tables where this is enabled. Check the documentation for your version.

So, we now have the SCN, whether for the BLOCK or the ROW. What good is that? Well, there’s a chance that Oracle will have remembered approximately when that SCN came out. I think you are guaranteed this for about 120 hours – nearly a week – but depending upon a number of factors including the flashback retention and undo retention times.

You can get a rough idea of the SCN time from V$LOG_HISTORY, a more accurate idea from SYS.SMON_SCN_TIME, or just use the SCN_TO_TIMESTAMP function to make your life easier! If you are within 120 hours and have rowdependencies enabled, it will be roughly accurate to the time of the commit, depending upon a couple of factors – please see comments.

Here’s a short worked example to show the sorting of SCN’s in both normal (block) and rowdependency-enabled tables. Note how the ORA_ROWSCN is the same for each row in the same block in the normal table.

 

00:29:34 NEIL @ ORCL01 > create table scn_block (col1 number, col2 date, c_scn number);
Table created.
00:29:34 NEIL @ ORCL01 > create table scn_row (col1 number, col2 date, c_scn number) rowdependencies;
Table created.
00:29:34 NEIL @ ORCL01 > insert into scn_block values (1,sysdate,userenv('commitscn') );
1 row created.
00:29:34 NEIL @ ORCL01 > commit;
Commit complete.
00:29:34 NEIL @ ORCL01 > host sleep 5
00:29:39 NEIL @ ORCL01 > insert into scn_row values (1,sysdate,userenv('commitscn') );
1 row created.
00:29:39 NEIL @ ORCL01 > commit;
Commit complete.
00:29:39 NEIL @ ORCL01 > host sleep 5
00:29:44 NEIL @ ORCL01 > insert into scn_block values (1,sysdate,userenv('commitscn') );
1 row created.
00:29:44 NEIL @ ORCL01 > commit;
Commit complete.
00:29:44 NEIL @ ORCL01 > host sleep 5
00:29:49 NEIL @ ORCL01 > insert into scn_row values (1,sysdate,userenv('commitscn') );
1 row created.
00:29:49 NEIL @ ORCL01 > commit;
Commit complete.
00:29:49 NEIL @ ORCL01 > column dt_1 format a30 truncate
00:29:49 NEIL @ ORCL01 > column dt_2 format a30 truncate
00:29:49 NEIL @ ORCL01 >
00:29:49 NEIL @ ORCL01 > select col1,col2,c_scn,ora_rowscn,scn_to_timestamp(c_scn) dt_1,scn_to_timestamp(ora_rowscn) dt_2 from scn_block;

COL1       COL2      C_SCN      ORA_ROWSCN DT_1                           DT_2
---------- --------- ---------- ---------- ------------------------------ ------------------------------
1          05-DEC-15 3670102    3670149    05-DEC-15 00.29.34.000000000   05-DEC-15 00.29.43.000000000
1          05-DEC-15 3670148    3670149    05-DEC-15 00.29.43.000000000   05-DEC-15 00.29.43.000000000

00:29:49 NEIL @ ORCL01 > select col1,col2,c_scn,ora_rowscn,scn_to_timestamp(c_scn) dt_1,scn_to_timestamp(ora_rowscn) dt_2 from scn_row;

COL1       COL2      C_SCN      ORA_ROWSCN DT_1                           DT_2
---------- --------- ---------- ---------- ------------------------------ ------------------------------
1          05-DEC-15 3670133    3670134    05-DEC-15 00.29.39.000000000   05-DEC-15 00.29.39.000000000
1          05-DEC-15 3670160    3670161    05-DEC-15 00.29.48.000000000   05-DEC-15 00.29.48.000000000

In an interesting convergance, whilst I was doing this, Martin Widlake was looking at the same thing in a slightly different way. How meta.

 

Auditing Read-Only Standbys

If your company has a passing interest in database security, you are probably running some sort of session auditing at the very least [audit session] (although this can also be useful for troubleshooting connectivity issues). There’s a reasonable chance you’re also running some level of object auditing, or even DML access auditing if your employer so dictates:

Check access/change of objects in the DB
  1  select audit_option, success, failure
  2  from dba_stmt_audit_opts
  3  union
  4  select privilege, success, failure
  5  from dba_priv_audit_opts
  6* order by 2,1
AUDIT_OPTION                                       SUCCESS                                  FAILURE
-------------------------------------------------- ---------------------------------------- -----------------
ALTER ANY PROCEDURE                                BY ACCESS                                BY ACCESS
ALTER ANY TABLE                                    BY ACCESS                                BY ACCESS
ALTER DATABASE                                     BY ACCESS                                BY ACCESS
ALTER PROFILE                                      BY ACCESS                                BY ACCESS
ALTER SYSTEM                                       BY ACCESS                                BY ACCESS
ALTER TABLE                                        BY ACCESS                                BY ACCESS
ALTER USER                                         BY ACCESS                                BY ACCESS
AUDIT SYSTEM                                       BY ACCESS                                BY ACCESS
CREATE ANY JOB                                     BY ACCESS                                BY ACCESS
CREATE ANY LIBRARY                                 BY ACCESS                                BY ACCESS
CREATE ANY PROCEDURE                               BY ACCESS                                BY ACCESS
CREATE ANY TABLE                                   BY ACCESS                                BY ACCESS
CREATE EXTERNAL JOB                                BY ACCESS                                BY ACCESS
CREATE LIBRARY                                     BY ACCESS                                BY ACCESS
CREATE PROCEDURE                                   BY ACCESS                                BY ACCESS
CREATE PUBLIC DATABASE LINK                        BY ACCESS                                BY ACCESS
CREATE SESSION                                     BY ACCESS                                BY ACCESS
CREATE TABLE                                       BY ACCESS                                BY ACCESS
CREATE USER                                        BY ACCESS                                BY ACCESS
DATABASE LINK                                      BY ACCESS                                BY ACCESS
DIRECTORY                                          BY ACCESS                                BY ACCESS
DROP ANY PROCEDURE                                 BY ACCESS                                BY ACCESS
DROP ANY TABLE                                     BY ACCESS                                BY ACCESS
DROP PROFILE                                       BY ACCESS                                BY ACCESS
DROP USER                                          BY ACCESS                                BY ACCESS
EXEMPT ACCESS POLICY                               BY ACCESS                                BY ACCESS
GRANT ANY OBJECT PRIVILEGE                         BY ACCESS                                BY ACCESS
GRANT ANY PRIVILEGE                                BY ACCESS                                BY ACCESS
GRANT ANY ROLE                                     BY ACCESS                                BY ACCESS
PROFILE                                            BY ACCESS                                BY ACCESS
PUBLIC DATABASE LINK                               BY ACCESS                                BY ACCESS
PUBLIC SYNONYM                                     BY ACCESS                                BY ACCESS
ROLE                                               BY ACCESS                                BY ACCESS
SYSTEM AUDIT                                       BY ACCESS                                BY ACCESS
SYSTEM GRANT                                       BY ACCESS                                BY ACCESS
USER                                               BY ACCESS                                BY ACCESS
CREATE JOB                                         BY SESSION                               BY SESSION

And you’re probably writing it into a database table [AUDIT_TRAIL=’DB’]

so how does that work if you open a Dataguard database read only? You are writing into sys.aud$ on the Primary, and that table is replicated to the Standby. So what happens?

From Oracle 11G, if you are running Dataguard and opening the standby up for read access, you may not notice the line in the ALERT log which reads:

AUDIT_TRAIL initialization parameter is changed to OS, as DB is NOT compatible for database opened with read-only access

So it starts writing down O/S audit trail files for all of your auditing options (well, the session connect and DML audit options – you can’t run DDL in a r/o DB). You might want to go and see just how many files it has written to [audit_file_dest], as you may be surprised at just how many are in there.

You may, one day, either run out of space or (more worryingly) have so many millions of files that it causes a performance problem when Oracle access the O/S directory. You might want to think about some sort of periodic clean-up job.

Releasing to schemas the easy way

Sometimes we occasionally just miss the obvious, for years. Just noticed that an easy way to release code to a particular schema is to login as your normal DBA user (USER1) [as preferred by audit], use the alter session command to switch to point to the release schema (USER2) and run your DDL. Oracle behaves, from an object-owner perspective, as if you are logged-in as the schema owner without all the negative aspects of actually having to login as the schema owner or prefix all object with the schema owner.

This is particularly useful when you have multiple schemas within the database and you are releasing to more than one schema at once.

 

e.g.

sqlplus USER1@ORCL

SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 17 12:17:36 2014

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

12:17:38 USER1@ORCL>select owner,table_name from dba_tables where table_name = 'N1';

no rows selected

12:17:46 USER1@ORCL>create table n1 (c1 date);

Table created.

12:17:56 USER1@ORCL>select owner,table_name from dba_tables where table_name = 'N1';

OWNER                          TABLE_NAME
------------------------------ ------------------------------
USER1                          N1

12:17:57 USER1@ORCL>alter session set current_schema=USER2;

Session altered.

12:18:08 USER1@ORCL>create table n1 (c1 date);

Table created.

12:18:11 USER1@ORCL>select owner,table_name from dba_tables where table_name = 'N1';

OWNER                          TABLE_NAME
------------------------------ ------------------------------
USER2                          N1
USER1                          N1

 

 

%d bloggers like this: