Problem in database trigger [message #353476] |
Tue, 14 October 2008 00:44 |
manudu20
Messages: 45 Registered: August 2008 Location: Mumbai
|
Member |
|
|
Hi All , Can anybody tell me why this is not working , I am trying to stop export and import in my server throgh command prompt(exp,imp). this trigger is not executing when we connect user in exp command through command prompt.
create or replace trigger prevent_exp_imp
after logon on database
begin
if user='TEST' and upper(sys_context('userenv','module')) in ('EXP.EXE','IMP.EXE') then
raise_application_error (-20001, 'You are not allowed to export or import data.');
end if;
end;
pls if anybody know the correct ans please tell me.
|
|
|
|
|
|
Re: Problem in database trigger [message #353487 is a reply to message #353476] |
Tue, 14 October 2008 00:51 |
manudu20
Messages: 45 Registered: August 2008 Location: Mumbai
|
Member |
|
|
When i run this command in toad
select upper(sys_context('userenv','module')) from dual
It returns toad (version)
When i run this command in sqlplus
select upper(sys_context('userenv','module')) from dual
It returns sql*plus
then it has to return exp.exe in command prompt
|
|
|
|
Re: Problem in database trigger [message #353491 is a reply to message #353489] |
Tue, 14 October 2008 00:58 |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Did you never learn to debug code? There are several ways to _make sure_ instead of guess what a value of a certain variable is at runtime.
Some possibilities: raise an error unconditionally, with the value recognizable in the error message (not advisable on databases you share with others), autonomous transaction procedure that writes to a log-table, etc.
|
|
|
Re: Problem in database trigger [message #353492 is a reply to message #353487] |
Tue, 14 October 2008 00:59 |
|
Michel Cadot
Messages: 68711 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
manudu20 wrote on Tue, 14 October 2008 07:51 | When i run this command in toad
select upper(sys_context('userenv','module')) from dual
It returns toad (version)
When i run this command in sqlplus
select upper(sys_context('userenv','module')) from dual
It returns sql*plus
then it has to return exp.exe in command prompt
|
WRONG!
Is "toad" "TOAD.EXE"?
Is "SQL*Plus" "SQLPLUS.EXE"?
Regards
Michel
[Updated on: Tue, 14 October 2008 01:00] Report message to a moderator
|
|
|
|
|
|
|
Re: Problem in database trigger [message #353501 is a reply to message #353497] |
Tue, 14 October 2008 01:04 |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
manudu20 wrote on Tue, 14 October 2008 08:01 | can u help me on this topic and about debugging code?
|
Sure, if you can show us what you tried. For example, did you Google? There are some good search words in my text. (debug autonomous Oracle) would be a nice set..
|
|
|
Re: Problem in database trigger [message #353502 is a reply to message #353498] |
Tue, 14 October 2008 01:06 |
manudu20
Messages: 45 Registered: August 2008 Location: Mumbai
|
Member |
|
|
1.I have a duser "edde/edde" In databse named "Orcl"
2.I had created this trigger after login on "edde"
create or replace trigger prevent_exp_imp
after logon on database
begin
if user='edde' and upper(sys_context('userenv','module')) in ('EXP.EXE','IMP.EXE') then
raise_application_error (-20001, 'You are not allowed to export or import data.');
end if;
end;
/
3. but when i connect user edde in exp command prompt this trigger would not fire can u tell me the reason why so?
|
|
|
|
|
Re: Problem in database trigger [message #353512 is a reply to message #353507] |
Tue, 14 October 2008 01:27 |
_jum
Messages: 577 Registered: February 2008
|
Senior Member |
|
|
this summary should help, but the hints where already given:
1. start an exp without giving a tablename, it waits for Your "debugging"
2. start a TOAD / SQLplus and test the result of
SELECT osuser, machine, program, module FROM v$session
3. correct Your trigger, could help to use UPPER() / LOWER() - function
|
|
|
|
|
Re: Problem in database trigger [message #353523 is a reply to message #353521] |
Tue, 14 October 2008 01:56 |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Michel Cadot wrote on Tue, 14 October 2008 08:53 | manudu20 wrote on Tue, 14 October 2008 08:30 | but how can we know the current session.
|
By checking all sessions from EDGE, isn't it obvious?
Regards
Michel
|
EDDE, not EDGE.
The db-user is edde, according to one of the previous messages
|
|
|
|
|
|
|
|
Re: Problem in database trigger [message #353762 is a reply to message #353759] |
Wed, 15 October 2008 00:52 |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
The post where your Caps-Lock key was stuck. Have the courtesy to correct that before you blindly hit Submit.
Posting in capitals is considered shouting in netiquette.
As for your question: we can't see where you failed if you don't post your code.
|
|
|
|
Re: Problem in database trigger [message #353769 is a reply to message #353759] |
Wed, 15 October 2008 01:12 |
|
Michel Cadot
Messages: 68711 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Quote: | Can you help me to write trigger "To stop export /import/ Toad " In Server?
|
Although we cannot say anything about your code until you post it you have to know that if someone really wants to use these tools you can't prevent from this with a trigger as he can rename the program as he wants.
Regards
Michel
|
|
|