try owa_util.get_cgi_env('REMOTE_ADDR') -- seems to reliably return the
client IP
--mcs
"Gregory N. Mirsky" <gmirsky_at_optonline.net> wrote in message
news:31eb00e7.0311250610.605c1e55_at_posting.google.com...
| I have a MOD_PLSQL problem that I can't seem to find and acceptable
| solution.
|
| Maybe it is a "can't see the forest for the trees" problem on my part
| but I think I need a slap upside the head in order to get the wheels
| spinning again.
|
| I've developed an audit object that we tag on to the end of tables to
| give us some easily accessable data about the creation and last
| update of a record in a table. The reason I developed this object was
| due to some unscrupulous users going into the application database
| with a shared id (management decision, not my call) and changing data
| so that their reports would balance out. The fact that our
| application displays such activity on the screen stopped such actions
| "dead in thier tracks".
|
| The object works fine while using a fat client, but when an insert or
| update takes place using a thin client (mod_plsql) the object only
| records the database server or the server that has the web server. I
| realize that the through a thin client I can only
| gather some of the data that would be available through a fat client
| but I cant seem to get the users workstation address and have the
| object work for both fat and thin clients.
|
| I have included the code for the object and a sample table using the
| object below.
|
| Any suggestions would be greatly appreciated!
|
| Thanks!
|
| Greg
| --------------- code starts
here -------------------------------------------
|
| CREATE SEQUENCE contact_type_seq
| INCREMENT BY 1
| START WITH 42
| MINVALUE 1
| MAXVALUE 999999999999
| NOCYCLE
| ORDER
| CACHE 10
| /
|
| CREATE OR REPLACE
| TYPE audit_type AS OBJECT(
| create_user VARCHAR2(30)
| , create_osuser VARCHAR2(30)
| , create_timestamp DATE
| , create_ip_address VARCHAR2(64)
| , create_local_host_name VARCHAR2(64)
| , create_terminal VARCHAR2(32)
| , update_user VARCHAR2(30)
| , update_osuser VARCHAR2(30)
| , update_timestamp DATE
| , update_ip_address VARCHAR2(64)
| , update_local_host_name VARCHAR2(64)
| , update_terminal VARCHAR2(32)
| , expire_timestamp DATE
| , MEMBER FUNCTION get_os_user_id(
| p_user IN VARCHAR2) RETURN VARCHAR2
| , PRAGMA restrict_references(get_os_user_id, WNDS)
| , MEMBER FUNCTION record_age(
| p_create_timestamp IN DATE DEFAULT SYSDATE)
| RETURN NUMBER
| , PRAGMA restrict_references(record_age, WNDS)
| , MEMBER FUNCTION get_client_ip_address RETURN VARCHAR2
| , PRAGMA restrict_references(get_client_ip_address, WNDS)
| , MEMBER FUNCTION get_local_host_name RETURN VARCHAR2
| , PRAGMA restrict_references(get_local_host_name, WNDS)
| , MEMBER FUNCTION get_terminal_id RETURN VARCHAR2
| , PRAGMA restrict_references(get_terminal_id, WNDS)
| , MEMBER PROCEDURE set_expire_timestamp
| , MEMBER PROCEDURE set_create_timestamp
| , MEMBER PROCEDURE set_update_timestamp
| , MEMBER PROCEDURE set_create_user
| , MEMBER PROCEDURE set_update_user
| , MEMBER PROCEDURE set_create_osuser
| , MEMBER PROCEDURE set_update_osuser
| , MEMBER PROCEDURE set_create_ip_address
| , MEMBER PROCEDURE set_update_ip_address
| , MEMBER PROCEDURE set_create_local_host_name
| , MEMBER PROCEDURE set_update_local_host_name
| , MEMBER PROCEDURE set_create_terminal
| , MEMBER PROCEDURE set_update_terminal
| );
| /
|
| CREATE OR REPLACE
| TYPE BODY audit_type
| AS
| MEMBER FUNCTION get_local_host_name
| RETURN VARCHAR2
| IS
| v_local_host_name VARCHAR2(64);
| BEGIN
| v_local_host_name := SYS_CONTEXT(UPPER('userenv'), UPPER
| ('host'));
| RETURN v_local_host_name;
| EXCEPTION
| WHEN OTHERS
| THEN
| RETURN initcap('unknown');
| END get_local_host_name;
| MEMBER FUNCTION get_terminal_id
| RETURN VARCHAR2
| IS
| v_terminal VARCHAR2(32);
| BEGIN
| v_terminal := SYS_CONTEXT(UPPER('userenv'), UPPER('terminal'));
| RETURN v_terminal;
| EXCEPTION
| WHEN OTHERS
| THEN
| RETURN NULL;
| END get_terminal_id;
| MEMBER FUNCTION get_os_user_id(
| p_user IN VARCHAR2)
| RETURN VARCHAR2
| IS
| v_osuser VARCHAR2(30);
| BEGIN
| SELECT a.osuser
| INTO v_osuser
| FROM sys.v_$session a
| WHERE a.username = p_user
| AND a.audsid = USERENV('sessionid');
| RETURN v_osuser;
| EXCEPTION
| WHEN NO_DATA_FOUND
| THEN
| RETURN NULL;
| WHEN OTHERS
| THEN
| RETURN NULL;
| END get_os_user_id;
| MEMBER FUNCTION record_age(
| p_create_timestamp IN DATE DEFAULT SYSDATE)
| RETURN NUMBER
| IS
| BEGIN
| RETURN ROUND(SYSDATE - p_create_timestamp);
| END record_age;
| MEMBER PROCEDURE set_expire_timestamp
| AS
| BEGIN
| expire_timestamp := TO_DATE('31-DEC-4712', UPPER('dd-mon-yyyy'));
| RETURN;
| EXCEPTION
| WHEN OTHERS
| THEN
| RETURN;
| END set_expire_timestamp;
| MEMBER PROCEDURE set_create_timestamp
| AS
| BEGIN
| create_timestamp := SYSDATE;
| RETURN;
| EXCEPTION
| WHEN OTHERS
| THEN
| RETURN;
| END set_create_timestamp;
| MEMBER PROCEDURE set_update_timestamp
| AS
| BEGIN
| update_timestamp := SYSDATE;
| RETURN;
| EXCEPTION
| WHEN OTHERS
| THEN
| RETURN;
| END set_update_timestamp;
| MEMBER PROCEDURE set_create_user
| AS
| BEGIN
| create_user := USER;
| RETURN;
| EXCEPTION
| WHEN OTHERS
| THEN
| RETURN;
| END set_create_user;
| MEMBER PROCEDURE set_update_user
| AS
| BEGIN
| update_user := USER;
| RETURN;
| EXCEPTION
| WHEN OTHERS
| THEN
| RETURN;
| END set_update_user;
| MEMBER PROCEDURE set_create_osuser
| AS
| BEGIN
| create_osuser := get_os_user_id(USER);
| RETURN;
| EXCEPTION
| WHEN OTHERS
| THEN
| RETURN;
| END set_create_osuser;
| MEMBER PROCEDURE set_update_osuser
| AS
| BEGIN
| update_osuser := get_os_user_id(USER);
| RETURN;
| EXCEPTION
| WHEN OTHERS
| THEN
| RETURN;
| END set_update_osuser;
| MEMBER FUNCTION get_client_ip_address
| RETURN VARCHAR2
| AS
| v_ip_address VARCHAR2(2000);
| BEGIN
|
| v_ip_address :=
| SYS_CONTEXT(UPPER('userenv'), UPPER('ip_address'));
|
| --IF trim(OWA_UTIL.get_cgi_env (param_name => 'REMOTE_ADDR'))
| <> NULL
| --AND trim(OWA_UTIL.get_cgi_env (param_name => 'REMOTE_ADDR'))
| <> trim(v_ip_address)
| --THEN
| --RETURN OWA_UTIL.get_cgi_env (param_name => 'REMOTE_ADDR');
| --else
| RETURN v_ip_address;
| --END IF;
| EXCEPTION
| WHEN OTHERS
| THEN
| RETURN NULL;
| END get_client_ip_address;
| MEMBER PROCEDURE set_create_ip_address
| AS
| BEGIN
| create_ip_address := get_client_ip_address;
| EXCEPTION
| WHEN OTHERS
| THEN
| RETURN;
| END set_create_ip_address;
| MEMBER PROCEDURE set_update_ip_address
| AS
| BEGIN
| update_ip_address := get_client_ip_address;
| EXCEPTION
| WHEN OTHERS
| THEN
| RETURN;
| END set_update_ip_address;
| MEMBER PROCEDURE set_create_local_host_name
| AS
| BEGIN
| create_local_host_name := get_local_host_name;
| EXCEPTION
| WHEN OTHERS
| THEN
| RETURN;
| END set_create_local_host_name;
| MEMBER PROCEDURE set_update_local_host_name
| AS
| BEGIN
| update_local_host_name := get_local_host_name;
| EXCEPTION
| WHEN OTHERS
| THEN
| RETURN;
| END set_update_local_host_name;
| MEMBER PROCEDURE set_create_terminal
| AS
| BEGIN
| create_terminal := get_terminal_id;
| EXCEPTION
| WHEN OTHERS
| THEN
| RETURN;
| END set_create_terminal;
| MEMBER PROCEDURE set_update_terminal
| AS
| BEGIN
| update_terminal := get_terminal_id;
| EXCEPTION
| WHEN OTHERS
| THEN
| RETURN;
| END set_update_terminal;
| END ;
| /
|
| CREATE TABLE contact_type
| (contact_type_id NUMBER(12,0) DEFAULT 0 NOT NULL,
| contact_type_description VARCHAR2(55) DEFAULT 'None' NOT
| NULL,
| effective_start_date DATE DEFAULT SYSDATE NOT NULL,
| effective_end_date DATE DEFAULT TO_DATE('31-dec-
| 4712','dd-mon-yyyy') NOT NULL,
| audit_data audit_type
| DEFAULT audit_type(USER
| ,USER
| ,SYSDATE
| ,'127.0.0.1'
| ,'?'
| ,'?'
| ,USER
| ,USER
| ,SYSDATE
| ,'127.0.0.1'
| ,'?'
| ,'?'
| ,TO_DATE('4712-12-31 00:00:00', 'yyyy-mm-dd
| hh24:mi:ss'))
| ,
| PRIMARY KEY (contact_type_id)
| USING INDEX
| PCTFREE 10
| INITRANS 2
| MAXTRANS 255
| TABLESPACE bcndx
| STORAGE (
| INITIAL 131072
| MINEXTENTS 1
| MAXEXTENTS 2147483645
| ))
| PCTFREE 10
| PCTUSED 40
| INITRANS 1
| MAXTRANS 255
| TABLESPACE userdata
| STORAGE (
| INITIAL 131072
| MINEXTENTS 1
| MAXEXTENTS 2147483645
| )
|
| /
|
|
| -- Indexes for CONTACT_TYPE
|
| CREATE UNIQUE INDEX contact_type_idx1 ON contact_type
| (
| contact_type_description ASC
| )
| PCTFREE 10
| INITRANS 2
| MAXTRANS 255
| TABLESPACE userndx
| STORAGE (
| INITIAL 131072
| MINEXTENTS 1
| MAXEXTENTS 2147483645
| )
| /
|
|
| -- Constraints for CONTACT_TYPE
|
| ALTER TABLE contact_type
| ADD CONSTRAINT contact_type_chk1 CHECK (effective_start_date <=
| effective_end_date)
| /
| ALTER TABLE contact_type
| ADD CONSTRAINT contact_type_chk2 CHECK (effective_end_date <=
| audit_data.expire_timestamp)
| /
|
| -- Triggers for CONTACT_TYPE
|
| CREATE OR REPLACE TRIGGER "BCADM".CONTACT_TYPE_T00
| BEFORE
| INSERT OR UPDATE
| ON CONTACT_TYPE
| REFERENCING OLD AS OLD NEW AS NEW
| FOR EACH ROW
| DECLARE
| v_next_number NUMBER;
| BEGIN
| IF deleting
| THEN
| RETURN;
| END IF;
| ----------------------------------------------------------------------
| -----
| --- IF INSERTING NEW ROW THEN GET THE NEXT SEQUENCE # IN THE SEQUENCER
| ----------------------------------------------------------------------
| -----
| BEGIN
| IF inserting
| THEN
| SELECT CONTACT_TYPE_SEQ.NEXTVAL
| INTO v_next_number
| FROM DUAL;
| :new.CONTACT_TYPE_ID := v_next_number;
| END IF;
| END;
| ----------------------------------------------------------------------
| -----
| --- UPDATE THE AUDIT VALUES
| ----------------------------------------------------------------------
| -----
| BEGIN
| :new.audit_data.set_update_osuser;
| :new.audit_data.set_update_user;
| :new.audit_data.set_update_timestamp;
| :new.audit_data.set_update_ip_address;
| :new.audit_data.set_update_local_host_name;
| :new.audit_data.set_update_terminal;
| IF updating
| THEN
| -------------------------------------------------------------
| --------
| -- To insure against user modifying the create_by fields
| -------------------------------------------------------------
| --------
| :new.CONTACT_TYPE_ID := :old.CONTACT_TYPE_ID;
| :new.audit_data.create_user := :old.audit_data.create_us
| er;
| :new.audit_data.create_osuser := :old.audit_data.create_os
| user;
| :new.audit_data.create_timestamp := :old.audit_data.create_ti
| mestamp;
| :new.audit_data.create_ip_address:= :old.audit_data.create_ip
| _address;
| :new.audit_data.create_local_host_name := :old.audit_data.cre
| ate_local_host_name;
| :new.audit_data.create_terminal := :old.audit_data.create_te
| rminal;
| if :new.audit_data.expire_timestamp <= SYSDATE
| THEN
| :new.audit_data.expire_timestamp := sysdate+1;
| END IF;
| ELSE
| -------------------------------------------------------------
| --------
| -- If not updating then it must be an insert
| -------------------------------------------------------------
| --------
| :new.audit_data.set_create_user;
| :new.audit_data.set_create_osuser;
| :new.audit_data.set_create_timestamp;
| :new.audit_data.set_create_ip_address;
| :new.audit_data.set_create_local_host_name;
| :new.audit_data.set_create_terminal;
| if :new.audit_data.expire_timestamp is null
| then
| :new.audit_data.set_expire_timestamp;
| else
| if :new.audit_data.expire_timestamp < sysdate
| then
| :new.audit_data.expire_timestamp := sysdate;
| end if;
| end if;
| END IF;
| END;
| --------------------------------------------------------------------------
| -- Error handling
| --------------------------------------------------------------------------
| EXCEPTION
| WHEN OTHERS
| THEN
| IF SQLCODE >= -20999
| AND SQLCODE <= -20000
| THEN
| raise_application_error(-20501
| , SQLERRM || ' [' || TO_CHAR(SQLCODE) || '];');
| ELSE
| raise_application_error(-20501
| , TO_CHAR(SQLCODE) || SQLERRM(SQLCODE) || ';');
| END IF;
| END;
| /
Received on Tue Nov 25 2003 - 08:52:07 CST
