Create trigger and raise application for Invalid login (merged) [message #247966] |
Wed, 27 June 2007 11:52  |
qasim845
Messages: 95 Registered: March 2007 Location: Philadelphia
|
Member |
|
|
Hi,
Is anybody share the code for trigger which can raise application of Access Denied, If any user is trying to login to the database from the table which is called "restricted_table". The "restricted_table" contain the user id's that are not allowed to login into the database. So, whenever the that user will login, raise application of access denied.
thanks in advance
qasim
|
|
|
|
|
Re: raise application for Invalid login [message #248017 is a reply to message #247986] |
Wed, 27 June 2007 15:39   |
andrew again
Messages: 2577 Registered: March 2000
|
Senior Member |
|
|
If you really need the login trigger thing - it would be similar to this untested code...
CREATE OR REPLACE TRIGGER dba.logon_restrict_trg
after logon on database
declare
v_cnt number;
begin
select count(*) into v_cnt from restricted_table where upper(restricted_user) = upper(ora_login_user);
if v_cnt > 0 then
raise_application_error (-20501, 'ERROR: Login using '||ora_login_user||' prohibited');
end if;
end;
/
As far as I remember, login triggers don't fire for users with DBA priv. (in all versions).
|
|
|
Create trigger [message #248049 is a reply to message #247966] |
Wed, 27 June 2007 19:59   |
qasim845
Messages: 95 Registered: March 2007 Location: Philadelphia
|
Member |
|
|
HI
I have a table which contain 2 columns one is operating system user id and second is schema. So now i need to create the trigger, if anybody will try to login the database.his/her osuserid and schema must match to my table osuserid and schema.if it does not match it show give raise application error.
Thanks in advance
|
|
|
|
|
|
|
|