3

Our application has its data structured across two schemas, let's call them Main and Archive. This is needed as some tables from the Main schema are archived into corresponding tables in the Archive schema.

The Main schema updates are run using a Liquibase servlet which is run by the Main user when the application first starts.

At the moment all archive updates are run as a separate DBA task. This is a bit inconvenient as we must engage a DBA for simple things like creating a new table in the archive schema and granting the right permissions to the Main user which is the absolute owner of all application data.

We are considering making Main user able to create/alter objects in the archive schema and embed this in our Liquibase scripts.

The Main user has been granted an APP_ADMIN_ROLE that make it entitled to CREATE, ALTER, COMMENT ON etc ANY TABLE so it is able to create and drop tables in the Archive schema.

However, we hit the problem that despite tha fact the Main user can create an Archive table it is not able to grant CRUD operations on that table to itself.

Is there any way to achieve this.

GRANT DBA TO MAIN is not an option.

Thanks in advance

William Robertson
  • 15,273
  • 4
  • 38
  • 44
Julian
  • 3,678
  • 7
  • 40
  • 72
  • https://stackoverflow.com/q/41692588/230471 suggests connecting as the other user to make grants in order to avoid `ORA-01749: you may not GRANT/REVOKE privileges to/from yourself`. Is that an option? – William Robertson May 01 '18 at 08:12
  • 1
    Alternatively you could create a procedure in the `ARCHIVE` schema that that you pass a table name to and performs a `create table x as select * from main.x`. – William Robertson May 01 '18 at 08:33
  • A lazy fix: you can also grant CRUD operations for `ANY TABLE`. In your case, you could either grant it to the Main user or the role. e.g. `grant select any table, update any table, delete any table, insert any table to APP_ADMIN_ROLE;` – kfinity May 01 '18 at 13:08
  • Tanks guys. All your comments are valuable. However the one that fits our need best is kfinity one. I was close to this myself but was greedy to do all the grants in one go which of course did not work `GRANT INSERT, UPDATE, DELETE, SELECT ANY TABLE TO main_usr;`. Please publish your comment as an answer so I can accept it. – Julian May 01 '18 at 21:17

1 Answers1

0

For future references (and extracted from the comment of kfinity), the following solution answered the OPs question best:

A lazy fix: you can also grant CRUD operations for ANY TABLE. In your case, you could either grant it to the Main user or the role. e.g.

grant select any table, update any table, delete any table, insert any table to APP_ADMIN_ROLE;

Lonzak
  • 9,334
  • 5
  • 57
  • 88