I do have a massive list of potential blog post subjects and topics that I always collect and write down. Some I have already started some research and tests and examples and some are just titles for now. As you will have seen I have blogged more in the last couple of years than I did for some time; I want to try and do more and more regularly and get the knowledge out there.
So here goes for todays subject. A guy from the USA sent me a question. He said in Oracle 11g he used syntax such as:
SQL> connect system/oracle1@//192.168.56.85:1521/bfora.localdomain
Connected.
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL> alter user pete identified by values 'LOCKED';
User altered.
SQL>
He said when he does this it creates a value in SYS.USER$.PASSWORD that can never be hashed to a real hex value so the account cannot be logged into if it is open. He asked if his thinking is correct or flawed.
Yes, this is the most famous undocumented feature of Oracle and I have been telling people to do this with accounts for 20 years or more to stop them from being logged into. In more recent times (since proxy was added to Oracle) this is a great tool to use make sure that a user does not log into a schema directly. So we do not give the schema passwords to developers / DBAs or release teams. Instead the schema remains inaccessible directly and instead the support or release is done via a proxy account. In this way no one logs onto schemas but code can still be run and installed to create objects such as tables, views, PL/SQL code. We can even enable audit targeted at the support person or release person using their proxy and audit every action whilst not auditing every action as the schema when not proxied.
But the syntax he showed and used in 11g is actually the syntax for 10g and earlier as the syntax in the BY VALUES clause didn't include the SHA1 password hash. We can see that it did work:
SQL> connect sys/oracle1@//192.168.56.85:1521/bfora.localdomain as sysdba
Connected.
SQL> select name,password,spare4 from sys.user$ where name='PETE';
NAME PASSWORD
------------------------------ ------------------------------
SPARE4
--------------------------------------------------------------------------------
PETE LOCKED
SQL>
But it is not strictly correct as we should also have the SHA1 hash in SYS.USER$.SPARE4 but it works still in 11g. The questioner said if the method is correct then he gets an error in 12c when he uses the same syntax. Lets try in 18c:
SQL> connect system/oracle1@//192.168.56.78:1523/xepdb1
Connected.
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
SQL>
Lets create a sample user to test with:
SQL> create user pete identified by pete;
User created.
SQL>
Now try and use the old syntax as the questioner said he used it in 12c and got an error:
SQL> alter user pete identified by values 'LOCKED';
alter user pete identified by values 'LOCKED'
*
ERROR at line 1:
ORA-02153: invalid VALUES password string
SQL>
Yes, we get the same issue that the questioner said he got in 12c. He asked is this still possible from 12c as he felt its more secure to stop connections as schemas. I agree, its better to never connect as the schema so this is a method BUT 18c and 19c have a new way. Lets look at the old way first. The BY VALUES clause failed because it doesn't include the later password hashes. So instead we can use DBMS_METADATA.GET_DDL to get the right syntax:
SQL> set serveroutput on
SQL> begin
2 dbms_output.put_line(dbms_metadata.get_ddl('USER','PETE'));
3 end;
4 /
CREATE USER "PETE" IDENTIFIED BY VALUES
'S:03E63AA057AE0C4C55D184E9BF44719C43ABBC5E8B8A5C8FE06ECAFE9EDA;T:BC005789B76E3B
7E1BCD1DFDEF98B04C8DA8B1DC02170444001A59092242301AA18D93BD99D9E60187BD38DDE9A274
21C0FB77C72166B53A27199F17203E7D8B69FCD1E1F8EE26A1CA57D8BD9ED78D97'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"
PL/SQL procedure successfully completed.
SQL>
This gives me the right syntax. I can modify the statement above and change CREATE USER to ALTER USER and also change the hashes to 0000... instead. I can run this as follows:
SQL> ALTER USER "PETE" IDENTIFIED BY VALUES 'S:000000000000000000000000000000000000000000000000000000000000;T:0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000';
User altered.
SQL>
What is stored in sys.user$:
SQL> connect sys/oracle1@//192.168.56.78:1523/xepdb1 as sysdba
Connected.
SQL> select name,password,spare4 from sys.user$ where name='PETE';
NAME
--------------------------------------------------------------------------------
PASSWORD
--------------------------------------------------------------------------------
SPARE4
--------------------------------------------------------------------------------
PETE
S:000000000000000000000000000000000000000000000000000000000000;T:000000000000000
00000000000000000000000000000000000000000000000000000000000000000000000000000000
00000000000000000000000000000000000000000000000000000000000000000
SQL>
Can we instead change the string to LOCKED or similar as with the older syntax:
SQL> ALTER USER "PETE" IDENTIFIED BY VALUES 'S:LOCKED;T:LOCKED';
ALTER USER "PETE" IDENTIFIED BY VALUES 'S:LOCKED;T:LOCKED'
*
ERROR at line 1:
ORA-02153: invalid VALUES password string
SQL>
No, it seems not, maybe we can use LOCKED as a string but match the length of the original strings, 60 characters for the SHA1 S: string and 160 characters for the SHA2 T: string:
SQL> ALTER USER "PETE" IDENTIFIED BY VALUES 'S:LOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKED;T:LOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCK';
ALTER USER "PETE" IDENTIFIED BY VALUES 'S:LOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKED;T:LOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCK'
*
ERROR at line 1:
ORA-02153: invalid VALUES password string
SQL>
No, that doesn't work, what if we just change the zeros to a HEX letter such as A:
SQL> ALTER USER "PETE" IDENTIFIED BY VALUES 'S:AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA;T:AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA';
User altered.
SQL>
So the number uses is most likely tested to see if it is a valid HEX string. Lets change one letter to an invalid hex string and see what happens:
SQL> ALTER USER "PETE" IDENTIFIED BY VALUES 'S:GAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA;T:AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA';
ALTER USER "PETE" IDENTIFIED BY VALUES 'S:GAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA;T:AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'
*
ERROR at line 1:
ORA-02153: invalid VALUES password string
SQL>
I changed the first letter to G of the SHA1 hash and the error comes back. So lets try one more test. If we change the SHA1 hash to 59 characters BUT valid HEX what happens:
SQL> ALTER USER "PETE" IDENTIFIED BY VALUES 'S:AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA;T:AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA';
ALTER USER "PETE" IDENTIFIED BY VALUES 'S:AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA;T:AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'
*
ERROR at line 1:
ORA-02153: invalid VALUES password string
SQL>
So, in summary, the old BY VALUES still works in 12c, 18c, 19c BUT its much stricter than the methods in 11g where you could type in any old text as an invalid password. Now you must supply a string for the SHA1 and SHA2 hashes that are the correct length, 60 characters and 160 characters and also the hash value must be valid HEX. So if you use this method set the strings to valid HEX, BUT that presumably means that because its valid hex then its maybe possible to enter some password that would hash to that HEX value. The chances of someone guessing a password combination that would hash to the HEX value you chose is pretty pretty slim.
In 18c and 19c there is now a better method. The BY VALUES is in effect now built into the DDL syntax with the NO AUTHENTICATION syntax. This is called "SCHEMA ONLY ACCOUNTS" in Oracle and is a new welcome shortcut. We can test this for my sample user PETE in 18cXE:
SQL> alter user pete no authentication;
User altered.
SQL>
The values in SYS.USER$ are:
SQL> select name,password,spare4 from sys.user$ where name='PETE';
NAME
--------------------------------------------------------------------------------
PASSWORD
--------------------------------------------------------------------------------
SPARE4
--------------------------------------------------------------------------------
PETE
S:000000000000000000000000000000000000000057C9FE8ED313BD2F8D26;T:000000000000000
00000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000104D5ED049FF791913E7242803485640
SQL>
The values are not all zeros. For instance the last 20 characters of the SHA1 hash are the SALT. A simple SQL script called sha1.sql on my site from 2007 shows how this password algorithm works and shows that the SALT is the last 20 characters of this hash. This is interesting and if you chose to use the BY VALUES instead of this feature in 18c or 19c or indeed 20c then it would make sense to generate a random SALT also and not just use zeros. If you are on 12.1 or 12.2 then the NO AUTHENTICATION syntax is not there anyway. Why not just use all ZEROs or all 99999s or AAAAs or whatever. Well if everyone just used all 000s then it may be possible that someone at some point could brute force inputs (possible passwords) and crack or find a password that could hash to 60 zeros or 60 AAAs or whatever. A random SALT would prevent that pre-defined hash/password problem. Also don't use DBMS_METADATA.GET_DDL and use the same SYS.USER$.SPARE4 values from one database for all passwords in another.
Any schema in your database should have one of these impossible passwords or from 18c a SCHEMA ONLY ACCOUNT - the same thing really.
More on this soon!! Bye from WFH (Working From Home)
