Error: 'ORA-23322: Insufficient privileges to access pipe' received by the Password Listener process (NETIQKB30503)

fact
VigilEnt Password Manager for Oracle 6.x

fact
VigilEnt Security Agent for Oracle 1.x

symptom
Error: 'ORA-23322: Insufficient privileges to access pipe' received by the Password Listener process.

cause

There are two possible causes that will produce this error:

• The SQLCQR user needs the EXECUTE privilege on the DBMS_PIPE package.
• An invalid DBMS_PIPE already exists with the same name.

fix

VigilEnt Password Manager for Oracle product uses internal Oracle processes to do some of the work for password changes. The Oracle JOBS and PIPE packages are used to process information and communicate between the application and the database. These internal processes have restrictions when being used. For example the only user that can start or stop an Oracle JOB is the user who has created it. There are a couple of reasons Oracle will generate the error "ORA-23322: Insufficient privilege to access pipe". In both cases below the error message would be seen when stopping or starting the Password Listener process (Pwd Listener) within the Scanner Processes tab inside the console.

1. In the first case a user attempting to execute the Oracle PIPE does not hold the EXECUTE privilege on the DMBS_PIPE package. To check for the privilege, use any Oracle tool like Security Manager to check for the EXECUTE privilege on a user or an Oracle 8.x select statement similar to the following:
   
   select GRANTEE, TABLE_NAME, PRIVILEGE from SYS.DBA_TAB_PRIVS
   where GRANTEE = 'SQLCQR';
   
   
   If the SQLCQR user does NOT hold the EXECUTE privilege on the Oracle DBMS_PIPE package, perform the following steps:
   
   a. Log into the database instance as the SYS user.
   b. Run the following SQL statement:
   
   grant execute on SYS.DBMS_PIPE TO SQLCQR;
   
2. The second case would be caused by removing the SQLCQR from the Oracle instance without stopping the Password Listener process (Pwd Listener) first. The Password Listener process creates an Oracle PIPE to process messages. This Oracle PIPE will become invalid if the user that originally created it is dropped. The invalid Oracle PIPE is the cause of the Oracle error message "ORA-23322: Insufficient privilege to access pipe". When possible use the documentation provided for uninstalling the software.
   
   To check for the Oracle PIPE:
   
   Use any Oracle tool like DBA Studio or Enterprise Manager to check for the Oracle PIPE. Optionally, you can use an Oracle 8.x select statements similar to the following:
   
   a. select OWNERID, NAME from SYS.V_$DB_PIPES;
   Note: The OWNERID value must be used to determine if the original user exist in the Oracle SYS.USER$ table.
   b. select USER#, NAME from SYS.USER$ where USER# = 'OWNERID VALUE';
   NOTE: Use the OWNERID from option A as the "OWNERID VALUE".
   
   If option "b" shows "No rows" then the original user has been dropped.
   The SQL script below can be run to cleanup an invalid Oracle PIPE. As a last resort stopping and restarting the Oracle instance will also clean up any invalid Oracle PIPE.
   
   # Login to the database as a privileged users like SYS with the "as SYSDBA" option.
   Connect sys/sys@orc1 as SYSDBA;
   
   # This script will remove the DBMS_PIPE created by the SQLCQR user.
   
   Variable retval number
   variable msg varchar2(1000)
   begin
   :retval :=dbms_pipe.remove_pipe ('SQLCQR_ORA8_PIPE');
   end;

Formerly known as NETIQKB30503