Tuesday, March 27, 2012

Hiding table Data from unauthorized Users

In this scenario I'll explain how to hide data on a table from unauthorized users even if these unauthorizes users have DBA privilege.
After hiding the data from unauthorized users whenever they try to select from the secured table they will receive "no rows selected" message.

The advantage of this way that the secured table will look like having no rows, at all will likely be less attractive to intruders than a table that has records but that hides or masks "interesting" columns.

In this demonstration I'll use SCOTT schema.

Firstly: let's give some powerful privileges to user HR on scott.emp table:

SQL> grant dba to hr;
Grant succeeded.

SQL> grant all on scott.emp to hr;
Grant succeeded.

Let's try to select from scott.emp

SQL> conn hr/hr

SQL> select count(*) from scott.emp; --user HR can select from scott.emp

SQL> delete from scott.emp; --user HR can also delete from scott.emp
15 rows deleted.

SQL> rollback;
Rollback complete.

Now we will not revoke any privileges from HR but we will hide the data from him only in two steps.

Step1: Create predicate generation function: 

This function will allow/block the access on table data upon specified conditions like (logon username, IP address, machine name ,..etc)

conn scott/tiger

cond VARCHAR2 (200);
  SYS_CONTEXT('USERENV','SESSION_USER')<>'HR')  --not allowed users(check the note below)
  cond := ' SAL IS NOT NULL';                                --Condition is set on SAL column
  cond := ' SAL IS NULL';
    END IF;
   RETURN (cond);
END sec_emp ;



You can specify the users who only allowed to access scott.emp data and block the others:
e.g. if users scott,Smith,John are the only ones allowed to access scott.emp table, line# 7 will be like that:

And vice versa, you can specify the users who are not allowed to access scott.emp and allow the other users:
e.g. if Users HR,Emma,Lee shouldn't access scott.emp data and the others are allowed, line# 7 will be like that:

Restrictions are NOT limited to usernames, as you can see in the function above you can restrict by machine name,IP, operating system username,... the thing that gives you the power in securing sensitive data.

Step2: Add a security policy:

Next we will add a policy to enforce the function conditions on SCOTT.EMP table:

Connect as sysdba

     DBMS_RLS.ADD_POLICY(object_schema=>'SCOTT', object_name=>'EMP',
                         policy_name=>'HIDE_EMP', function_schema=>'SYS',

--In case the policy is already exist drop it first:

EXEC dbms_rls.drop_policy(object_schema=>'SCOTT',object_name=>'EMP',policy_name=>'HIDE_EMP');

Secondly: Now let's check if user HR can access scott.emp data:

SQL>  select count(*) from scott.emp;  

SQL> delete from scott.emp;
0 rows deleted.

User HR can issue the commands on scott.emp table this means he have no problem with his privileges, but he can't access or play with the data inside scott.emp table, Now user HR will get an indication that table scott.emp is empty so it's highly probable that he will not do further investigations on that table ;-)

For more information and techniques please check this paper

Monday, March 26, 2012

Gathering Fixed Objects Statistics

What are the fixed objects:

Fixed objects are the x$ tables and their indexes.

Why we must gather statistics on fixed objects:

If the statistics are not gathered on fixed objects, the Optimizer will use predefined default values for the
statistics. These defaults may lead to inaccurate execution plans.

Does Oracle gather statistics on fixed objects:

Statistics on fixed objects are not being gathered automatically nor within gathering database stats procedure.

When we should gather statistics on fixed objects:

-After a major database or application upgrade.
-After implementing a new module.
-After changing the database configuration. e.g. changing the size of memory pools (sga,pga,..).
-Poor performance/Hang encountered while querying dynamic views e.g. V$ views.
-This task should be done only a few times per year.

-It's recommended to Gather the fixed object stats during peak hours (system is busy) or after the peak hours but the sessions are still connected (even if they idle), to guarantee that the fixed object tables been populated and the statistics well represent the DB activity.
-Performance degradation may be experienced while the statistics are gathering.
-Having no statistics is better than having a non representive statistics.

How to gather stats on fixed objects:

Firstly Check the last analyzed date:

select OWNER, TABLE_NAME, LAST_ANALYZED from dba_tab_statistics where table_name='X$KGLDP';

------------------------------ ------------------------------      ---------
SYS                   X$KGLDP      20-MAR-12

Secondly Export the current fixed stats in a table: (in case you need to revert back)

exec dbms_stats.create_stat_table('OWNER','STATS_TABLE_NAME','TABLESPACE_NAME');
exec dbms_stats.export_fixed_objects_stats(stattab=>'STATS_TABLE_NAME',statown=>'OWNER');

Thirdly Gather fixed objects stats:

exec dbms_stats.gather_fixed_objects_stats;

In case of reverting to the old statistics:
In case you experianced a bad performance on fixed tables after gathering the new statistics:

exec dbms_stats.delete_fixed_objects_stats(); 
exec DBMS_STATS.import_fixed_objects_stats(stattab =>’STATS_TABLE_NAME’,STATOWN =>'OWNER');

Wednesday, March 21, 2012

About Default And Password Protected Roles

There was a conversation between me and an auditor:

Auditor: What is the default role for the database?
Me:        What do you mean by default role for the database? !!!!!!!!!!!
Auditor: We found this output in the script log we asked you to run for us:

GRANTEE       GRANTED_ROLE                     ADM  DEFAULT_ROLE
--------------      ---------------------                          ---        ------------
SYSTEM       TTXLY_SUDI_ACCESS              YES      YES

Now let me explain:

Firstly, forget the auditor words about the database default role !

So what does column DEFAULT_ROLE represents in dba_role_privs view?

By default Oracle set the roles assigned to any user as a default role for him, to get rid of the headache of setting the roles manually every time the user try to use his roles.

This means the user HR doesn't need to explicitly set the "RESOURCE" role using "set role resource;" command each time he tries to create a table, because "RESOURCE" role is already been set as a DEFAULT role for him.

The following example will give you a clear picture:

Now I’ll set the role “resource” for user HR as a non-default role to see what will happen:

SQL> sho user

SQL> alter user hr default role all except resource;
User altered.

SQL>  select *from dba_role_privs where grantee='HR';
GRANTEE                   GRANTED_ROLE              ADM  DEFAULT_ROLE
---------------------------- ------------------------------        -------    -----
HR                                 RESOURCE                         NO        NO
HR                                 XXX                                       NO       YES

Now I’ll login with HR user and try to create a new table:

SQL> conn hr/hr

SQL> create table asd as select * from employees;
create table asd as select * from employees                                 
ERROR at line 1:
ORA-01031: insufficient privileges

This is what will happen when you set a role as a non default role, to use a non default role you have to explicitly enable the role “resource” using this command:

SQL> set role resource;
Role set.

Now user HR can create the table after enabling the "RESOURCE" role:

SQL> create table asd as select * from employees;
Table created.
Oracle gets the task of setting user's roles a hassle free one by automatically setting any role assigned to the user as a DEFAULT role unless the administrator set it as a non default role.

Here are some useful command:

To check how many roles are allowed to be  "DEFAULT ROLE" for each user in the the database:

SQL> sho parameter max_enabled_roles
------------------------------------ ----------- ------------------------------
max_enabled_roles     integer 150

To make a role as a NON-DEFAULT role:

SQL> alter user HR default role all except RESOURCE;

To make all roles assigned to a user default roles:

SQL> alter user HR default role all;

To check the default and non default roles assigned to a user:

SQL>  select *from dba_role_privs where grantee='HR';
GRANTEE                    GRANTED_ROLE        ADM    DEF
------------------------------ ------------------------------    ---        ---
HR                                 RESOURCE                      NO     YES
HR                                 XXX                                   NO     YES

SQL> desc dba_role_privs
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------
 GRANTEE                                          VARCHAR2(30)
 GRANTED_ROLE                              NOT NULL VARCHAR2(30)
 ADMIN_OPTION                              VARCHAR2(3)
 DEFAULT_ROLE                               VARCHAR2(3)

Now, What about password protected Roles:
When you grant a user a role protected by a password although it will be automatically set as a DEFAULT ROLE, the user must execute "set role  identified by " command, in order to be able to use that role:

Here is an example:

SQL> sho user

SQL> create role xxx identified by 123;
Role created.

SQL> grant select on scott.emp to xxx;
Grant succeeded.

SQL> grant xxx to hr;
Grant succeeded.

SQL> select *from dba_role_privs where grantee='HR';
GRANTEE                        GRANTED_ROLE               ADM   DEF
------------------------------ ------------------------------   ---        ---
HR                                 RESOURCE                        NO     YES
HR                                XXX                                     NO     YES

As we can observe xxx role is a default_role by default.

Now can we use "xxx" role before setting it? let's try

SQL> conn hr/hr

SQL> desc scott.emp
ORA-04043: object aa.ss does not exist

To use the password protected role "xxx" you have to explicitly set it using the following command:

SQL> set role xxx identified by 123;
Role set.

Now, "xxx" role is ready to be used by the user:

SQL> desc scott.emp
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

Monday, March 19, 2012



ORADEBUG is a debugger tool, can be used for (tracing any session, dump DB memory structure, suspend/resume a session and the most useful use is to analyze the hang when an instance is hanging by creating a report shows the blocked/blocker sessions).

How to use ORADEBUG:

First login to SQLPLUS with prelim option AS SYSDBA. prelim option will allow you connect to SGA without opening a session, it cannot be used in an already connected session.

# sqlplus /nolog
SQL> set _prelim on
SQL> conn / as sysdba

# sqlplus -prelim "/ as sysdba"

Second set your PID:

SQL> oradebug setmypid

To perform a DB cluster wide analysis:

SQL> oradebug setinst all

To make the tracefile size unlimited:

SQL> oradebug unlimit

To Run the Hang Analysis: <The most useful use for ORADEBUG>

SQL> oradebug -g def hanganalyze <level#>

-> This will create a logfile and will give you it's path.

-Available levels are:
10     Dump all processes (IGN state)
5      Level 4 + Dump all processes involved in wait chains (NLEAF state)
4*    Level 3 + Dump leaf nodes (blockers) in wait chains (LEAF,LEAF_NW,IGN_DMP state)
3      Level 2 + Dump only processes thought to be in a hang (IN_HANG state)
1-2  Only HANGANALYZE basic output, no process dump at all

*Level 4 and above are resource intensive + large output, which can impact the instance performance.
and thus Oracle recommends to use level 3: 
SQL> ORADEBUG -g def hanganalyze 3

How to read HANGANALYZE trace:

Generally: Read the log and search for these keywords "final blocker" if not found search for "blocked by" & "blocking".

Note: Open chains consist of processes waiting for a resource held by a blocker. These are not true hangs and can be resolved by killing the holding session and allowing the blocked processes to proceed. This can be useful when a process is holding a latch, frequent example is the library latch, and other processes are waiting for the latch.

Check section "State of ALL nodes" At the end of the HANGANALYZE log :

The data under this line means:

"cnode" column means: cluster node number
"sid" is the Oracle session ID!
"sess_srno" column means: SERIAL#
"ospid" The operating system process ID
"state" columns have the following vlaues:

Session with status "LEAF" or "LEAF_NW" means a session blocking others "Good candidate for kill" specially "LEAF".
Session with status "NLEAF" means waiting session and appear to be hanging. 
Session with status "IN_HANG" means there is a problem!
Session with status "IGN" or "IGN_DMP" means the session is idle.

Caution: Do not kill critical processes like SMON or PMON as that would terminate the instance.

Other Commands:

To Check the current trace file name:

SQL> oradebug tracefile_name

Flush any pending writes to the trace file and close it:

SQL> oradebug flush
SQL> oradebug close_trace

To trace a specific session with it's OS PID:

SQL> oradebug setospid

To trace a specific session with it's Oracle PID:

SQL> oradebug setorapid

Using ORADEBUG to freeze/suspend a session:

First you have to get the PID for that session and set ORADEBUG as it:
SQL> oradebug setospid 12518

*12518 is the PID for the session you want to kill

Second Freeze/Hang the session even it running in a middle of something it will freeze:
SQL> oradebug suspend

To UnFreeze/Resume the session to continue it's work:
SQL> oradebug resume

Trace Oracle Clusterware:

Trace CRS events:

SQL> oradebug dump crs 3

Trace CSS behaviour:

SQL> oradebug dump css 3

Trace OCR:

SQL> oradebug dump ocr 3

-You can run oradebug in parallel using different sqlplus sessions.

-ORADEBUG utility is poorly documented by Oracle because the caveats with using this tool to avoid potential damage to the database when calling kernel functions.

Reference: http://mgogala.byethost5.com/oradebug.pdf