6 Replies Latest reply: Nov 16, 2012 7:37 AM by user637001 RSS

    ORA-01031 Unsufficient privileges

    user637001
      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
          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
            12cdb
            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
              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
                12cdb
                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
                  12cdb
                  Also test following string,

                  sqlplus TSTR/password@oratest
                  • 6. Re: ORA-01031 Unsufficient privileges
                    user637001
                    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.