Search code examples
sqloracle-databasedatabase-administrationprivilegessql-grant

How to resolve ORA-04042 and ORA-00942 errors when granting privileges?


I am trying to grant EXECUTE and READ privilege to a user.

When I grant execute I get:

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 grant read I get:

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.

Query

select owner, object_name, object_type from dba_objects where object_name

gives this output:

dba_objects query output

The same grants have to given to another user in my database:

dba_object_privs output for another object

How can I resolve these errors?


Solution

  • According to your question edit, the object is a directory; so you have to include the DIRECTORY keyword:

    grant READ, EXECUTE on DIRECTORY KIR_DOKUMENT to ktest2;
    

    The documentation says (emphasis added):

    The on_object_clause identifies the object on which the privileges are granted. Users, directory objects, editions, data mining models, Java source and resource schema objects, and SQL translation profiles are identified separately because they reside in separate namespaces.)

    and

    ON DIRECTORY

    Specify the name of the directory object on which privileges are to be granted. You cannot qualify directory_name with a schema name.

    and also gives an example.