Loading...

Details

Type: Bug
Resolution: Unresolved
Priority: Low
Fix Version/s: None
Affects Version/s: 7.13.4
Component/s: Server - Installer / Setup
Labels:
None

Support reference count:
4
Symptom Severity:
Severity 3 - Minor
UIS:
1
Bug Fix Policy:
View Atlassian Server bug fix policy

Description

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:

Create a tablespace

CREATE TABLESPACE confluence DATAFILE 'confluence.dat' SIZE 500M;

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;

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.

Attachments

Issue Links

is related to

CONFSERVER-3613 Problem installing on Oracle schema when other schemas in the same database have one or more tables sharing the same name as any of Confluence's tables

Closed

mentioned in: Page Loading...

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