hazardous dropping of tables 5

[areza123]

hi,
I have a typically hazardous problem...
Many users login to my data base with the same userid / password.If one of them drop's a table I want to know atleast which machine (client IP address) that table was dropped from ? Is there any method to find out in Oracle ?
Hint : v$Session there is a column called command ? Is this an id of the command that was executed ? How can I see the script (PL/SQL) of this command ?

 

[shefea]

You would probaby want a trigger on any DDL for the user. that also selects the machine (which they are logged in from V$session), as part of the intsert statment that makes up the trigger, so effectively inserting this information into an table created solely for the use of this trigger. My guess is that you know how to write a trigger but if you do not I can show you if need be.

shefea

 

[jee]

Let me add some information - there are multiple ways to tackle this.

The first way is to turn on Oracle auditing. You can specify that all "DROP TABLE" statements will be audited. However this probably won't work for you since you have multiple real users logging in as the same Oracle user. I don't think Oracle auditing records anything about the client computer.

The second way is to write a trigger that will execute whenever a "Drop" statement is executed. Within this trigger you will want to determine the client machine from which this was executed and log this to some audit trail (a table or text file or whatever). There are a couple of ways to determine the client machine:

1) use the built-in USERENV function with the 'TERMINAL' argument. I think this returns the contents ofthe V$SESSION.TERMINAL column. For a Windows type machine, this is the System Name. The advantage of using this is that you don't have to worry about having privs. on the V$SESSION table.

2) Access one of the V$SESSION columns. First you need to determine which row to look at. Use the USERENV ('SESSIONID') function which returns the V$SESSION.AUDSID column contents. Now you need to decide which column you want. Choices are:
OSUSER - returns domain/name the user used to connect to the network.
MACHINE - returns the domain/name of the client computer
TERMINAL - returns the system name of the client computer.
Of course the "owner" of the trigger you write must have SELECT priv on the V$SESSION view.

None of these is the IP address. If you simply must use the IP address, you can do the following.

1) Write a trigger that fires AFTER LOGON. Within this trigger you can use the ora_client_ip_address function to obtain the IP address. Add a row to a user-created audit table that is made up of the session ID (from USERENV ('SESSIONID') and the IP address value.

2) Write a trigger that fires AFTER DROP. Read the IP address from your user-created audit table for the session ID. Do whatever you are planning to do with this info.

3) Write a BEFORE LOGOFF trigger that deletes the row you created in the AFTER LOGON trigger (for this session ID).

 

[Turkbear]

Hi, I just want to drop in my 2c here:
Unless forced to at gunpoint, I would NEVER allow a 'generic' user to have CREATE or DROP rights..

Either set up 'real' users or have the 'generic' ones send
the DBA a request to handle the DDL ---

 

[Jagon2002]

Consider activate the Oracle Audit funcionality

 

[areza123]

Thanks shefea ,jee and all for the immensly useful information provided.
I wish I had ur e-mail id's so that I could thank u personally !

The solution looks something like this

CONNECT system/manager

GRANT ADMINISTER DATABASE TRIGGER TO scott;

CONNECT scott/tiger

CREATE TABLE audit_table
(
user_at VARCHAR2(10),
time_now DATE,
term VARCHAR2(10),
missingtbl VARCHAR(100)
);
/
Create table tblnames(tbl varchar(100));
/
Create table MISSINGTBL(tbl varchar(100));
/

CREATE OR REPLACE PROCEDURE foo (c VARCHAR2)
AS
BEGIN
INSERT INTO MISSINGTBL
SELECT DISTINCT tbl
FROM tblnames
MINUS
select distinct object_name
from user_objects
where object_type = 'TABLE';

INSERT INTO Audit_table
(user_at,time_now,TERM,missingtbl)
SELECT c,sysdate,USERenv('TERMINAL'),'missingtbl'
FROM DUAL;
END;
/
CREATE OR REPLACE PROCEDURE populate (c VARCHAR2)
AS
BEGIN
DELETE TBLNAMES;
insert into tblnames
select distinct object_name
from user_objects
where object_type = 'TABLE';
END;

CREATE OR REPLACE TRIGGER logontrig1 AFTER LOGON ON DATABASE
CALL foo (sys.login_user)
/
CREATE OR REPLACE TRIGGER logontrig2 AFTER drop ON DATABASE
CALL foo (sys.login_user)
/
CREATE OR REPLACE TRIGGER logontrig3 before drop ON DATABASE
CALL populate (sys.login_user)


this solution is more or less ok except for the hardcoding
the value of column missingtbl in Audit_table (procedure foo !) ...the reason i hardcoded is that the query
SELECT DISTINCT tbl
FROM tblnames
MINUS
select distinct object_name
from user_objects
where object_type = 'TABLE';
does not yield the missing table as I expect...
I hope there is a solution to this neverthless I've been helped by inputs u already provided !

Thanks / areza123@yahoo.com