Oracle DBMS_CRYPTO – How to Grant User Access in Oracle 11g R2

oracleoracle-11g-r2

Couldn't find user

I tried this

grant execute on sys.dbms_crypto to "DB_Project";

But error: ORA-00942: table or view does not exist
00942. 00000 – "table or view does not exist"

Also tried this:

grant execute on sys.dbms_crypto to SYSTEM."DB_Project";

ERROR: 00000 – "SQL command not properly ended"

How to solve???

Best Answer

That error is caused by granting a privilege connected as SYSTEM.

If you're connected as SYS, it'll work just fine:

grant execute on dbms_crypto to "DB_Project";

(if user is really created using mixed case; otherwise, remove double quotes).

Related Solutions

Oracle – Table Does Not Exist Error

If you do intend on working with only in-memory PL/SQL type-based tables, this is what you need to do:

CREATE OR REPLACE PROCEDURE ProcedureX
AS
    TYPE AVERAGE_COUNT_TBL IS TABLE OF NUMBER;
    AVERAGE_COUNT AVERAGE_COUNT_TBL;
    CURSOR C IS SELECT 10 FROM DUAL;
BEGIN
    OPEN C;
    FETCH C BULK COLLECT INTO AVERAGE_COUNT;
    CLOSE C;
END ProcedureX;
/

The cursor is just used to demonstrate.

I'll add that your question is poor, as you didn't even take the time to make sure that AVERAGE_COUNT_TBL matched with itself on the next row - you used AVG_TRANS_HOUR_TBL instead. If you expect random internet people to spend time writing quality answers, would it hurt to make sure your question was of sufficient quality?

How to fix these broken synonyms

First of all, DBA_SYNONYMS.SYNONYM_NAME is the name of the synonym. DBA_SYNONYMS.TABLE_NAME is the name of the object the synonym points to. In your query, it is not the synonym that is INVALID, but the object that it points to. It is not the synonym that needs to be fixed, but the object.

If I try to recompile or drop these:

ALTER PUBLIC SYNONYM GV_$LOADPSTAT COMPILE;

It says:

SQL Error: ORA-01031: insufficient privileges

01031. 00000 - "insufficient privileges"

How could this be so? I'm logged in as SYSTEM.

It is a bug on your version.

If I try to drop it:

DROP PUBLIC SYNONYM GV_$LOADPSTAT; It says:

SQL Error: ORA-01432: public synonym to be dropped does not exist

01432. 00000 - "public synonym to be dropped does not exist"

How can it not exist when the query result shows it?

Then name of the synonym is GV$LOADPSTAT. You are trying to drop the object (view) here, called: GV_$LOADPSTAT. And that view is owned by SYS, not SYSTEM.

Where you see MISSING, the synonyms points to a non-existent object. Replace the object or drop the synonym.

Best Answer

Related Solutions

Oracle – Table Does Not Exist Error

How to fix these broken synonyms

Related Question