Using special characters in ORACLE DB passwords

Using special characters in ORACLE DB passwords

 
Using special characters in ORACLE DB passwords. Most times we restrict our selves from using complicated special characters as we think that we can't use them in passwords, but in oracle DB world we are allowed to use most special characters as passwords. See this simple example to see how to use special char's in passwords and login.




To log into DB using cmdline we need to use single quote ''. else oracle some times doesn't recognize the password. See below example :

I created a user with password using special characters.

SQL> alter user C##atest identified by "atest113..";

User C##ATEST altered.

[oracle@Linux03 admin]$ sqlplus C##atest/atest113..

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 20 16:39:56 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied


Solution to this is to use single quote:

[oracle@Linux03 admin]$ sqlplus C##atest/"atest113$$"

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 20 16:41:44 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name: ^C

press ctrl+c to escape from this line


[oracle@Linux03 admin]$ sqlplus C##atest/'atest113$$&&'

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 20 16:43:20 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Wed Jul 20 2016 16:42:54 -05:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show user
USER is "C##ATEST"

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@Linux03 admin]$ sqlplus C##atest/atest113$$&&

configure complex password (password verify function) in oracle database

 configure password verify function in oracle database

We can enable the oracle provided "password verify function" to enforce strong password restrictions for our DB users. This function with other profile parameters can create a strong security for the database.To enable the oracle password verification function you need to execute the utlpwdmg.sql file from ORACLE_HOME/rdbms/admin as sysdba.


[oracle@Linux03 home]$ cd /u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/
[oracle@Linux03 admin]$ ls utlpwdmg.sql

[oracle@Linux03 admin]$ sqlplus / as sysdba
SQL> @/u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/utlpwdmg.sql

SQL> @/u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/utlpwdmg.sql

Function created.

Grant succeeded.

Function created.

Grant succeeded.

Function created.

Grant succeeded.

Profile altered.

Note : You can query current default profile settings using as below

select * from DBA_PROFILES where profile='DEFAULT';

Starting from 12c this file (utlpwdmg.sql) creates four functions under user SYS. Here we have an option to select one from four functions.

ORA12C_STRONG_VERIFY_FUNCTION
ORA12C_VERIFY_FUNCTION
VERIFY_FUNCTION
VERIFY_FUNCTION_11G

This function makes the minimum complexity checks like the minimum length of the password, password not same as the
username, etc. The user may enhance this function according to your need.

You can change the password verify function by using below sql :

ALTER PROFILE "DEFAULT" LIMIT PASSWORD_VERIFY_FUNCTION ORA12C_VERIFY_FUNCTION

IT SETS UP FOLOWING PARAMETERS IN DEFAULT PROFILE:

ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 180
PASSWORD_GRACE_TIME 7
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX  UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LOCK_TIME 1

-- This script alters the default parameters for Password Management. This means that all the users on the system have Password Management enabled and set to the following values unless another profile is created with parameter values set to different value or UNLIMITED  is created and assigned to the user.

LETS TEST THE PASSWORD FUNCTION BY CREATING A NEW USER.

SQL> create user C##atest identified by atest11;

Error starting at line : 2 in command -
create user C##atest identified by atest11
Error report -
SQL Error: ORA-28003: password verification for the specified password failed
ORA-20001: Password length less than 8
28003. 00000 -  "password verification for the specified password failed"
*Cause:    The new password did not meet the necessary complexity
           specifications and the password_verify_function failed
*Action:   Enter a different password. Contact the DBA to know the rules for
           choosing the new password

          
While using length of 8 chars I was able to create user.

SQL> create user C##atest identified by atest113;

User C##ATEST created.


Follow this link on how to disable Password verify function  : 

http://arvindasdba.blogspot.com/2013/06/disable-password-verify-function-in.html

Oracle Password Verification function

Password Verification : Enforcing Password Complexity

Password complexity is enforced using a verification function. Oracle provide an example password verification function in the "$ORACLE_HOME/rdbms/admin/utlpwdmg.sql" file, but you can code a custom one if you prefer. The function must accept three parameters (username, password and old_password) and return a boolean value, where the value TRUE signifies the password is valid. The simple example below just forces the password to be at least 8 characters long.

CREATE OR REPLACE FUNCTION my_verification_function (
  username      VARCHAR2,
  password      VARCHAR2,
  old_password  VARCHAR2)
  RETURN BOOLEAN AS
BEGIN
  IF LENGTH(password) < 8 THEN
    RETURN FALSE;
  ELSE
    RETURN TRUE;
  END IF;
END my_verification_function;
/

In the above we have restricted that password must contain greater that 8 characters.

Once the function is compiled under the SYS schema it can be referenced by the PASSWORD_VERIFY_FUNCTION parameter of a profile.

You can enable Password verify Function as below:
 

ALTER PROFILE my_profile LIMIT
  PASSWORD_VERIFY_FUNCTION my_verification_function;

The code below assigns the completed profile to a user and tests it.

SQL> ALTER USER scott PROFILE my_profile;

User altered.

SQL> ALTER USER scott IDENTIFIED BY small;
ALTER USER scott IDENTIFIED BY small
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-28003: password verification for the specified password failed


SQL> ALTER USER scott IDENTIFIED BY much_bigger;

User altered.

SQL>

A more complete example of a password verification function is provided by the "$ORACLE_HOME/rdbms/admin/utlpwdmg.sql" script.

If you have trouble thinking of strong passwords, try using a password generator like RandPass.com. It creates random strong passwords with a phonetic sound, making them easier to remember.
In Oracle 11g the "$ORACLE_HOME/rdbms/admin/utlpwdmg.sql" script has been updated to improve the default password verification function. It also applies the function to the DEFAULT profile, enabling it for all users.