0

When I am try to access a package from normal user/schema (non-sys user) I am getting below error:

/* ERROR WHILE READING SOURCE
SQL Error [942] [42000]: ORA-00942: table or view does not exist
*/

enter image description here

When accessing this package from SYS user/schema it looks fine.

Probably there are some permissions which are missing for non-sys user.

Can anyone please help me and let me know what needs to be done to resolve this?

I tried checking the permissions of non-sys user using below queries to check if there are any permissions specific to oracle packages:

SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'USER2';
SELECT * FROM USER_ROLE_PRIVS WHERE USERNAME = 'USER2';
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = 'USER2';

But couldn't find anything specific for packages, it seems that this is the right way maybe.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
Rudra
  • 1
  • 1
  • 2
    What do you mean by 'access' - are you trying to look at the source code in say `dba_source`? If so it's *that* view that's throwing the error, not the package privileges. Or are you trying to call the package? What client/tool are you using and exactly what are you doing? – Alex Poole May 23 '23 at 07:44
  • By access I mean that I am simply trying to open it to see what is there inside package declaration and body. I just want to see the source code. I am using DBeaver tool. Haven't checked yet if I execute it or not. At first, I want to see what the source code of it is. – Rudra May 23 '23 at 07:55
  • I don't know if DBeaver looks as `dba_source` or `all_source`. If you grant execute permission on the package to your non-SYS user then it ought to be able to see the source code through `all_source`. But the error suggests it's actually looking at `dba_source`, which would mean your user would need extra privileges to see that view. I'm not sure why you'd really want someone who's supposed to call the procedure to be able to see the source, that isn't usually helpful (and can be a risk). Which schema have you created your package in - hopefully you aren't creating your own objects under SYS? – Alex Poole May 23 '23 at 08:11
  • It sounds like [this bug](https://github.com/dbeaver/dbeaver/issues/726), but that's from 2016... are you on a recent version of DBeaver? Also [this](https://github.com/dbeaver/dbeaver/issues/9649) shows an 'always use DBA_\* views' option, do you have that ticked? – Alex Poole May 23 '23 at 08:36
  • I have just tried executing this procedure and found that I can execute it successfully, but I just can't view it. I have created my package inside non-sys user, not created it inside SYS user. Dbeaver which I am using is old version and it is of 2016. I will check above bug which you have shared, let's see if this solves the problem. – Rudra May 23 '23 at 09:15
  • I just updated the DBeaver version, and it solved the problem. May be this issue is there for old versions. Thank you so much for your help. – Rudra May 23 '23 at 09:28

0 Answers0