QUESTION: raise_application_error results in ORA-00604...?
BD
I've seen a few postings on this, but am not entirely clear if there's
anything I can do about it.
I have created a trigger which will prevent logon under certain
specific conditions.
The direct means of preventing logon is being done via a
raise_application_error (-20001, "Sorry - access denied") kind of
scenario.
What I'd hoped for was that the user would only see the "ORA-20001:
Sorry - access denied" error, but as well, they see
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Sorry, access denied
ORA-06512: at line 19
It's not a huge deal if they see the 00604 and the 06512, but my
preference would be that they don't. Anyone know of a way to limit the
error messages that the user sees? Maybe something I can do with
serveroutput??
Thanks!!
BD
Mark D Powell
The Oracle provided routine raise_application_error cannot be used to
terminate a session logon on the same version that database event
triggers were introduced on, but can be used successfully in the second
or third release after database event triggers were introduced.
I do not think I get the 00604 error message on our EE 9.2.0.6 system,
but I will not be able to test until tomorrow, time allowing.
HTH -- Mark D Powell --
BD
Sorry, I do normally post that, but thought this was a generic enough
question not to...
Enterprise, 8.1.7.4, on AIX 5.2.0.2.
;-)
Sybrand Bakker
IIRC a simple exception section with a single dbms_output.put_line
message is sufficient.
The trigger *has* to fail or you won't be disconnected.
--
Sybrand Bakker, Senior Oracle DBA
BD
Hmmm... doesn't seem to be working.
If I put in a raise_application_error (-20001) as the exception action,
it rejects the logon, but returns the ORA-00604 as well. If I use a
dbms_output.put_line, it does nothing. I know the exception is being
raised because I also tested it with an insert into an audit table as
the exception action, and the insert was performed.
So the dbms_output.put_line on its own doesn't seem to do it.
No big deal; it works - it'd just be nice if I didn't see that 00604.
Here's my code, fwiw.
create or replace trigger browse_disconnect
after logon on database
declare
v_username varchar2 (30);
v_osuser varchar2 (30);
v_servername varchar2 (64);
v_program varchar2 (48);
v_terminal varchar2 (30);
invalid_logon EXCEPTION;
begin
execute immediate 'alter session set nls_date_format=''YYYY-MM-DD
hh:mi:ss'' ';
select upper (host_name) into v_servername from ctbdba.v_host_name;
-- this is a view which selects host_name from v$instance
select user into v_username from dual;
select upper (program) into v_program from v$session where audsid =
userenv('sessionid');
select upper (osuser) into v_osuser from v$session where audsid =
userenv('sessionid');
select upper (terminal) into v_terminal from v$session where audsid
= userenv('sessionid');
if upper (v_servername) = 'PROD_SERVER' and
upper (v_username) = 'BROWSE_ID' and
upper (v_osuser) in ('VALID_ID_1, 'VALID_ID_2') then
insert into ctbdba.logon_audit values (v_osuser, v_username,
v_terminal, v_program, sysdate);
commit;
raise invalid_logon;
end if;
EXCEPTION
WHEN invalid_logon then
dbms_output.put_line ('GO AWAY'); -- doesn't seem to have much
effect, even though the exception is being raised.
end browse_disconnect;
/
BD
"upper (v_osuser) in ('VALID_ID_1, 'VALID_ID_2')" should be
"upper (v_osuser) NOT in ('VALID_ID_1, 'VALID_ID_2')"
I was in troubleshooting mode.
;-)
Sybrand Bakker
1 Trigger code is parsed for every call. Replace this by a stored
procedure, and you loose all of the parsing
2 Many of your selects from v$session can and should be replaced by
calling the sys_context function
3 audsid is always 0 for user SYS and any user with SYSDBA. Your code
is going to fail, as all SYS users have the same audsid, 0
4 If you are going to reject users, the execute immediate should be
the last statement before logging in.
5 Currently you don't allow sys to connect. That is going to be a
problem.
alek
course, SYS) do not trigger the after logon trigger therefore the SYS
user, in this case, is not an issue.
alec.
BD
prevent the BROWSE_ID from logging on under the specific circumstances.
The code grabs USER from DUAL. In the case of sys, USER from DUAL is
'SYS', not BROWSE_ID.
>2 Many of your selects from v$session can and should be replaced by calling the sys_context function
An earlier iteration of this trigger did use sys_context, but a
co-worker recommended v$session. Can you give me a sense as to why
sys_context is better than v$session?
>3 audsid is always 0 for user SYS and any user with SYSDBA. Your code is going to fail, as all SYS users have the same audsid, 0
I'll have to look closer at that - I'm not yet quite sure what you're
telling me, but I'll look into it.
Thanks!!
vc
The error you are seeing is caused not by your RAISE statement, but
rather by 'commit' which is illegal in the trigger. So you are just
lucky that it 'works', sort of.
If you get rid of 'commit', your trigger still won't work because you
intercept the exception you've just raised, and the login won't be
denied. So you need to get rid both of the commit and the EXCEPTION
clause.
BD
umm... I have to disagree, simply based on the evidence. I removed the
commit and I still get the 00604.
As well, the 06512 error points to the line in the trigger that calls
raise_application_error. I have no indication that the commit is
contributing to the additional error messages at all.
vc
BD wrote:
> >You need to get rid both of the commit and the EXCEPTION clause.
>
> umm... I have to disagree, simply based on the evidence. I removed the
> commit and I still get the 00604.
It's not a matter of opinion but rather of fact especially if you
bother to read the relevant Oracle manual.
When you remove the commit, your error would be coming from raise,
sort of obvious, no ?
>
> As well, the 06512 error points to the line in the trigger that calls
> raise_application_error. I have no indication that the commit is
> contributing to the additional error messages at all.
See above.
BD
I guess to those who already know such stuff as well as you clearly do,
many things are obvious. I am still climbing the learning curve, so
notably fewer things are obvious to me.
Mark D Powell
sys_context call because back when we were on 8.1.7 the direct select
was faster than the use of sys_context.
You should test the call. In a database logon trigger even small 1/100
of a second advantages are important when you consider how many total
logon per day take place.
IMHO -- Mark D Powell --
BD
>of a second advantages are important when you consider how many total
>logon per day take place.
Oh, I quite agree. And thanks for that, because it prompts another
thought:
Given that it's only this 'browse_id' schema that is at issue here, I
wonder if I should create a schema-specific logon trigger instead. It
would certainly reduce the number of times the trigger fires, and given
that the number of people who use this specific ID numbers
approximately a dozen, it might be wiser than a global database
trigger. A full-on database-wide logon trigger does kind of seem
wasteful in this case.
Regardless. Yes, testing is always a smart thing to do. ;-))
Cheers,
BD.
Sybrand Bakker
>>5 Currently you don't allow sys to connect. That is going to be a problem.
>Sys is able to connect (I just tried). The code is intended to only
>prevent the BROWSE_ID from logging on under the specific circumstances.
>The code grabs USER from DUAL. In the case of sys, USER from DUAL is
>'SYS', not BROWSE_ID.
>
>>2 Many of your selects from v$session can and should be replaced by calling the sys_context function
>An earlier iteration of this trigger did use sys_context, but a
>co-worker recommended v$session. Can you give me a sense as to why
>sys_context is better than v$session?
>
My concern would be that it would avoid explicit grants to the
v_$session view, and it would avoid an explicit select.
>>3 audsid is always 0 for user SYS and any user with SYSDBA. Your code is going to fail, as all SYS users have the same audsid, 0
>I'll have to look closer at that - I'm not yet quite sure what you're
>telling me, but I'll look into it.
>
Your select into's should end into exception as they return more than
one row. SYS is always connected (remember the background sessions) so
there will be always multiple sessions with audsid=0
You would need to use v$mystat. (... and sid = (select sid from
v$mystat) )
Yet another reason to avoid the select
>Thanks!!