Search code examples
sqloracle-databasestored-proceduresplsqloracle-ebs

What privileges could be missing in the case of granted permission to call a package that is calling a package?

I can unfortunately not provide any code, but I will try to explain as best as I can and will provide additional information as necessary.

There are 3 different schemas at play in this scenario: X_SCHM, APPS, and HR

APPS has a package called X_PKG to run some functionality for a resource, and X_PKG also makes a call to a procedure in package HR_EMPLOYEE_API, owned by HR schema. APPS grants execute privileges on X_PKG to X_SCHM, and X_SCHM can successfully call procedures inside X_PKG.

However, I want to move as much away from APPS and into X_SCHM, so the package body of X_PKG is copied over to a new X2_PKG in X_SCHM. X2_PKG still needs to call a procedure in HR_EMPLOYEE_API, so a grant is given to X_SCHM to execute that package.

Now when X_SCHM tries to call X2_PKG, which is by all accounts identical to X_PKG owned by APPS, it successfully enters HR_EMPLOYEE_API owned by HR and then starts running into "table or view does not exist" errors inside of it, a problem that APPS or X_SCHM running X_PKG owned by APPS does not run into.

I'm not sure if this is an issue of additional grants needing to be made. I would think since X_SCHM has execute privilege on HR_EMPLOYEE_API that procedures it calls from HR_EMPLOYEE_API would be able to access tables owned by HR, unless there's information I'm missing regarding packages needing their own granted privileges separate from the schemas that own them.

Please let me know where I can be clearer or provide more information to get this issue solved.

Solution

  • The package HR_EMPLOYEE_API is defined as AUTHID CURRENT_USER. This means that all the code inside the package is executed as the user who is calling it. So basically you have 3 choices :

    1) You leave the package in the APPS User as suggested by @Sudipta Mondal. It sounds like the safest choice.

    2) If X2_PKG is defined as AUTHID DEFINER, you could try to copy all the direct rights from APPS to X_SCHM and hope for the best. But it's really not that good because i'm quite sure that APPS has a lot of rights. Luckily you don't have to bother with permissions obtained through roles as they don't apply in packages. try something like that

    select 'GRANT '||PRIVILEGE||' ON '||OWNER||'.'||TABLE_NAME||' TO X_SCHM;' FROM DBA_TAB_PRIVS WHERE GRANTEE = 'APPS';

    3) Try to figure out which rights to add either by trial and error or by fetching them from DBA_DEPENDENCIES if they are referenced :

    select REFERENCED_OWNER, REFERENCED_TYPE, REFERENCED_NAME from dba_dependencies where NAME = 'HR_EMPLOYEE_API';

    I, personally, agree with Sudipta Mondal, you should probably reconsider moving it if not mandatory.