1

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.

sdv
  • 21
  • 1
  • 3
  • 2
    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. –  Apr 17 '19 at 15:30
  • 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 Apr 17 '19 at 15:55
  • 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 Apr 17 '19 at 16:13
  • 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 Apr 17 '19 at 17:34
  • @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 Apr 18 '19 at 07:23

2 Answers2

4

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

edocollado
  • 471
  • 6
  • 7
0

username: SYS as SYSDBA

Works for me

user10823113
  • 1
  • Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Mar 25 '22 at 22:44