Thursday, March 29, 2012

what comes next, after enabling audit?

After enabling and setting the audit options, another process should start, surveillance. As everyone can figure it out easily, auditing is not for just setting and leave it alone. Responsible group should inspect the reports in a timely fashion. DBA can obtain these kind of reports with a time window. Or a centralized logging application can be installed. Oracle also has an "Audit Vault" product for this purpose which collects the audit data from all databases and produces reports to the audit and/or data security departments.

In this individual case, i have created Custom Reports for the most common cases in Oracle Enterprise Manager Grid Control. The mentioned reports are sent to the Security and Audit departments in a daily basis. The output of the SQL commands below are also collected by a centralized logging application.

Some assumptions should be made in order to understand the following queries. First of all the application users (database users which are ment to be used by only through the application interface) are grouped into the application profiles. By assigning individual profiles to the application. dblink. admin users etc.. , DBA can force the password complexity and password lifetime values dedicated to the application behaviour. Clearly, nobody wants to have a critical downtime of a frequently used application because of a password lock of the application user in the middle of the daytime (so, yes I prefer password_lifetime value of the application related users to unlimited). But this subject belongs to some other topic about security.

Probably one of the most important audit data to be reported is the connections to the database with the application users. Because of the high amount of the data produced by the application user actions, in most of the cases auditing every operation of the application user is impossible. But this reveals a vulnerability which is; any user connects to the database with the application user can do anything without a trace. So it is very important to audit the connections with the application user and from a source outside of the application server(s) which means from a workstation.


-- all access from application users via workstations
select   
  OS_USERNAME, 
  USERNAME, 
  USERHOST, 
  TERMINAL, 
  TIMESTAMP,    
  OWNER,         
  OBJ_NAME,
  ACTION_NAME,         
  COMMENT_TEXT,      
  SQL_TEXT,
  RETURNCODE    
  from sys.dba_audit_trail
  where username in 
    (select username 
      from dba_users 
      where profile in 
        (select 
           distinct(profile) 
         from 
           dba_profiles 
         where profile like '%APPLICATION%')) 
  and lower(userhost) not in (select hostname from t_application_servers)
  and os_username != 'debug'
  and action_name in ('LOGON','LOGOFF')
  and timestamp > sysdate-7
  order by timestamp desc;



Any drop table operation should be reported if the action user is not an application user. Operation itself may not be risky at all times because any user can drop any object from their own schemas. But still this operation should be reported in timely fashion to be sure that nobody is dropping a vital object/table for the sake of the application avalibility.


-- unknown user drops or truncates any table
select 
  OS_USERNAME, 
  USERNAME, 
  USERHOST, 
  TERMINAL, 
  TIMESTAMP,    
  OWNER,         
  OBJ_NAME,
  ACTION_NAME,         
  COMMENT_TEXT,      
  SQL_TEXT,
  RETURNCODE    
  from sys.dba_audit_trail  
  where username not in 
      (select username 
      from dba_users 
      where profile in 
        (select 
           distinct(profile) 
         from 
           dba_profiles 
         where profile like '%APPLICATION%')) 
  and action_name in ('TRUNCATE TABLE','DROP TABLE','DROP INDEX')
  and timestamp > sysdate-7
  order by timestamp desc;



Object privileges are very important, when a DBA breaks his/her rules and the rest comes in a very short time. Any privilege granted to any user out of the roles should be reported and investigated. If this is a temporary grant then it should be followed up to be revoked.


-- any grant to unauthorized user
select 
  OS_USERNAME, 
  USERNAME, 
  USERHOST, 
  TERMINAL, 
  TIMESTAMP,    
  OWNER,         
  OBJ_NAME,
  GRANTEE,
  ACTION_NAME,         
  COMMENT_TEXT,      
  SQL_TEXT,
  RETURNCODE  
  from sys.dba_audit_trail
  where grantee not in 
    (select username 
      from dba_users 
      where profile in 
        (select 
           distinct(profile) 
         from 
           dba_profiles 
         where profile like '%APPLICATION%')) 
  and action_name like 'GRANT%'
  and timestamp > sysdate-7
  order by timestamp desc;



Public synonyms and public dblinks makes the general database operation very easy going. But these objects also cause severe security impact on the operation of the database. Application operation can easly be directed to custom created tables or dblinks and cause unwanted actions and operations. These public object creations should be investigated and followed in a timely fashion.


-- create public synonym or create public dblink where user is not dba
select 
  OS_USERNAME, 
  USERNAME, 
  USERHOST, 
  TERMINAL, 
  TIMESTAMP,    
  OWNER,         
  OBJ_NAME,
  ACTION_NAME,         
  COMMENT_TEXT,      
  SQL_TEXT,
  RETURNCODE  
  from sys.dba_audit_trail
  where username not in 
    (select username 
      from dba_users 
      where profile in 
        ('DBA_PROFILE'))
  and (action_name='CREATE PUBLIC SYNONYM' or action_name='CREATE PUBLIC DBLINK')
  and timestamp > sysdate-7
  order by timestamp desc;



Alter System or Alter Database operations should only be done by the privileged DBA's (please note that in this database all DBA users are grouped in a profile named DBA_PROFILE). Any user tries or even successes the Alter System or Alter Database command should be reported immediately. The list of the users for the last one week can be obtained from the following query.


-- alter system and alter database operations where user not in dba list 
select
  OS_USERNAME, 
  USERNAME, 
  USERHOST, 
  TERMINAL, 
  TIMESTAMP,    
  OWNER,         
  OBJ_NAME,
  ACTION_NAME,         
  COMMENT_TEXT,      
  SQL_TEXT,
  RETURNCODE   
  from sys.dba_audit_trail
  where  username not in 
    (select username 
      from dba_users 
      where profile in 
        ('DBA_PROFILE'))
  and (action_name like 'ALTER SYSTEM' or action_name like 'ALTER DATABASE')
  and timestamp > sysdate-7
  order by timestamp desc;



Any operation from any user (out of the application users) should be reported if they are trying (or successes) to change/alter an object in another schema. This can be normal development operation but in some cases it can be considered as an unwantend operation.


-- any operation within another schema
select
  OS_USERNAME, 
  USERNAME, 
  USERHOST, 
  TERMINAL, 
  TIMESTAMP,    
  OWNER,         
  OBJ_NAME,
  GRANTEE,
  ACTION_NAME,         
  COMMENT_TEXT,      
  SQL_TEXT,
  RETURNCODE  
  from sys.dba_audit_trail  
  where username not in      
      (select username 
      from dba_users 
      where profile in 
        (select 
           distinct(profile) 
         from 
           dba_profiles 
         where profile like '%APPLICATION%'))    
  and username != owner 
  and action_name not in ('LOGOFF', 'LOGON', 'LOGOFF BY CLEANUP') 
  and timestamp > sysdate-7
  order by timestamp desc;



Last but not the least failed login attemps may also be reported. Actually there will be some output of this query but in case of the count of the records are rising up inexplicably then the reason of this rise should be investigated as it may be someone is trying to login continously (If "failed login attemps" value is not set in the database profile).


-- failed login attempt
select 
  os_username, 
  username, 
  userhost, 
  timestamp, 
  returncode 
from 
  dba_audit_session
where 
  action_name = 'LOGON'
  and returncode > 0 
  and timestamp > sysdate-7
  order by timestamp;




Here I can provide only my experiences of the further reporting of the Oracla Database Audit option. Of course this list of reports can be widen or broaden according to the audit/security department needs or expects from the database administrators which should be decided all together.

Wednesday, March 14, 2012

Collection Types in table columns may cause performance problems

As this is not one of the relational database rules supported by Boyce-Codd, in Oracle Database table columns can be defined as NESTED TABLES or COLLECTION TYPES. This functionality seems to be handy in some cases as you can fetch all the values listed in one column but there may be some consequences especially in performance.

To demonstrate the collection and nested table types, i will create three different users table. First table will use a COLLECTION/VARRAY TYPE as the column type, the second one will use the NESTED TABLE and the third table will use built-in VARCHAR type to hold the demo users phone numbers. After all, i will insert some random data to query and to examine the execution plans and statistics information.


--create the test array type
create type TYP_PHONE_VARRAY as varray(5) of varchar2(15); 

--create the test table type
create type TYP_PHONE_TABLE as table of varchar2(15); 


--create table which uses the varray type
drop table T_USER_VARRAY;
create table T_USER_VARRAY
(
username varchar2(25),
fullname varchar2(25),
phone TYP_PHONE_VARRAY default null
);

--create table which uses the nested table
drop table T_USER_TABLE;
create table T_USER_TABLE
(
username varchar2(25),
fullname varchar2(25),
phone TYP_PHONE_TABLE default null
)
NESTED TABLE phone STORE AS nt_t_user_table_phone;

--create table which uses the built-in varchar as column type
drop table U_EPEKER.T_USER_STR;
create table U_EPEKER.T_USER_STR
(
username varchar2(25),
fullname varchar2(25),
phone1 varchar2(15) default null,
phone2 varchar2(15) default null,
phone3 varchar2(15) default null,
phone4 varchar2(15) default null,
phone5 varchar2(15) default null
);



After creating the types and the tables, some random data would be very useful to query and examine the execution plans of the queries. And of course i should not forget to gather statistics, also on the nested table.



--fill the varray typed table with the test data.
declare

  i number;
  v_name varchar2(10);
  v_surname varchar2(10);
  v_username varchar2(25);
  v_phone varchar2(15);
  
begin

  execute immediate ('truncate table T_USER_VARRAY');
  i:=0;
 
  while i<10 loop
    v_name := DBMS_RANDOM.STRING('u', 10);
    v_surname := DBMS_RANDOM.STRING('u', 10);
    v_username := substr(v_name,1,1) || '_' || v_surname;
    v_phone := '+31' || round(DBMS_RANDOM.VALUE(60,70)) || 
                        round(DBMS_RANDOM.VALUE(1000000,9999999)); 
    
    insert into t_user_varray
    values
    (
    v_username,
    v_name || ' ' || v_surname,
    TYP_PHONE_VARRAY(v_phone)
    );
  i:=i+1;
  end loop;
  
  commit;
  
end; 


--fill the nested table with the test data.
declare

  i number;
  v_name varchar2(10);
  v_surname varchar2(10);
  v_username varchar2(25);
  v_phone varchar2(15);
  
begin

  execute immediate ('truncate table T_USER_TABLE');
  i:=0;
 
  while i<10 loop
    v_name := DBMS_RANDOM.STRING('u', 10);
    v_surname := DBMS_RANDOM.STRING('u', 10);
    v_username := substr(v_name,1,1) || '_' || v_surname;
    v_phone := '+31' || round(DBMS_RANDOM.VALUE(60,70)) || 
                        round(DBMS_RANDOM.VALUE(1000000,9999999)); 
    
    insert into t_user_table
    values
    (
    v_username,
    v_name || ' ' || v_surname,
    TYP_PHONE_TABLE(v_phone)
    );
  i:=i+1;
  end loop;
  
  commit;
  
end; 


--fill the conventional table with the test data.
declare

  i number;
  v_name varchar2(10);
  v_surname varchar2(10);
  v_username varchar2(25);
  v_phone varchar2(15);
  
begin

  execute immediate ('truncate table T_USER_STR');
  i:=0;
 
  while i<10 loop
    v_name := DBMS_RANDOM.STRING('u', 10);
    v_surname := DBMS_RANDOM.STRING('u', 10);
    v_username := substr(v_name,1,1) || '_' || v_surname;
    v_phone := '+31' || round(DBMS_RANDOM.VALUE(60,70)) || 
                        round(DBMS_RANDOM.VALUE(1000000,9999999)); 
    
    insert into t_user_str
    (
    username,
    fullname,
    phone1
    )
    values
    (
    v_username,
    v_name || ' ' || v_surname,
    v_phone
    );
  i:=i+1;
  end loop;
  
  commit;
  
end; 

--gather the statistics of the filled tables
exec sys.dbms_stats.gather_table_stats(ownname=>'U_EPEKER', tabname=>'T_USER_STR', estimate_percent=>100, cascade=>true);
exec sys.dbms_stats.gather_table_stats(ownname=>'U_EPEKER', tabname=>'T_USER_VARRAY', estimate_percent=>100, cascade=>true);
exec sys.dbms_stats.gather_table_stats(ownname=>'U_EPEKER', tabname=>'T_USER_TABLE', estimate_percent=>100, cascade=>true);
exec sys.dbms_stats.gather_table_stats(ownname=>'U_EPEKER', tabname=>'NT_T_USER_TABLE_PHONE', estimate_percent=>100, cascade=>true);


After the preparation of the tables and filling them with some test data we can examine the costs of the same identical queries on these tables.

Selecting all five of the columns and only the one column has both 8 bytes of "consistent gets" but the returned amount of data differs as expected. As the developer cannot guess how many phone number exists for an individual user, probably in the code all columns of the phones be selected to be sure of it.

But when selecting from the table which has the VARRAY type as the "phone" column there is an unexpected amount of bytes and "consistent gets" in the first look. As it is also explained in the Oracle Documentation columns and variables which are defined as VARRAY types are objects which should be instantiated once in the memory. This is the most probable reason of this excessive consistent gets which is almost 5 times more than the conventinal VARCHAR column. Even there is only one phone number stored in the VARRAY, read operation results as if there are five values in the list because the object instantiated as it is defined before.

On the other hand, when i examine the query on the table which uses the NESTED TABLE type for the phone column there is a considerable difference in the "consistent gets" and "bytes read" when comparing with the VARRAY type. Most probably this result indicates that NESTED TABLE types are not instantiated as objects in the memory. They are real tables in the database which are nested in another table and returns the results which they store physically in the database. If you investigate you will find the index and the table segments in the tablespace.


EXPLAIN PLAN FOR
SELECT 
  phone1, phone2, phone3, phone4, phone5 
FROM t_user_str 
where username='M_VKFHWFQOKL';  --1 row 48 bytes

/*
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        810  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
*/

EXPLAIN PLAN FOR
SELECT 
  phone1
FROM t_user_str 
where username='M_VKFHWFQOKL';  --1 row 26 bytes

/*
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        534  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
*/

EXPLAIN PLAN FOR
SELECT 
  phone 
FROM t_user_varray 
where username='V_RLQDMEPGBJ';  --1 row 35 bytes

/*
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         44  consistent gets
          0  physical reads
          0  redo size
       4619  bytes sent via SQL*Net to client
       1865  bytes received via SQL*Net from client
         11  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
*/


EXPLAIN PLAN FOR
SELECT 
  phone 
FROM t_user_table 
where username='N_RNJASPGQOS';  --1 row 30 bytes

/*
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         16  consistent gets
          0  physical reads
          0  redo size
       1675  bytes sent via SQL*Net to client
        800  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
*/



As a conclusion; if you are not sure that the VARRAY typed column will not get filled properly then, instead of using VARRAY type using NESTED TABLES may be more convenient for the performance of the application. These small decreases of IO and consistent gets may be very valuable in a busy application for an enterprise environment.


Resources:
http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/objects.htm
http://docs.oracle.com/cd/B28359_01/appdev.111/b28371/adobjdes.htm#i448939
http://docs.oracle.com/cd/B28359_01/appdev.111/b28371/adobjcol.htm#i454908