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.

SUBSTR Versus LIKE in Oracle 11.2

I was reading an old SQL tuning book which was printed in 2002. It said that a where clause with like could often use an index whereas a similar clause using substr could not. I wondered if this might still be the case in an Oracle 11.2.0.1 database. To find out, I created a table:

SQL> conn andrew/reid
Connected.
SQL> create table tab1 as
  2  select table_name from dba_tables
  3  /

Table created.

SQL>

... and made sure it had plenty of data:

SQL> begin
  2  for a in 1..12 loop
  3  insert into tab1 select * from tab1;
  4  end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> select count(*) from tab1
  2  /

  COUNT(*)
----------
  13348864

SQL>

I added an extra row which I could look for later:

SQL> insert into tab1 values('DAILY_FORECAST')
  2  /

1 row created.

SQL>

...added an index to help find it:

SQL> create index ind1 on tab1(table_name)
  2  /

Index created.

SQL>

...and collected statistics:

SQL> exec dbms_stats.gather_table_stats(-
> ownname=>'andrew', -
> tabname=>'tab1', -
> cascade=>true);

PL/SQL procedure successfully completed.

SQL>

I used like to find the row and it took 0.39 seconds:

SQL> alter session set sql_trace = true
  2  /

Session altered.

SQL> set timing on
SQL> select count(*) from tab1
  2  where table_name like 'DAILY%'
  3  /

  COUNT(*)
----------
         1

Elapsed: 00:00:00.39

SQL>

... but when I used substr, it took 28.79 seconds:

SQL> select count(*) from tab1
  2  where substr(table_name,1,5) = 'DAILY'
  3  /

  COUNT(*)
----------
         1

Elapsed: 00:00:28.79

SQL> set timing off
SQL> alter session set sql_trace = false
  2  /

Session altered.

SQL>

I ran the trace file through tkprof to see how Oracle had executed the SQL. The statement which used substr had done a full table scan:

********************************************************************************

select count(*) from tab1
where substr(table_name,1,5) = 'DAILY'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      7.16      28.78      38936      38940          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      7.16      28.78      38936      38940          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 8891  (ANDREW)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=38940 pr=38936 pw=0 time=0 us)
      1   TABLE ACCESS FULL TAB1 (cr=38940 pr=38936 pw=0 time=0 us cost=9169 size=2135824 card=133489)

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
      1   SORT (AGGREGATE)
      1    TABLE ACCESS   MODE: ANALYZED (FULL) OF 'TAB1' (TABLE)

********************************************************************************

...but the statement which used like had used the index:

********************************************************************************

select count(*) from tab1
where table_name like 'DAILY%'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.02       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.01          3          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.02       0.02          3          3          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 8891  (ANDREW)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=3 pr=3 pw=0 time=0 us)
      1   INDEX RANGE SCAN IND1 (cr=3 pr=3 pw=0 time=0 us cost=19 size=65792 card=4112)(object id 211183)

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
      1   SORT (AGGREGATE)
      1    INDEX   MODE: ANALYZED (RANGE SCAN) OF 'IND1' (INDEX)

********************************************************************************

Can You Use BETWEEN and a Subquery Together?

I saw a colleague about to throw away his copy of Oracle 7 The Complete Reference so I rescued it from the bin and started to read it (as you do). It said that you could not use BETWEEN and a subquery in the same SQL statement and I wondered if this restriction still applied. The oldest version of Oracle I have access to is Oracle 9 so I decided to try it out there. The examples I came up with are a bit artificial but they show that you can now use BETWEEN and a subquery in the same SQL statement. Furthermore, the subquery can be:

 

(1)  Before the AND.

(2)  After the AND.

(3)  Before and after the AND.   

 

SQL> select count(*) from dba_users

  2  where created between

  3  (select created from dba_users

  4   where username = 'DATALOAD')

  5  and '31-DEC-2013'

  6  /

 

  COUNT(*)

----------

        73

 

SQL> select count(*) from dba_objects

  2  where object_id between

  3  100 and

  4  (select max(object_id) from dba_objects

  5   where owner = 'SYSTEM')

  6  /

 

  COUNT(*)

----------

      8850

 

SQL> select count(*) from dba_tables

  2  where initial_extent between

  3  (select min(initial_extent)

  4   from dba_tables

  5   where owner = 'SYSTEM')

  6  and

  7  (select max(initial_extent)

  8   from dba_tables

  9   where owner = 'SYSTEM')

10  /

 

  COUNT(*)

----------

      1196

 

SQL>

SQL ANY Clause

The SQL ANY clause allows you to compare a series of columns with some other value. In this example, the other value is a literal. I tested it on Oracle 11.1.0.6.0. First I created a table showing what I ate for each meal on a given day:

SQL> create table meals
  2  (day       varchar2(3),
  3   breakfast varchar2(10),
  4   lunch varchar2(10),
  5   dinner    varchar2(10))
  6  /

Table created.

SQL>

Then I added some imaginary data (I don't really eat nut roast):

SQL> insert into meals values
  2  ('MON', 'WEETABIX', 'PIZZA', 'KIPPERS')
  3  /

1 row created.

SQL> insert into meals values
  2  ('TUE', 'CORNFLAKES', 'LASAGNE', 'BEEF')
  3  /

1 row created.

SQL> insert into meals values
  2  ('WED', 'PORRIDGE', 'LAMB', 'NUT ROAST')
  3  /

1 row created.

SQL> select * from meals
  2  /

DAY BREAKFAST  LUNCH      DINNER
--- ---------- ---------- ----------
MON WEETABIX   PIZZA      KIPPERS
TUE CORNFLAKES LASAGNE    BEEF
WED PORRIDGE   LAMB       NUT ROAST

SQL>

Finally, I used the ANY clause to check whether I ate a particular food for breakfast, lunch or dinner:

SQL> select * from meals
  2  where 'WEETABIX' = any(breakfast, lunch, dinner)
  3  /

DAY BREAKFAST  LUNCH      DINNER
--- ---------- ---------- ----------
MON WEETABIX   PIZZA      KIPPERS

SQL> select * from meals
  2  where 'LASAGNE' = any(breakfast, lunch, dinner)
  3  /

DAY BREAKFAST  LUNCH      DINNER
--- ---------- ---------- ----------
TUE CORNFLAKES LASAGNE    BEEF

SQL> select * from meals
  2  where 'NUT ROAST' = any(breakfast, lunch, dinner)
  3  /

DAY BREAKFAST  LUNCH      DINNER
--- ---------- ---------- ----------
WED PORRIDGE   LAMB       NUT ROAST

SQL>