0

I am trying to grant EXECUTE and READ privilege on two tables to a user ktest1.

grant EXECUTE on SYS.KIR_DOKUMENT to ktest2;
grant READ on SYS.KIR_DOKUMENT to ktest2;

when i give grant execute i get below error

grant EXECUTE on SYS.KIR_DOKUMENT to ktest2
Error report -
ORA-04042: procedure, function, package, or package body does not exist
04042. 00000 -  "procedure, function, package, or package body does not exist"

when i give grant read i get below error

grant READ on SYS.KIR_DOKUMENT to ktest2
Error report -
ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:    

But i checked the status of the table in dba_objects and they are valid.

the type here is directory. enter image description here

Kindly advice.

  • Post the output that shows SYS.KIR_DOKUMENT is an existing object and shows its type. At this point the problem could be a case or type mismatch. – Balazs Papp Jun 15 at 13:23
  • @BalazsPapp - I don't see proof that SYS owns the object. Much less if it is Case Sensitive. I'm not even sure if everything is in the same database (CDB/PDB). – Michael Kutz Jun 15 at 13:57
  • Cross-posting on multiple sites is discouraged. In this case this might have been the more appropriate site; but in future please pick one – Alex Poole Jun 15 at 14:23
  • @MichaelKutz Neither did I, that is why I asked for the output. I suspected that KIR_DOKUMENT was a directory object (type mismatch) or defined as case-sensitive. Turns out it was indeed a directory object. – Balazs Papp Jun 15 at 14:25
  • The image does not look like it is from DBA_OBJECTS. Please post proof that the Object exists in the real DBA_OBJECTS view. Please post proof of who is performing the GRANT (eg screen shot of SELECT USER FROM DUAL and attempted GRANT) – Michael Kutz Jun 15 at 14:31
3

That is not how you grant privileges on a directory.

SQL> create or replace directory KIR_DOKUMENT as '/tmp';

Directory created.

SQL> select owner, object_type, status from dba_objects where object_name = 'KIR_DOKUMENT';

OWNER
--------------------------------------------------------------------------------
OBJECT_TYPE             STATUS
----------------------- -------
SYS
DIRECTORY               VALID


SQL> grant read on SYS.KIR_DOKUMENT to bp;
grant read on SYS.KIR_DOKUMENT to bp
                  *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> grant execute on SYS.KIR_DOKUMENT to bp;
grant execute on SYS.KIR_DOKUMENT to bp
                     *
ERROR at line 1:
ORA-04042: procedure, function, package, or package body does not exist

Use the GRANT privilege ON DIRECTORY ... as:

SQL> grant read on directory SYS.KIR_DOKUMENT to bp;

Grant succeeded.

SQL> grant execute on directory SYS.KIR_DOKUMENT to bp;

Grant succeeded.

SQL>
| improve this answer | |
0

Is KIR_DOKUMENT a part of the data dictionary or did someone at your company create it? I assume that this is something that was written at your company. When the package gets executed, it is probably not using sys privileges. You probably have it set up to be run with invoker rights. If that is the case then ktest2 needs rights to do everything that the package is trying to do and it doesn't have select rights on at least one table in the KIR_DOCUMENT package. When asking a question you should provide more information like: version of Oracle, OS, 32/64 bit, and if this is a package any special options that the package uses. Options like invokers rights.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.