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 a 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 more requirements:
- 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.
Michiel Arentsen, Rhenus