RE: Privileges by session

  • From: "Barun, Vlado" <Vlado.Barun@xxxxxxx>
  • To: "kjped1313@xxxxxxxxx" <kjped1313@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>, "wblanchard@xxxxxxxxxxxxxxxxxxxx" <wblanchard@xxxxxxxxxxxxxxxxxxxx>
  • Date: Fri, 8 Jan 2010 10:51:23 -0500

I was in the exact same position, with management agreeing that developers 
should not be using application logins and that they should only have read-only 
access.

The way I resolved the issue was to enable auditing and created a job that 
sends an email every 4 hours with a list of users that broke that policy.
Initially, the email was just going to me and I would personally remind the 
developers for the reasoning behind the policy, that their management approved 
it and why they were continuing to ignore the policy. That took care of most 
developers.

For those few that continued to ignore the policy, I setup a meeting with the 
director responsible for security in the company, the developers and their 
management. That helped changed the attitude of the developers and also forced 
management to be more serious about (not just talk the walk, but also walk the 
walk :)).
After that, I just had the email be send to the security person directly and I 
don't even have to worry about it anymore.

Of course, the above is more of a process/policy/mindset kind of approach and 
does not guarantee that a policy violation will not occur. You could use a 
logon trigger to prevent a developer to logon with an application account, by 
checking the appropriate combination of IP, osuser, username, process, machine, 
etc. But even that would not guarantee 100% policy adherence, as any good 
developer who is also an admin of the application can misuse the application to 
execute ad-hoc queries.

Vlado Barun, M.Sc., OCE, OCA, MCP
Sr. Database Architect/Manager, Database Engineering and Operations
Jewelry Television

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Kellyn Pedersen
Sent: Thursday, January 07, 2010 4:16 PM
To: oracle-l@xxxxxxxxxxxxx; wblanchard@xxxxxxxxxxxxxxxxxxxx
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.

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 01-2010