Oracle auditing of user’s connections

July 27, 2009 Larry Catt Leave a comment

Oracle provides the ability to audit a great range of activities within the Oracle RDBMS environment. An administrator has the ability to audit as much as the activities of the entire database all the way to any select, insert, or update on a single table. Care should be taken on what you wish to audit, due to the amount of disk space required to store all of this information. In this article, we will discuss the auditing of connects by a single user account.

1. Logon to SQL*PLUS as sysdba

mylinux:>sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Mon Jul 27 19:13:26 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

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

SQL>

2. Ensure that audit_trail and audit_sys_operations parameters are set to TRUE.

SQL> show parameter audit

NAME TYPE VALUE
———————————— ———– ——————————
audit_file_dest string /U01/ORACLE/PRODUCT/10.2.0/ADMIN
/ORCL10G/ADUMP
audit_sys_operations boolean FALSE
audit_trail string NONE

SQL> alter system set audit_trail=TRUE scope=spfile;

System altered.

SQL> alter system set audit_sys_operations= TRUE scope=spfile;

System altered.

SQL>

3. Restart the database if you had to edit the init parameters.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup open
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 209718148 bytes
Database Buffers 394264576 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL>

4. Ensure that the new values took effect.

SQL> show parameter audit

NAME TYPE VALUE
———————————— ———– ——————————
audit_file_dest string /U01/ORACLE/PRODUCT/10.2.0/ADMIN
/ORCL10G/ADUMP
audit_sys_operations boolean TRUE
audit_trail string TRUE
SQL>

5. Begin auditing of acccount which you suspect is causing an issue.

SQL> audit connect by ljcatt;

Audit succeeded.

SQL>

6. Logon as the user and perform some actions.

SQL> connect ljcatt/ljcatt
Connected.
SQL>

7. Log back in to oracle as sysdba

SQL> connect sys as sysdba
Connected.
SQL>

8. Perform the following SQL to extract the connection information for the USER LJCATT.

SQL> select userhost, terminal, timestamp, action_name from dba_audit_session wh
ere username=’LJCATT’;

USERHOST TERMINAL TIMESTAMP ACTION_NAME
———— ——– ——— ——————
Mylinux Mylinux1 27-JUL-09 LOGON
Mylinux Mylinux1 27-JUL-09 LOGOFF

9. There is a wealth of information that can be obtained by looking at the various dictionary views which cover the Oracle auditing process. You can easily see if someone is accessing your database in an improper way or manipulating data with a direct connection such as SQL*PLUS instead of through an application. This article covered a brief overview of auditing a single userâ€™s connections; however it is easy to see the power that Oracle auditing can provide.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g, oracle 9i, SQL

Recreate Oracle View

July 20, 2009 Larry Catt Leave a comment

Oracle provides views to allow you to hide the structures of under lying tables, to combine multiple tables into a single logical structure, and to reduce the complexity of SQL being generated against a database. Views allow the developer the flexibility to give users the look and feel of the database they desire, while maintaining a normalized structure under the covers. In this article we will review the SQL to generate a viewâ€™s DDL for review or transport.

1. Connect to your Oracle database server and logon to SQL*PLUS.

MyLinux:>sqlplus ‘\ as sysdba’
SQL*Plus: Release 10.2.0.4.0 – Production on Tue Jul 20 23:51:33 2009

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
SQL>

2. Logon as the user SCOTT and generate the view TEST_VIEW to demonstrate the DDL extraction.

SQL> grant create view to scott;

Grant succeeded.

SQL> connect scott/tiger
Connected.
SQL> create view TEST_VIEW as
2 select emp.ename as employee, dept.dname as department, bonus.sal as salary
from
3 dept, emp, bonus where dept.deptno=emp.deptno and emp.ename=bonus.ename;

View created.

SQL>

3. Execute the following PL/SQL block to generate your DDL.

set serveroutput on
declare

v_ct number;
v_owner varchar2(30):=’SCOTT’;

begin

for v_view in(select view_name, text from dba_views where owner=v_owner)
loop

dbms_output.put_line(‘create view ‘||v_view.view_name||’ as’);
dbms_output.put_line(v_view.text||’;’);

end loop;
end;
/

SQL> connect / as sysdba
Connected.
SQL>
SQL> set serveroutput on
SQL> declare
2
3 v_ct number;
4 v_owner varchar2(30):=’SCOTT’;
5
6 begin
7
8 for v_view in(select view_name, text from dba_views where owner=v_owner)
9 loop
10
11 dbms_output.put_line(‘create view ‘||v_view.view_name||’ as’);
12 dbms_output.put_line(v_view.text||’;’);
13
14 end loop;
15 end;
16 /
create view TEST_VIEW as
select emp.ename as employee, dept.dname as department, bonus.sal as salary from
dept, emp, bonus where dept.deptno=emp.deptno and emp.ename=bonus.ename;

PL/SQL procedure successfully completed.

SQL>
SQL>

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

11g, Oracle, Oracle 10g, oracle 9i, SQL

Account Status within a Oracle RDBMS

July 15, 2009 Larry Catt Leave a comment

Oracle provides the column ACCOUNT_STATUS in the dictionary view DBA_USERS to determine the current status of an Oracle user account. I have recently come across several DBA using this column to incorrectly determine the status of an Oracle user and how their account could be placed back into a workable status. This article addresses the ACCOUNT_STATUS and what a DBA should do with the information.

There are 6 basic types of account status and various combinations of the types. The typical system will contain the following ACCOUNT_STATUS values and each is explained in detail with its meaning and examples.

EXPIRED
EXPIRED & LOCKED
EXPIRED(GRACE)
LOCKED
LOCKED(TIMED)
OPEN

EXPIRED

Meaning – The EXPIRED status indicates one of three events.
1. The DBA has created a new account and expired the password, so the user must change their password on the first logon. An example of this situation is the create user statement below: An example of this situation is the create user statement below:

SQL> Create user TEST identified by test password expire
Default tablespace user_data
Quota unlimited on user_data;
2 3
User created.

SQL> Grant create session to test;
Grant succeeded.
SQL>Grant resource to test;
Grant succeeded.
SQL>
SQL> connect test/test
ERROR:
ORA-28001: the password has expired

Changing password for test
New password:
Retype new password:
Password changed
Connected.
SQL>

2. The password has exceeded the profile limit of PASSWORD_LIFE_TIME and the password must be changed.
3. The administrator alter the user with clause “password expire” to force a password change. An example of this can be seen in the SQL below:

SQL> alter user test password expire;

User altered.

SQL>

EXPIRED & LOCKED

Meaning – An account status of EXPIRED & LOCKED means the password is no longer valid and the account is locked. This is normally caused by the password not being changed before the PASSWORD_LIFE_TIME profile limit is exceeded and the administrator has locked the account.

EXPIRED(GRACE)

Meaning – An account status of EXPIRED(GRACE) indicates that the account password is locked due to the password not being changed before the PASSWORD_LIFE_TIME profile parameter pasted, however you have the additional time defined by the PASSWORD_GRACE_TIME profile parameter to actually change your password. If you do not change the password within the grace time the status will be changed to EXPIRED.

NOTE: The status of the account will not change until you attempt to connect with that username. The example below demonstrates the occurrence of EXPIRED(GRACE).

SQL> select account_status from dba_users where username=’TEST’;

ACCOUNT_STATUS
——————————–
OPEN

SQL> connect test/pass
ERROR:
ORA-28002: the password will expire within 0 days

Connected.
SQL> connect / as sysdba
Connected.

SQL> select account_status from dba_users where username=’TEST’;

ACCOUNT_STATUS
——————————–
EXPIRED(GRACE)

SQL> connect test/pass
ERROR:
ORA-28001: the password has expired

Changing password for test
New password:
Retype new password:
ERROR:
ORA-00988: missing or invalid password(s)

Password unchanged
Warning: You are no longer connected to ORACLE.
SQL> connect / as sysdba
Connected.
SQL> select account_status from dba_users where username=’TEST’;

ACCOUNT_STATUS
——————————–
EXPIRED

SQL>

LOCKED

Meaning – The account was locked by a DBA with the SQL statement:

Alter user “username” account lock;

LOCKED(TIMED)

Meaning – The account was locked by a user trying to access the username with the incorrect password beyond the profile limit of FAILED_LOGIN_ATTEMPTS. The account will remained locked until the profile limit of PASSWORD_LOCK_TIME is reached. PASSWORD_LOCK_TIME is a definition of time in minutes.

OPEN

Meaning – The account status of OPEN means the account is open for logon and the user can perform any task defined by his granted rights.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

11g, Oracle, Oracle 10g, oracle 9i, SQL

Oracle auditing insert, update, and delete on a table

July 3, 2009 Larry Catt Leave a comment

Oracle provides the ability to audit a great range of activities within the Oracle RDBMS environment. Care should be taken on what you wish to audit, due to the amount of disk space required to store all of this information. In this article we will discuss the auditing of insert, update, and deletes by user account access.

1. Logon to SQL*PLUS as sysdba

mylinux:>sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Fri Jul 03 19:18:21 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

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

SQL>

2. Ensure that audit_trail is set to DB_EXTENDED and audit_sys_operations parameter is set to TRUE. Note: It is important to realize that setting the AUDIT_TRAIL to DB_EXTENDED will dramatically increase storage, so monitor this closely.

SQL> show parameter audit

NAME TYPE VALUE
———————————— ———– ——————————
audit_file_dest string /U01/ORACLE/PRODUCT/10.2.0/ADMIN
/ORCL10G/ADUMP
audit_sys_operations boolean FALSE
audit_trail string NONE

SQL>

System altered.

SQL> alter system set audit_sys_operations= TRUE scope=spfile;

System altered.

SQL>

3. Restart the database if you had to edit the init parameters.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup open
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 209718148 bytes
Database Buffers 394264576 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL>

4. Ensure that the new values took effect.

SQL> show parameter audit

5. Execute the audit command below to begin monitoring changes to the table SCOTT.DEPT.

SQL> audit insert, update, delete on scott.dept by session;

Audit succeeded.

SQL>

6. Connect to the database as the user LJCATT and perform a insert, update and delete operation on the table scott.dept.

SQL> connect ljcatt/ljcatt
Connected.
SQL> insert into scott.dept(deptno, dname, loc) values(50,’PARTNERS’,’NEW YORK’)
;

1 row created.

SQL> commit;

Commit complete.

SQL> update scott.dept set loc=’WASHINGTON’ where deptno=50;

1 row updated.

SQL> commit;

Commit complete.

SQL> delete from scott.dept where deptno=50;

1 row deleted.

SQL> commit;

Commit complete.

SQL>

7. Login as sysdba again.

SQL> connect sys as sysdba
SQL>

8. Execute the following SQL to extract the statements executed by LJCATT.
â€œselect timestamp, sql_text from dba_audit_object where username=’LJCATT’;â€

SQL> select timestamp, sql_text from dba_audit_object where username=’LJCATT’;

TIMESTAMP SQL_TEXT
——— ——————————————————-
27-AUG-09 update scott.dept set loc=’WASHINGTON’ where deptno=50
27-AUG-09 insert into scott.dept(deptno, dname, loc) values(50,’PARTNERS’,’NEW YORK’)
27-AUG-09 delete from scott.dept where deptno=50

SQL>

9. That completes the use of Oracle Auditing to monitor the changes to the Oracle database by users.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Catt Solutions

Monthly Archives: July 2009

Oracle auditing of user’s connections

Recreate Oracle View

Account Status within a Oracle RDBMS

Oracle auditing insert, update, and delete on a table

Oracle tips and tricks.