TOAD: Effective tool for Oracle DB object management

TOAD acronym stands or Tools for Oracle Application Development. Amazing since now we have TOAD for SQL-Server, TOAD for DB2, TOAD for MySQL, TOAD for managing your database.

REALLY, TOAD has come a long way. You can check-out everything you want to know about TOAD here!

However, this post is not about DELL or Quest, but a specific capability that TOAD offers, which I find very useful. That feature is :- Oracle DB objects management!

Essentially, as DB configuration manager one must ensure that the application upgrade script performed its objective; All its objective; Only its objective. In other words, when DBA executes a deployment script on a weekend, he or she wants to be sure that script did all it needs to without messing something else up.

One effective way, I found is to use TOAD Compare SCHEMA or Compare DB… In this post we will explore this TOAD feature.

Scenario:

DBA is executing a application upgrade script, provided by development/configuration management team that changes DB structure. This script has been tested in unit /QA environment. This is being done at Sunday 2:00 AM in PRODUCTION.

• Adds columns to certain tables; Adds some constraints; Removes one constraint; Changes several packages (or procedures/functions); Modifies some triggers; Adds a view and drops another view.

May be some other changes…

• Adds some FGAC policies; Removes some users and roles.; Provides some grants to certain users and roles; Changes audit setting for some DB objects.

Of course DBA is executing this script at 2:00 AM on Sunday morning.

How can the DBA be confident, that all the changes and only the changes that were intended were actually applied.

TOAD privdes capabilities for schema (or DB) compare feature that helps the DBA to capture the changes, identify the differential in the database over time. It is a very effective tool.

Application upgrade PROCESS in DB

Step 1: Prior to changes DBA captures current schema, in “definition file”.

Step 2: Executes the script to implement the changes.

Step 3: Execute compare schema (or DB) utility in TOAD between current schema (or DB) to captured definition file.

That’s it.

At this point DBA still has to worry about a whole suite of things to make the application available.

Let us take a quick walkthrough the Schema/DB compare steps in TOAD.

Capture the current schema/DB definition in SDXML file.

From main menu, DBA can generate Schema / DB definition file using Export menu.

The Export Schema screen provides options to export DDLs or schema definition. Also it provides options for setting various options and filters (including storage clauses) to be set and saved.

Once extraction is complete (this could take few minutes depending on the schema (or DB) size (number of objects), TOAD will display the inventory summary of the objects.

DBA can close this export  window in TOAD and perform the application upgrade.

Once the upgrade is complete, DBA can use compare utility to compare the upgrade schema (current) to the schema that was captured in previous step to produce the differential report.

This option lets the DBA compare current schema to a target schema or saved schema definition. The target for compare can be selected by clicking on “+” sign.

When compare is executed, TOAD provides a very intuitive report, as follows. This report can be extracted as XL file and emailed to other team members / management.

In addition, there are several other options to analyze the schema comparison report.

Any DBA who is intimidated by the additional manual steps to be performed at 2:00 am Sunday morning, should check out TOAD automation designer (which generates DBMS_SCHEDULER scripts).

In any case, I have found this tool very useful. Most of the time in retaining my sanity and sometime my JOB ;-)

There is nice video on TOAD forum (by Mark Kurtz): http://www.quest.com/tv/All-Videos/1254935431001/Compare-and-Sync-Schemas-with-Toad-for-Oracle/Video/

If you have any questions, please contact us @ www.tsp-inc.us

Good Luck!

FGAC / EXEMPT and resulting Audit records – More explored

In my previous post I demonstrated, the potential for excessive audit record generation in Oracle 11g. In this post, I’ll explore the effect of a “Keyed function call” from view (or select statements) on generation of BY ACCESS audit records.

I’ll use the same setup as described in the previous post for this demonstration too.

Setup:

In addition to previous setup, we’ll create a function T_ROW that selects and returns value of T.C2 for given key T.C1.

Here is the function…

CREATE OR REPLACE

  FUNCTION t_row(

      in_id NUMBER)

    RETURN VARCHAR2

  AS

    v2 VARCHAR2 (100);

  BEGIN

    SELECT t.c2 INTO v2 FROM hr.t WHERE t.c1 = in_id;

    RETURN v2;

  END;

We’ll modify the view, to select a column using the function, show below…

create or replace view view_t as

SELECT t."C1",

  t."C2",

  (SELECT COUNT(*) FROM t t1 WHERE t1.c1 = t.c1

  ) scalar_cnt,

  t_row(t.c1) func_value

FROM t;

Let us verify the result-set of the view.

SQL> revoke exempt access policy from hr;

 

Revoke succeeded.

 

SQL> connect a/a;

Connected.

SQL> set line 200 ;

SQL> set wrap off;

SQL> col func_value format a20;

SQL> select * from hr.view_t;

 

        C1 C2           SCALAR_CNT FUNC_VALUE

---------- ------------ ---------- --------------------

         1 AAAA                  1 AAAA

         2 AAAA                  1

         3 AAAA                  1 AAAA

         4 AAAA                  1

         5 AAAA                  1 AAAA

         6 AAAA                  1

         7 AAAA                  1 AAAA

         8 AAAA                  1

         9 AAAA                  1 AAAA

        10 AAAA                  1

 

10 rows selected.

 

SQL>

Check the result set above and let us review the cause of this behavior.

User A has been granted EXEMPT ACCESS POLICY system privilege. So when user A, selects from HR.VIEW_T which directly accesses table T (twice) the FGAC policy function is by-passed, returning all 10 rows.

However, since the column FUNC_VALUE is derived from a call to function HR.T_ROW, which executes under the privilege of user HR (check definer rights versus invoker rights) and user HR cannot bypass FGAC policy (though HR owns all the objects) only rows that satisfy the FGAC policy are returned. For a quick review of FGAC policy function defined effect, click here.

Review Audit Trail:

Now with this set up let us check the audit record generated by user A when accessing the view HR.VIEW_T.

SQL> select * from hr.view_t;

 

Execution Plan

----------------------------------------------------------

Plan hash value: 3335345748

 

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |    10 |    80 |     3   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |

|*  2 |   TABLE ACCESS FULL| T    |     1 |     3 |     3   (0)| 00:00:01 |

|   3 |  TABLE ACCESS FULL | T    |    10 |    80 |     3   (0)| 00:00:01 |

---------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - filter("T1"."C1"=:B1)

Here table HR.T is accessed twice (using FTS), and for each access and audit record is generated for each access. One of the audit record is for EXPLAIN plan.

SQL> select username, count(*) from dba_audit_trail group by username;

 

USERNAME                         COUNT(*)

------------------------------ ----------

A                                       3

 

SQL>

Now let us see what happens, if we add an index on T.C1 and use the index for accessing the view HR.VIEW_T.

We’ll create an index, add primary key and gather statistics.

SQL> create index t_indx on t (c1);

Index created.

 

SQL> alter table t add (constraint t_pk primary key (c1) using index t_indx enable validate);

Table altered.

 

SQL> insert into t select level + 10 c1, 'BBBBB' from dual connect by level < 10001;

10000 rows created.

 

SQL> commit;

Commit complete.

 

SQL>  analyze table t compute statistics ;

Table analyzed.

 

SQL> analyze table t compute statistics for all indexed columns;

Table analyzed.

 

SQL>

Let us check the explain plan and audit records when user A selects from HR.VIEW_T.

SQL> select * from hr.view_t

10010 rows selected.

 

Execution Plan

----------------------------------------------------------

Plan hash value: 700470346

 

----------------------------------------------------------------------------

| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |        | 10010 | 80080 |     9   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE   |        |     1 |     3 |            |          |

|*  2 |   INDEX RANGE SCAN| T_INDX |     1 |     3 |     1   (0)| 00:00:01 |

|   3 |  TABLE ACCESS FULL| T      | 10010 | 80080 |     9   (0)| 00:00:01 |

----------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - access("T1"."C1"=:B1)

 

 

Statistics

----------------------------------------------------------

      11595  recursive calls

        127  db block gets

      32080  consistent gets

         13  physical reads

      23228  redo size

     175242  bytes sent via SQL*Net to client

       7757  bytes received via SQL*Net from client

        669  SQL*Net roundtrips to/from client

         25  sorts (memory)

          0  sorts (disk)

      10010  rows processed

 

SQL>

The audit records generated is still 3.

SQL> select count(*) from dba_audit_trail;

 

  COUNT(*)

----------

         5

 

SQL>

 
The experiment above demonstrates that in release 11.1.0.7 of Oracle 11g, audit records generated due to EXEMPT ACCESS POLICY (per access) with FGAC policy in effect, is managed as expected.

However, under certain conditions I have experienced excessive audit record generation.

I’ll explore that more in future postings.

 

Till then BYE!!!!

Excessive AUDIT records with FGAC/EXEMPT privilege

Last week, I posted an article demonstrating the audit record generation with EXEMPT ACCESS POLICY in effect in Oracle 11g.

In this article, I'll demonstrate the potential for undesired audit record generation, due to in-effective audit configuration.

Setup:

1.      User HR ownes the table T, as defined in my previous post.

2.      User A, FGAC policy, and policy functions are also defined in the referenced post.

3.      I'll define a view, HR.VIEW_T using table HR.T. The script for view is below.

SELECT t."C1",

  t."C2",

  (SELECT COUNT(*) FROM t t1 WHERE t1.c1 = t.c1

  ) scalar_cnt

FROM t

;

Note: The scalar sub-query in the view definition.

4.      Grant user A only the SELECT privilege on view HR.VIEW_T.

5.      Grant the user HR (the owner of view) EXEMPT ACCESS POLICY system privilege.

Demonstration:

·         Truncate AUD$, and verify by selecting count from DBA_AUDIT_TRAIL.

SQL> connect /  as sysdba;

Connected.

SQL> truncate aud$;

truncate aud$

            *

ERROR at line 1:

ORA-03290: Invalid truncate command - missing CLUSTER or TABLE keyword

 

SQL> truncate table aud$;

Table truncated.

 

SQL> select count(*) from dba_audit_trail;

         0

 

SQL>

·         Check DBA_SYS_PRIVS for access for user HR and user A.

SQL> select * from dba_sys_privs;

 

SQL>

·         Grant the EXEMPT ACCESS POLICY privilege to HR user. Grant SELECT on view HR.VIEW_T to user A.

SQL> grant exempt access policy to hr;

 

SQL> connect hr/hr;

Connected.

SQL> grant select on view_t to a;

 

Grant succeeded.

 

SQL>

·         Connect as user A and select from HR.VIEW_T. Also note that policy is by-passed for user A.

SQL> connect a/a;

Connected.

SQL> select count(*) from hr.view_t;

 

        10

 

SQL> /

 

        10

 

SQL> /

 

        10

 

SQL> connect / as sysdba;

Connected.

SQL> select count(*) from dba_audit_trail;

 

         6

 

SQL> connect a/a;

Connected.

SQL> select * from hr.view_t;

 

         1 AAAA                          1

         2 AAAA                          1

         3 AAAA                          1

         4 AAAA                          1

         5 AAAA                          1

         6 AAAA                          1

         7 AAAA                          1

         8 AAAA                          1

         9 AAAA                          1

        10 AAAA                          1

 

10 rows selected.

 

SQL> connect / as sysdba;

Connected.

SQL> select count(*) from dba_audit_trail;

 

         9

 

SQL>

·         Note that there are 9 audit records generated, although only FOUR selects have been performed over two sessions.

·         These records are generated due to BY ACCESS option and not BY SESSION.

Depending on the usage of the view in an application, this could result in excessive audit record generation. 

Also, suppose there is a PL/SQL function defined that accepts key of table as input parameter and performs a KEYED retrieval of data from table. Such a function can easily (and often) used in views a scalar sub-queries. In such implementation each select executed will be treated as AN ACCESS to the table and audit record will be generated.

 

It is important to monitor the content of DBA_AUDIT_TRAIL view.

 

More explorations next week. Till then BYE!