Keep getting "Insufficient Oracle privileges for Applications Manager User" error

Products

• CA Automic Applications Manager (AM)

Issue/Introduction

After providing the Oracle User the correct grants, the installation keeps throwing the below error:

ERROR at line 1:
ORA-20000: Access Error   ORA-20000: Access Error
ORA-06512: at line 140   ORA-06512: at line 140

Insufficient Oracle privileges for Applications Manager User
Oracle Sys Password to add privileges              [] ?

Entering the sys password results in the same error.

Environment

Applications Manager 9.4 and above

Cause

One or more of the Oracle user's grants may not have SYS as the grantor

Resolution

If the required grants have been given to the Oracle User, it is likely that the GRANTOR for one or more grants is not the required value of SYS, but another value such e.g. SYSTEM, etc.

To check your grants, including the granter value, run the below sql and replace <oracle user> with Applications Manager Oracle user value:

SELECT 
    p.PRIVILEGE, 
    p.ADMIN_OPTION, 
    p.GRANTEE, 
    p.GRANTOR, 
    p.OBJECT_OWNER, 
    p.OBJECT_TYPE, 
    p.OBJECT_NAME, 
    p.GRANT_TYPE
FROM (
    SELECT 
        tp.PRIVILEGE, 
        tp.GRANTABLE AS ADMIN_OPTION, 
        tp.GRANTEE, 
        tp.GRANTOR, 
        tp.OWNER AS OBJECT_OWNER, 
        (SELECT OBJECT_TYPE FROM DBA_OBJECTS 
         WHERE OWNER = tp.OWNER AND OBJECT_NAME = tp.TABLE_NAME AND ROWNUM = 1) AS OBJECT_TYPE,
        tp.TABLE_NAME AS OBJECT_NAME,
        'OBJECT' AS GRANT_TYPE
    FROM DBA_TAB_PRIVS tp
    
    UNION ALL

    SELECT 
        sp.PRIVILEGE, 
        sp.ADMIN_OPTION, 
        sp.GRANTEE, 
        'SYS' AS GRANTOR, 
        NULL AS OBJECT_OWNER, 
        'SYSTEM PRIVILEGE' AS OBJECT_TYPE,
        NULL AS OBJECT_NAME, 
        'SYSTEM' AS GRANT_TYPE
    FROM DBA_SYS_PRIVS sp
) p
WHERE p.GRANTEE = UPPER('<oracle user>')
AND (
    p.PRIVILEGE IN ('CREATE VIEW', 'CREATE PROCEDURE', 'CREATE TRIGGER', 'CREATE TABLE', 
                   'CREATE DATABASE LINK', 'ALTER SESSION', 'CREATE SYNONYM', 'EXECUTE', 'SELECT')
    AND (
        p.OBJECT_NAME IN ('V_$SESSION', 'V_$LOCK', 'V_$LOCKED_OBJECT', 'V_$SQLTEXT', 
                         'V_$SQLTEXT_WITH_NEWLINES', 'DBMS_SQL', 'DBMS_LOCK', 
                         'DBMS_OUTPUT', 'DBMS_AQ', 'DBMS_AQADM')
        OR p.GRANT_TYPE = 'SYSTEM'
    )
)
ORDER BY p.GRANT_TYPE, p.PRIVILEGE;

If there grantor value for one or more rows does not return the value SYS, said grant needs to be updated/regranted with the value SYS.