Oledb provider (9.2) from VB 6

From: Fran?ois Bourdages <francois.bourdages_at_harfan.com>
Date: 18 Jan 2005 14:19:41 -0800
Message-ID: <92e05f1e.0501181419.3824f08d@posting.google.com>

Hi ,
Is there a way to pass the program name with the connect string, when a user log on to an application ussing this provider (from oracle)
within Visual basic ?

on the database , i want to have a "after logon on database" trigger, that will verify that the user who logs in use the good application. so the program field in v$session would be use., But how to fill it from
the connection in VB.. ?

Example of the trigger :
CREATE OR REPLACE TRIGGER logon_allow_trigger AFTER LOGON ON DATABASE
DECLARE
   CURSOR LOGON_ALLOWED (P_USERNAME VARCHAR2) IS

      SELECT LOGON_ALLOWED
      FROM   MSP.USER_ALLOWED_TEST
      WHERE USERNAME = P_USERNAME;

 CURSOR INFO_SESSION IS 
    	SELECT OSUSER, MACHINE , SID , SERIAL# , PROGRAM 
        FROM V$SESSION 
       WHERE AUDSID = USERENV( 'sessionid' );       
   --
   ENR_LOGON_ALLOWED	LOGON_ALLOWED%ROWTYPE; 
   ENR_INFO_SESSION	INFO_SESSION%ROWTYPE; 
   

BEGIN

• add others users here if you need IF ORA_LOGIN_USER !='SYSTEM' AND ORA_LOGIN_USER !='SYS' THEN OPEN LOGON_ALLOWED(ORA_LOGIN_USER); FETCH LOGON_ALLOWED INTO ENR_LOGON_ALLOWED; --
  • TABLE MSP.USER_ALLOWED_TEST must be filled with users that can
  • connect IF (ENR_LOGON_ALLOWED.LOGON_ALLOWED = 'NO' OR ENR_LOGON_ALLOWED.LOGON_ALLOWED IS NULL) THEN
    • probably not to do... : OR V_ALLOWED IS NULL if user not defined. CLOSE logon_allowed; -- RAISE_APPLICATION_ERROR (num => -20000, msg => 'Connexion impossible pour cet utilisateur Oracle.'); ELSE
    • here user can connect
  • verification if program name is authorise OPEN INFO_SESSION; FETCH INFO_SESSION INTO enr_INFO_SESSION; -- IF ENR_INFO_SESSION.PROGRAM = 'abcdefg.EXE' THEN
    • if so, connexion OK, audit.. INSERT INTO msp.logon_audit_table (username,logon_time, PROGRAM_NAME) VALUES (ORA_LOGIN_USER,SYSDATE,'V_ALLOWED='||ENR_LOGON_ALLOWED.LOGON_ALLOWED||' and ENR_INFO_SESSION.PROGRAM='||ENR_INFO_SESSION.PROGRAM); CLOSE INFO_SESSION; CLOSE LOGON_ALLOWED; ELSE CLOSE INFO_SESSION; CLOSE LOGON_ALLOWED; -- RAISE_APPLICATION_ERROR (num => -20001, msg => 'Connexion impossible pour ce PROGRAMME.');

		END IF;	   		
	   END IF;
	END IF;

END;
/
SHOW ERRORS the reason why I am asking this is that the application :

• is already develop,
• is using the schema owner to connect (don't tell me it's bad, i know...)
• one of our client don't whant that and it is ok for me,
• i knew about database role that give acces to objects for some task (or action, like select , insert, update)
  • we have where clause in the application that restrict data for user, depending of their role,
  • we want to find a another way to do this than using fine grain acces control and dbms_rls package, because it will be costly.

so my idea was to restrict acces to database instead of granting role to user, and instaling row level filtering via fine grain acces control.  

It is easy to restrict database acces to user, but how can i restrict all programm except THEONE for certain users ? Received on Tue Jan 18 2005 - 16:19:41 CST