This discussion is archived
6 Replies Latest reply: Nov 16, 2012 5:37 AM by user637001 RSS

ORA-01031 Unsufficient privileges

user637001 Newbie
Currently Being Moderated
Hello,

I'm facing a weird issue.
When logging with a specific user onto sqlplus, i'm able to issue a request which give me output.

However when the very same request is issued from the web server i get the ORA-01031 error.
I'm running Oracle 11.2.0.1.0.

Regards,
  • 1. Re: ORA-01031 Unsufficient privileges
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Show us how you connect and what are you doing
    Error:  ORA 1031
    Text:   insufficient privileges
    -------------------------------------------------------------------------------
     Cause: An attempt was made to change the current username or password without
            the appropriate privilege. This error also occurs if attempting to
            UPDATE a table with only SELECT privileges, if attempting to CONNECT
            INTERNAL, or if attempting to install a database without the necessary
            operating system privileges.
    Action: Ask the database administrator to perform the operation or grant the
            required privileges.
  • 2. Re: ORA-01031 Unsufficient privileges
    user296828 Expert
    Currently Being Moderated
    When logging with a specific user onto sqlplus, i'm able to issue a request which give me output.
    It works, seems you are supplying correct password.
    However when the very same request is issued from the web server i get the ORA-01031 error.
    I'm running Oracle 11.2.0.1.0.
    Thanks for posting the database version, but you did not explain how does web server connects e.g. connection pool etc. to the database?

    Homework; Does webserver use the same username & password? You can find this in webserver properties file.
  • 3. Re: ORA-01031 Unsufficient privileges
    user637001 Newbie
    Currently Being Moderated
    Ok, hereunder some more details:

    On the Dtabase itself:

    sqlplus TSTR@oratest

    select ORGANIZATION_CODE, ORGANIZATION_NAME, ROOT_ID, ACCOUNT_NUMBER, STATUS_CODE, EXTERNAL_ID, ORGANIZATION_TYPE, SITE_ID, GDS_BUSINESS_PROFILE, COMPANY_RESOURCE_ID, COMPANY_RULE_ID, COM_COMPANY_RESOURCE_ID, COM_COMPANY_RULE_ID, LOGO_URL, BILLING_ACCOUNT_ID from COMPANY_ORGANIZATION where COMPANY_ORGANIZATION_ID = 1 and EXPIRY_DATE is null ;

    And it works.

    On the web server:

    Pool is configured for the jboss using the very same information, configuration is done like this:
    <Resource name="tstrPool" auth="Container" type="javax.sql.DataSource" maxActive="20" maxIdle="3" maxWait="20" username="TSTR" password="password" driverClassName="oracle.jdbc.driver.OracleDriver" url="jdbc:oracle:thin:@ip:1521:oratest"/>

    The same request is issued, and we get ORA-01031 error.

    If i tried once more on the db itself, the request is still workable...

    After some investigations, i found that when the error is raised through the web server, i'm not able to kill TSTR session on the db anymore.
    Restarting the database solved the problem for the web server, but i know that will not last long.

    Any insight?
  • 4. Re: ORA-01031 Unsufficient privileges
    user296828 Expert
    Currently Being Moderated
    sqlplus TSTR@oratest
    Can you check value

    sql>show parameter sec_case_sensitive_logon

    Make sure it is set to FALSE
  • 5. Re: ORA-01031 Unsufficient privileges
    user296828 Expert
    Currently Being Moderated
    Also test following string,

    sqlplus TSTR/password@oratest
  • 6. Re: ORA-01031 Unsufficient privileges
    user637001 Newbie
    Currently Being Moderated
    SQL> show parameter sec_case_sensitive_logon

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    sec_case_sensitive_logon boolean TRUE
    SQL>


    Anyway we use UPPER CASE only.

    sqlplus TSTR/password@oratest is also working.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points