CODEWITHSUNDEEP
pythonsqloracle11goracle10gcx-oracle
Satya Prasad emmandi
Satya Prasad emmandi

Reputation: 49

cx_Oracle giving ORA-01031 Insufficient privileges Logging in as SYSDBA

I am trying to connect Oracle remote database server using Python cx_Oracle. In sqlplus I do use "sqlplus / as sysdba" for connecting server using putty Console and i am to login successfully. Can you please help me on this why it was throwing this error.

import cx_Oracle
tns= cx_Oracle.makedsn('ip', port, SERVICE NAME = 'SID')   
db = cx_Oracle.connect('SYS', 'password', tns, mode=cx_Oracle.SYSDBA)

It was throwing Error :

cx_Oracle Database Error ORA-01031: insufficient privileges

I already made one normal DB user and tried connecting with and it was Successful.

db = cx_Oracle.connect('USERNAME', 'password', tns)

Upvotes: 0

Views: 830

Answers (1)

pmdba
pmdba

Reputation: 7033

You should be able to do most (all?) of the kind of things you mentioned with the normal "DBA" role. SYSDBA is often only necessary when doing things that require restart of the database, software and patch installation, or with backup/recovery scenarios, and is insanely overpowered for day-to-day uses. Depending on your version of Oracle, it is capable of bypassing many security features entirely, and is generally not safe to use within most scripts and applications for that reason. Use should be limited to things that only SYSDBA can do.

Additionally, the SYS user may not be allowed to connect over the network (you're using a TNS connection), as it is authenticated by the server operating system which implies local login only. That's why "sqlplus / as sysdba" works when you're logged in to the database server.

Consider the following:

  1. If you must have actual SYSDBA privileges and nothing else will do, grant the SYSDBA role to another user and use that instead. As @Littlefoot suggested, never use the SYS or SYSTEM accounts for day-to-day administrative work, either on the server or remotely.
  2. Never embed username and password in your scripts or application code, especially for an account that powerful. Use an Oracle Wallet to hold encrypted user credentials instead, or better yet prompt the user to enter them at runtime. Putting credentials like that directly in a script is a sure way to fail a security audit.
  3. Follow the principle of least privilege and don't use a SYSDBA or DBA-role enabled account for anything else other than what you need those specific privileges to do (adding files, etc.). Use lesser accounts where possible.
  4. When using privileged accounts like those with DBA or SYSDBA privileges over the network, you should encrypt all communications.

See these links for details:

Upvotes: 1

Related Questions