Uploaded image for project: 'Confluence Data Center'
  1. Confluence Data Center
  2. CONFSERVER-78634

Problem installing on Oracle schema when the current user does not match schema

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Unresolved
    • Icon: Low Low
    • None
    • 7.13.4
    • None

      Issue Summary

      When Confluence is setup with Oracle using a User that differs from the Schema Owner, errors appear in the application logs that it cannot create these table names:

      • AO_21D670_WHITELIST_RULES
      • AO_187CCC_SIDEBAR_LINK
      • AO_9412A1_AOTASK
      • AO_A0B856_WEBHOOK
      • AO_950DC3_TC_SUBCALS
      • AO_950DC3_TC_REMINDER_SETTINGS

      Error log snippet:

      2022-05-04 13:48:55,191 ERROR [active-objects-init-system tenant-0] [net.java.ao.sql] handleUpdateError Exception executing SQL update <CREATE TABLE "AO_21D670_WHITELIST_RULES" (
          "ALLOWINBOUND" NUMBER(1),
          "AUTHENTICATIONREQUIRED" NUMBER(1) DEFAULT 0 NOT NULL,
          "EXPRESSION" CLOB NOT NULL,
          "ID" NUMBER(11) NOT NULL,
          "TYPE" VARCHAR(255) NOT NULL,
          PRIMARY KEY("ID")
      )>
       -- url: /c7135/setup/setuppaths.action | referer: http://localhost:27135/c7135/setup/setupdbtype-start.action | traceId: 0010c6398a075fb8 | userName: anonymous | action: setuppaths
      java.sql.SQLSyntaxErrorException: ORA-00955: name is already used by an existing object
      

      These tables are actually already created by the Install but it appears the installation attempts to re-create them.

      Environment

      One scenario where this occurs is when setting up an Oracle User to authenticate against an external LDAP user. e.g.

      create user USER1 identified by USER1PASSWORD default tablespace confluence;
      
      alter user USER1 identified globally as 'cn=my_ad_user,cn=Users,dc=atlassian,dc=com';
      

      Running these queries in Oracle will show the follwoing:

      select sys_context('USERENV','SESSION_USER') SessionUser,
      sys_context('USERENV','CURRENT_SCHEMA') CurrentSchema,
      sys_context('USERENV','ENTERPRISE_IDENTITY') EnterPriseUser
      from dual;
      

      result:

      Result
      SESSIONUSER   CURRENTSCHEMA   ENTERPRISEUSER                                                     
      ------------- --------------- -------------------------------------------------------------------
      USER1         USER1           cn=my_ad_user,cn=Users,dc=atlassian,dc=com  
      

      confluence.cfg.xml will have my_ad_user, whereby the actual schema owner in the database belongs to USER1.

      Steps to Reproduce

      The above issue can be simulated through a "LOGON" trigger:

      Setup Oracle as such:

      1. Create a tablespace
        CREATE TABLESPACE confluence DATAFILE 'confluence.dat' SIZE 500M;
        
      2. Create two DB authenticated users:
        create user user1 identified by user1p default tablespace confluence quota unlimited on confluence;
        create user user2 identified by user2p default tablespace confluence quota unlimited on confluence;
        
        • Added this trigger as per https://dba.stackexchange.com/questions/27869/how-to-set-an-oracle-users-default-schema:
          CREATE OR REPLACE TRIGGER LOGON_TRG AFTER LOGON ON DATABASE
          BEGIN
              if (user in ('USER2')) then
                EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = user1';
              end if;
          exception 
            when others 
              then null; -- prevent a login failure due to an exception
          END logon_trg;
          
        • Grant these priviliges:
          grant connect to user1;
          grant resource to user1;
          grant create table to user1;
          grant create sequence to user1;
          grant create trigger to user1;
          
          grant connect to user2;
          grant resource to user2;
          grant create table to user2;
          grant create sequence to user2;
          grant create trigger to user2;
          
        • For our testing purposes, grant this so USER2 can create tables in USER1 schema:
          grant dba to user1;
          grant dba to user2;
          
        • Connected to Oracle database as USER2 and confirmed the default schema automatically changed to USER1
          • Did a quick test that USER2 had CREATE PRIVILEGES into USER1 SCHEMA
            CREATE TABLE user1.persons(
                person_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
                first_name VARCHAR2(50) NOT NULL,
                last_name VARCHAR2(50) NOT NULL,
                PRIMARY KEY(person_id)
            );
            
            drop table user1.persons;
            
      3. Once the DB side was all set, start Confluence
        • When prompted for the User to connect to the Oracle database, use user: user2 / password: user2p

      Expected Results

      Confluence should start up with a clean, error free log in relation to the creation of Oracle database tables.

      Actual Results

      The front end install will look like it completes, however, checking atlassian-confluence.log these errors appear in the logs:

      2022-05-04 13:45:15,146 INFO [Catalina-utility-1] [com.atlassian.confluence.lifecycle] contextInitialized Starting Confluence 7.13.5 [build 8703 based on commit hash da3c91d58b49066fe2f0052e452d9f35d99cdd6f] - synchrony version 4.0.0-master-bfb398fb
      ...
      ..
      2022-05-04 13:48:55,191 ERROR [active-objects-init-system tenant-0] [net.java.ao.sql] handleUpdateError Exception executing SQL update <CREATE TABLE "AO_21D670_WHITELIST_RULES" (
          "ALLOWINBOUND" NUMBER(1),
          "AUTHENTICATIONREQUIRED" NUMBER(1) DEFAULT 0 NOT NULL,
          "EXPRESSION" CLOB NOT NULL,
          "ID" NUMBER(11) NOT NULL,
          "TYPE" VARCHAR(255) NOT NULL,
          PRIMARY KEY("ID")
      )>
       -- url: /c7135/setup/setuppaths.action | referer: http://localhost:27135/c7135/setup/setupdbtype-start.action | traceId: 0010c6398a075fb8 | userName: anonymous | action: setuppaths
      java.sql.SQLSyntaxErrorException: ORA-00955: name is already used by an existing object
      
      	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:494)
      	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:446)
      	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1054)
      	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:623)
      	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:252)
      	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:612)
      	at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:213)
      	at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:37)
      	at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:896)
      	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1119)
      	at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1661)
      	at oracle.jdbc.driver.OracleStatement.executeLargeUpdate(OracleStatement.java:1626)
      	at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1613)
      	at oracle.jdbc.driver.OracleStatementWrapper.executeUpdate(OracleStatementWrapper.java:282)
      	at com.mchange.v2.c3p0.impl.NewProxyStatement.executeUpdate(NewProxyStatement.java:410)
      	at net.java.ao.DatabaseProvider.executeUpdate(DatabaseProvider.java:2342)
      	at net.java.ao.DatabaseProvider.executeUpdateForAction(DatabaseProvider.java:2398)
      	at net.java.ao.DatabaseProvider.executeUpdatesForActions(DatabaseProvider.java:2370)
      	at net.java.ao.schema.SchemaGenerator.migrate(SchemaGenerator.java:91)
      	at net.java.ao.EntityManager.migrate(EntityManager.java:131)
      ...
      ..
      

      The above error is repeated for the list of tables in the Issue Summary section.

      Looking into the Oracle database, it appears all the tables that return ORA-00955: name is already used by an existing object are actually created in schema USER1.

      USER2 schema is empty.

      Workaround

      Connect to Confluence with a username that matches the Schema to which the database tables are being created into.

              Unassigned Unassigned
              hlam@atlassian.com Eric Lam
              Votes:
              1 Vote for this issue
              Watchers:
              5 Start watching this issue

                Created:
                Updated: