SQL> sho con_name
CON_NAME
------------------------------
PDB1
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
SQL> select name,value from v$parameter where name='utl_file_dir';
no rows selected
SQL>
A few other initial things popped out after a quick look around. When Oracle released 12.1.0.1 users password hashes held in SYS.USER$ PASSWORD and SPARE4 columns for COMMON users were visible in the pluggable database. So any user of a pluggable database in 12.1.0.1 who had access to SYS.USER$ could get the hashes of common users and crack them. In Oracle 12.1.0.2 the hashes for COMMON users were restricted to the root container for COMMON users and so therefore could not be read in a PDB by a user with access to SYS.USER$. A typical user record in USER$ in the PDB for SYS in 18c showed just:
SQL> select name,password,spare4 from sys.user$ where type#=1;
NAME PASSWORD SPARE4
-------------------- ---------------- ------------------------------------------------------------------------------------------------------------------------------------------------------
SYS S: ;T:
So the hashes cannot be got and cracked. In 18c however there are hashes for AUDSYS and OJVMSYS in the PDB even though these are common users, so this is a slight backwards step from 12.1.0.2 onwards:
SQL> select name,password,spare4 from sys.user$ where type#=1;
NAME PASSWORD SPARE4
-------------------- ---------------- ------------------------------------------------------------------------------------------------------------------------------------------------------
SYS S: ;T:
AUDSYS S:0000000000000000000000000000000000000000238A9F2533A860B69A14;T:0000000000000000000000000000000000000000000000000000000000000000000000000000000000000
00000000000000000000000000000000000000000009EF28DDC54D8B11F24544FD430FA7305
...
OJVMSYS S:0000000000000000000000000000000000000000D72C9C79DA0AF29C4DA1;T:0000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000AB86EC181DBAB04F1506023214FA02B2
...
The hashes in the root container are:
...
AUDSYS S:0000000000000000000000000000000000000000238A9F2533A860B69A14;T:0000000000000000000000000000000000000000000000000000000000000000000000000000000000000
00000000000000000000000000000000000000000009EF28DDC54D8B11F24544FD430FA7305
...
OJVMSYS S:0000000000000000000000000000000000000000EA7E84C5B64D0B47720E;T:0000000000000000000000000000000000000000000000000000000000000000000000000000000000000
00000000000000000000000000000000000000000008A409374A738DEC7418ECA7FC16527E2
...
Note that the AUDSYS hash is the same in the PDB and CDB but the hashes for OJVMSYS are the different. In creating this 18c database I used a CREATE DATABASE scripted command in SQL*plus and then ran catcdb.ql which runs catcon.pl to install the dictionary in the root and seed containers. I also ran catcon.pl via SQL*Plus to install the Java VM into the database. I have not touched these accounts. So slight backwards step BUT take a look at the hashes for 11g and 12c; the S: and the T: values in the SPARE4 column of USER$. Note that the first section of the AUDSYS hash is all zero's for the 11g and the same for the 12c password. The last part is the SALT. So 0000000000000000000000000000000000000000 is the hashed SHA1 password for 11g and the last part 238A9F2533A860B69A14 is the SALT. So interesting. The past method to set an account so it could not log in was to say ALTER USER BLAH ACCOUNT LOCK and this sets the account status and prevents a login BUT someone can simply unlock it and the same password hash stored is used again. The second method is to use the so called UNDOCUMENTED mode of ALTER USER or CREATE USER using the BY VALUES...clause. This effectively writes a value direct to the SYS.USER$ table to the PASSWORD or SPARE4 columns. If you look at the XS$NULL user in 18c it clearly uses this method:
...
XS$NULL S:000000000000000000000000000000000000000000000000000000000000
...
This shows the SHA1 hash for XS$NULL; interestingly it doesn't include a SHA2 hash for 12c. The file catts.sql in $ORACLE_HOME/rdbms/admin in 18c shows:
...
Rem
Rem Create the XS$NULL user. This user represents the state where DB UID
Rem is invalid but the schema ID is valid. Currently used by Fusion since 11gR1
Rem
create user XS$NULL identified by values
'S:000000000000000000000000000000000000000000000000000000000000'
account lock password expire default tablespace system
/
...
So why the new hash syntax; storing a SALT and a zero hash and why not just use IDENTIFIED BY VALUES? in 18c we can now create a schema with no password authentication. Oracle has done this in creating the AUDSYS user in 18c. The file $ORACLE_HOME/rdbms/admin/dsec.bsq shows this:
...
create user AUDSYS no authentication account lock
/
...
So Oracle now has in effect a supported syntax for an invalid password in the NO AUTHENTICATION addition to the CREATE USER and ALTER USER syntax. This forces a password hash of all zeros; my preference for DES hashes was "--locked--" and all 9999's in 11g and 12c but 0000's are good too. The interesting difference is the SALT being there? why? Also AUDSYS is created LOCKED; sort of pointless other than to prevent PROXY access. If we look at the record on 18c in DBA_USERS there is one other thing:
SQL> @print 'select * from dba_users where username=''''AUDSYS'''''
old 33: --lv_str:=translate('&&1','''','''''');
new 33: --lv_str:=translate('select * from dba_users where username=''AUDSYS''','''','''''');
old 34: print('&&1');
new 34: print('select * from dba_users where username=''AUDSYS''');
Executing Query [select * from dba_users where username='AUDSYS']
USERNAME : AUDSYS
USER_ID : 8
PASSWORD :
ACCOUNT_STATUS : LOCKED
LOCK_DATE : 05-MAY-18
EXPIRY_DATE :
DEFAULT_TABLESPACE : DEFTBS
TEMPORARY_TABLESPACE : TEMPTS1
LOCAL_TEMP_TABLESPACE : TEMPTS1
CREATED : 05-MAY-18
PROFILE : DEFAULT
INITIAL_RSRC_CONSUMER_GROUP : DEFAULT_CONSUMER_GROUP
EXTERNAL_NAME :
PASSWORD_VERSIONS :
EDITIONS_ENABLED : N
AUTHENTICATION_TYPE : NONE
PROXY_ONLY_CONNECT : N
COMMON : YES
LAST_LOGIN :
ORACLE_MAINTAINED : Y
INHERITED : YES
DEFAULT_COLLATION : USING_NLS_COMP
IMPLICIT : NO
ALL_SHARD : NO
-------------------------------------------
PL/SQL procedure successfully completed.
SQL>
Note that the column AUTHENTICATION_TYPE states NONE and there is no password versions in 18c. In 12.2.0.1 it shows:
SQL> @print 'select * from dba_users where username=''''AUDSYS'''''
old 33: --lv_str:=translate('&&1','''','''''');
new 33: --lv_str:=translate('select * from dba_users where username=''AUDSYS''','''','''''');
old 34: print('&&1');
new 34: print('select * from dba_users where username=''AUDSYS''');
Executing Query [select * from dba_users where username='AUDSYS']
USERNAME : AUDSYS
USER_ID : 8
PASSWORD :
ACCOUNT_STATUS : EXPIRED & LOCKED
LOCK_DATE : 26-JAN-17
EXPIRY_DATE : 26-JAN-17
DEFAULT_TABLESPACE : USERS
TEMPORARY_TABLESPACE : TEMP
LOCAL_TEMP_TABLESPACE : TEMP
CREATED : 26-JAN-17
PROFILE : DEFAULT
INITIAL_RSRC_CONSUMER_GROUP : DEFAULT_CONSUMER_GROUP
EXTERNAL_NAME :
PASSWORD_VERSIONS : 11G 12C
EDITIONS_ENABLED : N
AUTHENTICATION_TYPE : PASSWORD
PROXY_ONLY_CONNECT : N
COMMON : YES
LAST_LOGIN :
ORACLE_MAINTAINED : Y
INHERITED : NO
DEFAULT_COLLATION : USING_NLS_COMP
IMPLICIT : NO
ALL_SHARD : NO
-------------------------------------------
PL/SQL procedure successfully completed.
SQL>
The authentication_type is not a new column as it was there in 12c as well but we could not create database accounts with the syntax NO AUTHENTICATION as in 18c.
We can create our own accounts using the same syntax in 18c:
SQL> connect sys/oracle1@//192.168.1.69:1521/pdb1 as sysdba
Connected.
SQL> select sys_context('USERENV','CON_NAME') from dual;
SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
PDB1
SQL> set serveroutput on
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
SQL> create user pete no authentication;
User created.
SQL> create user proxy identified by proxy;
User created.
SQL> grant create session to proxy;
Grant succeeded.
SQL> alter user pete grant connect through proxy;
User altered.
SQL> connect proxy[pete]/proxy@//192.168.1.69/pdb1
ERROR:
ORA-01045: user PETE lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
SQL> connect sys/oracle1@//192.168.1.69:1521/pdb1 as sysdba
Connected.
SQL> grant create session to pete;
Grant succeeded.
SQL> connect proxy[pete]/proxy@//192.168.1.69/pdb1
Connected.
SQL> sho user
USER is "PETE"
SQL> connect sys/oracle1@//192.168.1.69:1521/pdb1 as sysdba
Connected.
SQL> alter user pete account lock;
User altered.
SQL> connect proxy[pete]/proxy@//192.168.1.69/pdb1
ERROR:
ORA-28000: The account is locked.
Warning: You are no longer connected to ORACLE.
SQL> connect sys/oracle1@//192.168.1.69:1521/pdb1 as sysdba
Connected.
SQL>
The above example shows that we can also create a schema called PETE and set the AUTHENTICATION to NONE. This means that the account cannot be logged into. So how do we deploy code or tables to this schema without using a DBA and CREATE ANY rights? well the answer is PROXY. We can create an account called PROXY who can connect through PETE (the schema) and in all intents and purposes in the database this account PROXY is PETE but in terms of audit trails we can identify that it was indeed the proxy user caller PROXY. Now, to allow a user to proxy we needed to grant CREATE SESSION to the schema and also at the end if we LOCK the schema we cannot proxy. We need the proxy only for CREATE time and any changes to the structure OR we can use sweeping rights as a DBA (less good). This new syntax is a step in the right direction BUT anyone who knows me and has employed me knows that I have recommended the same approach since 9i (harder to do because of the limited proxy support) using an account that is a schema and is locked with an impossible password. So its not just for 18c we can still use the IDENTIFIED BY VALUES clause in earlier versions; the missing piece of course is that 18c has supported syntax to achieve this and also adds the NONE to the authentication type in DBA_USERS.
I always recommend locking schemas, revoking CREATE SESSION and using PROXY for maintenance as we can get targeted audit trails via proxy.
More 18c later....
