grant compile to other users package gives insufficient privilages

I have 2 users..U1 and U2………. U1 contains some packages… I want U2 to compile U1 packages……. I have given Execute and debug grants to U2 but it says insufficient privilages and I can not give ‘alter any procedure’ grant as it will have access to compile any package which will be security issue.Do anyone have perfect solution on this? as i have tried almost all suggestions mentioned in the blogs.

Here, the other side of my requirement ‘why owner should not compile procedure’ is

We have U1 (example) user/schema which has all objects(table/package/proc/function) and currently everyone is logging in with U1 and doing their tasks.

So this make complete sense by logging in as owner and compile for any changes in that proc.

But the actual reason is we have audit requirements where every user should login by their own userid say U2/U3/U4/%…so on

Now those all users wants to access U1 users objects (table/package/proc/function)

So even though they are doing same task what they were doing earlier as owner ….now they are doing as separate users…so everything on their account is audited.

I know in oracle we do have many options to audit by tracing machine/terminal name even though there is only one user but still this is our audit requirement.

So any changes in table or proc may need to compile that object and every separate user should do that.

As UTLRP will do all DB objects compile which is not needed.

And at the same time tose users should not have access to any other schema objects too apart from U1

Please suggest…!!

Click to visit the source...

We're happy to share this resource that we found. The content displayed on this page is property of it's original author and/or their organization.