Oracle Role for liquibase user

Question

I am working on adding liquibase in my spring boot app which connects to an oracle DB. I don't feel comfortable using the schema ID in my application. So I would like to create another user id that will be used to connect to the DB from my app. Since I am using liquibase this new user id will need to have create, drop, select, insert, update, delete on all table in that schema.

In Mysql I can ran the following command:

GRANT CREATE, DROP, SELECT, INSERT, UPDATE, DELETE ON SCHEMA_NAME.* TO 'liquidbase_local_usr'@'localhost';

is there a similar query in Oracle?

Answer 1

I did this with the Datical product that was built on Liquibase.

First I created a role called DATICAL_PACKAGER_ROLE and assigned it to the user $DaticalUser:

CREATE ROLE DATICAL_PACKAGER_ROLE NOT IDENTIFIED;
 
-- When using the tracefileLocation=REMOTE, you will need to setup the following permissions.
-- Note: These permissions are only required on schemas running the Convert SQL Scripts command.  This is only needed on the packaging / reference database.
 
GRANT EXECUTE ANY PROCEDURE TO DATICAL_PACKAGER_ROLE;
GRANT ALTER SESSION TO DATICAL_PACKAGER_ROLE;
GRANT CREATE ANY DIRECTORY TO DATICAL_PACKAGER_ROLE;
GRANT DROP ANY DIRECTORY TO DATICAL_PACKAGER_ROLE;
 
-- The following permissions are needed for backing up and restoring the database.  This is only needed on the packaging / reference database.
GRANT EXP_FULL_DATABASE TO DATICAL_PACKAGER_ROLE;
GRANT IMP_FULL_DATABASE TO DATICAL_PACKAGER_ROLE;
 
 
-- The following must be run as sysdba
-- SQL > conn / as sysdba
GRANT EXECUTE on SYS.UTL_FILE TO DATICAL_PACKAGER_ROLE;
 
-- Grant the new role to the Datical User
GRANT DATICAL_PACKAGER_ROLE to $DaticalUser;

Then I granted the role to the schema I wanted:

GRANT DATICAL_PACKAGER_ROLE to <yourschema>;