It seems at times when roles are assigned they are not assigned as DEFAULT role to a user. The reason for seems to be the following: In the table sys.user$ there is a column called "DEFROLE" which indicates if the role will be a default role or not. The possible values that seem to be … Continue reading DEFAULT role explained
Category: Security
Changing Oracle password in 11g using ALTER USER IDENTIFIED BY VALUES
In 11g the Oracle the hash password is no longer stored in DBA_USERS, it is stored in SYS.USER$ table in the column "PASSWORD" and "SPARE4". So there are different ways password can be set depending on if "PASSWORD" and "SPARE4" are set in SYS.USER$ and what you want PASSWORD_VERSIONS to be. If only "SPARE4" is … Continue reading Changing Oracle password in 11g using ALTER USER IDENTIFIED BY VALUES
Oracle passwords in 11g
In 11g Oracle supports password to be case sensitive. But the password can be case insensitive depending on value of "PASSWORD_VERSIONS" and "sec_case_sensitive_logon". In 11g dba_users view has a new column called "PASSWORD_VERSIONS", it can have 3 possible values "10G", "10G 11G", "11G". Below shows what they mean along with parameter "sec_case_sensitive_logon". Scenario when the … Continue reading Oracle passwords in 11g
ORA-28221: REPLACE not specified
When a user name has a profile that has a password verify function set and the user tries to change the password using "ALTER USER" without the REPLACE clause, and also the user doesn't have ALTER USER privilege Oracle will generate the "ORA-28221: REPLACE not specified" error message. So the user needs to include the … Continue reading ORA-28221: REPLACE not specified
How to extend an account whose password is expiring?
One of the ways to extend the password of an expiring account is to change the profile of the user. -- show the current user profile which shows the password expires 180 days SQL> select * FROM dbA_profiles where profile = 'APPLICATIONS'; PROFILE RESOURCE_NAME RESOURCE LIMIT ------------------------------ -------------------------------- -------- ---------------------------------------- .. APPLICATIONS FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED … Continue reading How to extend an account whose password is expiring?
How to turn off case sensitive in 11g
SQL> SHOW PARAMETER SEC_CASE_SENSITIVE_LOGON NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ sec_case_sensitive_logon boolean TRUE SQL> SQL> ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE; System altered.
How to find users with default password in 11g?
Now Oracle has a quick way to find users with detault password. SQL> SELECT * FROM dba_users_with_defpwd; And here is the output: USERNAME ------------------------------ DIP MDSYS WK_TEST CTXSYS OLAPSYS OUTLN EXFSYS SCOTT MDDATA ORDPLUGINS ORDSYS XDB LBACSYS SI_INFORMTN_SCHEMA WMSYS