How to grant temporary access to an application-user?

First, a brief explanation of the terms of Oracle “user” and “schema”. It is like in Unix where you have a “user”, “login”, an “account” which is similar to the Oracle “user”, and you have a “home” (a directory for your files). Similar, you have a “schema”

You have a production application schema APP1 that owns the objects, but normally no users will login as this application schema. But now, a developer needs temporary access. The developer requested a 2-hour maintenance window, and this was approved by the application manager.

We as DBAs can unlock the account, give the developer the password, wait 2 hours, then lock the password and kill all sessions of the application user.

It would be easier that Oracle will do that for us. Isn’t it?

My approach is to use a logon trigger creating a scheduler job to kill the session.

  • Check if it is a logon to APP1
  • Check if there is already a scheduler job to kill the sessions of APP1
  • If not, it is the 1st login and we create the job

The job will do:

  • lock the account
  • kill the sessions of APP1

In this example, I will use the SYSTEM user. It is also possible to use another user with the required privileges, e.g. “ADMINISTER DATABASE TRIGGER“.

For the pl/sql part, we need a direct grant to the SYSTEM user:

-- as sysdba:
grant select on v_$session to system;
grant create job to system;
grant alter user to system;

To be flexible I will create a table with the users and timeouts

create table USER_TIMEOUT (
  USERNAME varchar2(30) not null,
  TIMEOUT number not null
);
alter table USER_TIMEOUT
  add constraint USER_TIMEOUT_PD 
  primary key ( USERNAME ) enable;

First, create the procedure to lock the user and kill all existin sessions of it.

create or replace procedure EXPIRE_USER (v_username in varchar2) as
  v_cnt number default 0;
  v_error_msg VARCHAR2(4000);
begin
  execute immediate 'alter user '|| V_USERNAME ||' ACCOUNT LOCK';
  for s in (select SID, SERIAL# from V$SESSION where USERNAME = v_username) loop
    begin
      execute immediate 'alter system kill session ''' || s.SID || ',' || s.SERIAL# || ''' immediate';
    exception
      when others then null;  -- session already ended?
    end;
  
  end loop;

end;
/

This procedure will be called by the job submitted by the logon-trigger

create or replace NONEDITIONABLE TRIGGER app1_logon_timer
AFTER LOGON ON DATABASE
DECLARE
  v_cnt NUMBER;
  v_timeout number;
  v_error_msg VARCHAR2(4000);
BEGIN


    select timeout into v_timeout from user_timeout where username=USER; 
    -- NO_DATA_FOUND is handled by WHEN OTHERS...
    
    select count(*) into v_cnt from user_scheduler_jobs
    where job_name = 'EXPIRE_JOB_'||USER;
    if v_cnt = 0 then    
      DBMS_SCHEDULER.CREATE_JOB(
        job_name   => 'EXPIRE_JOB_'||USER,
        job_type   => 'STORED_PROCEDURE',
        job_action => 'EXPIRE_USER',
        number_of_arguments => 1,
        start_date => SYSTIMESTAMP + NUMTODSINTERVAL(v_timeout, 'MINUTE'),
        enabled    => FALSE,
        auto_drop  => TRUE,
        comments   => 'Kills '||USER||' sessions and locks account '||v_timeout||' hour after first login');   
        
      DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
        job_name          => 'EXPIRE_JOB_'||USER,
        argument_position => 1,
        argument_value    => USER
      );

      DBMS_SCHEDULER.ENABLE('EXPIRE_JOB_'||USER);

  end if;
EXCEPTION
  WHEN OTHERS THEN NULL;  -- never block logons if this fails

END;
/

And for your convenience, a small pl/sql procedure to unlock a user and to set a timeout. There is no errorhandling. It is up to you to add it or to enter correct data.

create or replace NONEDITIONABLE PROCEDURE set_user_timeout (
  p_username IN VARCHAR2,
  p_timeout  IN NUMBER
) AUTHID DEFINER
AS
BEGIN
  EXECUTE IMMEDIATE 'ALTER USER ' || p_username || ' ACCOUNT UNLOCK';

  MERGE INTO user_timeout t
  USING ( SELECT p_username AS username, p_timeout AS timeout    FROM dual) s
  ON (t.username = s.username)
  WHEN MATCHED THEN
    UPDATE SET t.timeout = s.timeout
  WHEN NOT MATCHED THEN
    INSERT (username, timeout) VALUES (s.username, s.timeout);
  COMMIT;
END;
/

All the above PL/SQL code is tested in my laboratory environment. Before using the code in a production environment, test it thoroughly to make sure it runs correctly in your environment. I cannot guarantee this. And of course, you can adapt the code to suit your needs.

Views: 1
Thumbnail [60x60]
by
Martin Bracher