Pete Finnigan's Oracle Security Weblog

I have been embroiled in a twitter thread today about the post i made in this blog yesterday around granting privileges to a user and who should do the granting. Patrick today asked a further question: How do you make grants against SYS for another user. Bryn suggested get a user created that has the grants granted WITH GRANT OPTION (wow so many uses of the word GRANT in one phrase!) and I suggested two options:

1 - (me) If these grants to SYS objects are needed to be done once at the start of the deployment of the application then i don't see an issue to simply login as SYSDBA and make the grants; its better than granting these grants to another user
2 - Bryns suggestion is to grant to another user WITH GRANT so it can make the grants to further users. My issue with this is that the grants are made to a user who doesn't need them simply so it can be used to grant to further users. OK, its better than logging in as SYSDBA if these grants need to be done regularly.
3 - (me) I also suggested making a quick API to be used by the DBA so that a non SYS user can make grants without having to have the grants made to it. This would use DBMS_SYS_SQL.PARSE_AS_USER() and run the code then as SYS. Bryn told me off for suggesting this as its unsupported to use this package in customer code - he is right we should not use un-supported methods and code. This package is in fact used in a lot of places such as APEX but Bryn is of course right that if the package were changed or removed because its unsupported then your code would break.

Anyway whilst I am not supposed to do this; sorry Bryn.:-). I wanted to make one point about this package use; so here is a quick demo. First connect as SYS and create a user called HACKER and grant him CREATE SESSION so he can log on and also grant EXECUTE on DBMS_SYS_SQL (This is a 12.2 database):

SQL> connect sys/oracle1@//192.168.56.95:1539/orcl.localdomain as sysdba
Connected.
SQL> 
SQL> create user hacker identified by hacker;

User created.

SQL> grant create session to hacker;

Grant succeeded.

SQL> grant execute on dbms_sys_sql to hacker;

Grant succeeded.

SQL>

Now connect as the new hacker user and use the package to run code as SYS and grant DBA to ourselves:

SQL> connect hacker/hacker@//192.168.56.95:1539/orcl.localdomain
Connected.
SQL> 
SQL> !vi s.sql

SQL> get s
  1  declare
  2  sqlt varchar2(32767):='grant dba to hacker identified by hacker';
  3  i integer;
  4  begin
  5  i:=sys.dbms_sys_sql.open_cursor();
  6  sys.dbms_sys_sql.parse_as_user(i,sqlt,dbms_sql.native,0);
  7  sys.dbms_sys_sql.close_cursor(i);
  8* end;
SQL> @s

PL/SQL procedure successfully completed.

SQL> sho user
USER is "HACKER"
SQL> select * from user_role_privs;

USERNAME
--------------------------------------------------------------------------------
GRANTED_ROLE
--------------------------------------------------------------------------------
ADM DEL DEF OS_ COM INH
--- --- --- --- --- ---
HACKER
DBA
NO  NO	YES NO	NO  NO


SQL> select * from session_roles;

no rows selected

SQL>

We got the DBA role it's in USER_ROLE_PRIVS but its not enabled. We can enable if we want. But now the point I want to make:

SQL> !vi s.sql

SQL> get s
  1  declare
  2  sqlt varchar2(32767):='grant sysdba to hacker';
  3  i integer;
  4  begin
  5  i:=sys.dbms_sys_sql.open_cursor();
  6  sys.dbms_sys_sql.parse_as_user(i,sqlt,dbms_sql.native,0);
  7  sys.dbms_sys_sql.close_cursor(i);
  8* end;
SQL> @s
declare
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SYS_SQL", line 1412
ORA-06512: at line 6


SQL> 

We ran the same piece of code but this time try and grant SYSDBA not DBA and it does not work; this is because we are running as SYS and not SYSDBA; interesting; The dictionary accessibility is safe so we are protecting %ANY% rights and we cannot connect as SYS without SYSDBA but we cannot grant SYSDBA because we would need to be SYSDBA to do it. So whilst this package can run code as any user; we used an ID :=0 to mean run as SYS it is not SYSDBA.

Patrick Jolliffe posted a question via a tweet back in April but due to personal health pressures with a close relative of mine I have not had the time to deal with much over the last few months. I did make a note though to come back to this question as it is a good one and answer at least my view on this. Patricks post on twitter and Bryn's reply is here. I will repeat the question here though in case the Twitter post ages into infinity in the future:

"#smartdb & best practices dictate granting fine-grained object level privs.
Which user(s) should do the granting?
1) Objects owner(s)
2) SYS
3) adedicated granting schema
4) personal account of person doing the grant
5) other (please elaborate)"

This is an interesting question and is one that comes up in my training classes when i teach them live in person or on line via webex. Details of all my up-coming online classes and also a live in person class in York, UK are on our Public Training dates page. I will be adding at least one more public live in person training date in York soon and possibly two dates and possibly one in Europe as well as I am also working to offer live in person classes in some choice other countries with partners (more soon, when if/its agreed!).

Back to the question; sorry for the interlude so soon in the post. People regularly do ask me this same question; how do we make grants on objects in the database such as tables, views or procedures; do we do it as the schema; do we do it as the DBA? or what?

A similar issue occurs related to this question in that who do we create the objects "as" in the database in the first place? do we log onto the schema and with the schemas CREATE PROCEDURE and CREATE TABLE system privileges run the scripts as "create table...", "create procedure..." or do we connect as a DBA and use his CREATE ANY PROCEDURE or his CREATE ANY TABLE and alter all the scripts to say "create scott.table...." or "create scott.procedure....". There are issues with both of these so lets address these first as we cannot make grants until we have created objects.

So, in 18c Oracle has created a supported way to have a schema that cannot be logged into. This is great BUT i have been doing this for years anyway. The new syntax is CREATE USER blah NO AUTHENTICATION. We can do the same in versions before 18c by using the syntax CREATE USER blah IDENTIFIED BY VALUES... so the only difference really is that its supported and the AUTHENTICATION_TYPE column of SYS.USER$ is now set to NONE rather than PASSWORD even if the password hash value was all zeros or in DES syntax some non HEX text.

So Oracle clearly also think its a good idea to not log into the schema at all. I agree and have been telling people for years to not do that. Also in a lot of systems that I perform audits I find that not only do support and DBAs log into the schemas so does the applications from application servers or pc based logins.

So my view is to not use a DBA account either that therefore needs %ANY% type grants. Also a developer or release account with these types of rights is not correct either as it means that these accounts can also change any other schema except SYS with these rights. Also if you take this approach the code needs to be written as CREATE SCOTT.OBJECT not CREATE OBJECT; not neat and not flexible if you decided to do application/schema as a service.

A better approach is to write code as CREATE TABLE.... CREATE PROCEDURE.... and use proxy. Create an account that can be used by the release team to proxy to the schema. Then when it logs in with user[connect to user]/password you are in all intents and purposes the schema BUT you can audit actually who did the changes by either using proxy audit .... ON BEHALF OF... or you can simply use a log on trigger and get the proxy ID and link it to normal audit records. In this way you can audit every action of the release without the need to modify the application audit to capture everything just for the release. You can then instead use the AUDIT ALL STATEMENTS IN SESSION CURRENT from a log on trigger; capture the proxy as well when the connection is proxying to the schema. In this way the proxy user cannot disable this audit; another security feature.

There are a couple of caveats - see my 18c blog post from last week. You cannot LOCK the account you want to proxy through - that stops proxy from working. Also even if you have locked the schema with an impossible password or the new NO AUTHENTICATION in 18c then you cannot proxy if you don't grant CREATE SESSION to the schema - see my 18c post for an example.

A better addition to this is to grant the CREATE rights to a role and then grant the role to the schema. Then the role should be granted only for creation time and maintenance windows. All other times it should be revoked. This means that no CREATE SESSION, no direct connection either with 18c or with BY VALUES and no connections via proxy (CREATE SESSION is revoked). This gives layers of security. The maintenance needs the DBA to grant the role BUT the DBA doesn't need CREATE ANY type rights as the PROXY user uses those via the role and the proxy connection.

So back to Patricks question; you can guess the answer; use the same PROXY connection to connect to the schema, be audited for all actions, so we know who actually did it and make the grants as the SCHEMA but through the PROXY. So to answer Patricks points:

1) Don't connect direct to the schema in this way we can control schema access
2) Not SYSDBA; we should not use SYSDBA for application support. its the superuser (sort of) and we should not use it on a daily basis
3) Don't use a dedicated granting schema as this means we would have to grant the privileges WITH GRANT OPTION - this is bad as it moves the chain of control from the schema owner to another user who has the control to grant to whoever he pleases. This moves the security out a layer. Bad
4) A personal account of the person doing the grant - not sure what he means by this? - either way it would have to be an account with an ANY right or a GRANT OPTION right; again bad
5) others - a DBA, not SYSDBA, same as above we don't need the %ANY% rights

The solution is PROXY as it's neat; to allows us to revoke the CREATE rights during run time by attaching those to a ROLE and making the schema less of a risk if its attacked via SQL Injection or similar and also allows us to revoke CREATE SESSION via the same ROLE so making PROXY not possible with out two steps; grant the role back; connect as a proxy; enable audit in session current.

Hope this helps! - Sorry i think i stole that phrase from Tim..

I have managed to build an 18c database this weekend to test and learn on. I have not had a massive time to look into 18c yet but I will do over the coming days and weeks. The new features for security are not extensive and I will visit those in another blog post soon. For this first quick look in wanted to look at users and passwords and a parameter. The parameter utl_file_dir has been with us for a very long time and when I wrote the https://www.amazon.co.uk/Oracle-Security-Step-Step-Finnigan/dp/0972427341/ref=sr_1_8?ie=UTF8&qid=1525719867&sr=8-8&keywords=pete+finnigan - (broken link) SANS Oracle Security step-by-step guide book back at the end of 2002 we suggested at that time not to set this parameter to "*" or "/" or "\" or "." or ".." or sensitive locations. This book became the basis for the first version of the CIS Oracle benchmark and of course this parameter was included there as well. It was also covered by US Dod Stig, ISACA and others. When Oracle added DIRECTORY objects to the database we needed to ensure that utl_file_dir was not set to anything as this parameter is global and anyone who connects to the database with even just CREATE SESSION has access to this parameter. DIRECTORY objects are much better as they allow some level of fine grained privilege control to the file system resource. Oracle said that it deprecated this parameter in 12.2 and it has gone in 18c:

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....