| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Detecting IP address on fat &thin clients (web vs. sql*net)
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
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'))
/
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
)
/
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)
/
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;
 |
 |