Author: Biju

OracleDB12c New Feature: Unified Auditing
Unified Auditing is a new feature introduced in Oracle Database 12c, to streamline and standardize all audit trail information. The traditional auditing architectures involve many audit-trail locations and tables to review information for the auditors, and do not follow a standard. Every new product introduced in the database had to essentially have a new audit table because the AUD$ table is limited. The Oracle Unified Audit feature introduced in Oracle Database 12c addresses these issues and gives a standard interface and single location for the audit trail.

Unified Auditing is a database option and is not enabled by default. The following query shows if Unified Auditing is enabled or not.
```
SQL> SELECT VALUE FROM V$OPTION
     WHERE PARAMETER = 'Unified Auditing';

VALUE
----------
FALSE
```
To enable Unified Auditing, shut down the database and listener, and then relink the oracle executable using the following options:
```
cd $ORACLE_HOME /rdbms/lib

make -f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=$ORACLE_HOME
```
Unified Auditing is managed by creating and enabling audit policies. Unified audit-trail records can be read using the UNIFIED_AUDIT_TRAIL view. This view includes audit records from standard and fine-grained auditing, along with auditing of data pump, SQL loader, database vault, label security, recovery manager, and real application security products.

Oracle Database 12c has two roles to support Unified Auditing. The AUDIT_ADMIN role has privileges to create, alter, and drop audit policies. It also has privileges to enable or disable audit policies for each business requirement, to view audit records, and to clean up the audit trail. The AUDIT_VIEWER role is for users who only need to view the audit-trail contents. Unified Auditing is owned by AUDSYS user, not SYS user.

Privilege auditing in traditional auditing has to be enabled by setting the AUDIT_SYS_OPERATIONS=true. The audit records are written to operating system files. With Unified Auditing, privilege audit is enabled by default and can be queried using the same UNIFIED_AUDIT_TRAIL view. In a unified audit database, the following actions are audited mandatory without any policy:
- CREATE AUDIT POLICY
- ALTER AUDIT POLICY
- DROP AUDIT POLICY
- AUDIT
- NOAUDIT
- EXECUTE of DBMS_FGA
- EXECUTE of DBMS_AUDIT_MGMT
Read more at
http://docs.oracle.com/cd/E16655_01/network.121/e17607/audit_admin.htm#DBSEG1026

http://docs.oracle.com/cd/E16655_01/server.121/e17609/tdpsg_auditing.htm#TDPSG50000
May 2, 2014

ASM User Management Q&A

Q: Can you create users in ASM instance?

A: Yes, absolutely.

Q: Why?

A: For better security.

Q: I was told there is no database associated with ASM, it is just an instance, so where is this user really created?

A: You must have a password file to create users. The users are added to password file.

Q: Ha! are there default users?

A: Yes, SYS and ASMSNMP are default users.

Q: What privileges can I grant to a user?

A: You can grant SYSDBA, SYSASM or SYSOPER privileges.

Q: How do you create a new user?

A: Use SQL*Plus, connect to the instance as SYSASM for all user management. You may also use orapwusr command in asmcmd.

Q: Can you show an example of creating a new user using SQL*Plus?

A: Sure.

$ sqlplus / as sysasm

SQL*Plus: Release 12.1.0.1.0 Production on Wed Mar 5 21:29:59 2014

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Automatic Storage Management option

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS                            TRUE  FALSE TRUE  FALSE FALSE FALSE          0

SQL> create user bt1 identified by bt1;

User created.

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS                            TRUE  FALSE TRUE  FALSE FALSE FALSE          0
BT1                            FALSE FALSE FALSE FALSE FALSE FALSE          0

SQL> grant sysdba, sysasm to bt1;

Grant succeeded.

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS                            TRUE  FALSE TRUE  FALSE FALSE FALSE          0
BT1                            TRUE  FALSE TRUE  FALSE FALSE FALSE          0

SQL>

Q: I notice that you used 12.1 release database. Is this feature available in lower release as well?

A: Absolutely. the syntax, privileges and functionality is exactly same in 11gR2. This feature is not available in 10g ASM.

$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.2.0 Production on Wed Mar 5 23:07:34 2014

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Automatic Storage Management option

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  TRUE
ASMSNMP                        TRUE  FALSE FALSE

SQL>

Q: How do you add user using ASMCMD?

A: Use the orapwusr command to add, modify or delete a user. Use the lspwusr command to list the user and privileges.

Q: Can you show how to?

A: Here, I create an user, modify its privilege, list the user, and finally drop the user. Notice that only one privilege can be granted using the –privilege option, and all other privileges gets revoked. Using SQL*Plus, I was able to grant more than one privilege.

$ asmcmd -p
ASMCMD [+] > lspwusr
Username sysdba sysoper sysasm 
     SYS   TRUE   FALSE   TRUE 
     BT1   TRUE   FALSE   TRUE 
ASMCMD [+] > orapwusr --add --privilege sysasm bt2
Enter password: ***
ASMCMD [+] > lspwusr
Username sysdba sysoper sysasm 
     SYS   TRUE   FALSE   TRUE 
     BT1   TRUE   FALSE   TRUE 
     BT2  FALSE   FALSE   TRUE 
ASMCMD [+] > orapwusr --modify --privilege sysdba bt2
ASMCMD [+] > lspwusr
Username sysdba sysoper sysasm 
     SYS   TRUE   FALSE   TRUE 
     BT1   TRUE   FALSE   TRUE 
     BT2   TRUE   FALSE  FALSE 
ASMCMD [+] > orapwusr --modify --password bt1
Enter password: ***
ASMCMD [+] > orapwusr --delete bt2
ASMCMD [+] > lspwusr
Username sysdba sysoper sysasm 
     SYS   TRUE   FALSE   TRUE 
     BT1   TRUE   FALSE   TRUE 
ASMCMD [+] >

Q: How do I connect as the new user when logging in?

A: In SQL*Plus, you use the id and password. For “asmcmd”, there is no option – connect as sysasm or sysdba privilege based on the OS group membership.

$ sqlplus bt1 as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Wed Mar 5 21:43:41 2014

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

Enter password: 

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Automatic Storage Management option

SQL> 
SQL> show user
USER is "SYS"
SQL>

Q: Does ASM write anywhere which user logged in?

A: I would guess so, but in reality, it does not write which user. Just says [/], instead of user name. Looking the audit file generated in AUDIT_FILE_DEST location.

$ sqlplus bt1 as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Wed Mar 5 21:55:34 2014

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

Enter password: 

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Automatic Storage Management option

SQL> exit;

$ 
$ cat +ASM_ora_8024_20140305215536821385143795.aud
Audit file /u03/app/oracle/grid/12.1.0/rdbms/audit/+ASM_ora_8024_20140305215536821385143795.aud
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Automatic Storage Management option
ORACLE_HOME = /u03/app/oracle/grid/12.1.0
System name:    Linux
Node name:      btsj.com
Release:        2.6.32-200.13.1.el5uek
Version:        #1 SMP Wed Jul 27 21:02:33 EDT 2011
Machine:        x86_64
Instance name: +ASM
Redo thread mounted by this instance: 0 
Oracle process number: 21
Unix process pid: 8024, image: oracle@btsj.com (TNS V1-V3)

Wed Mar  5 21:55:36 2014 -08:00
LENGTH : '149'
ACTION :[7] 'CONNECT'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/1'
STATUS:[1] '0'
DBID:[0] ''

Q: You are connected as SYSDBA, how about if you connect as SYSASM?

A: Good question, here the audit file shows the actual user.

$ sqlplus bt1 as sysasm

SQL*Plus: Release 12.1.0.1.0 Production on Wed Mar 5 21:59:43 2014

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

Enter password: 

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Automatic Storage Management option

SQL> show user
USER is "SYS"
SQL> 
SQL> exit

$ cat +ASM_ora_8106_20140305215946201566143795.aud
Audit file /u03/app/oracle/grid/12.1.0/rdbms/audit/+ASM_ora_8106_20140305215946201566143795.aud
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Automatic Storage Management option
ORACLE_HOME = /u03/app/oracle/grid/12.1.0
System name:    Linux
Node name:      btsj.com
Release:        2.6.32-200.13.1.el5uek
Version:        #1 SMP Wed Jul 27 21:02:33 EDT 2011
Machine:        x86_64
Instance name: +ASM
Redo thread mounted by this instance: 0 
Oracle process number: 21
Unix process pid: 8106, image: oracle@btsj.com (TNS V1-V3)

Wed Mar  5 21:59:46 2014 -08:00
LENGTH : '151'
ACTION :[7] 'CONNECT'
DATABASE USER:[3] 'bt1'
PRIVILEGE :[6] 'SYSASM'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/1'
STATUS:[1] '0'
DBID:[0] ''

March 6, 2014

Author: Biju

OracleDB12c New Feature: Unified Auditing

ASM User Management Q&A