Oracle DB installation: Permission error

[EN]

Hello to the community.

We are currently trying to set up a new confluence Installation using an Oracle Database.

During the Installation, the installer is showing the following error:

Permission error

Your database user needs the 'CREATE TABLE' privilege.

Now, I've checked with our Database Administrator and he confirmed that the user actually has this privilege. This privilege is in the role Resource already defined.

Is there a way to bypass this check during the installation?

Thank you

Evangelos

[Marco Paleani]

@atlassian: fix this for Oralce please!

Update your code and use this sql: 

select privilege from sys.role_sys_privs where role in (select granted_role from SYS.USER_ROLE_PRIVS);

instead of checking the name of roles...

On big Companies and big Oracle Clusters you cant ask the admin, to give you just a special role, because your vendor is not working excately.

Also to the community leaders: You missed the point...

@others

if you have the same problem, here is your workaround / hack, if you are sure, that you have all the needed rights on your installation:

--------------------------------------------------------
-- DDL for Table USER_ROLE_PRIVS
--------------------------------------------------------

CREATE TABLE USER_ROLE_PRIVS
( USERNAME VARCHAR2(20), 
GRANTED_ROLE VARCHAR2(20)
);
Insert into USER_ROLE_PRIVS (USERNAME,GRANTED_ROLE) values ('<name of the schema owner>','CONNECT');
Insert into USER_ROLE_PRIVS (USERNAME,GRANTED_ROLE) values ('<name of the schema owner>','RESOURCE');

--------------------------------------------------------
-- DDL for Table USER_SYS_PRIVS
--------------------------------------------------------

CREATE TABLE USER_SYS_PRIVS
( USERNAME VARCHAR2(20), 
PRIVILEGE VARCHAR2(20)
);
Insert into USER_SYS_PRIVS (USERNAME,PRIVILEGE) values ('<name of the schema owner>','CREATE TABLE');
Insert into USER_SYS_PRIVS (USERNAME,PRIVILEGE) values ('<name of the schema owner>','CREATE SEQUENCE');
Insert into USER_SYS_PRIVS (USERNAME,PRIVILEGE) values ('<name of the schema owner>','CREATE TRIGGER');

This will create the "needed" tables for the installation checkup on your own schema

[Nic Brough -Adaptavist-]

There's nothing to fix on the Atlassian side, your database needs to be set up correctly.

[Marco Paleani]

DB is set up correctly - if you like to check, that a user has specific rights, you should check the rights.

If you have a role assigned with that name, but not the expected rights behind, the installation fails.

[EN]

Found a solution.

Looks like Confluence is using another way to check if the user has some privileges.

In Jira, the installer was smart enough to check for the different roles that the user has.

During Confluence installation, our DB admin granted temporarily all the necessary privileges for the confluence user. When the installation was finished, we changed the privileges back again to the same ones that the Jira user also uses.

The Confluence runs without any issues.

I guess that the developers should have a look in the methods that check what the user can/cannot do during the installation process.

[EN]

But with the Jira software and another user(with exactly the same rights and privileges) the installer worked and the Tables were created.

[Nic Brough -Adaptavist-]

Then there's a difference in privileges between the two users.

[EN]

Hi all,

as I said, the user can create tables. This doesn't seem to be the problem.

The problem is how confluence checks if the user is granted to Create Tables.

That's why, I need to somehow change the way confluence decides if the user has these rights or not.

The "resource" and "connect" are already granted to the user, the rest of the privileges are inside another role which the Confluence_user is also a member of.

[Nic Brough -Adaptavist-]

The check is blunt - it tries to create a table.

Forget about roles, they're not working for you here.  Give the user the right permissions directly.

In fact, I'd strongly recommend that you remove the user from all roles - it should be a single user, with full access, and used exclusively by the atlassian application so that it has no need to be in any other roles.

[Nic Brough -Adaptavist-]

There's two possibilities here

• You are not using the right user for the database, and you've got one who does not have create access
• Your database admin is wrong, the user does not have Create Table.

[Thomas Schlegel]

No, bypassing this check would make no sense, because Confluence has to create or alter tables during installation.

Are you really sure, that your database user has the create table permission for the schema?

[EN]

Hi Thomas,

I can create a table with this user using either a software(like DBArtisan) or using the command line (sqlplus).

We are using an extra Role and the needed privileges are inside this role.

My guess is that Confluence checks if the user has direct Create_Table privilege but doesn't check if the needed privileges are somewhere else in another role.

[Nic Brough -Adaptavist-]

You need to grant the user the right to create tables.  Other roles are irrelevant, as they are not used.

[Thomas Schlegel]

This is necessary according to https://confluence.atlassian.com/doc/database-setup-for-oracle-173821.html :

grant connect to <user>;
grant resource to <user>;
grant create table to <user>;
grant create sequence to <user>;
grant create trigger to <user>;