5

Code is as follows - 

import groovy.sql.Sql

def driver = "oracle.jdbc.OracleDriver"
def jdbcUrl = "jdbc:oracle:thin@myhost:1521:MYSID"
def sql = Sql.newInstance(jdbcUrl , "sys", "password", driver)

But I am getting following error

Caught: java.sql.SQLException: ORA-28009: connection as SYS should be as SYSDBA or SYSOPER

java.sql.SQLException: ORA-28009: connection as SYS should be as SYSDBA or SYSOPER

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:389)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:382)
        at oracle.jdbc.driver.T4CTTIfun.processError(T4CTTIfun.java:600)
        at oracle.jdbc.driver.T4CTTIoauthenticate.processError(T4CTTIoauthenticate.java:445)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
        at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:380)
        at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:760)
        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:401)
        at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:546)
        at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:236)
        at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
        at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:521)

I tried replacing user as 'sys as sysdba' as mentioned in this SO answer.

I also tried using 

Properties props = new Properties()
props.put("user","sys")
props.put("password", "password")
props.put("internal_logon", "sysdba")
jdbcUrl = "jdbc:oracle:thin@myhost:1521:MYSID"
def sql=Sql.newInstance(jdbcUrl,props)

as suggested here

but here I am getting 

Caused by: java.sql.SQLException: ORA-01017: invalid username/password; logon denied

Any suggestions?

Community
  • 1
  • 1
Aniket Thakur
  • 66,731
  • 38
  • 279
  • 289
  • 4
    Why are you trying to log in as SYS in the first place? That is an incredibly powerful account that should be used very, very infrequently. Unless you are building an application specifically for DBAs to manage a database and building a screen in that application that actually requires the elevated privileges of SYS, you wouldn't want to use that account to connect to the database. You'd want to use a much less privileged account. – Justin Cave Apr 06 '15 at 18:19
  • @JustinCave I am doing something on that lines only and I need sys as sysdba to do that. – Aniket Thakur Apr 06 '15 at 18:27
  • do not log on as sys through an application, create a user, grant them DBA and connect with them – kevinskio Apr 06 '15 at 18:34
  • Why should there be any difference? [Oracle docs](http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-faq-090281.html#05_11) also says it can be done but doesn't seem to work and I wish to know why if anyone has prior experienced this. – Aniket Thakur Apr 06 '15 at 18:39
  • In the last example where you have the "internal_logon" property, are you supplying the password for `SYS` or `SYSDBA`? Or have you tried both? – Bob Jarvis - Слава Україні Apr 06 '15 at 19:57
  • Listen to @JustinCave, he is giving good advice. And worth noting is that `SYS` and `SYSTEM` [are different users](http://stackoverflow.com/questions/915853/whats-the-difference-between-the-oracle-sys-and-system-accounts). Don't use `SYS` in an application. – mmmmmpie Apr 06 '15 at 20:01
  • What error you got when trying http://stackoverflow.com/questions/10101517/how-to-connect-in-java-as-sys-to-oracle – Jayan Apr 07 '15 at 03:11
  • @BobJarvis the password corresponds to sys user. – Aniket Thakur Apr 07 '15 at 04:46
  • @Jayan that link corresponds to Java. This is a similar issue but in groovy. As i said in question I to use `sys as sysdba` in username. No luck. Gives invalid logon. – Aniket Thakur Apr 07 '15 at 04:47

1 Answers1

16

The following does work. Only difference is the driver name

import groovy.sql.Sql

def driver = "oracle.jdbc.driver.OracleDriver"
def jdbcUrl = "jdbc:oracle:thin:@oraclehost:1521:SID"
def sql = Sql.newInstance(jdbcUrl , "sys as sysdba", "syspassword", driver)
Jayan
  • 18,003
  • 15
  • 89
  • 143