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. Starting with 12.1 fixed object statistics will be gathered for the tables that don't have statistics, but still, this will not produce accurate statistics and hence Oracle recommends the DBA do this job himself as he/she knows better the peak time of his/her DB.

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.
-Features that are heavily dependent on fixed objects by design are performing slow, like RMAN, EM and Grid Control.
-This task should be done only a few times per year.

Notes to consider before starting gathering fixed object statistics: 
-It's recommended to Gather the fixed object statistics during peak hours (while the 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 have been populated and the statistics are well representing the DB workload.
-Performance degradation may be experienced during gathering fixed object statistics.
-Having no statistics is better than having a non-representative statistics.

How to gather stats on fixed objects:

First Check the last analyzed date: 
SQL> select OWNER, TABLE_NAME, LAST_ANALYZED from dba_tab_statistics where table_name='X$KGLDP';

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

Second Export the current fixed stats in a table: (in case you need to revert)
-- Create a Stats table to hold the statistics:
SQL> exec dbms_stats.create_stat_table('SYS','STATS_TABLE_BKP');

-- Export the current statistics into the Stats table
SQL> exec dbms_stats.export_fixed_objects_stats(stattab=>'STATS_TABLE_BKP',statown=>'SYS'); 

Last Gather fixed objects stats:
SQL> exec dbms_stats.gather_fixed_objects_stats;

Important Note:
Above command will take a while to complete, once it finishes, it's important to flush the shared pool to get an immediate reflection of the new gathered statistics and let the magic happen:

Remember, it's always recommended to run FLUSH SHARED_POOL command during the least workload times on the DB; as it will force the hard parse of most of the queries on the DB, the thing will slow down the applications for some time; till most of the queries get hard parsed again.

In case of reverting to the old statistics:
In case you experienced bad performance on fixed tables after gathering the new statistics you can revert to the old statistics you already backed up:

 SQL> exec dbms_stats.delete_fixed_objects_stats();  
 SQL> exec DBMS_STATS.import_fixed_objects_stats(stattab =>'STATS_TABLE_BKP', STATOWN =>'SYS');

Update on uncovered fixed objects by gather_fixed_objects_stats procedure: [05-01-2020]
Some fixed objects are not covered by gather_fixed_objects_stats procedure like X$KTFBUE which used by views that show segments size; like DBA_EXTENTS and DBA_FREE_SPACE, in case you observe slowness when querying those views then you have to gather the statistics for the underlying fixed table using the following procedure:
It's recommended to flush the shared pool:

In case you still facing slowness after gathering the fixed objects' statistics, it's recommended to purge the RECYCLEBIN as a workaround:

 For more reading on Statistics topic:

Oracle white paper: Best Practices for Gathering Optimizer Statistics

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:

If you are lazy to do it yourself, there is a script can do it for you: 👍

If you want to do it yourself then follow these steps:

Login to SQLPLUS AS SYSDBA or in case you cannot login in a normal way (e.g. database is hanging and it's not accepting new sessions) you can use the prelim option which will allow you to connect to SGA without opening a session. Note: prelim option 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, a 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 values:

A 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 or the instance will get terminated.

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

I've created a script that can make this job easy:

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

Using ORADEBUG to poke/wake up the SMON or PMON:

In case the SMON process is not timely cleaning up temporary segments, you can poke it by waking it up using oradebug:

First: you need to know the PID of SMON process:
SQL> select pid from v$process p, v$bgprocess b where b.paddr = p.addr and name='SMON';
Second: Wake up the SMON using its pid:
SQL> oradebug wakeup 13

Same thing for PMON, if the PMON is not timely cleaning up the killed sessions traces & locks, you can poke it using oradebug:

First: Get the PMON's PID using this statement:
SQL> select pid from v$process p, v$bgprocess b where b.paddr = p.addr and name='PMON';
Second: Wake up the PMON using its PID:
SQL> oradebug wakeup 2

-You can execute oradebug from more than one sqlplus sessions.

-ORADEBUG utility is poorly documented by Oracle, because it's dangerous and can do a potential damage to the database, this is why it should be used under Oracle support supervision.

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