how to specify DBA role while connecting to Oracle DB from a spring boot application

Question

I am creating a spring boot application which is connecting to an Oracle DB instance.My application is required to process SQL commands like CREATE PLUGGABLE DATABASE,ALTER PLUGGABLE DATABASE,ALTER SESSION,CREATE TABLESPACE,ALTER USER etc.

My Application.properties is as below

spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver
spring.datasource.url=jdbc:oracle:thin:@x.x.x.x:port/servicename
spring.datasource.username=sys
spring.datasource.password=somepassword

Since ALTER PLUGGABLE DATABASE COMMANDS require sysdba privilege to execute,I've given the sysdba user "sys" and its password in the application.properties file.

However when I execute the command,I get the error "connection as SYS should be as SYSDBA or SYSOPER".This user has the sysdba privilege ,however when I run from SQLPLUS I mention SQLPLUS / as sysdba before executing the alter commands.

I have tried specifying

spring.datasource.username=sys as sydba,

however that results in an ORA-01017: invalid username/password; logon denied error.

Can you please suggest how I can connect as sysdba from my application and execute the alter commands? Note:I just heard that it is not possible to connect as "sys" from an application,if so could you please suggest what type of user and what privileges would be required for an user which can be connected from application to execute ALTER PLUGGABLE DATABASE commands Note 2:My application itself is designed to create and alter PDBs based on JSON inputs.Its not an one time task to be done by a DBA.

An application should NEVER, ever connect as sysdba. That's a really, really, really bad idea. Your application should rely on the DBA to have setup everything correctly. — a_horse_with_no_name
My application itself is designed to create and alter PDBs based on JSON inputs.Its not an one time task to get it done by a DBA. Please suggest an alternate solution incase sysdba shouldnot be used. — sdv
Do you need SYSDBA privileges, or will DBA do the trick? Creating a list of privileges that are needed for your requirements would probably be a good start. You could use that list to create a role containing those privileges. Once the role is created, you can grant the role to users. You can grant the "SYSDBA" privilege to a user/service account other than SYS, however, you should be extremely careful. It may be necessary if you are using the pdb_change_state clause, and if so, make sure that the account is secure. You could create a service account for this. — 1991DBA
It needs to present the username as sys as sysdba to Oracle when connecting. I don't know much about spring but using JDBC you'd have this as a single string i.e. DriverManager.getConnection("jdbc:oracle:thin:@x.x.x.x:port/servicename","sys as sysdba","passwd"). Does adding double quotes like spring.datasource.username="sys as sydba" make a difference? I'm thinking perhaps it is missing the " as sysdba" part. — TenG
@TenG : the behaviour from jdbc is also the same.I'm using Oracle 19c Database.As you said,I also tried out spring.datasource.username="sys as sydba" that gives the same error "invalid logon" — sdv

edocollado edocollado · Accepted Answer · 2020-04-06T18:07:17

I use spring boot framework and oracle 12c (database).

my application.properties file look like this:

# ===============================
# = DATA SOURCE
# ===============================
# Set here configurations for the database connection
spring.datasource.url=jdbc:oracle:thin:@127.0.0.1:1521:ORCLCDB
spring.datasource.username=sys as sysdba
spring.datasource.password=Oradoc_db1
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver

# ===============================
# = JPA / HIBERNATE
# ===============================
# Show or not log for each sql query
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=create-drop
# Naming strategy
spring.jpa.hibernate.naming.implicit-strategy=org.hibernate.boot.model.naming.ImplicitNamingStrategyLegacyHbmImpl
spring.jpa.hibernate.naming.physical-strategy=org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy
# Allows Hibernate to generate SQL optimized for a particular DBMS
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.Oracle12cDialect
spring.jpa.database-platform=org.hibernate.dialect.Oracle12cDialect

you have to specify the username in this way:

spring.datasource.username=sys as sysdba

you could find the file here

how to specify DBA role while connecting to Oracle DB from a spring boot application

1 Answers