Password expiration is dead long live your password!

Good old Microsoft - somebody has finally seen the light. I have thought this for a very long time.

Password-expiration-is-dead-long-live-your-password


I still think passwords have a place in IT but with two factor authentication such as www.duo.com this obsession with changing passwords and having users writing them down or storing in the likes of KeePass needs to end; just let the user remember a decent password and go with what Microsoft are saying.

"Recent scientific research calls into question the value of many long-standing password-security practices such as password expiration policies, and points instead to better alternatives … If a password is never stolen, there’s no need to expire it. And if you have evidence that a password has been stolen, you would presumably act immediately rather than wait for expiration to fix the problem.
…If an organization has successfully implemented banned-password lists, multi-factor authentication, detection of password-guessing attacks, and detection of anomalous logon attempts, do they need any periodic password expiration? And if they haven’t implemented modern mitigations, how much protection will they really gain from password expiration? …Periodic password expiration is an ancient and obsolete mitigation of very low value."

Login to Oracle when max sessions exceeded

Quick tip - thanks to Russell Willcox for this one :)

See also :

 http://www.dba-oracle.com/t_connecting_hung_instance_using_prelim_option.htm

Login when max sessions exceeded (and you can’t log in using standard methods)

This is the backdoor entry method to access the database in a hung situation where no other methods are allowing you to create new sessions. 

This will not create any session in the database and connects to the SGA directly.


cmd> sqlplus –prelim “/ as sysdba”  (or… sqlplus –prelim sys/password as sysdba)

note: just try typing sqlplus –prelim / as sysdba (don’t cut and paste – it doesn’t work)

sql> shutdown abort

sql> startup restrict

sql> exit

cmd> sqlplus / as sysdba

sql> alter system set sessions=600 scope=spfile;

sql> alter system set processes=600 scope=spfile;

sql> alter system set parallel_max_servers=0 scope=both; (IF NECESSARY... i.e. parallel processes swamping database)

sql> alter database mount;

sql> alter database open;

sql> create pfile from spfile;

sql> alter system disable restricted session;

Check sqlbind variable values

Very simple example to check sqlbind variable (will update as I go on - work in progress).

SQL > variable TABLSPC varchar2(50);
SQL> exec :TABLSPC := 'AP_MAIN';
PL/SQL procedure successfully completed.
SYS@abp1 SQL> select TABLESPACE_NAME, FILE_NAME from dba_data_files where tablespace_name = :TABLSPC

TABLESPACE_NAME FILE_NAME
--------------- --------------------------------------------------
AP_MAIN +DATA/AP/DATAFILE/ap_main.01.dbf

SQL>select sql_text, sql_id, LAST_ACTIVE_TIME from v$sqlarea where sql_text like '%TABLSPC%'

SQL_TEXT SQL_ID LAST_ACTI
------------------------------------------------------------------------------------------------------------------------ ------------- ---------
select FILE_NAME, TABLESPACE_NAME from dba_data_files where tablespace_name = :TABLSPC 6wg3unwbc0qk5 08-JUL-19

From here : http://www.oracle-wiki.net/startsqlshowbindvariable


SET PAUSE ON

SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300

COLUMN sql_text FORMAT A120
COLUMN sql_id FORMAT A13
COLUMN bind_name FORMAT A10
COLUMN bind_value FORMAT A26

SELECT sql_id, t.sql_text sql_text, b.name bind_name, b.value_string bind_value
FROM v$sql t JOIN v$sql_bind_capture b using (sql_id) WHERE
b.value_string is not null AND sql_id='&sqlid'


Enter value for sqlid: 6wg3unwbc0qk5
old 13: sql_id='&sqlid'
new 13: sql_id='6wg3unwbc0qk5'
Press Return to Continue
SQL_ID SQL_TEXT BIND_NAME BIND_VALUE
------------- ------------------------------------------------------------------------------------------------------------------------ ---------- --------------------------
6wg3unwbc0qk5 select FILE_NAME, TABLESPACE_NAME from dba_data_files where tablespace_name = :TABLSPC :TABLSPC AP_MAIN