Changing the Authentication Mode in Oracle Primavera P6 EPPM Using SQL

Oracle Primavera P6 EPPM

I am currently in the process of setting up an Oracle Primavera P6 EPPM 18.8 environment. The operating system on the application and Web server is Windows Server 2012 R2, the middleware is Oracle WebLogic 12c, and Oracle Database 12c for the RDBMS.

This is the first time I’ve ever set up P6 from scratch, and I’m doing it without any assistance from Oracle, other than their documentation.

I’ve learned quite a lot in my adventure so far. One thing that I learned is that you REALLY don’t want to turn on LDAP Authentication everywhere until you’re sure it’s working.

(Relevant documentation: Configuring LDAP Settings in the Primavera P6 Administrator)

In the P6 Administration website, there are two places to change the type of authentication used: NATIVE, LDAP, and WebSSO. The Authentication tab:

P6 Administration screen - Authentication tab

And the authentication section on the database instance itself:

P6 Administration screen - Authentication section on DB instance

If both are set to “LDAP” (and presumably “WebSSO” as well) when things aren’t yet set up properly, you’ll get this lovely error: “Primavera P6 was configured to use a different authentication mode than the database selected”.

Google suggests that you read this Oracle doc to fix the problem. However, this problem is only fixed with this solution if only the DB instance setting is “LDAP”. If both are set that way, this solution does not fix the problem – though it should still be run so as to fix the setting on the DB instance page.

After poking around in the database, I discovered that the offending configuration setting was stored in a BLOB in the ADMIN_CONFIG table. Unfortunately, editing this BLOB is not easy without some assistance. Fortunately, I found an article on StackOverflow that had functions for converting a BLOB into a CLOB, at which point the REPLACE function could be used. Then convert the CLOB back to a BLOB, and UPDATE the database table.

First, create the two conversion functions:

CREATE OR REPLACE FUNCTION convert_to_clob(l_blob BLOB) RETURN CLOB IS
      l_clob         CLOB;
      l_dest_offset  NUMBER := 1;
      l_src_offset   NUMBER := 1;
      l_lang_context NUMBER := dbms_lob.default_lang_ctx;
      l_warning      NUMBER;
   BEGIN
      dbms_lob.createtemporary(l_clob, TRUE);
      dbms_lob.converttoclob(dest_lob     => l_clob,
                             src_blob     => l_blob,
                             amount       => dbms_lob.lobmaxsize,
                             dest_offset  => l_dest_offset,
                             src_offset   => l_src_offset,
                             blob_csid    => nls_charset_id('AL32UTF8'),
                             lang_context => l_lang_context,
                             warning      => l_warning);
      RETURN l_clob;
   END convert_to_clob;
   /



CREATE OR REPLACE FUNCTION convert_to_blob(l_clob CLOB) RETURN BLOB IS
      l_blob         BLOB;
      l_dest_offset  NUMBER := 1;
      l_src_offset   NUMBER := 1;
      l_lang_context NUMBER := dbms_lob.default_lang_ctx;
      l_warning      NUMBER;
   BEGIN
      dbms_lob.createtemporary(l_blob, TRUE);
      dbms_lob.converttoblob(dest_lob     => l_blob,
                             src_clob     => l_clob,
                             amount       => dbms_lob.lobmaxsize,
                             dest_offset  => l_dest_offset,
                             src_offset   => l_src_offset,
                             blob_csid    => nls_charset_id('AL32UTF8'),
                             lang_context => l_lang_context,
                             warning      => l_warning);
      RETURN l_blob;
   END convert_to_blob;
   /

Then run an UPDATE statement, something like this, changing the CONFIG_NAME appropriately:

UPDATE ADMIN_CONFIG
     SET CONFIG_DATA = convert_to_blob(
                          REPLACE(convert_to_clob(CONFIG_DATA),
                                 '<mode desc="SET_AUTHENTICATION_MODE" o1="NATIVE" o2="WebSSO" o3="LDAP" type="javax.swing.JComboBox">LDAP</mode>',
                                 '<mode desc="SET_AUTHENTICATION_MODE" o1="NATIVE" o2="WebSSO" o3="LDAP" type="javax.swing.JComboBox">NATIVE</mode>')
                          )
   WHERE CONFIG_NAME = 'myprimavera.bre.YourConfigName';
COMMIT;

After running this script and restarting the application servers, you should once again be back in NATIVE mode and able to get back into P6 to see what went wrong, and no longer crying over the website you thought you had bricked just a few minutes prior.

Overriding the USER_TABLES and USER_VIEWS Public Synonyms

Oracle Database logo

To increase the level of security on one of the applications I support, I had to create a new user (we’ll call this one APPUSERREAD) in our Oracle Database that had read-only access to the previous application user schema (and this one will be APPUSER).

One of the challenges at this point was to avoid changing the queries that were hard-coded into the application. Unfortunately, these queries referenced tables without the schema name, assuming the default schema.

So, the first step was to alter the default schema of the APPUSERREAD account each time a session was created. Aside from adding the required code into the application, it seemed that a trigger would be the best option to do this.

The code below creates a trigger for the new schema that resets the default schema at each logon. This would have to be run while logged on as APPUSERREAD.

CREATE OR REPLACE TRIGGER LOGON_TRG
  AFTER LOGON ON SCHEMA
BEGIN
     EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = APPUSER';
EXCEPTION
  WHEN OTHERS
    THEN NULL; -- prevent a login failure due to an exception
END;

Though this fixes the problem of omitting references to the APPUSER schema in the queries, we weren’t done yet.

As it turns out, the code also runs SELECT statements against two public synonyms: USER_TABLES and USER_VIEWS.

Even changing the default schema will not change the results of a query against these views.

Selecting USER_TABLES returns all tables owned by the logged on user (not schema), and USER_VIEWS similarly returns all views owned by the user.

To get something similar to the contents of the USER_TABLES for the APPUSER account when logged on as APPUSERREAD (or any other account), this query will give you what you need:

SELECT * FROM ALL_TABLES WHERE OWNER = 'APPUSER';

For USER_VIEWS, the query is similar:

SELECT * FROM ALL_VIEWS WHERE OWNER = 'APPUSER';

The only problem is that there is an OWNER column present in both of these result sets, whereas the OWNER is known as the current user for USER_TABLES and USER_VIEWS.

The solution was to create views in the original schema that only had the needed columns – and in the correct order – such that the new views mirrored the public synonyms.

To get the list of columns and their order, I used the LISTAGG command to return a comma-delimited list that would become the column list for each new view.

For USER_TABLES:

SELECT LISTAGG(COLUMN_NAME,',')
WITHIN GROUP (ORDER BY COLUMN_ID)
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME='USER_TABLES'
ORDER BY COLUMN_ID;

For USER_VIEWS:

SELECT LISTAGG(COLUMN_NAME,',')
WITHIN GROUP (ORDER BY COLUMN_ID)
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME='USER_VIEWS'
ORDER BY COLUMN_ID;

Using the results from these queries, I built new views:

APPUSER.USER_TABLES:

CREATE VIEW APPUSER.USER_TABLES AS
SELECT TABLE_NAME, TABLESPACE_NAME, CLUSTER_NAME, IOT_NAME, STATUS, PCT_FREE, PCT_USED, INI_TRANS, MAX_TRANS, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE, FREELISTS, FREELIST_GROUPS, LOGGING, BACKED_UP, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN, AVG_SPACE_FREELIST_BLOCKS, NUM_FREELIST_BLOCKS, DEGREE, INSTANCES, CACHE, TABLE_LOCK, SAMPLE_SIZE, LAST_ANALYZED, PARTITIONED, IOT_TYPE, TEMPORARY, SECONDARY, NESTED, BUFFER_POOL, FLASH_CACHE, CELL_FLASH_CACHE, ROW_MOVEMENT, GLOBAL_STATS, USER_STATS, DURATION, SKIP_CORRUPT, MONITORING, CLUSTER_OWNER, DEPENDENCIES, COMPRESSION, COMPRESS_FOR, DROPPED, READ_ONLY, SEGMENT_CREATED, RESULT_CACHE, CLUSTERING, ACTIVITY_TRACKING, DML_TIMESTAMP, HAS_IDENTITY, CONTAINER_DATA, INMEMORY, INMEMORY_PRIORITY, INMEMORY_DISTRIBUTE, INMEMORY_COMPRESSION, INMEMORY_DUPLICATE
FROM ALL_TABLES
WHERE OWNER = 'APPUSER';

APPUSER.USER_VIEWS:

CREATE VIEW APPUSER.USER_VIEWS AS
SELECT VIEW_NAME, TEXT_LENGTH, TEXT, TEXT_VC, TYPE_TEXT_LENGTH, TYPE_TEXT, OID_TEXT_LENGTH, OID_TEXT, VIEW_TYPE_OWNER, VIEW_TYPE, SUPERVIEW_NAME, EDITIONING_VIEW, READ_ONLY, CONTAINER_DATA, BEQUEATH, ORIGIN_CON_ID
FROM ALL_VIEWS
WHERE OWNER = 'APPUSER';

Since the default schema of APPUSERREAD is now APPUSER, a call to USER_TABLES or USER_VIEWS without specifying the schema, will retrieve these instead of the public synonyms.

Problem solved!

Executing Stored Procedures in Loops Using PL/SQL Tables

Oracle Database PL/SQL

I needed to be able to run a stored procedure once for each item in a list. In Microsoft SQL Server, I would probably do this with a table variable and a cursor, and the code would look something like this:

DECLARE @wo_table TABLE (wo_id VARCHAR(10) NOT NULL);
DECLARE @wo_id VARCHAR(10);

INSERT INTO @wo_table (wo_id) VALUES
('123456')
,('234567')
,('345678')
,('456789')
,('567890');

DECLARE wo_cursor CURSOR FOR
SELECT wo_id FROM @wo_table;

OPEN wo_cursor;
FETCH NEXT FROM wo_cursor INTO @wo_id;
WHILE @@FETCH_STATUS=0
BEGIN
	EXEC dbo.MyStoredProc @wo_id;
	FETCH NEXT FROM wo_cursor INTO @wo_id;
END;

CLOSE wo_cursor;
DEALLOCATE wo_cursor;

However, PL/SQL does not have table variables. While an option like a global temporary table could work, I decided that a PL/SQL table was a better solution.

Declare the PL/SQL table type, instantiate the PL/SQL table with the list of values (strings, in this case), then loop over the PL/SQL table, running the stored proc with each value. Notice that the “EXECUTE” keyword is missing. It is not only unnecessary in this context; it will not work here.

Below is the completed code:

DECLARE
  TYPE wo_table_type IS TABLE OF VARCHAR2(10);
  wo_table wo_table_type := wo_table_type(
    '123456'
    ,'234567'
    ,'345678'
    ,'456789'
    ,'567890');

BEGIN
  FOR i IN 1..wo_table.COUNT LOOP
    PackageName.MyStoredProc(wo_table(i));
  END LOOP;
END;