-1

Suppose you have an Oracle user "A" that needs EXECUTE privilege on a set of packages. From time to time, a different user "B" creates a new package that needs to be executable by "A". As such, and as part of the package creation process, "B" must be able to grant EXECUTE privilege to "A" on the new package. (Note: "A" could be a user or a role). It is important that "B" will ONLY be able to grant EXECUTE privilege to "A" on the new package, and will NOT be able to grant any other privilege to any other user. Moreover, any new such package would display a well defined pattern in its name, for instance "ABCD_PACK_?????????". Would it be possible to grant "B" the GRANT privilege to "A" solely on packages whose name start with "ABDC_PACK"? Thanks.

FDavidov
  • 3,505
  • 6
  • 23
  • 59
  • Would you mind formatting your question a bit better? Thanks! – sstan Jul 12 '15 at 06:06
  • 1
    Why isn't B just granting necessary privileges as it creates its new package? If that's a controlled process (source control, review etc) then I'm not sure what the problem is. B doesn't need any additional privileges to be able to grant execute on its package to A, but that also means it can't be restricted. It may be possible to use [a DDL trigger](http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/create_trigger.htm#CIHGCJHC) to check the grant but that seems like overkill; depends if this requirement really makes sense. – Alex Poole Jul 12 '15 at 10:10
  • Alex, we are talking about an environment that must comply with the highest possible security requirements, meaning, _you would want to have each and every Oracle user just the exact privileges it needs to function, and nothing more_. As such, "B" should be able to **GRANT** only the specific privilege to the specific user. – FDavidov Jul 12 '15 at 12:10
  • 1
    For security the B account should be locked then, and only approved changes made (including package build and grants) during a maintenance window where it is unlocked, or modified by a DBA account. Trying to restrict ad hoc changes at permission level seems complicated and error prone, and thus potentially insecure. You're only guarding against the things you've though of. – Alex Poole Jul 12 '15 at 13:23
  • Alex, thanks for your comment. What I'm looking for is anything but ad-hoc. As explained, my need is to grant **B** the ability to grant EXECUTE privilege to a well-defined user on a (almost) well defined package (i.e. whose name looks like "ABCD_PACK_??????"), and that would be the ONLY thing **B** can GRANT. After some additional research, it would appear that what I wish to do cannot be done without special mechanisms (e.g. like the trigger you mentioned). Pity... – FDavidov Jul 14 '15 at 06:21

1 Answers1

0

It looks that there is no simple way to defined a sort of CUSTOM GRANT to a user, and the only way to achieve what I was looking for is to implement special code that would OPEN B's privilege, and then CLOSE it once the grant was done. This approach, however, does not resolve in a comprehensive way the issue of security. It would be nice if ORACLE would consider enhancing the security capabilities of its DB, specially in view of the tightening requirements of the market.

FDavidov
  • 3,505
  • 6
  • 23
  • 59