capture and divert Oracle DDL

mbevilacqua
mbevilacqua used Ask the Experts™
on
We are running a product that creates DDL on the fly based on design-time decisions. I need a means of capturing the DDL and preventing it from executing. I dont want to turn off the permission, I want to capture the statements and run that at a later date after review.

The DDL would include drops and creates of all types of objects (tables, indexes, triggers, sequences, permissions)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
the best approach i can think of is turn off the permissions and have some product that knows how to analyze the oracle network protocol and extract the statements from there

Commented:
I don't know of any way to keep the spl from running short of disablling the permissions at least temporarily.
Is it that you want to be able to capure the sql and run it later with the same user id?
If so, you might try granting permissions to the user id in a role, making it a non-default role.
Then create a logon trigger on that user id to enable the role if you login in with something other than that tool to run the sql.
This handles the 'diabling sql' part but allows you to run sql in a tolol pother than the one you are wanting to capture.

Now , to capture the sql, you will need to use something on the client side.
What does your tool use for client connectivity definition?  Sqlnet, ODBC,JDBC?
Most of those have trace settings that will allow you to capture sql being sent whether they execute or not.   I used to do this a lot with ODBC and UDB or Oracle connections.
johnsoneSenior Oracle DBA
Commented:
You could create a ddl trigger to capture the ddl statement.  There are restrictions on the trigger firing, so I suggest you check the documentation.

http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/create_trigger.htm#LNPLS01374

I'm not exactly sure how you would get the statement, as we use them primarily to prevent operations.  If you can figure out the statement, then you could use an autonomous transaction to store it in a table and then have the trigger raise an error to prevent it from happening at that time.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

johnsoneSenior Oracle DBA

Commented:
You may be able to recreate the statment using the event attributes:

http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/triggers.htm#i1007895
johnsoneSenior Oracle DBA
Commented:
Looking into it further, look at ora_sql_txt.  There is an example there of how to retrieve the sql statement that fired the ddl trigger.
One approach to this is to set up 2 instances connected by Oracle Streams, and allow the DDL changes in a staging environment where they are reviewed and only then allow them to propagate to production via streams.  Streams rules may be set to limit this to specific schemas and object types if needed.
Top Expert 2008
Commented:
I got such assignment in the past.
It was; Allow DDLs from some machines only.
CREATE OR REPLACE TRIGGER restrict_ddl
BEFORE ddl ON DATABASE
DECLARE
   machinename VARCHAR2(64);
   message     VARCHAR2(150) := 'You have no rights to execute DDL statements on DEVELOPBETA! Call the DBA! ';
BEGIN
   SELECT SYS_CONTEXT ('USERENV', 'host') INTO machinename FROM DUAL;
   IF machinename IS NULL then
   message := message || '   ' || 'NULL';
   else
   message := message || '   ' || machinename;
   end if;
  IF    INSTR(lower(machinename),'bg\shvertner',1) <> 0
     OR INSTR(lower(machinename),'bg\fed',1) <> 0
     OR INSTR(lower(machinename),'bg\plamen',1) <> 0
     OR INSTR(lower(machinename),'developbeta',1) <> 0
     OR INSTR(lower(machinename),'oralin1',1) <> 0
     OR machinename IS NULL
THEN
       null;
  ELSE
     RAISE_APPLICATION_ERROR (num => -20000,msg => message);
  END IF;
END;
/ 
 
 
 
DROP TRIGGER restrict_ddl;

Open in new window

Top Expert 2008

Commented:
To identify the application use SYS_CONTEXT function:
http://www.psoug.org/reference/sys_context.html
Top Expert 2009

Commented:
johsone's comments should to be included as well since the question was regarding not just catching the event, but capturing the DDL text.

http:#24903387
http:#24903438
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
mrjoltcola,

 the question asked for also how to prevent the sql to run.
 the following line from schwertner's comment:
 
     RAISE_APPLICATION_ERROR (num => -20000,msg => message);

 is the key for that.
 just giving a general link to the CREATE TRIGGER syntax of oracle page is not enough, compared to the full code example.

a3
Top Expert 2009

Commented:
The question also asks: "I want to capture the statements and run that at a later date after review."

>> just giving a general link to the CREATE TRIGGER syntax of oracle page is not enough

Neither does the trigger by itself. johnsone pointed out to use ora_sql_txt() which is the appropriate piece of the pie that is missing and should be included in the solution.

mjc



Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
agreed. objection, and reasking to close with split
a3
Top Expert 2009

Commented:
Sorry, to clarify, I should not have suggested both johsone's posts. Was thinking his link pointed to ora_sql_text() specifically, but it does not. I agree schwertner needs to get the majority of the points but I think http:#24903438 is also the other important post.

Author

Commented:
Sorry for delay award. Thank you all for your assistance
Top Expert 2008

Commented:
I am as usually highly surprised by mrjoltcola intervention in a question he does not partricipates!!!
Top Expert 2009

Commented:
I consider intervention == participation and do enjoy living in a country where I have such freedom.
Top Expert 2009

Commented:
In light of schwertner's comment, for what it is worth, I was subscribed to the question, so honestly did not realize I had not participated. I often will monitor a thread if it is interesting, whether I comment or not. I don't make a habit of Objection on other's behalf, but this question was interesting to me and has good PAQ value for the future reader.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial