0

I have a peculiar issue which i cant figureout

Database: Oracle 11g USER: PASSWORD_VERSIONS column in DBA_USERS table had value 10g 11g initially.

sec_case_sensitive_logon value in V$PARAMETER is null

SYS.USER$ table had some values in PASSWORD and SPARE4 columns.

I wanted to reset the password for the user SAMPLEUSER to same password again.

Steps performed to reset the password the user to same old password (by a db person)

1.updated PASSWORD_VERSIONS column value to 10g ( not sure if this got updated during below step or it was updated manually)

2.Took the Hash value from PASSWORD column from SYS.USER$ table.

3.used the old hash value(from password column in sys.user$) to reset the password -- alter user SAMPLEUSER identified by values 'hashvalue'

Now the user is not able to connect to DB as wrong password .what is the issue here. Is it possible to fix the problem?

Improve this question
1
  • Take a look at the PL/SQL block I created here to change a password to itself. The process is unfortunately much more complicated then it sounds. Commented Feb 2, 2021 at 1:57

2 Answers 2

Reset to default
0

updated PASSWORD_VERSIONS column value to 10g

You should not be directly updating data dictionary views.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

not sure this column was updated on it own when the local DB person ran the alter command.what could be the fix for the problem
0

What version of the client are you using? If the only hash you provided in the alter user command was the 10G version, then you may not have re-created the proper hashes for higher version clients.

The only way to set PASSWORD_VERSIONS is by changing the SQLNET.ALLOWED_LOGON_VERSION parameter (11g) SQLNET.ALLOWED_LOGON_VERSION_SERVER (12c and up) parameter in the server's sqlnet.ora file and restarting the server. This will change the types of clients that the database can support, as specific client versions will require specific password hash values. This should NOT be set to 10 unless you actually support 10g clients. It should always/only be set to match the minimum supported client version in your architecture.

In Oracle 11g, the setting of SQLNET.ALLOWED_LOGON_VERSION has the following effects:

  • SQLNET.ALLOWED_LOGON_VERSION = 10 will allow both 10G and 11G passwords
  • SQLNET.ALLOWED_LOGON_VERSION = 11 will allow only 11G passwords

In Oracle 12 and up, the setting of SQLNET.ALLOWED_LOGON_VERSION has the following effects:

  • SQLNET.ALLOWED_LOGON_VERSION_SERVER=11 will keep generating 10G, 11G and 12c password versions
  • SQLNET.ALLOWED_LOGON_VERSION_SERVER=12 will generate both 11G and 12C password versions, and also remove the 10G password version.
  • SQLNET.ALLOWED_LOGON_VERSION_SERVER=12a will generate only 12c password versions

See the following link on how Oracle creates and stores password hashes, with examples for using 'identified by value' with multiple hash types for 10g, 11g, and 12c passwords:

And this advice for making sure passwords are as secure as possible given the setting of SQLNET.ALLOWED_LOGON_VERSION or SQLNET.ALLOWED_LOGON_VERSION_SERVER (specifically getting rid of the least secure 10G passwords, if possible):

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.