RE: Privileges by session
Date: Thu, 7 Jan 2010 14:47:35 -0600
Message-ID: <CB340D772D072D47A5DE07533432A7E50E2E797A_at_exch1.soc.int>
I thought about this as well but I'm afraid it won't work here because of how the applications are coded... seriously, why would you code drops and creates to run a report? How about a temp table or even pl/sql. <steps off soapbox>
WGB
-----Original Message-----
From: Michael Fontana [mailto:michael.fontana_at_enkitec.com]
Sent: Thursday, January 07, 2010 2:45 PM
To: Blanchard, William
Subject: Re: Privileges by session
I have been in this position many times. It is a very desirable but often unattainable goal, especially when the behavior has been imbedded in an organization for some time.
It's like getting invited to the party on the condition that you are to tell all the participants that it's over, they should collect their belongings, and immediately head toward the exit. You will not be very popular!
The only way to accomplish the end goal is to create a readonly account that can view all of the objects in the owning schema, give everyone involved a chance to verify that it is working, and then announce a sunset date when you will change the password to the owning schema.
The developers will immmdiately come back and say that bad things will happen when you do that. Scripts and processes will fail, app servers will cease to function, and generally all hell will break loose. You need to be prepared to turn it back around on them, and ask how, from a security perspective, they cannot be ready, at a moment's notice, to support a database security initiative (I hope you really have your management's back, because this is the point where you'll really find out). If they do say these things, change the password on a development or test system first (with their knowledge), and assess it's impact. You and they might be surprised at the results.
In many cases, the developers management will get your initiative postponed. While they will agree it's important, they will claim that business change agenda items are of higher priority. Then, in about 3-4 years, you will be trying to get this task's priority raised from #127 or thereabouts....
- Original Message ----- From: "William Blanchard" <wblanchard_at_societyinsurance.com> To: oracle-l_at_freelists.org Sent: Thursday, January 7, 2010 2:21:47 PM GMT -06:00 US/Canada Central Subject: Privileges by session
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.
-- Michael Fontana Sr. Technical Consultant Enkitec M: 214.912.3709 enkitec oracle_certified_partner -- http://www.freelists.org/webpage/oracle-lReceived on Thu Jan 07 2010 - 14:47:35 CST