Saturday, November 19, 2016

Automatically log in to Gitora when connecting to Oracle

In order to modify database objects that are managed by Gitora source control, the user first needs to log in to Gitora with a user name and password.
Usually this is no problem, but we lately encountered a case where this was an problem.

To be more specific, we still use Oracle Designer (yes, i know..) to maintain our Database Models. From Designer we generate database changes to our development database that is managed by Gitora.
Almost all changes from Designer can be generated as scripts and you can add the Gitora login there,
but we also generate TAPI packages from Designer and these are generated directly to the database without the option of generating scripts. So that was a problem.
Also having to manually add the Gitora login to all scripts is quite tedious.

The solution is to use a database after logon trigger.
But we had some more requirement:

  • log in to Gitora as an actual user, not a database user
  • track what applications are used to log on
  • log succesfull and failed login attemps

Here is how we solved it.

First we need a table to match the Gitora username and passwords with the possible Operating System user names.
(I will not secure the passwords in this example, i leave that up to you if you need it)

create table GITORA_USERS_MATCH
(
  os_user     VARCHAR2(64) not null,
  gitora_user VARCHAR2(64) not null,
  gitora_pw   VARCHAR2(64) not null
);

alter table GITORA_USERS_MATCH
  add constraint GUH_PK primary key (OS_USER);

Then a second table to log successfull and failed login attempts.

create table GITORA_LOGON_TRG_LOG
(
  logon_message VARCHAR2(512) not null,
  logon_date    DATE not null
);

Now we can create a trigger.

create or replace trigger gitora_auto_logon_trg
   after logon on database
declare
   l_program     varchar2(100);
   l_gitora_user gitora_users_match.gitora_user%type;
   l_gitora_pw   gitora_users_match.gitora_pw%type;

   cursor c_gitora_user
   is
      select guh.gitora_user
      ,      guh.gitora_pw
      from tmi_sys.gitora_users_match guh
      where upper(guh.os_user) = upper(sys_context('userenv','OS_USER'))
      ;

begin
   /* Get the program that is connecting.
      For example:
     'dwfde61.exe'      -- Oracle Designer Generator
     'sqlplusw.exe'     -- Oracle sqlplus
     'sqlplus.exe'      -- Oracle sqlplus
     'SQL Developer'    -- Oracle SQL developer
     'Toad.exe'         -- Toad
     'plsqldev.exe'     -- PL/SQL Developer
     'JDBC Thin Client' 
   */
   select program
   into   l_program
   from   v$session
   where  audsid=sys_context('USERENV','SESSIONID');
   
   insert into tmi_sys.gitora_logon_trg_log 
      (logon_date
      ,logon_message
      ) 
   values 
      (sysdate
      ,'logon: os_user: '''||sys_context('userenv','OS_USER')||''' program: '||l_program
      );

   open  c_gitora_user;
   fetch c_gitora_user
   into  l_gitora_user
   ,     l_gitora_pw;
   close c_gitora_user;

   if l_gitora_user is null
   then
      -- log failure to match os_usr to Gitora user
      insert into tmi_sys.gitora_logon_trg_log 
         (logon_date
         ,logon_message
         )
      values 
         (sysdate
         ,'os_user name '''||sys_context('userenv','OS_USER')||''' cannot be matched to a Gitora user)'
         );
   else
      -- try to log on to Gitora. 
      begin
         gitora.api_gitora.login(l_gitora_user,l_gitora_pw);
      exception
         when others
         then
            -- just log, no need to raise
            insert into tmi_sys.gitora_logon_trg_log 
               (logon_date
               ,logon_message
               ) 
            values 
               (sysdate
               ,'Login to Gitora failed for username '||l_gitora_user
               );
         end;
   end if;
exception
  when others
  then
     -- this trigger should never cause a user to not be able to log in to the database.
     -- It only needs to check if an os_name can be linked to a Gitora user.
     null; 
end; 

With this in place, you can connect to the database with any tool you want and log in to Gitora automatically. This makes working with Gitora much easier.

Have fun!

2 comments:

  1. Is your Gemini transaction time expired? Well, you don’t have to worry as if once the Gemini transaction created, it never expires but it may stuck due to high/low fee or due to traffic. Or else, you can reach Gemini support number for help and guidance related to the same. The skilled and talented customer experts are always at 24/7 continuous service. The support team will guide you in steps and also provide alternatives so that such errors don’t occur in future. Call customer care executive and resolve the queries at every point in time.

    ReplyDelete
  2. Are you facing error in selling coins in Bithumb account? Is the error creating another trouble for you or you need solutions as soon as possible to fix the error? If you don’t know how to deal with such issues and looking for solutions to deal with it, you can always take help from the professionals who’re there to support you. You have to call on Bithumb support which is functional and the best source to deal with queries in no time.

    ReplyDelete