Re: security without using different usernames

From: Pete Finnigan <oracle_list_at_peterfinnigan.demon.co.uk>
Date: Wed, 16 Jul 2003 15:37:19 +0100
Message-Id: <25937.338033@fatcity.com>

Hi

You can get the ip address as follows:

oracle:jupiter> sqlplus system/manager_at_emil

SQL*Plus: Release 9.0.1.0.0 - Production on Wed Jun 25 20:45:54 2003

(c) Copyright 2001 Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.0.1.0.0 - Production With the Partitioning option
JServer Release 9.0.1.0.0 - Production

SQL> select sys_context('userenv','ip_address') ip,username,machine   2 from v$session
  3 where sys_context('userenv','sessionid')=audsid;

IP

---

---

USERNAME

---

MACHINE

---

172.16.240.11
SYSTEM
jupiter

SQL> you need to provide the service name when you log on otherwise the ip address is not available using sys_context.

Also new with 9i as part of application contexts you can use the "using" clause of create role that a pl/sql package can be used to verify the user, for example something like this, typed in from memory so check the syntax!:

create role some_role identified using sys.confirm_user;

create or replace procedure confirm_user authid current user is

        lv_ipaddress varchar2(30);
begin

        select sys_context('userenv','ip_address')
        into lv_ipaddress
        from sys.dual;
        if lv_ipaddress='172.16.140.1' then
                dbms_session.set_role('some_role');
        end if;

end;
/

hth

kind regards

Pete

-- 
Pete Finnigan
email:pete_at_petefinnigan.com
Web site: http://www.petefinnigan.com - Oracle security audit specialists

Received on Wed Jul 16 2003 - 09:37:19 CDT