Oracle – How to Resolve ORA-04042 and ORA-00942 Errors

dynamic-sqloracleoracle-12cpermissions

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.

Best Answer

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>
Related Question