Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: August 30, 2023 - 6:56 am UTC

Last updated: August 31, 2023 - 1:41 am UTC

Version: Oracle Database version 11.2.0.4

Viewed 1000+ times

You Asked

Hi Tom,

Is there a way we can lock users on a particular date?


Your support is much appreciated.

Regards,
Ajay

and Connor said...

A couple of relatively simple options

1) scheduler job

procedure lock_all
begin
for i in ( select username from dba_users where ... ) loop
  execute immediate 'alter user '||i.username||' account lock';
end loop;
end;


and then schedule that with dbms_scheduler

2) logon trigger

create or replace
trigger TRACE_ALL_LOGINS
after logon on database
begin
  if user in ( .... ) and sysdate > date '2024-01-01' then
     raise_application_error(-20000,'Sorry your time is up');
  end if;
end;



We're not taking comments currently, so please try again later if you want to add a comment.

More to Explore

APEX

Keep your APEX skills fresh by attending their regular Office Hours sessions.