Upgrade to SP8 gives error The user should have CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, CREATE PROCEDURE authority

Products

• CA Workload Automation AE

Issue/Introduction

The CA Workload Automation AE instance upgrade process requires the following information.

Instance Name:                 ACE

Oracle Service ID:             ORCL.....................................................................................................................................................[<]

Oracle Home Directory:         /opt/oraClient/ââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââ................................................... [ ... ]

                                                           âThe user should have CREATE SESSION, CREATE TABLE, CREATE VIEW,       â

TNS_ADMIN Directory:           /opt/CA/WorkloadAutomationAEâCREATE SEQUENCE, CREATE PROCEDURE authority                           â.................................................... [ ... ]

                                                           â                                                                      â

Database Host:                 dbhostnameâ                                                                      â............................................................

                                                           â[ OK ]  [ Abort installation ]                                        â

Database TCP Port:             1521........................â                                                                      â............................................................

                                                           ââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââ

'aedbadmin' DB User Password:  ********........................................................................................................................................................

 

 

 

Environment

Release : 11.3.6

Component : CA Workload Automation AE (AutoSys)

Cause

aedbadmin is missing necessary privileges

 

When wa_setup.sh is run, it extracts the installer files to /tmp/LSM*** folder and  further below like this:  /tmp/LSM*****/CAWorkloadAutomationAE.d/$ASI_AUTOSYS_INSTDIR/as_common.sh 

 

It is the as_common.sh script that checks for below privileges and roles. If any privileges are missing, it errors out

`$SQLCMD <<END

SPOOL /tmp/sessionprivs.txt

SELECT * FROM SESSION_PRIVS;

SPOOL OFF;

quit;

END`

if [ ! -f /tmp/sessionprivs.txt ]; then

    exit 133

fi

SPFILE=/tmp/sessionprivs.txt

if [ -z "`grep 'CREATE SESSION' ${SPFILE}`" -o -z "`grep 'CREATE TABLE' ${SPFILE}`" -o -z "`grep 'CREATE VIEW' ${SPFILE}`" -o -z "`grep 'CREATE SEQUENCE' ${SPFILE}`" -o -z "`grep 'CREATE PROCEDURE' ${SPFILE}`" ]; then

    rm -rf /tmp/sessionprivs.txt

    exit 128

fi

rm -rf /tmp/sessionprivs.txt

 

`$SQLCMD <<END

SPOOL /tmp/sessionroles.txt

SELECT * FROM SESSION_ROLES;

SPOOL OFF;

quit;

END`

if [ ! -f /tmp/sessionroles.txt ]; then

    exit 133

fi

SRFile="/tmp/sessionroles.txt"

if [ -z "`grep UJOADMIN ${SRFile}`" ];

then

    rm -rf /tmp/sessionroles.txt

    exit 129

fi

rm -rf /tmp/sessionroles.txt

}

Resolution

provide appropriate missing privileges (create session/table/view/sequence/procedure)  to the aedbadmin account and re-run the wa_setup.sh script again.