Database improvements
Introduce Hibernate
Remove stored procedures
* Eliminating Stored Procedures
Add foreign key constraints
...
Remove unused database tables and other objects - Adding new database logging mechanism
We have to see in application which tables in database are obsolete. One of the possible solutions is to see every table one-by-one where it occurs in the application and in the stored procedures. This will be a huge and time consuming task, so we will have to use the other approach.
Because we currently use an Oracle database, we can use an Oracle-specific approach in order to solve this problem. Oracle vron version 9i and above has implemented package DBMS_FGA. More about this package can be found in Oracle documentation DBMS_FGA description. We will use this package in order to achieve our goal.
Briefly, via few procedures from this package, we can watch all actions performed over all database objects. In this case, we want to examine actions performed over tables. This package works very similar to triggers, but we can see all queries executed over a table, even select queries with all parameters. Queries from stored procedures are available, too, with all bind variables and its values. We can use this "tool", not only for this purpose, but also to see which exactly queries are performed over some table for certain action.
Here are steps you need to perform:
- Grant PNET user appropriate privilegies
DBMS_FGA package belongs to system user SYS, so PNET user can not use it unless SYS user allows that. So SYS user must grant execution privilegies for this package to PNET user. This action should be done in following way:
SQL> conn sys/change_on_install@pnet as sysdba Connected. SQL> grant execute on dbms_fga to pnet 2 / Grant succeeded.
So as you can see you should connect as SYS user and execute appropriate GRANT command.
- Create log table and appropriate stored procedure
We need to create table where we will place our informations about SQL queries. One table will be enough. This should be done with the following SQL code and as PNET user, NOT SYS user as above:
create table audit_log ( whodidit varchar2(40) , whenwasit timestamp , sql_executed varchar2(4000) ) /
In this table will be placed all important informations about SQL query performed. Now we need to create stored procedure which will handle all events over tables and store informations in this table. This also must be done as PNET user with folloving code:
create or replace
package AUDIT_HANDLER
is
PROCEDURE HANDLE_TABLE_ACCESS
( object_schema VARCHAR2
, object_name VARCHAR2
, policy_name VARCHAR2
);
end;
/
create or replace
package body AUDIT_HANDLER
is
PROCEDURE HANDLE_TABLE_ACCESS
( object_schema VARCHAR2
, object_name VARCHAR2
, policy_name VARCHAR2
) is
PRAGMA AUTONOMOUS_TRANSACTION;
begin
insert into audit_log
( whodidit, whenwasit, sql_executed)
values
( user, systimestamp, 'SQLTEXT: ' || SYS_CONTEXT('USERENV', 'CURRENT_SQL' ) ||' SQLBIND: ' || SYS_CONTEXT('USERENV', 'CURRENT_BIND'))
;
commit;
end HANDLE_TABLE_ACCESS;
end;
/
Here is explanation of code in this stored procedure. Procedure have few parameters important for SYS_CONTEXT function. You will not call this procedure directly, Oracle will do that itself and this will be explained latter. Function SYS_CONTEXT is very powerfull, more informations can be found in Oracle documentation SYS_CONTEXT info. This function from parameters we have in stored procedure can extract all information about SQL query. Parameters which are important for us are CURRENT_SQL and CURRENT_BIND where are placed current SQL performed over some table and all bind variable values for that query. For example, here you can see query with bind variables like this:
SQLTEXT: SELECT * FROM QRTZ_CRON_TRIGGERS WHERE TRIGGER_NAME = :1 AND TRIGGER_GROUP = :2 SQLBIND: #1(17):HourlyJobsTrigger #2(7):DEFAULT
Here you can see exact values for all bind variables in this query. This is also very useful for prepared statement calls from Java where all input parameters are not logged into log file. Now we created all database objects we need for this kind of logging.
- Dynamically create scripts
We need to execute ADD_POLICY procedure from DBMS_FGA package. This procedure creates unique policy for certain database object (table in this case). Here is one simple example how we can add policy to some database object:
begin dbms_fga.add_policy ( object_schema=>'PNET' , object_name=>'ZZ_SQLN_EXPLAIN_PLAN' , policy_name=>'ZZ_SQLN_EXPLAIN_PLAN_ACCESS' , audit_column => null , audit_condition => null , handler_schema => 'PNET' , handler_module => 'AUDIT_HANDLER.HANDLE_TABLE_ACCESS' ); end; /
Explanation and syntax for this package and its procedures can be found in link specified above so here we will not explain into great detail that. All specified parameters in procedure are self explanatory. This SQL we need to create for all objects we want to monitor. We can do this dynamicly in the following way:
set echo off set heading off set verify off set feedback off spool c:\add_policies.sql select 'BEGIN'||chr(10)||'DBMS_FGA.ADD_POLICY'||chr(10)||'( object_schema=>'||chr(39)||'PNET'||chr(39)||chr(10)||', object_name=>'||chr(39)||table_name||chr(39)||chr(10)||', policy_name=>'||chr(39)||table_name||chr(39)||chr(10)||', audit_column => null'||chr(10)||', audit_condition => null'||chr(10)||', handler_schema => '||chr(39)||'PNET'||chr(39)||chr(10)||', handler_module => '||chr(39)||'AUDIT_HANDLER.HANDLE_TABLE_ACCESS'||chr(39)||chr(10)||');'||chr(10)||'END;'||chr(10)||'/'||chr(10) from all_tables where owner='PNET' order by table_name / spool off set echo on set heading on set verify on set feedback on
Previous query created this SQL code, place it in add_policies.sql SQL script in root of drive C. This can be modified. Above script creates this code for all tables that belongs to PNET user, so you will have complete script with policies for all tables.
When you add policy on some table that policy will be active until you disable it or drop it so in a similar way you can create SQL script for dropping all policies:
set echo off set heading off set verify off set feedback off spool c:\drop_policies.sql select 'BEGIN'||chr(10)||'DBMS_FGA.DROP_POLICY'||chr(10)||'( object_schema=>'||chr(39)||'PNET'||chr(39)||chr(10)||', object_name=>'||chr(39)||table_name||chr(39)||chr(10)||', policy_name=>'||chr(39)||table_name||chr(39)||chr(10)||');'||chr(10)||'END;'||chr(10)||'/'||chr(10) from all_tables where owner='PNET' order by table_name / spool off set echo on set heading on set verify on set feedback on
When you create those SQL scripts it is important to edit them and delete in the beginning and in the end of file some input from SQL console you may not want to be there, because the command "spool file_name" will probably be present at the very beginning of the file. You need to delete this and avoid possible errors that may occur because of this. In the similar way you can create all SQL scripts for ENABLE_POLICY/DISABLE_POLICY procedures if you want to use them. You probably wonder what CHR(10) and CHR(39) represents. CHR is function which returns one character from character table for specified character number. CHR(10) represents new line delimiter sign and CHR(39) represents apostrophe (single quotation mark). Those two scripts you need to execute as PNET user too because we use ALL_TABLES table (we could use USER_TABLES too).
When you create thos two SQL script files (add_policies.sql and drop_policies.sql) you need to execute the one you need, if you want to log all SQL queries in you database then you need to call add_policies.sql script, if you want to stop logging then you should call drop_policies.sql script. For this purpose it will be more appropriate to create script which will call DISABLE_POLICY procedure from DBMS_FGA package. But it is up to developers choice.
