Re: AUTHID Change Script
- From: William Robertson <william@xxxxxxxxxxxxxxxxxxxx>
- To: mark.powell2@xxxxxxx
- Date: Thu, 14 Apr 2016 17:32:31 +0100
Yes, exactly.
I suppose there might be a case where your role had INSERT privilege on some
table but not UPDATE or DELETE (for example). Then if you called employee.fire
it would fail, but you could still use employee.hire.
Of course, now you can insert anything you like into that table instead of
being restricted to the supplied interface, so it's still less secure and
robust than it was under definer rights.
On 14 Apr 2016, at 17:10, Powell, Mark <mark.powell2@xxxxxxx> wrote:
The justification is that if someone gets into the database they will be
able to do anything the package can do as long as they can execute it <<
You need permission to execute a package and since the package executes as the
owner using the owner and not your permissions you cannot do anything that the
package does not provide access to via a public procedure or function. That
is, you do not have the ability to perform DML contained within the package
outside the pack unless you get that privilege from a role or direct grants
issued to your id in which case access to the package is a moot point.
On the other hand if the package runs as the current user then the current user
has to have the necessary object permissions so if the current user can connect
outside of the application they then have all the privileges the package
requires and can perform the same DML as in the package directly. This is a
lot less secure than only have execute to a definer rights package.
From: Lange, Kevin G [
mailto:kevin.lange@xxxxxxxxx] ;
Sent: Thursday, April 14, 2016 11:44 AM
To: niall.litchfield@xxxxxxxxx
Cc: Oracle-L@xxxxxxxxxxxxx; Powell, Mark <mark.powell2@xxxxxxx>
Subject: RE: AUTHID Change Script
The justification is that if someone gets into the database they will be able
to do anything the package can do as long as they can execute it. If all sql
is written for AUTHID current_user, they could only do what the ID they are on
has access to do.
We were a legacy app that has been outside their rulings for a while and now
that its being upgraded the word is this exception to their rules must be
removed before you can upgrade.
We wrote a script to do the changes last night. And now, we are starting on
the testing run to see if/what broke.
Ce La Vi
From: oracle-l-bounce@xxxxxxxxxxxxx [
mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Niall Litchfield
Sent: Tuesday, April 12, 2016 1:59 PM
To: Lange, Kevin G
Cc: Oracle-L@xxxxxxxxxxxxx; mark.powell2@xxxxxxx
Subject: RE: AUTHID Change Script
Does it come with an accompanying justification. It's a large effort that
*will* break things. I'd be astonished to find much pl/sql that assumed objects
reside in the executing users schema. Why would you even want that?
On 12 Apr 2016 16:45, "Lange, Kevin G" <kevin.lange@xxxxxxxxx> wrote:
Good idea or not, it’s the message from on high that DEFINER is not allowed.
But, I do think it’s a good idea for security. We have most security based on
roles so the user just needs the appropriate roles to be able to run the
procedures correctly. Yes, we will find issues that have to be corrected, but
, overall, we have been leaning towards this already so users should already
have the appropriate roles.
I was just trying to make the coding as painless as possible.
From: oracle-l-bounce@xxxxxxxxxxxxx [
mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Powell, Mark
Sent: Monday, April 11, 2016 10:16 AM
To: Oracle-L@xxxxxxxxxxxxx
Subject: RE: AUTHID Change Script
Kevin, are you sure this is a good idea? We write pretty much all our stored
code to run as DEFINER because the owner’s privileges are what are needed for
the code to work correctly. Unless the stored code was initially designed to
run as the current user changing the code may result in breaking it. Have all
the necessary privileges for the code to run as the current user been
identified and issued?
From: oracle-l-bounce@xxxxxxxxxxxxx [
mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Lange, Kevin G
Sent: Friday, April 08, 2016 6:23 PM
To: Oracle-L@xxxxxxxxxxxxx
Subject: AUTHID Change Script
Evening all;
I was wondering if anyone out here might already have a script to change the
authid from definer to current_user on all Procedures, Functions, types, and
packages that exist in a database with an AUTHID of definer?
We find that we have a number of these in multiple databases and instead of
opening them up by hand and editing every one of them, I was hoping for an
existing script/procedure that could to that for us.
Oracle Version: 10.2.0.4
Appreciate any help.
Kevin
Other related posts: