Simpler Navigation for Servers and Operating Systems
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

create trigger on logon between hours

Ratzie
Super Advisor

create trigger on logon between hours

Has anyone done this...

I want to create a trigger that will only allow the user to log into the database between 12 and 4 am.
My next question is can I assign this trigger to the role that the user has been assigned to?

I have pulled some similar code that I would like to use but do not know how to modify it to be either assigned to a user: npq or to the role ntq_ro.

I do not know what the declare dba number means...

create or replace trigger logon_time after logon on database
declare dba number;
begin
if to_char(sysdate,'HH24') between 4 and 24
then
raise_application_error(-20001,'Not allowed to logon database during this time');
end if;
end
/

3 REPLIES
Suraj K Sankari
Honored Contributor

Re: create trigger on logon between hours

Hi,
I dont know more about "trigger"
I think you can set these kind of things in database itself.

If you are talking about unix login then yes we can create a script and put into the users .profile there we can restricted this kind of things.

Suraj
Michal Kapalka (mikap)
Honored Contributor

Re: create trigger on logon between hours

hi,

from security reason it should be set on the DB level.

mikap
Jean-Luc Oudart
Honored Contributor

Re: create trigger on logon between hours

Hi

declare dba number; => you declare a variable (but it's not used in your code!)

If I understand you want to check the user's role. you need to add a few lines of code and check the table user_role_privs.

The user will be able to connect at 23:59 !

Regards
Jean-Luc
fiat lux