mbevilacqua
asked on
capture and divert Oracle DDL
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)
The DDL would include drops and creates of all types of objects (tables, indexes, triggers, sequences, permissions)
momi_sabag
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
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/triggers.htm#i1007895
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
To identify the application use SYS_CONTEXT function:
http://www.psoug.org/reference/sys_context.html
http://www.psoug.org/reference/sys_context.html
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
http:#24903387
http:#24903438
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
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
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
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
>> 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
agreed. objection, and reasking to close with split
a3
a3
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.
ASKER
Sorry for delay award. Thank you all for your assistance
I am as usually highly surprised by mrjoltcola intervention in a question he does not partricipates!!!
I consider intervention == participation and do enjoy living in a country where I have such freedom.
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.