Wednesday, August 26, 2015

Password Expire

If a user forgets his password, he may ask you to reset it for him. You will then know his new password, which you may see as a security issue. By including the password expire clause in the alter user command, you can force the user to change his password the next time he logs in. After this, you will no longer know his password. The examples which follow show a DBA changing a password in red and a user logging in afterwards in green.
 
The first example shows a DBA using an Oracle 11 version of SQL*Plus to change a password in an Oracle 11 database:

TEST11 > sqlplus / as sysdba
 
SQL*Plus: Release 11.1.0.6.0 - Production on Wed Aug 26 11:03:51 2015
 
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> alter user a identified by b
  2  password expire
  3  /
 
User altered.
 
SQL>

The user then logs in with the same Oracle 11 version of SQL*Plus and is prompted to change his password. After doing this, he reconnects to the database. This is not necessary, it is just to show that the password change has taken effect:

TEST11 > sqlplus a/b
 
SQL*Plus: Release 11.1.0.6.0 - Production on Wed Aug 26 11:11:51 2015
 
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
 
ERROR:
ORA-28001: the password has expired
 
Changing password for a
New password:
Retype new password:
Password changed
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> conn a/c
Connected.
SQL>

The DBA then resets and expires the password again using the same Oracle 11 version of SQL*Plus:

TEST11 > sqlplus / as sysdba
 
SQL*Plus: Release 11.1.0.6.0 - Production on Wed Aug 26 11:56:10 2015
 
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> alter user a identified by b
  2  password expire
  3  /
 
User altered.
 
SQL>

The user logs in using an Oracle 10 version of SQL*Plus this time. He is prompted to change his password but is unable to do so:

TEST10 > sqlplus a/b@test11
 
SQL*Plus: Release 10.2.0.3.0 - Production on Wed Aug 26 11:59:46 2015
 
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
 
ERROR:
ORA-28001: the password has expired
 
Changing password for a
New password:
Retype new password:
ERROR:
ORA-01017: invalid username/password; logon denied
 
Password unchanged
Enter user-name: 

So, if you want to expire a password in an Oracle 11 database, you need to check that the person who will be logging in to that user afterwards is using an Oracle 11 version of SQL*Plus, not an Oracle 10 one.

Thursday, August 20, 2015

PL/SQL CASE Statement

This is an alternative to if… then… else… elsif… end if when you want to use conditional statements in PL/SQL. Here is a simple example, which checks whether a given number is 1 or 2. You start with the word case. Then you add one or more conditions followed by the action to take if that condition is satisfied. Each condition is preceded by the word when. You finish with the words end case:

SQL> declare
  2    one_or_two number := 1;
  3  begin
  4    case
  5    when one_or_two = 1 then
  6      dbms_output.put_line('One');
  7    when one_or_two = 2 then
  8      dbms_output.put_line('Two');
  9    end case;
 10  end;
 11  /
One
 
PL/SQL procedure successfully completed.
 
SQL>

The next example puts the variable name after the word case. This saves you having to include it in every when line:

SQL> declare
  2    one_or_two number := 2;
  3  begin
  4    case one_or_two
  5    when 1 then dbms_output.put_line('One');
  6    when 2 then dbms_output.put_line('Two');
  7    end case;
  8  end;
  9  /
Two
 
PL/SQL procedure successfully completed.
 
SQL>

If none of the when clauses are satisfied, you get an ORA-06592:

SQL> declare
  2    one_or_two number := 3;
  3  begin
  4    case
  5    when one_or_two = 1 then
  6      dbms_output.put_line('One');
  7    when one_or_two = 2 then
  8      dbms_output.put_line('Two');
  9    end case;
 10  end;
 11  /
declare
*
ERROR at line 1:
ORA-06592: CASE not found while executing CASE
statement
ORA-06512: at line 4
 
SQL>

One way to avoid this is to add an else clause before the end case:

SQL> declare
  2    one_or_two number := 3;
  3  begin
  4    case one_or_two
  5    when 1 then dbms_output.put_line('One');
  6    when 2 then dbms_output.put_line('Two');
  7    else dbms_output.put_line('Try again');
  8    end case;
  9  end;
 10  /
Try again
 
PL/SQL procedure successfully completed.
 
SQL>

An alternative is to add an exception section and check for case_not_found:

SQL> declare
  2    one_or_two number := 3;
  3  begin
  4    case
  5    when one_or_two = 1 then
  6      dbms_output.put_line('One');
  7    when one_or_two = 2 then
  8      dbms_output.put_line('Two');
  9    end case;
 10  exception
 11    when case_not_found then
 12      dbms_output.put_line('Try again');
 13  end;
 14  /
Try again
 
PL/SQL procedure successfully completed.
 
SQL>

Once a condition has been satisfied, Oracle jumps to the end case statement, ignoring the remaining conditions:

SQL> declare
  2    one_or_two number := 1;
  3  begin
  4    case
  5    when one_or_two = 1 then
  6      dbms_output.put_line('One');
  7    when one_or_two < 2 then
  8      dbms_output.put_line('Less than two');
  9    end case;
 10  exception
 11    when case_not_found then
 12      dbms_output.put_line('Try again');
 13  end;
 14  /
One
 
PL/SQL procedure successfully completed.
 
SQL>

You can even use a Boolean expression after the word case, as you can see in the two examples below:

SQL> begin
  2  case 1 = 1
  3  when true then
  4    dbms_output.put_line('1 = 1');
  5  when false then
  6    dbms_output.put_line('1 != 1');
  7  end case;
  8  end;
  9  /
1 = 1
 
PL/SQL procedure successfully completed.
 
SQL> begin
  2  case 1 = 2
  3  when true then
  4    dbms_output.put_line('1 = 2');
  5  when false then
  6    dbms_output.put_line('1 != 2');
  7  end case;
  8  end;
  9  /
1 != 2
 
PL/SQL procedure successfully completed.
 
SQL>

These examples were all tested in an Oracle 11 database.