I've got an Oracle FREEPDB running in Docker, and I would like to create a user with appropriate Roles & Grants when the database is initialized. I have a docker-compose.yml:
services:
oracle-db:
image: gvenzl/oracle-free:23-slim-faststart
container_name: oracle-db
environment:
- ORACLE_PASSWORD=test
- ORACLE_PDB=FREEPDB1
ports:
- "1521:1521"
volumes:
- ./app/src/main/resources/db/changelog/init-scripts:/docker-entrypoint-initdb.d
command: >
bash -c "
/opt/oracle/runOracle.sh &&
sqlplus system/test@localhost:1521/FREEPDB1 @/docker-entrypoint-initdb.d/create-user-ddl.sql"
And my init script:
-- create-user-ddl.sql
SET SERVEROUTPUT ON;
BEGIN
EXECUTE IMMEDIATE 'CREATE USER TESTACC IDENTIFIED BY "StrongP4ssword12345" DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS';
EXECUTE IMMEDIATE 'GRANT CREATE SESSION TO TESTACC';
EXECUTE IMMEDIATE 'GRANT CONNECT TO TESTACC';
EXECUTE IMMEDIATE 'GRANT RESOURCE TO TESTACC';
EXECUTE IMMEDIATE 'GRANT CREATE SEQUENCE TO TESTACC';
EXECUTE IMMEDIATE 'GRANT CREATE TABLE TO TESTACC';
EXECUTE IMMEDIATE 'GRANT CREATE TRIGGER TO TESTACC';
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/
-- verify & output
SELECT granted_role FROM dba_role_privs WHERE grantee = 'TESTACC';
SELECT privilege FROM dba_sys_privs WHERE grantee = 'TESTACC';
SELECT owner, table_name, privilege FROM dba_tab_privs WHERE grantee = 'TESTACC';
It's all working nicely. When I start the container (docker-compose up), I see the dbms output and can see all the privileges & roles assigned to my_user. I even exec into the container and double check:
sqlplus system/test
SELECT granted_role FROM dba_role_privs WHERE grantee = 'TESTACC';
SELECT privilege FROM dba_sys_privs WHERE grantee = 'TESTACC';
SELECT owner, table_name, privilege FROM dba_tab_privs WHERE grantee = 'TESTACC';
and verified that user is created with appropriate permissions. Logging in again as testaccount and I verify I am able to connect and perform the actions that I have permissions for.
The Problem:
When I start my SpringBootApplication, I cannot connect to the datasource because I do not have the CONNECT role. Here is my environment configuration (the relevant datasource props):
spring:
config:
activate:
on-profile: local
liquibase:
primary:
enabled: true
change-log: classpath:/db/changelog/db-changelog-fgl.xml
datasource:
app1:
primary:
username: testacc
password: StrongP4ssword12345
jdbc-url: jdbc:oracle:thin:@//localhost:1521/FREEPDB1
driver-class-name: oracle.jdbc.OracleDriver
ORA-01045: Login denied. User TESTACC does not have CREATE SESSION privilege.
What's even more confusing is I use a sql client (dbeaver) to connect to that db on localhost as system user and I can see the user was created, but there are no roles or privileges associated to it.
Does anyone have a solution for this issue?
Thanks!
EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM);It is easier and safer to remove the custom exception handling and let Oracle use its default error propagation. That will ensure that errors and all relevant debug information are included. (Although you say you can see the DBMS_OUTPUT, so this likely isn't the cause. But it's a good habit to get into anyway.)