RE: Privileges by session

From: "Blanchard, William" <wblanchard@xxxxxxxxxxxxxxxxxxxx>
To: "Kellyn Pedersen" <kjped1313@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
Date: Thu, 7 Jan 2010 15:20:31 -0600
That's pretty much what I'm doing.  I created a DDL trigger to log all
of the DDL the developers were running.  You should see their face when
you walk to their cube and say "I just noticed you did...".  I created
another trigger in test now as a precursor to locking down production in
which I write to a log table everytime they log in.  I will test it for
a while and then surprise them with a lockout trigger.
 
Let me know if you see any flaws in my trigger so far.
 
CREATE OR REPLACE TRIGGER logon_privs
AFTER LOGON ON DATABASE
BEGIN
  IF((SUBSTR(sys_context('USERENV','HOST'),1,7) = 'XXX\IS-'
      OR SUBSTR(sys_context('USERENV','HOST'),1,3) = 'IS-')
    AND sys_context('USERENV','OS_USER') NOT IN ('xxx','xxx')) THEN
    -- For now, just log the event.  Eventually this trigger will change
    -- the role for the session to read_only.
    --EXECUTE IMMEDIATE 
    INSERT INTO loguser.ddl_log
        (user_name, ddl_date, ddl_type,
        object_type, owner,
        object_name, text,
        os_user, current_user,
        host, terminal)
    VALUES (ora_login_user, LOCALTIMESTAMP, 'DEV LOGON',
        NULL, NULL,
        NULL, NULL,
        sys_context('USERENV','OS_USER'),
        sys_context('USERENV','CURRENT_USER'),
        sys_context('USERENV','HOST'),
        sys_context('USERENV','TERMINAL'));
  END IF;
END logon_privs;
/
 
WGB


  _____  

From: Kellyn Pedersen [mailto:kjped1313@xxxxxxxxx] 
Sent: Thursday, January 07, 2010 3:16 PM
To: oracle-l@xxxxxxxxxxxxx; Blanchard, William
Subject: Re: Privileges by session


OK, maybe I'm just evil, but I've gone through this at the last company
I was lead DBA at.  I was hired with the intention of having me lock
down the environment, (they didn't break that to me until a couple weeks
in, to be honest...:))  
I actually started auditing the databases with my own scripts, tracking
the osuser/vs. logins and started to report them, along with the risks.
It's not fun and you aren't most people's favorite person, but most
developers actually started to appreciate it by the end of my tenure
there.
 
Not sure if it will work, but you may want to look into different authid
options:
http://www.adp-gmbh.ch/ora/plsql/authid.html
 
I have always used them with specific user logins, but you may be able
to pull the rights right out from under them at the session level this
way, too!
 
Good luck,


Kellyn Pedersen
Multi-Platform DBA
I-Behavior Inc.
http://www.linkedin.com/in/kellynpedersen
 
"Go away before I replace you with a very small and efficient shell
script..."


--- On Thu, 1/7/10, Blanchard, William <wblanchard@xxxxxxxxxxxxxxxxxxxx>
wrote:



        From: Blanchard, William <wblanchard@xxxxxxxxxxxxxxxxxxxx>
        Subject: Privileges by session
        To: oracle-l@xxxxxxxxxxxxx
        Date: Thursday, January 7, 2010, 1:21 PM
        
        
        Greetings, 
        I have convinced management to allow me to grant read-only
access to the developers.  The problem is that they know the application
passwords and have been using those passwords to circumvent my controls.
Is there a way via a trigger, role, etc to change individual sessions
privileges so they have read only (select) permissions?  The easiest way
would be to change the permissions on the applications but that's not an
option.
        Thank you, 
        WGB 
        -
        
        This email and any information, files, or materials transmitted
with it
        are confidential and are solely for the use of the intended
recipient.
        If you have received this email in error, please delete it and
notify
        the sender.
References:
- Re: Privileges by session
  - From: Kellyn Pedersen
RE: Privileges by session

Other related posts:
