which DB objects in oracle exadata needs user's admin rights.

[SW]

I am trying to install bitbucket in our company ystem, but facing issue when I select external database (which is oracle exadata in our case).

If I use ADMIN credential it works fine. But, in real time scenario, we can not use ADMIN account, this is prohibited in our environment. So I created a normal user and followed instruction as mentioned in Atlassian documentation (https://confluence.atlassian.com/bitbucketserver/connecting-bitbucket-server-to-oracle-776640379.html), but I found error.

 
2017-06-12 10:09:00,539 WARN  [http-nio-9090-exec-7] @2JMMOQx608x17x0 12soj9v 0:0:0:0:0:0:0:1 "POST /setup HTTP/1.1" c.a.s.i.db.DefaultDatabaseManager An unexpected exception prevented validating the target database
com.atlassian.stash.internal.backup.liquibase.LiquibaseDataAccessException: Failed to obtain snapshot. Database type: oracle; nested exception is liquibase.exception.DatabaseException: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist


I also figured that if I give admin access to the normal user it works fine. But we need to know the DB objects specifically those need admin priviledge to access, so that we can grant only those objects, not the entire database.

[Nic Brough -Adaptavist-]

All of them.  The user you use to connect to the database should have all rights on all tables.

Trying to give partial rights is possible, but it's a pain in the neck to work out, harder to maintain, and there is absolutely no good reason to do it.

[SW]

I have created new user as per https://confluence.atlassian.com/bitbucketserver/connecting-bitbucket-server-to-oracle-776640379.html.

-------------------------------------------

but from error mentioned in log, i.e.

Caused by: liquibase.exception.DatabaseException: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

I see it is expecting some table in the empty database. So need to know which specific table it is trying to access and why it is not able to create the table/view with normal user (as per instruction given in Atlassian page).  

[Nic Brough -Adaptavist-]

Yes, it should have created all the required tables when you first installed it.  If you were using the wrong account, then it may have set it up incorrectly and hence can't connect with the right one.

There may well be no way to fix this without really comparing a clean valid installation with your database.

You can try to do it without doing a full reinstall, but you need to read ALL of the error log to start to work out what might be missing, and bear in mind that because it can't read/write the tables correctly, that you may have lost data.

[SW]

Sorry for the confusion, I think the scenario is not well prescribed above.

FYI, I have installed the new Bitbucket server instance with bitbucket's own internal databse first(with no data yet). And now I want to connect it with external database .i.e. exadata(oracle).

I have followed the atlassian link to create "userone" on databse but its giving error while trying to connect.

The "userone" is normal user which has connect and resource access to database.

"userone" needs admin permissions on database ?

I seen that if I give admin access to the "userone" it works fine.

And if error suggest its "table or view does not exist" what are these tables or views, how can I find them. Please suggest.

[Nic Brough -Adaptavist-]

Yes, that user needs full access to the database.

[SW]

Thank you for answer Nic.

Could you please help with what addition of steps or sql statement I need to add to below sql statements given by atlassian page to gain full access to the database.

CREATE USER bitbucket
  IDENTIFIED BY jdHyd6Sn21
  DEFAULT TABLESPACE USERS
  QUOTA UNLIMITED ON USERS;
GRANT CONNECT, RESOURCE to bitbucket;
CREATE VIEW bitbucket.all_objects AS
  SELECT *
  FROM sys.all_objects
  WHERE owner = upper('bitbucket');

[Nic Brough -Adaptavist-]

I don't know.

That statement works, but it looks like you've changed the rights for the user and broken them.  I don't know what you've changed.

[SW]

We have followed exactly those 3 statements to run (each successfully). We didn’t not modify anything.

Please note, we are using oracle exadata (hence, a pluggable database with PDB files).

The new user is created by admin user and hence the schema is empty.

[Nic Brough -Adaptavist-]

If the schema is empty, that answers the question.

Can you confirm what you have been doing, as the previous conversation seems to contradict itself.

I think what you have done is:

• Installed bitbucket using one database (internal?)
• Created an empty database, with the user bitbucket and run the SQL to grant it access to the database
• Pointed the installed Bitbucket to it

Is that correct?

[SW]

Steps performed,

1. Created an empty USER (i.e. schema), granted connect and resource permissions and finally created view for all objects (as mentioned in atlassian link)
2. Started installing bitbucket, In setup wizard; pointed the database to external(oracle) and Connection failed (snippet from error stack is below)

2017-06-12 09:49:08,937 WARN  [spring-startup]  c.a.s.i.p.s.OsgiBundledPathScanner Cannot scan directory /com/atlassian/oauth/shared/servlet/ in bundle com.atlassian.oauth.atlassian-oauth-service-provider-plugin; it does not exist
2017-06-12 09:49:10,578 INFO  [spring-startup]  c.a.s.internal.scm.PluginScmService Bitbucket is using F:\app\Git\cmd\git.exe version 2.12.0
2017-06-12 09:49:10,593 INFO  [spring-startup]  c.a.stash.internal.home.HomeTracker Capturing benchmark shared home directory: G:\Bitbucket\shared
2017-06-12 09:49:10,734 INFO  [spring-startup]  c.a.s.i.hazelcast.HazelcastLifecycle Updating Hazelcast instance capabilities
2017-06-12 10:09:00,336 INFO  [http-nio-9090-exec-7] @2JMMOQx608x17x0 12soj9v 0:0:0:0:0:0:0:1 "POST /setup HTTP/1.1" c.a.s.i.b.l.DefaultLiquibaseSession Examining structure of source database
2017-06-12 10:09:00,539 WARN  [http-nio-9090-exec-7] @2JMMOQx608x17x0 12soj9v 0:0:0:0:0:0:0:1 "POST /setup HTTP/1.1" c.a.s.i.db.DefaultDatabaseManager An unexpected exception prevented validating the target database
com.atlassian.stash.internal.backup.liquibase.LiquibaseDataAccessException: Failed to obtain snapshot. Database type: oracle; nested exception is liquibase.exception.DatabaseException: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

..

.. ... 49 common frames omitted

[Nic Brough -Adaptavist-]

So this is a completely clean installation?  You've done nothing more than take a completely empty database and run the bitbucket installer?

[SW]

yes, We are installing it from scratch.

[Nic Brough -Adaptavist-]

Ok, well, the error message is implying that it's trying to take a backup of something, which suggests that there is data in the database, and/or pre-existing data setup in the home directory. 

As the user you want to use for the database, log into it and check there are no tables.  While you are there, check that you have complete rights for adding and dropping tables, indexing, and so on.

[SW]

The database might have tables or other objects which are created by other users. But we created a new user (hence it has empty schema) and hence tables or views or any other objects created/updated/dropped by the bitbucket user should be in the scope of that user. If anything is accessed beyond this user’s scope, then we clearly need to know that. But we can not grant dba role to this user; this is not recommended by our business. So we would like to know, what are the objects the user is trying to access outside of its scope.

Also to add, we have granted “all privileges” to the bitbucket user using grant statement, but the issue or error message is same.

[Nic Brough -Adaptavist-]

I understand that, and you don't need dbo privileges for the bitbucket user.  But you do need to give it full access to everything within its own database/view.

It's not trying to access anything outside its scope.  It needs to  be able to use create, drop, update, index and so-on inside its own area.

Have you checked that there are no tables in the database when before you start your new install?

[SW]

Sorry, I don’t understand your question.

Yes there are other tables, because there are other users who are using the same database. But, as we mentioned in beginning, we created a completely new user and hence schema is empty.

So if I login with the user, it does not show any table. I am not a database expert, but our expert says, it should not be an issue. Are looking for any other information?

[Nic Brough -Adaptavist-]

No, that's good, if you log in as the "bitbucket" user and see no tables, that's what we want.

Next step is to test that that user can create tables, index them, and drop them.

Then, we need to start the installation of a clean bitbucket server again.