Re: methodology to keep only certain programs to connect to

[Yechiel Adar]

Hello Joe

I implemented today Tom's trigger in one of our 
test databases and it works fine.
I am using it to track which users / applications 
are connecting so we can notify them
in case of scheduled down time or 
problems.

You need to run dbmsrand.sql to add dbms_random 
package to the database.

Yechiel AdarMehish

  - Original Message - 
  From: 
  JOE 
  TESTA 
  To: Multiple recipients of list ORACLE-L 
  Sent: Tuesday, September 10, 2002 5:58 
  PM
  Subject: methodology to keep only certain 
  programs to connect to
  
  I've been tasked to ensure only certain app programs access the 
  database.
  
  I'm thinking on-logon trigger, check the program field from 
  v$session. unfortunately v$session is for all sessions, i can't seem to 
  find the view that tells me only MY info during login. I only want the 
  sid, serial#, username and program for my just now connection to the 
  database.
  
  Does this exist or am I going about this the wrong way?
  
  We're thinking of checking those fields to make sure sql*plus, toad, etc 
  can't connect as a particular user(even though the password is known out in 
  the community).
  
  any ideas would be greatly appreciated.
  
  joe
  

RE: methodology to keep only certain programs to connect to

[Kurth, Michael J.]

I have seen invalid triggers cause ORA-604 
errors.

  -Original Message-From: Markham, Richard 
  [mailto:[EMAIL PROTECTED]]Sent: Tuesday, September 10, 
  2002 12:19 PMTo: Multiple recipients of list 
  ORACLE-LSubject: RE: methodology to keep only certain programs to 
  connect to
  what are the drawbacks with such a 
  trigger, what if the code went invalid and would not compile is
  it possible that you could lock yourself 
  out, or would the base login functionality still work regardless
  or the status of this 
trigger?
  
-Original Message-From: Mercadante, Thomas F 
[mailto:[EMAIL PROTECTED]]Sent: Tuesday, September 10, 
2002 12:24 PMTo: Multiple recipients of list 
ORACLE-LSubject: RE: methodology to keep only certain programs to 
connect to
Joe,

I use the following with decent success 
on a logon database trigger:


-- Set a unique string for the 
session and update the session info.
client_info_str := 
'WTWLOGIN_' || 
LTRIM(dbms_random.value,'.');
DBMS_APPLICATION_INFO.SET_CLIENT_INFO(client_info_str);

-- look into the 
v$session view for the session just connected.
SELECT 
program, 
username,
osuser, 
terminal, 
machine
INTO 
loc_program, 
loc_username,
loc_osuser,loc_terminal,loc_machine
FROM 
V$SESSION
WHERE 
client_info=client_info_str;

From here, you can test the loc_program variable against the 
loc_username to see if the combination is correct.
Stuff 
like:


IF 
loc_username='TESTLOGIN'then
 
RAISE kill_Login;
END IF;
EXCEPTION
WHEN kill_Login 
THEN
 
RAISE_APPLICATION_ERROR(-20999,'Login''s using this account and 
this tool are Invalid');


Hope 
this helps!

Tom Mercadante Oracle 
Certified Professional 

  -Original Message-From: JOE TESTA 
  [mailto:[EMAIL PROTECTED]]Sent: Tuesday, September 10, 
  2002 11:58 AMTo: Multiple recipients of list 
  ORACLE-LSubject: methodology to keep only certain programs to 
  connect to
  I've been tasked to ensure only certain app programs access the 
  database.
  
  I'm thinking on-logon trigger, check the program field from 
  v$session. unfortunately v$session is for all sessions, i can't seem 
  to find the view that tells me only MY info during login. I only 
  want the sid, serial#, username and program for my just now connection to 
  the database.
  
  Does this exist or am I going about this the wrong way?
  
  We're thinking of checking those fields to make sure sql*plus, toad, 
  etc can't connect as a particular user(even though the password is known 
  out in the community).
  
  any ideas would be greatly appreciated.
  
  joe
  



Privileged/Confidential information may be contained in this message.  The information contained in this message is intended only for the use of the recipient(s) named above and their co-workers who are working on the same matter.


The recipient of this information is prohibited from disclosing the information to any other party unless this disclosure has been authorized in advance.


If you are not intended recipient of this message or any agent responsible for delivery of the message to the intended recipient, you are hereby notified that any disclosure, copying, distribution or action taken in reliance on the contents of this message is strictly prohibited.  You should immediately destroy this message and kindly notify the sender by reply E-Mail.


Please advise immediately if you or your employer does not consent to Internet E-Mail for messages of this kind.  Opinions, conclusions and other information in this message that do not relate to the official business of the firm shall be understood as neither given nor endorsed by it.

Re: methodology to keep only certain programs to connect to

[Mark J. Bobak]

Hmm...feels like I missed something here.  Joe, in response to your
original question, try:
select what you need from v$session where audsid=userenv('sessionid');

That ought to return data for only your current session.

-Mark
On Wed, 2002-09-11 at 09:13, Yechiel Adar wrote:
 Hello Joe
 
 I implemented today Tom's trigger in one of our test databases and it works fine.
 I am using it to track which users / applications are connecting so we can notify 
them
 in case of scheduled down time or problems.
 
 You need to run dbmsrand.sql to add dbms_random package to the database.
 
 Yechiel Adar
 Mehish
   - Original Message - 
   From: JOE TESTA 
   To: Multiple recipients of list ORACLE-L 
   Sent: Tuesday, September 10, 2002 5:58 PM
   Subject: methodology to keep only certain programs to connect to
 
 
   I've been tasked to ensure only certain app programs access the database.
 
   I'm thinking on-logon trigger, check the program field from v$session.  
unfortunately v$session is for all sessions, i can't seem to find the view that tells 
me only MY info during login.  I only want the sid, serial#, username and program for 
my just now connection to the database.
 
   Does this exist or am I going about this the wrong way?
 
   We're thinking of checking those fields to make sure sql*plus, toad, etc can't 
connect as a particular user(even though the password is known out in the community).
 
   any ideas would be greatly appreciated.
 
   joe
 
-- 
--
Mark J. Bobak
Oracle DBA
[EMAIL PROTECTED]
It is not enough to have a good mind.  The main thing is to use it
well.
-- Rene Descartes
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mark J. Bobak
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: methodology to keep only certain programs to connect to

[Mercadante, Thomas F]

Joe,

I use the following with decent success on a 
logon database trigger:


-- Set a unique string for the session 
and update the session info.
client_info_str := 
'WTWLOGIN_' || 
LTRIM(dbms_random.value,'.');
DBMS_APPLICATION_INFO.SET_CLIENT_INFO(client_info_str);

-- look into the 
v$session view for the session just connected.
SELECT program, username,
osuser, 
terminal, machine
INTO 
loc_program, 
loc_username,
loc_osuser,loc_terminal,loc_machine
FROM V$SESSION
WHERE 
client_info=client_info_str;

From 
here, you can test the loc_program variable against the loc_username to see if 
the combination is correct.
Stuff 
like:


IF 
loc_username='TESTLOGIN'then
 
RAISE kill_Login;
END IF;
EXCEPTION
WHEN kill_Login 
THEN
 
RAISE_APPLICATION_ERROR(-20999,'Login''s using this account and this 
tool are Invalid');


Hope this 
helps!

Tom Mercadante Oracle Certified 
Professional 

  -Original Message-From: JOE TESTA 
  [mailto:[EMAIL PROTECTED]]Sent: Tuesday, September 10, 2002 
  11:58 AMTo: Multiple recipients of list ORACLE-LSubject: 
  methodology to keep only certain programs to connect to
  I've been tasked to ensure only certain app programs access the 
  database.
  
  I'm thinking on-logon trigger, check the program field from 
  v$session. unfortunately v$session is for all sessions, i can't seem to 
  find the view that tells me only MY info during login. I only want the 
  sid, serial#, username and program for my just now connection to the 
  database.
  
  Does this exist or am I going about this the wrong way?
  
  We're thinking of checking those fields to make sure sql*plus, toad, etc 
  can't connect as a particular user(even though the password is known out in 
  the community).
  
  any ideas would be greatly appreciated.
  
  joe
  

RE: methodology to keep only certain programs to connect to

[Shaw John-P55297]

use v_$mystat - it has the sid - then do 
your join with v$session

  -Original Message-From: JOE TESTA 
  [mailto:[EMAIL PROTECTED]]Sent: Tuesday, September 10, 2002 
  10:58 AMTo: Multiple recipients of list ORACLE-LSubject: 
  methodology to keep only certain programs to connect to
  I've been tasked to ensure only certain app programs access the 
  database.
  
  I'm thinking on-logon trigger, check the program field from 
  v$session. unfortunately v$session is for all sessions, i can't seem to 
  find the view that tells me only MY info during login. I only want the 
  sid, serial#, username and program for my just now connection to the 
  database.
  
  Does this exist or am I going about this the wrong way?
  
  We're thinking of checking those fields to make sure sql*plus, toad, etc 
  can't connect as a particular user(even though the password is known out in 
  the community).
  
  any ideas would be greatly appreciated.
  
  joe
  

RE: methodology to keep only certain programs to connect to

[Thomas Day]

Yes.  This works great.  You posted your logon trigger before and I've used
it with considerable success (and modification).  We (will) use the logon
trigger to ensure that a particular Oracle userid is logged on only from
one machine (no sharing of userids).  We also allow certain exemptions,
either by userid or machine.  I'll post our trigger but it's based on Mr.
Mercandante's ideas.

--create_LOGON_MULTIPLE_CHECK.sql
 CREATE OR REPLACE TRIGGER LOGON_MULTIPLE_CHECK
AFTER logon ON DATABASE
DECLARE
  client_info_str V$SESSION.CLIENT_INFO%TYPE;
  var_usernameV$SESSION.USERNAME%TYPE := null;
  kill_Login  EXCEPTION;
  PRAGMA EXCEPTION_INIT( kill_Login, -20997 );
begin
-- Set information string to uniquely identify this session
 client_info_str := 'Logon_Trigger_' || LTRIM(dbms_random.value,'.');
-- Push information string into v$session
 DBMS_APPLICATION_INFO.SET_CLIENT_INFO(client_info_str);
-- query v$session and see if this user is logged on twice on machines that
are not exempt
 begin
  SELECT unique(b.username)
  INTO var_username
-- look for more than one logon
 from v$session a,v$session b where a.username=b.username
-- is the user exempt?
-- trim off the null character that occasionally gets added to the name
  AND rtrim(A.USERNAME,CHR(0)) NOT IN (SELECT LME_exemptee FROM
   LOGON_MULTIPLE_EXEMPTIONS WHERE LME_exemption_type = 'U')
-- look for two different machines
  and a.machine != b.machine
-- are any of the machines exempt?
-- trim off the null character that occasionally gets added to the machine
name
  AND rtrim(A.MACHINE,CHR(0)) NOT IN (SELECT LME_exemptee FROM
   LOGON_MULTIPLE_EXEMPTIONS WHERE LME_exemption_type = 'M')
  AND rtrim(B.MACHINE,CHR(0)) NOT IN (SELECT LME_exemptee FROM
   LOGON_MULTIPLE_EXEMPTIONS WHERE LME_exemption_type = 'M')
-- make sure that we are looking at this logon session
  and a.client_info=client_info_str;
  EXCEPTION WHEN OTHERS THEN
  NULL;
 end;
--  if the user has a logon from more than 1 non-exempt machine then kill
this logon!
 IF var_username is not null
  THEN
 RAISE kill_Login;
 END IF;
 EXCEPTION
  WHEN kill_Login THEN
   RAISE_APPLICATION_ERROR(-20997,'This account is logged on via
another machine!');
 WHEN OTHERS THEN
  null;
END;
/

Hope this helps and thanks Tom.



   

Mercadante,   

Thomas FTo: Multiple recipients of list ORACLE-L 
[EMAIL PROTECTED]   
NDATFM  cc:   

@labor.state.Subject: RE: methodology to keep only 
certain programs to connect to  
ny.us 

Sent by: root  

   

   

09/10/2002 

12:23 PM   

Please 

respond to 

ORACLE-L   

   

   






Joe,

I use the following with decent success on a  logon database trigger:


--  Set a unique string for the session  and update the session info.
client_info_str :=  'WTWLOGIN_' ||  LTRIM(dbms_random.value,'.');
DBMS_APPLICATION_INFO.SET_CLIENT_INFO(client_info_str);


-- look into the  v$session view for the session just connected.
SELECT program, username,

osuser,  terminal, machine
INTO  loc_program,  loc_username,

loc_osuser,loc_terminal,loc_machine
FROM V$SESSION
WHERE  client_info=client_info_str;

From  here, you can test the loc_program variable against the loc_username
to see if  the combination is correct.
Stuff  like:


IF  loc_username

RE: methodology to keep only certain programs to connect to

[Lord, David - CSG]

Joe

You can use the sys_context function to get 
the auditing session id - 

 
select * from v$session where audsid = 
sys_context('USERENV','SESSIONID');

David Lord

  -Original Message-From: JOE TESTA 
  [mailto:[EMAIL PROTECTED]]Sent: 10 September 2002 
  16:58To: Multiple recipients of list ORACLE-LSubject: 
  methodology to keep only certain programs to connect to
  I've been tasked to ensure only certain app programs access the 
  database.
  
  I'm thinking on-logon trigger, check the program field from 
  v$session. unfortunately v$session is for all sessions, i can't seem to 
  find the view that tells me only MY info during login. I only want the 
  sid, serial#, username and program for my just now connection to the 
  database.
  
  Does this exist or am I going about this the wrong way?
  
  We're thinking of checking those fields to make sure sql*plus, toad, etc 
  can't connect as a particular user(even though the password is known out in 
  the community).
  
  any ideas would be greatly appreciated.
  
  joe
  

**
This message (including any attachments) is confidential and may be 
legally privileged.  If you are not the intended recipient, you should 
not disclose, copy or use any part of it - please delete all copies 
immediately and notify the Hays Group Email Helpdesk at
[EMAIL PROTECTED]
Any information, statements or opinions contained in this message
(including any attachments) are given by the author.  They are not 
given on behalf of Hays unless subsequently confirmed by an individual
other than the author who is duly authorised to represent Hays.
 
A member of the Hays plc group of companies.
Hays plc is registered in England and Wales number 2150950.
Registered Office Hays House Millmead Guildford Surrey GU2 4HJ.
**

RE: methodology to keep only certain programs to connect to

[Fink, Dan]

Can you use the USERENV or SYS_CONTEXT 
function?

  -Original Message-From: JOE TESTA 
  [mailto:[EMAIL PROTECTED]]Sent: Tuesday, September 10, 2002 
  9:58 AMTo: Multiple recipients of list ORACLE-LSubject: 
  methodology to keep only certain programs to connect to
  I've been tasked to ensure only certain app programs access the 
  database.
  
  I'm thinking on-logon trigger, check the program field from 
  v$session. unfortunately v$session is for all sessions, i can't seem to 
  find the view that tells me only MY info during login. I only want the 
  sid, serial#, username and program for my just now connection to the 
  database.
  
  Does this exist or am I going about this the wrong way?
  
  We're thinking of checking those fields to make sure sql*plus, toad, etc 
  can't connect as a particular user(even though the password is known out in 
  the community).
  
  any ideas would be greatly appreciated.
  
  joe
  

RE: methodology to keep only certain programs to connect to

[Kirsh, Gary]

Select sid
from v$msystat
where rownum = 1


Gary Kirsh
Next Extent Consulting

  -Original Message-From: JOE TESTA 
  [mailto:[EMAIL PROTECTED]]Sent: Tuesday, September 10, 2002 
  11:58 AMTo: Multiple recipients of list ORACLE-LSubject: 
  methodology to keep only certain programs to connect to
  I've been tasked to ensure only certain app programs access the 
  database.
  
  I'm thinking on-logon trigger, check the program field from 
  v$session. unfortunately v$session is for all sessions, i can't seem to 
  find the view that tells me only MY info during login. I only want the 
  sid, serial#, username and program for my just now connection to the 
  database.
  
  Does this exist or am I going about this the wrong way?
  
  We're thinking of checking those fields to make sure sql*plus, toad, etc 
  can't connect as a particular user(even though the password is known out in 
  the community).
  
  any ideas would be greatly appreciated.
  
  joe
  

RE: methodology to keep only certain programs to connect to

[Kevin Lange]

With a setup like this, how do you stop a 
user from simply renaming the program they are using to match what you expect to 
see and, therefore, getting past your security ??

  -Original Message-From: Shaw John-P55297 
  [mailto:[EMAIL PROTECTED]]Sent: Tuesday, September 10, 2002 
  11:59 AMTo: Multiple recipients of list ORACLE-LSubject: 
  RE: methodology to keep only certain programs to connect 
  to
  use v_$mystat - it has the sid - then do 
  your join with v$session
  
-Original Message-From: JOE TESTA 
[mailto:[EMAIL PROTECTED]]Sent: Tuesday, September 10, 2002 
10:58 AMTo: Multiple recipients of list 
ORACLE-LSubject: methodology to keep only certain programs to 
connect to
I've been tasked to ensure only certain app programs access the 
database.

I'm thinking on-logon trigger, check the program field from 
v$session. unfortunately v$session is for all sessions, i can't seem 
to find the view that tells me only MY info during login. I only want 
the sid, serial#, username and program for my just now connection to the 
database.

Does this exist or am I going about this the wrong way?

We're thinking of checking those fields to make sure sql*plus, toad, 
etc can't connect as a particular user(even though the password is known out 
in the community).

any ideas would be greatly appreciated.

joe

RE: methodology to keep only certain programs to connect to

[Nastase, Mr. C. (Catalin)]

Joe,

you may try: 

select sid, serial#, 
username, program from v$session where audsid = userenv( 
'sessionid')

Regards,
Catalin 
Nastase


  -Message d'origine-De: JOE TESTA 
  [mailto:[EMAIL PROTECTED]]Date: mardi 10 septembre 2002 
  17:58À: Multiple recipients of list ORACLE-LObjet: 
  methodology to keep only certain programs to connect to
  I've been tasked to ensure only certain app programs access the 
  database.
  
  I'm thinking on-logon trigger, check the program field from 
  v$session. unfortunately v$session is for all sessions, i can't seem to 
  find the view that tells me only MY info during login. I only want the 
  sid, serial#, username and program for my just now connection to the 
  database.
  
  Does this exist or am I going about this the wrong way?
  
  We're thinking of checking those fields to make sure sql*plus, toad, etc 
  can't connect as a particular user(even though the password is known out in 
  the community).
  
  any ideas would be greatly appreciated.
  
  joe
  

RE: methodology to keep only certain programs to connect to

[Mercadante, Thomas F]

Kevin,

That has been my point in the past. It 
is really not feasible to establish connection policy this way.

For example: anybody can change the 
name of the sqlplus.exe executable on their desktop, run it, and connect to the 
database. v$session.program now reports the new executable name - not 
sqlplus.

The same goes for any tool on the desktop - 
including odbc connections.

Security policy has to start at the 
account/password level.

Tom Mercadante Oracle 
Certified Professional 

  -Original Message-From: Kevin Lange 
  [mailto:[EMAIL PROTECTED]]Sent: Tuesday, September 10, 2002 1:54 
  PMTo: Multiple recipients of list ORACLE-LSubject: RE: 
  methodology to keep only certain programs to connect to
  With a setup like this, how do you stop a 
  user from simply renaming the program they are using to match what you expect 
  to see and, therefore, getting past your security ??
  
-Original Message-From: Shaw John-P55297 
[mailto:[EMAIL PROTECTED]]Sent: Tuesday, September 10, 2002 
11:59 AMTo: Multiple recipients of list 
ORACLE-LSubject: RE: methodology to keep only certain programs to 
connect to
use v_$mystat - it has the sid - then do 
your join with v$session

  -Original Message-From: JOE TESTA 
  [mailto:[EMAIL PROTECTED]]Sent: Tuesday, September 10, 
  2002 10:58 AMTo: Multiple recipients of list 
  ORACLE-LSubject: methodology to keep only certain programs to 
  connect to
  I've been tasked to ensure only certain app programs access the 
  database.
  
  I'm thinking on-logon trigger, check the program field from 
  v$session. unfortunately v$session is for all sessions, i can't seem 
  to find the view that tells me only MY info during login. I only 
  want the sid, serial#, username and program for my just now connection to 
  the database.
  
  Does this exist or am I going about this the wrong way?
  
  We're thinking of checking those fields to make sure sql*plus, toad, 
  etc can't connect as a particular user(even though the password is known 
  out in the community).
  
  any ideas would be greatly appreciated.
  
  joe
  

Re: methodology to keep only certain programs to connect to

[Jared . Still]

Joe,

Try this:

select
   s.username,
   s.sid,
   s.serial#
from v$session s
where  userenv('SESSIONID') = s.audsid;

Jared






JOE TESTA [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
 09/10/2002 08:58 AM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc: 
Subject:methodology to keep only certain programs to connect to


I've been tasked to ensure only certain app programs access the database.
 
I'm thinking on-logon trigger, check the program field from v$session. 
unfortunately v$session is for all sessions, i can't seem to find the view 
that tells me only MY info during login.  I only want the sid, serial#, 
username and program for my just now connection to the database.
 
Does this exist or am I going about this the wrong way?
 
We're thinking of checking those fields to make sure sql*plus, toad, etc 
can't connect as a particular user(even though the password is known out 
in the community).
 
any ideas would be greatly appreciated.
 
joe
 


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: methodology to keep only certain programs to connect to

[JOE TESTA]

Jared(and others) 
thanks, a bunch you all had what i was looking for perfectly.

joe
 [EMAIL PROTECTED] 09/10/02 12:55PM 
Joe,Try this:select 
s.username, s.sid, s.serial#from v$session 
swhere userenv('SESSIONID') = 
s.audsid;Jared"JOE TESTA" 
[EMAIL PROTECTED]Sent by: [EMAIL PROTECTED]09/10/2002 08:58 
AMPlease respond to 
ORACLE-L 
To: Multiple recipients of list ORACLE-L 
[EMAIL PROTECTED] cc: 
 
Subject: methodology to keep only 
certain programs to connect toI've been tasked to ensure only 
certain app programs access the database.I'm thinking on-logon trigger, 
check the program field from v$session. unfortunately v$session is for all 
sessions, i can't seem to find the view that tells me only MY info during 
login. I only want the sid, serial#, username and program for my just 
now connection to the database.Does this exist or am I going about this 
the wrong way?We're thinking of checking those fields to make sure 
sql*plus, toad, etc can't connect as a particular user(even though the 
password is known out in the community).any ideas would be greatly 
appreciated.joe

Re: methodology to keep only certain programs to connect to

[Glenn Stauffer]

I'm working with an application that uses a combination of encrypted
seed numbers and password protected roles to limit access to the
application tables to the specific application and version.

In this database, any external application (sqlplus, etc) cannot provide
access to the application tables since that requires activation of the
password protected role.  The only default role for users is a connect
role that has only connect privs.  And, you can't just grab a copy of
the application from anywhere and use it against the database since the
encrypted seed number compiled into the application is checked against
the value in the database before a connection is permitted.

Glenn Stauffer

On Tue, 2002-09-10 at 11:58, JOE TESTA wrote:
 I've been tasked to ensure only certain app programs access the database.
 
 I'm thinking on-logon trigger, check the program field from v$session.  
unfortunately v$session is for all sessions, i can't seem to find the view that tells 
me only MY info during login.  I only want the sid, serial#, username and program for 
my just now connection to the database.
 
 Does this exist or am I going about this the wrong way?
 
 We're thinking of checking those fields to make sure sql*plus, toad, etc can't 
connect as a particular user(even though the password is known out in the community).
 
 any ideas would be greatly appreciated.
 
 joe


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Glenn Stauffer
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: methodology to keep only certain programs to connect to

[Stephane Faroult]

Joe,

   Create a view over V$SESSION with the condition

 where audsid = SYS_CONTEXT('USERENV', 'SESSIONID')

 call it USER_SESSION and grant SELECT TO PUBLIC on it.

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: methodology to keep only certain programs to connect to

[Markham, Richard]

what are the drawbacks with such a trigger, 
what if the code went invalid and would not compile is
it possible that you could lock yourself 
out, or would the base login functionality still work regardless
or the status of this trigger?

  -Original Message-From: Mercadante, Thomas F 
  [mailto:[EMAIL PROTECTED]]Sent: Tuesday, September 10, 2002 
  12:24 PMTo: Multiple recipients of list ORACLE-LSubject: 
  RE: methodology to keep only certain programs to connect 
  to
  Joe,
  
  I use the following with decent success on 
  a logon database trigger:
  
  
  -- Set a unique string for the 
  session and update the session info.
  client_info_str := 
  'WTWLOGIN_' || 
  LTRIM(dbms_random.value,'.');
  DBMS_APPLICATION_INFO.SET_CLIENT_INFO(client_info_str);
  
  -- look into the 
  v$session view for the session just connected.
  SELECT program, username,
  osuser, 
  terminal, machine
  INTO 
  loc_program, 
  loc_username,
  loc_osuser,loc_terminal,loc_machine
  FROM 
V$SESSION
  WHERE 
  client_info=client_info_str;
  
  From here, you can test the loc_program variable against the 
  loc_username to see if the combination is correct.
  Stuff 
  like:
  
  
  IF 
  loc_username='TESTLOGIN'then
   
  RAISE kill_Login;
  END IF;
  EXCEPTION
  WHEN kill_Login 
  THEN
   
  RAISE_APPLICATION_ERROR(-20999,'Login''s using this account and 
  this tool are Invalid');
  
  
  Hope 
  this helps!
  
  Tom Mercadante Oracle 
  Certified Professional 
  
-Original Message-From: JOE TESTA 
[mailto:[EMAIL PROTECTED]]Sent: Tuesday, September 10, 2002 
11:58 AMTo: Multiple recipients of list 
ORACLE-LSubject: methodology to keep only certain programs to 
connect to
I've been tasked to ensure only certain app programs access the 
database.

I'm thinking on-logon trigger, check the program field from 
v$session. unfortunately v$session is for all sessions, i can't seem 
to find the view that tells me only MY info during login. I only want 
the sid, serial#, username and program for my just now connection to the 
database.

Does this exist or am I going about this the wrong way?

We're thinking of checking those fields to make sure sql*plus, toad, 
etc can't connect as a particular user(even though the password is known out 
in the community).

any ideas would be greatly appreciated.

joe

Re: methodology to keep only certain programs to connect to

[Stephane Faroult]

More thoughts :

   SQL*Plus fills MODULE in, don't know about TOAD (I think it does),
but typically a number of PC clients may appear as the name of a DLL. I
think that you shoud rather allow in than exclude out, and (ab)use
DBMS_APPLICATION_INFO in the programs which are allowed.
-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: methodology to keep only certain programs to connect to

[Kevin Lange]

I have always thought this was the best way to implement a security package.
Nice to see you implemented the seed number for changing encryption.

-Original Message-
Sent: Tuesday, September 10, 2002 1:49 PM
To: Multiple recipients of list ORACLE-L


I'm working with an application that uses a combination of encrypted
seed numbers and password protected roles to limit access to the
application tables to the specific application and version.

In this database, any external application (sqlplus, etc) cannot provide
access to the application tables since that requires activation of the
password protected role.  The only default role for users is a connect
role that has only connect privs.  And, you can't just grab a copy of
the application from anywhere and use it against the database since the
encrypted seed number compiled into the application is checked against
the value in the database before a connection is permitted.

Glenn Stauffer

On Tue, 2002-09-10 at 11:58, JOE TESTA wrote:
 I've been tasked to ensure only certain app programs access the database.
 
 I'm thinking on-logon trigger, check the program field from v$session.
unfortunately v$session is for all sessions, i can't seem to find the view
that tells me only MY info during login.  I only want the sid, serial#,
username and program for my just now connection to the database.
 
 Does this exist or am I going about this the wrong way?
 
 We're thinking of checking those fields to make sure sql*plus, toad, etc
can't connect as a particular user(even though the password is known out in
the community).
 
 any ideas would be greatly appreciated.
 
 joe


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Glenn Stauffer
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Kevin Lange
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: methodology to keep only certain programs to connect to

[Thomas Day]

My experience is that an invalid trigger doesn't fire --- no effect.

Also, userids with the DBA role don't fire the trigger.  So you can't lock
yourself out of the database.  Just go in with a DBA role userid and drop
the logon trigger.




   

Markham,  

Richard To: Multiple recipients of list ORACLE-L 
[EMAIL PROTECTED]   
RMarkhamcc:   

@hafeleamericSubject: RE: methodology to keep only 
certain programs to connect to  
as.com

Sent by: root  

   

   

09/10/2002 

01:18 PM   

Please 

respond to 

ORACLE-L   

   

   






what are the drawbacks with such a trigger,  what if the code went invalid
and would not compile is
it possible that you could lock yourself  out, or would the base login
functionality still work regardless
or the status of this trigger?
-Original Message-
Sent: Tuesday, September 10, 2002  12:24 PM
To: Multiple recipients of list ORACLE-L


Joe,

I use the following with decent success on  a logon database trigger:


--  Set a unique string for the  session and update the session info.
client_info_str :=  'WTWLOGIN_' ||  LTRIM(dbms_random.value,'.');
DBMS_APPLICATION_INFO.SET_CLIENT_INFO(client_info_str);


-- look into the  v$session view for the session just connected.
SELECT program, username,

osuser,  terminal, machine
INTO  loc_program,  loc_username,

loc_osuser,loc_terminal,loc_machine
FROM  V$SESSION
WHERE  client_info=client_info_str;

From here, you can test the loc_program variable against the  loc_username
to see if the combination is correct.
Stuff  like:


IF  loc_username='TESTLOGIN' then

    RAISE kill_Login;

END IF;

EXCEPTION

WHEN kill_Login  THEN

     RAISE_APPLICATION_ERROR(-20999,'Login''s using this account and  this
tool are Invalid');


Hope  this helps!


Tom Mercadante
Oracle  Certified Professional
-Original Message-
Sent: Tuesday, September 10, 2002  11:58 AM
To: Multiple recipients of list  ORACLE-L


I've been tasked to ensure only certain app programs access the  database.

I'm thinking on-logon trigger, check the program field from  v$session.
unfortunately v$session is for all sessions, i can't seem  to find the view
that tells me only MY info during login.  I only want  the sid, serial#,
username and program for my just now connection to the  database.

Does this exist or am I going about this the wrong way?

We're thinking of checking those fields to make sure sql*plus, toad,  etc
can't connect as a particular user(even though the password is known out
in the community).

any ideas would be greatly appreciated.

joe




--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Thomas Day
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: methodology to keep only certain programs to connect to

[Jared . Still]

You can't.

This is one of the reasons I haven't tried to use this.

Jared





Kevin Lange [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
 09/10/2002 10:53 AM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc: 
Subject:RE: methodology to keep only certain programs to connect to


With a setup like this, how do you stop a user from simply renaming the 
program they are using to match what you expect to see and, therefore, 
getting past your security ??
-Original Message-
Sent: Tuesday, September 10, 2002 11:59 AM
To: Multiple recipients of list ORACLE-L

use v_$mystat - it has the sid - then do your join with v$session
-Original Message-
Sent: Tuesday, September 10, 2002 10:58 AM
To: Multiple recipients of list ORACLE-L

I've been tasked to ensure only certain app programs access the database.
 
I'm thinking on-logon trigger, check the program field from v$session. 
unfortunately v$session is for all sessions, i can't seem to find the view 
that tells me only MY info during login.  I only want the sid, serial#, 
username and program for my just now connection to the database.
 
Does this exist or am I going about this the wrong way?
 
We're thinking of checking those fields to make sure sql*plus, toad, etc 
can't connect as a particular user(even though the password is known out 
in the community).
 
any ideas would be greatly appreciated.
 
joe
 


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: methodology to keep only certain programs to connect to

[Jamadagni, Rajendra]

Title: RE: methodology to keep only certain programs to connect to





Revoke all roles from all apps. You will have to change some code in authorized apps to enable roles after they log in to allow them to access the database. All stray applications won't do this, so even if they log in they won't be able to access anything.

BTW SQLPLUS and TOAD use dbms_application_info to set the module column in v$session. This you can capture in db-logon trigger and kill them. At that stage, it is way too early to change the module information.

Raj
__
Rajendra Jamadagni  MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. 
QOTD: Any clod can have facts, but having an opinion is an art!



-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, September 10, 2002 4:03 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: methodology to keep only certain programs to connect to



You can't.


This is one of the reasons I haven't tried to use this.


Jared




This e-mail 
message is confidential, intended only for the named recipient(s) above and may 
contain information that is privileged, attorney work product or exempt from 
disclosure under applicable law. If you have received this message in error, or are 
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 
and delete this e-mail message from your computer, Thank 
you.*2