DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Security violations

  1. #1
    Join Date
    Dec 2001
    Posts
    96

    Security violations

    Hi Friends,

    I need a lit help. Is there a way to track users who are not authorised to access a table or procedure etc ?
    if so, how to do that. I need to keep a track of users. is there a procedure or something like that which can help me with that ?

    Thanks

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    If you don't have permissions to access a particular table or procedure, then you can't access it. Simple
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Aug 2002
    Location
    Sofia, Bulgaria
    Posts
    84
    Can you explain in detail what do you want to do. Do you want to restrict access to some objects or you want to audit some actions for some users without restricting access to objects for these users
    Radoslav Rusinov
    OCP 8i,9i,10g DBA
    http://dba-blog.blogspot.com

  4. #4
    Join Date
    Dec 2001
    Posts
    96
    Thanks for your replies.

    I want to report or entries in a table to check which unauthorised user is trying to access database objects.

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    There's an example of creating server error triggers here ... http://asktom.oracle.com/pls/ask/f?p...:4018285967344
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    May 2003
    Location
    Pretoria, Rep of South Africa
    Posts
    191
    Enable audit parameters and use this as example

    AUDIT SESSION BY HR
    AUDIT DELETE ON hr.EMPLOYEES by access
    AUDIT UPDATE ON hr.EMPLOYEES by access
    AUDIT INSERT ON hr.EMPLOYEES by access

    AUDIT SESSION BY access
    AUDIT DELETE ON hr.EMPLOYEES by access whenever successful
    AUDIT UPDATE ON hr.EMPLOYEES by access whenever successful
    AUDIT INSERT ON hr.EMPLOYEES by access whenever successful

    or use ...whenever unsuccessful...i think

    ok?
    Able was I ere I saw Elba

  7. #7
    Join Date
    Dec 2001
    Posts
    96
    Thanks a lot to all for your replies

  8. #8
    Join Date
    Dec 2001
    Posts
    96
    OOps, i put this in wrong place earlier, my mistake, sorry.

    here it is...

    Create a table for auditing the illegal access.
    -------------------------------

    I created this table in SCOTT schema.

    CREATE TABLE LOGONAUDITTABLE (
    EVENT VARCHAR2 (10),
    TIMESTAMP DATE,
    SCHEMA VARCHAR2 (30),
    OSUSERID VARCHAR2 (30),
    username varchar2(30),
    MACHINENAME VARCHAR2 (64),
    SID NUMBER,
    SERIAL# NUMBER,
    PROGRAM VARCHAR2 (100));


    Create the following trigger on the scott schema.

    CREATE OR REPLACE TRIGGER logonauditing
    AFTER LOGON ON scott.SCHEMA
    DECLARE
    machinename VARCHAR2(64);
    osuserid VARCHAR2(30);
    username varchar2(30);
    sid NUMBER;
    serial# NUMBER;
    program VARCHAR2(100);
    CURSOR c1 IS
    SELECT osuser, username,machine , sid , serial# , program
    FROM v$session WHERE audsid = USERENV( 'sessionid' );
    BEGIN
    --DELETE LOGONAUDITTABLE;
    --COMMIT;
    OPEN c1;
    FETCH c1 INTO osuserid,username, machinename, sid , serial# , program ;

    INSERT INTO LOGONAUDITTABLE VALUES ( 'LOGON', SYSDATE,
    USER, osuserid, username,machinename , sid , serial#, program);

    CLOSE c1;
    COMMIT;
    --dbms_job.isubmit(12345,'sys.killsession;',SYSDATE);
    END;

    i created a user TEST and tried to access scott.test table.

    then i went to scott, and selected from logonaudittable.

    select * from logonaudittable;

    EVENT TIMESTAMP SCHEMA OSUSERID USERNAME MACHINENAME SID SERIAL# PROGRAM
    ---------- --------- ------------------------------ --------------------------------------------------------------------------------
    LOGON 09-SEP-04 SCOTT oracle SCOTT braves 11 19 sqlplus@braves (TNS V1-V3)

    I see only name scott, i wanted to see the username,osuerid of TEST who is not authorised to the objects of SCOTT schema, i cannot see this entry in the above table, what is that i am doing wrong, is there a way to track unauthorised users ?

    thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width