Auditing an Oracle database for security issues is very important. PeteFinnigan.com provides all of the information and tools that you will need Click here for details of PeteFinnigan.com Limited's detailed Oracle database security audit service Click here for details of PeteFinnigan.com Limited's Oracle Security Training Courses
There are 58 visitors online    
Cookie Policy:We only use essential cookies on small sections of this website. For details see here.

Pete Finnigan's Oracle security weblog


Oracle 12c Security - SQL Translation and Last Logins

July 31st, 2013 by Pete

There has been some big new security items added to 12cR1 such as SHA2 in DBMS_CRYPTO, code based security in PL/SQL, Data Redaction, unified audit or even privilege analysis but also as I hinted in some previous blogs there are lots of little things that have changed related to security or big things that have changed that affect security such as the multitenant architecture.

I am going to discuss a couple here this time in this short post. The first came from a post on Kerry Osborne's blog that I found via the oaktable list, titled "SQL Translation Framework" that discusses that the SQL translation framework from SQl Developer is now in the database as a feature. This was intended to translate non Oracle SQL to Oracle SQL but the key thing is that it can translate Oracle SQL to Oracle SQL. The first comment in Kerry's blog by Stew Ashton summed it up: " Wow, generalised bottom-up SQL injection!". It is not really SQL injection but SQL replacement but the concerns and the sentiment are justified.

The feature is great for resolving application issues with bad code for performance or workability issues but quite obviously it could be abused for attacking the database as well. It could also be use for security "good" as well as security "bad". It could be used for instance to aid an application to use a shared "view/logon" account instead of having users use the schema to connect to, it could be used to enable security within an existing application, it could maybe be used to enable encryption BUT the bigger issue is to make sure it is not used to bypass security. Imagine an application that logs in and then says "select role from app_roles where username=lowest of the low" it could be changed to say "select role from app_roles where username=god" . To enable the translation the package DBMS_SQL_TRANSLATOR must be available:





who_can_access: Release 1.0.3.0.0 - Production on Wed Jul 31 09:59:46 2013
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.

NAME OF OBJECT TO CHECK [USER_OBJECTS]: DBMS_SQL_TRANSLATOR
OWNER OF THE OBJECT TO CHECK [USER]: SYS
OUTPUT METHOD Screen/File [S]:
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:
EXCLUDE CERTAIN USERS [N]:
USER TO SKIP [TEST%]:

Checking object => SYS.DBMS_SQL_TRANSLATOR
====================================================================


Object type is => PACKAGE (TAB)
Privilege => EXECUTE is granted to =>
Role => PUBLIC (ADM = NO)

PL/SQL procedure successfully completed.


For updates please visit http://www.petefinnigan.com/tools.htm

SQL>






And the user must be able to do:





SQL> alter session set sql_translation_profile='blah';
SQL> alter session set events = '10601 trace name context forever, level 32';






So this needs access to ALTER SESSION to enable the event:





SQL> alter session set events='10601 trace name context forever, level 32';
ERROR:
ORA-01031: insufficient privileges


SQL>






So we can test who has this:





who_has_priv: Release 1.0.3.0.0 - Production on Wed Jul 31 10:10:21 2013
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.

PRIVILEGE TO CHECK [SELECT ANY TABLE]: ALTER SESSION
OUTPUT METHOD Screen/File [S]:
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:
EXCLUDE CERTAIN USERS [N]:
USER TO SKIP [TEST%]:

Privilege => ALTER SESSION has been granted to =>
====================================================================
Role => DBA (ADM = YES) which is granted to =>
User => SYS (ADM = YES)
User => SYSTEM (ADM = YES)
User => SYS (ADM = NO)
Role => RECOVERY_CATALOG_OWNER (ADM = NO) which is granted to =>
User => SYS (ADM = YES)
User => CTXSYS (ADM = NO)
User => APEX_040200 (ADM = NO)
User => SYSBACKUP (ADM = NO)
User => APPQOSSYS (ADM = NO)
User => GSMADMIN_INTERNAL (ADM = NO)
User => XDB (ADM = NO)
User => SYSDG (ADM = NO)
User => WMSYS (ADM = NO)

PL/SQL procedure successfully completed.

For updates please visit http://www.petefinnigan.com/tools.htm

SQL>






By default ALTER SESSION is only granted to some default accounts, my 12cR1 system shows this:





who_has_priv: Release 1.0.3.0.0 - Production on Wed Jul 31 10:10:21 2013
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.

PRIVILEGE TO CHECK [SELECT ANY TABLE]: ALTER SESSION
OUTPUT METHOD Screen/File [S]:
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:
EXCLUDE CERTAIN USERS [N]:
USER TO SKIP [TEST%]:

Privilege => ALTER SESSION has been granted to =>
====================================================================
Role => DBA (ADM = YES) which is granted to =>
User => SYS (ADM = YES)
User => SYSTEM (ADM = YES)
User => SYS (ADM = NO)
Role => RECOVERY_CATALOG_OWNER (ADM = NO) which is granted to =>
User => SYS (ADM = YES)
User => CTXSYS (ADM = NO)
User => APEX_040200 (ADM = NO)
User => SYSBACKUP (ADM = NO)
User => APPQOSSYS (ADM = NO)
User => GSMADMIN_INTERNAL (ADM = NO)
User => XDB (ADM = NO)
User => SYSDG (ADM = NO)
User => WMSYS (ADM = NO)

PL/SQL procedure successfully completed.

For updates please visit http://www.petefinnigan.com/tools.htm

SQL>






So; if you are worried that the SQL Translator can be used for bad then block access to the API DBMS_SQL_TRANSLATOR package and also make sure no one has ALTER SESSION. Also ensure that you review whats set up already.

Finally if you read Kerrys blog it would seem that it uses exact match so if you used this package to enable some security be aware as Kerry exampled that comments in the SQL can make it do something else, i.e. not translate.

The second simple new feature added to 12cR1 is the last logon. I have mentioned this many times in the past that it would be nice to see users last logon. I even wrote a script in my Oracle security tools page that used audit to test for last logon because up until 12cR1 you needed audit to be able to do this.

It states in the documentation that last logon is recorded in the SYS.USER$ table, in fact its recorded in SYS.USER$.SPARE6 or you can get it from the DBA_USERS.LAST_LOGIN column or the SYS.CDB_USERS.LAST_LOGIN column. In fact this information is only available in these two views and SYS.USER$:





SQL> select table_name,column_name from dba_tab_columns
2 where owner='SYS'
3 and column_name like 'LAST_LOG%';

TABLE_NAME
--------------------------------------------------------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
DBA_USERS
LAST_LOGIN

CDB_USERS
LAST_LOGIN


2 rows selected.

SQL>






That is a simplistic search of course!.

It also states in the documentation that security administrators can view when accounts were last used with this column and also that individuals can see their own last logon via the SQL*Plus banner. That of course assumes that you are using SQL*Plus!

For my system I can see:





SQL> @print 'select username,last_login from dba_users where last_login is not null'
Executing Query [select username,last_login from dba_users where last_login is
not null]
USERNAME : SYSTEM
LAST_LOGIN : 31-JUL-13 10.10.13.000000000 +01:00
-------------------------------------------
USERNAME : CTXSYS
LAST_LOGIN : 24-JUL-13 16.27.05.000000000 +01:00
-------------------------------------------
USERNAME : C##TST
LAST_LOGIN : 31-JUL-13 10.07.41.000000000 +01:00
-------------------------------------------

PL/SQL procedure successfully completed.

SQL>






This is interesting as I have logged in as SYSTEM and also C##TST today but I didnt login as CTXSYS on my Oracle 12cR1 PE installation. That means Oracle itself logged in as CTXSYS during the installation. Also if i do this:





SQL> connect sys/oracle1@//192.168.1.40:1521/orcl as sysdba
Connected.
SQL> set serveroutput on
SQL> @print 'select username,last_login from dba_users where last_login is not null'
Executing Query [select username,last_login from dba_users where last_login is
not null]
USERNAME : SYSTEM
LAST_LOGIN : 31-JUL-13 10.10.13.000000000 +01:00
-------------------------------------------
USERNAME : CTXSYS
LAST_LOGIN : 24-JUL-13 16.27.05.000000000 +01:00
-------------------------------------------
USERNAME : C##TST
LAST_LOGIN : 31-JUL-13 10.07.41.000000000 +01:00
-------------------------------------------

PL/SQL procedure successfully completed.

SQL>






It does not record last login as SYSDBA. The mandatory audit trail records the logon as SYSDBA already but it would be nice to distinguish an interactive logon as sysdba and record it.

Also the banner does not show up if you use an older SQL Client or older SQL*Plus. I am using 11.2 SQL client and SQL*Plus and no banner for 12cR1, this is to be expected of course as the banner must be built into SQL*Plus. If i instead login with a 12c client and I have logged in previously as the same user then I get the banner:





SQL*Plus: Release 12.1.0.1.0 Production on Wed Jul 31 10:53:20 2013

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Last Successful login time: Wed Jul 31 2013 10:46:03 +01:00

Connected to:
Personal Oracle Database 12c Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>






The interesting thought would be how does it read the spare6 column or the last_login column of cdb_users or dba_users as these are not available to all users normally, it would imply that access to the user$ table is exposed to everyone for this column at least. USER$ is removed from SELECT ANY DICTIONARY but its data is available still of course. Either SQL*Plus runs code as SYS or its exposed via a public view.
[No Comments]


Hacking Oracle 12c COMMON Users

July 23rd, 2013 by Pete

The main new feature of Oracle 12cR1 has to be the multitennant architecture that allows tennant databases to be added or plugged into a container database. I am interested in the security of this of course and one element that permeates the whole container database architecture is the use of local and common user accounts and local and common privileges. We can create common accounts and these are identified by columns on various views including DBA_USERS:






SQL> @print 'select username,common,oracle_maintained from dba_users where username=''''C##ORASCAN'''''
old 33: lv_str:=translate('&&1','''','''''');
new 33: lv_str:=translate('select username,common,oracle_maintained from dba_users where username=''C##ORASCAN''','''','''''');
Executing Query [select username,common,oracle_maintained from dba_users where
username='C##ORASCAN']
USERNAME : C##ORASCAN
COMMON : YES
ORACLE_MAINTAINED : N
-------------------------------------------

PL/SQL procedure successfully completed.

SQL>







As you can see my container database has a common user called C##ORASCAN; the C## part of the name is demanded by Oracle in its documentation and is enforced in the database. All common users that you create must start C## and also any local user or indeed any user (as you cannot create common users in a plug database from the plug database) cannot start C##. But, you may ask then how come Oracle can create its users with names it chooses such as SYS, SYSTEM, DBSNMP etc. Are Oracles created users special, is there a maintained list somewhere that says its an Oracle user and not one we created? There is a column on DBA_USERS and other views that is called ORACLE_MAINTAINED that shows Oracle installed or created accounts. But what if we want to control the names of accounts.

OK, so the rules say we cannot create a common user without C##, lets try:






SQL> create user mycommon identified by mycommon container=all;
create user mycommon identified by mycommon container=all
*
ERROR at line 1:
ORA-65096: invalid common user or role name


SQL>







So it fails, the rules are met. So lets try again:






SQL> alter session set "_ORACLE_SCRIPT"=true;

Session altered.

SQL> create user mycommon identified by mycommon container=all;

User created.

SQL> alter session set "_ORACLE_SCRIPT"=false;

Session altered.

SQL>







So it seems to have worked; I was able to create a common user with a name i defined; i.e. not defined by Oracle. Check the user exists and its settings:






SQL> @print 'select username,common,oracle_maintained from dba_users where username=''''MYCOMMON'''''
old 33: lv_str:=translate('&&1','''','''''');
new 33: lv_str:=translate('select username,common,oracle_maintained from dba_users where username=''MYCOMMON''','''','''''');
Executing Query [select username,common,oracle_maintained from dba_users where
username='MYCOMMON']
USERNAME : MYCOMMON
COMMON : YES
ORACLE_MAINTAINED : Y
-------------------------------------------

PL/SQL procedure successfully completed.

SQL>







It is there and it is a COMMON account and also it states it is ORACLE_MAINTAINED because we tricked the database to think its an Oracle account

Is it visible in the plug database (first show its the plug by the connect string):






SQL> connect sys/oracle1@//192.168.1.86:1521/pdborcl.localdomain as sysdba
Connected.
SQL> set serveroutput on
SQL> select * from dba_users where username like '%MY%';

no rows selected

SQL>







The user is not there in the plug. Hmmmm, it says its a common user in the root container, see the output above but it isn't as it does not exist in the plug. Lets create a second account MYSECOND without the container clause:






SQL> connect sys/oracle1@//192.168.1.86:1521/orcl.localdomain as sysdba
Connected.
SQL> alter session set "_ORACLE_SCRIPT"=true;

Session altered.

SQL> create user mysecond identified by mysecond;

User created.

SQL> alter session set "_ORACLE_SCRIPT"=false;

Session altered.

SQL> @print 'select * from dba_users where username=''''MYSECOND'''''
old 33: lv_str:=translate('&&1','''','''''');
new 33: lv_str:=translate('select * from dba_users where username=''MYSECOND''','''','''''');

PL/SQL procedure successfully completed.

SQL> set serveroutput on
SQL> @print 'select * from dba_users where username=''''MYSECOND'''''
old 33: lv_str:=translate('&&1','''','''''');
new 33: lv_str:=translate('select * from dba_users where username=''MYSECOND''','''','''''');
Executing Query [select * from dba_users where username='MYSECOND']
USERNAME : MYSECOND
USER_ID : 110
PASSWORD :
ACCOUNT_STATUS : OPEN
LOCK_DATE :
EXPIRY_DATE : 01-JAN-14
DEFAULT_TABLESPACE : USERS
TEMPORARY_TABLESPACE : TEMP
CREATED : 05-JUL-13
PROFILE : DEFAULT
INITIAL_RSRC_CONSUMER_GROUP : DEFAULT_CONSUMER_GROUP
EXTERNAL_NAME :
PASSWORD_VERSIONS : 10G 11G
EDITIONS_ENABLED : N
AUTHENTICATION_TYPE : PASSWORD
PROXY_ONLY_CONNECT : N
COMMON : YES
LAST_LOGIN :
ORACLE_MAINTAINED : Y
-------------------------------------------

PL/SQL procedure successfully completed.

SQL>







So the user is created and its again a COMMON user and also ORACLE_MAINTAINED. Has this created the COMMON user in the plug:






SQL> connect sys/oracle1@//192.168.1.86:1521/pdborcl.localdomain as sysdba
Connected.
SQL>
SQL> set serveroutput on
SQL> @print 'select * from dba_users where username=''''MYSECOND'''''
old 33: lv_str:=translate('&&1','''','''''');
new 33: lv_str:=translate('select * from dba_users where username=''MYSECOND''','''','''''');
Executing Query [select * from dba_users where username='MYSECOND']

PL/SQL procedure successfully completed.

SQL>








Nope, it still has not worked, it is not a real common account. So we have a common user in the root container and its name doesnt start with C##. What about if we do it properly. We can create a common user starting c## and it should appear in the plug database also.






SQL> connect sys/oracle1@//192.168.1.86:1521/orcl.localdomain as sysdba
Connected.
SQL> create user c##mycommon identified by c##mycommon;

User created.

SQL> set serveroutput on
SQL> @print 'select * from dba_users where username=''''C##MYCOMMON'''''
old 33: lv_str:=translate('&&1','''','''''');
new 33: lv_str:=translate('select * from dba_users where username=''C##MYCOMMON''','''','''''');
Executing Query [select * from dba_users where username='C##MYCOMMON']
USERNAME : C##MYCOMMON
USER_ID : 111
PASSWORD :
ACCOUNT_STATUS : OPEN
LOCK_DATE :
EXPIRY_DATE : 01-JAN-14
DEFAULT_TABLESPACE : USERS
TEMPORARY_TABLESPACE : TEMP
CREATED : 05-JUL-13
PROFILE : DEFAULT
INITIAL_RSRC_CONSUMER_GROUP : DEFAULT_CONSUMER_GROUP
EXTERNAL_NAME :
PASSWORD_VERSIONS : 10G 11G
EDITIONS_ENABLED : N
AUTHENTICATION_TYPE : PASSWORD
PROXY_ONLY_CONNECT : N
COMMON : YES
LAST_LOGIN :
ORACLE_MAINTAINED : N
-------------------------------------------

PL/SQL procedure successfully completed.

SQL>







So we have a COMMON user, not ORACLE_MAINTAINED and does it exist in the plug?






SQL> connect sys/oracle1@//192.168.1.86:1521/pdborcl.localdomain as sysdba
Connected.
SQL> set serveroutput on
SQL> @print 'select * from dba_users where username=''''C##MYCOMMON'''''
old 33: lv_str:=translate('&&1','''','''''');
new 33: lv_str:=translate('select * from dba_users where username=''C##MYCOMMON''','''','''''');
Executing Query [select * from dba_users where username='C##MYCOMMON']
USERNAME : C##MYCOMMON
USER_ID : 114
PASSWORD :
ACCOUNT_STATUS : OPEN
LOCK_DATE :
EXPIRY_DATE : 01-JAN-14
DEFAULT_TABLESPACE : USERS
TEMPORARY_TABLESPACE : TEMP
CREATED : 05-JUL-13
PROFILE : DEFAULT
INITIAL_RSRC_CONSUMER_GROUP : DEFAULT_CONSUMER_GROUP
EXTERNAL_NAME :
PASSWORD_VERSIONS : 10G 11G
EDITIONS_ENABLED : N
AUTHENTICATION_TYPE : PASSWORD
PROXY_ONLY_CONNECT : N
COMMON : YES
LAST_LOGIN :
ORACLE_MAINTAINED : N
-------------------------------------------

PL/SQL procedure successfully completed.

SQL>







Yes, so it works correctly when we use the correct name for a common user. What about if we change the common users password for the account that didn't have C## in its name:






SQL> alter user mycommon identified by newpwd;
alter user mycommon identified by newpwd
*
ERROR at line 1:
ORA-65048: error encountered when processing the current DDL statement in
pluggable database PDBORCL
ORA-01918: user 'MYCOMMON' does not exist


SQL>







Hmmm, this does not look good but is obvious really as the common user does not exist in the pluggable database therefore we cannot change its password. The error also gives a clue to how this all works; the ORA-01918 is from the plug and is a normal error, the DDL executed in the root is obviously grabbed by a trigger/DV/OLS/VPD policy and run against the plug from the container.

Interesting; what if we now create a local user in the plug database with the same name as the common user in the root container:






SQL> connect sys/oracle1@//192.168.1.86:1521/pdborcl.localdomain as sysdba
Connected.
SQL> create user mycommon identified by mycommon;

User created.

SQL> set serveroutput on
SQL> @print 'select * from dba_users where username=''''MYCOMMON'''''
old 33: lv_str:=translate('&&1','''','''''');
new 33: lv_str:=translate('select * from dba_users where username=''MYCOMMON''','''','''''');
Executing Query [select * from dba_users where username='MYCOMMON']
USERNAME : MYCOMMON
USER_ID : 115
PASSWORD :
ACCOUNT_STATUS : OPEN
LOCK_DATE :
EXPIRY_DATE : 01-JAN-14
DEFAULT_TABLESPACE : USERS
TEMPORARY_TABLESPACE : TEMP
CREATED : 05-JUL-13
PROFILE : DEFAULT
INITIAL_RSRC_CONSUMER_GROUP : DEFAULT_CONSUMER_GROUP
EXTERNAL_NAME :
PASSWORD_VERSIONS : 10G 11G
EDITIONS_ENABLED : N
AUTHENTICATION_TYPE : PASSWORD
PROXY_ONLY_CONNECT : N
COMMON : NO
LAST_LOGIN :
ORACLE_MAINTAINED : N
-------------------------------------------

PL/SQL procedure successfully completed.

SQL>







So it works; we now have a user MYCOMMON in the root, marked as COMMON but it doesnt exist in the plug (as a common user, it exists as a local user) and we obviously have the same user in the plug but its local not common. What happens when we now change the password for MYCOMMON in the root container.






SQL> connect sys/oracle1@//192.168.1.86:1521/orcl.localdomain as sysdba
Connected.
SQL> alter user mycommon identified by newpwd;

User altered.

SQL>







I am in the root container and have changed the common user in root:






SQL> set serveroutput on
SQL> @print 'select password,spare4 from sys.user$ where name=''''MYCOMMON'''''
old 33: lv_str:=translate('&&1','''','''''');
new 33: lv_str:=translate('select password,spare4 from sys.user$ where name=''MYCOMMON''','''','''''');
Executing Query [select password,spare4 from sys.user$ where name='MYCOMMON']
PASSWORD : ADD0BB1368A68A28
SPARE4 :
S:9019FACA912F1FBB19CD0456BD65BE9D744F7A208B1BAE6CF7F8CEDEEC9C;H:1D7C0286938CB26
4DCB54C660FD94564
-------------------------------------------

PL/SQL procedure successfully completed.

SQL>







In the plug container:






SQL> connect sys/oracle1@//192.168.1.86:1521/pdborcl.localdomain as sysdba
Connected.
SQL> set serveroutput on
SQL> @print 'select password,spare4 from sys.user$ where name=''''MYCOMMON'''''
old 33: lv_str:=translate('&&1','''','''''');
new 33: lv_str:=translate('select password,spare4 from sys.user$ where name=''MYCOMMON''','''','''''');
Executing Query [select password,spare4 from sys.user$ where name='MYCOMMON']
PASSWORD : ADD0BB1368A68A28
SPARE4 :
S:68FBFE1C81A6D6EAF9E0B56F1A371CF53B8B44511E90EED661902D0339CE;H:5F847339D2FEA92
6BC1F172E25CCF351
-------------------------------------------

PL/SQL procedure successfully completed.

SQL>







It has the same 10g password in both the root and the plug container. The MYCOMMON user is still obviously not a common user in the plug but i changed its password from the root container; so I was able to change a local users password in a pluggable container from the root container when i should not be able to do that:






SQL> @print 'select * from dba_users where username=''''MYCOMMON'''''
old 33: lv_str:=translate('&&1','''','''''');
new 33: lv_str:=translate('select * from dba_users where username=''MYCOMMON''','''','''''');
Executing Query [select * from dba_users where username='MYCOMMON']
USERNAME : MYCOMMON
USER_ID : 115
PASSWORD :
ACCOUNT_STATUS : OPEN
LOCK_DATE :
EXPIRY_DATE : 01-JAN-14
DEFAULT_TABLESPACE : USERS
TEMPORARY_TABLESPACE : TEMP
CREATED : 05-JUL-13
PROFILE : DEFAULT
INITIAL_RSRC_CONSUMER_GROUP : DEFAULT_CONSUMER_GROUP
EXTERNAL_NAME :
PASSWORD_VERSIONS : 10G 11G
EDITIONS_ENABLED : N
AUTHENTICATION_TYPE : PASSWORD
PROXY_ONLY_CONNECT : N
COMMON : NO
LAST_LOGIN :
ORACLE_MAINTAINED : N
-------------------------------------------

PL/SQL procedure successfully completed.

SQL>







If we are logged into the plug and try and change a common users password we get this:






SQL> @conn

ID
--------------------------------------------------------------------------------
NAME
------------------------------
3
PDBORCL


SQL> alter user c##orascan identified by newpwd;
alter user c##orascan identified by newpwd
*
ERROR at line 1:
ORA-65066: The specified changes must apply to all containers


SQL> alter user c##orascan identified by newpwd container=all;
alter user c##orascan identified by newpwd container=all
*
ERROR at line 1:
ORA-65050: Common DDLs only allowed in CDB$ROOT


SQL>







But what happens with MYCOMMON:






SQL> alter user mycommon identified by x;

User altered.

SQL>







If we compare the hashes:






SQL> @print 'select name,password,spare4 from sys.user$ where name=''''MYCOMMON'''''
old 33: lv_str:=translate('&&1','''','''''');
new 33: lv_str:=translate('select name,password,spare4 from sys.user$ where name=''MYCOMMON''','''','''''');
Executing Query [select name,password,spare4 from sys.user$ where
name='MYCOMMON']
NAME : MYCOMMON
PASSWORD : 7A9DD10633C8ED6B
SPARE4 :
S:B447EF343C518C6060F15BF8E5CCA4071DD3AED27AEAA7A85395784E6627;H:62597AA76948E25
907B5B99D8CF13206
-------------------------------------------

PL/SQL procedure successfully completed.

SQL> @conn

ID
--------------------------------------------------------------------------------
NAME
------------------------------
3
PDBORCL


SQL>







And in the root container:






SQL> connect sys/oracle1@//192.168.1.86:1521/orcl.localdomain as sysdba
Connected.
SQL> set serveroutput on
SQL> @print 'select name,password,spare4 from sys.user$ where name=''''MYCOMMON'''''
old 33: lv_str:=translate('&&1','''','''''');
new 33: lv_str:=translate('select name,password,spare4 from sys.user$ where name=''MYCOMMON''','''','''''');
Executing Query [select name,password,spare4 from sys.user$ where
name='MYCOMMON']
NAME : MYCOMMON
PASSWORD : ADD0BB1368A68A28
SPARE4 :
S:9019FACA912F1FBB19CD0456BD65BE9D744F7A208B1BAE6CF7F8CEDEEC9C;H:1D7C0286938CB26
4DCB54C660FD94564
-------------------------------------------

PL/SQL procedure successfully completed.

SQL> @conn

ID
--------------------------------------------------------------------------------
NAME
------------------------------
1
CDB$ROOT


SQL>







Obviously the passwords are different. If we have a common account the passwords should be sync'd across all databases but the issue is that for the plug the account is local so we can change it, for the root its common so we can also change it in the plug. What happens if we use the same ideas to create an account in the plug database that starts with c## which according to the documentation we are not supposed to be able to do:






SQL> connect sys/oracle1@//192.168.1.86:1521/pdborcl.localdomain as sysdba
Connected.
SQL> set serveroutput on
SQL> create user c##c identified by c;
create user c##c identified by c
*
ERROR at line 1:
ORA-65094: invalid local user or role name


SQL>







This fails as expected as thats the rules that are documented, we are not supposed to create accounts called C## or c## in the plug databases, only accounts called C## or c## are allowed in the root container. OK, lets use the same hack as see what happens:






SQL> alter session set "_ORACLE_SCRIPT"=true;

Session altered.

SQL> create user c##c identified by c;
create user c##c identified by c
*
ERROR at line 1:
ORA-65050: Common DDLs only allowed in CDB$ROOT


SQL> alter session set "_ORACLE_SCRIPT"=false;

Session altered.

SQL>







OK, this is good as we cannot bypass the name prefix in this way so there is an unbalanced issue.

There is another hidden parameter related to this area that we can play with "_common_user_prefix" - this is what enforces the 'C##' name for a common user. I have changed this in my database to a NULL string:






SQL> sho parameter _comm

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_common_user_prefix string
SQL>







Now create a common user with a name i decide and check it:






SQL> @conn

ID
--------------------------------------------------------------------------------
NAME
------------------------------
1
CDB$ROOT


SQL> create user mycomm identified by mycomm container=all;

User created.

SQL> set serveroutput on
SQL> @print 'select * from dba_users where username=''''MYCOMM'''''
old 33: lv_str:=translate('&&1','''','''''');
new 33: lv_str:=translate('select * from dba_users where username=''MYCOMM''','''','''''');
Executing Query [select * from dba_users where username='MYCOMM']
USERNAME : MYCOMM
USER_ID : 112
PASSWORD :
ACCOUNT_STATUS : OPEN
LOCK_DATE :
EXPIRY_DATE : 01-JAN-14
DEFAULT_TABLESPACE : USERS
TEMPORARY_TABLESPACE : TEMP
CREATED : 05-JUL-13
PROFILE : DEFAULT
INITIAL_RSRC_CONSUMER_GROUP : DEFAULT_CONSUMER_GROUP
EXTERNAL_NAME :
PASSWORD_VERSIONS : 10G 11G
EDITIONS_ENABLED : N
AUTHENTICATION_TYPE : PASSWORD
PROXY_ONLY_CONNECT : N
COMMON : YES
LAST_LOGIN :
ORACLE_MAINTAINED : N
-------------------------------------------

PL/SQL procedure successfully completed.

SQL>







This works. Has it been created in the plug database?






SQL> connect system/oracle1@//192.168.1.86:1521/pdborcl.localdomain
Connected.
SQL> set serveroutput on
SQL> @print 'select * from dba_users where username=''''MYCOMM'''''
old 33: lv_str:=translate('&&1','''','''''');
new 33: lv_str:=translate('select * from dba_users where username=''MYCOMM''','''','''''');
Executing Query [select * from dba_users where username='MYCOMM']
USERNAME : MYCOMM
USER_ID : 117
PASSWORD :
ACCOUNT_STATUS : OPEN
LOCK_DATE :
EXPIRY_DATE : 01-JAN-14
DEFAULT_TABLESPACE : USERS
TEMPORARY_TABLESPACE : TEMP
CREATED : 05-JUL-13
PROFILE : DEFAULT
INITIAL_RSRC_CONSUMER_GROUP : DEFAULT_CONSUMER_GROUP
EXTERNAL_NAME :
PASSWORD_VERSIONS : 10G 11G
EDITIONS_ENABLED : N
AUTHENTICATION_TYPE : PASSWORD
PROXY_ONLY_CONNECT : N
COMMON : YES
LAST_LOGIN :
ORACLE_MAINTAINED : N
-------------------------------------------

PL/SQL procedure successfully completed.







So, yes this is much cleaner if you need to name common accounts without C##. The first method with the _oracle_script hidden parameter creates a complex scenario where we are able to create an account with the same name in the root and also the pluggable database but they can have different passwords and one is a common account and the other a local account. Finally if we need to drop an account created under the regime of the _oracle_script variable then this happens:






SQL> drop user mysecond;
drop user mysecond
*
ERROR at line 1:
ORA-28014: cannot drop administrative users


SQL>







The solution is to drop under the _oracle_script parameter:






SQL> alter session set "_ORACLE_SCRIPT"=true;

Session altered.

SQL> drop user mysecond;

User dropped.

SQL> alter session set "_ORACLE_SCRIPT"=false;

Session altered.

SQL>







And of course it works. If you need to create accounts with your own name convention then use the hidden parameter _common_user_prefix but its hidden so you would need to check with Oracle whether you are still supported when doing this, my gut feeling would be yes because this parameter must exist for a reason and its most likely to ensure legacy applications (including Oracle default schemas) where the name cannot be changed will still work.

We should not rely on _oracle_script as its clearly coded to work under certain circumstances when installing Oracle defined accounts and appears to be inconsistant.

What else does all of this tell us? well we cannot rely on the COMMON column in views such as DBA_USERS as i created a COMMON user that is not COMMON and did not exist in each pluggable database until i created manually. Then I could sync passwords BUT i could still create seperate passwords if I needed. We cannot assume a COMMON account in the root container has the same password in all pluggable databases. Also the account in the plug cannot be considered common as i had to create it manually and it was listed as not common. We also cannot rely on a COMMON user being called C## something. We also cannot rely on the ORACLE_MAINTAINED column as I created a user that was not ORACLE_MAINTAINED.

Obviously do not use these parameters in production without discussing it with support; the first parameter _oracle_script seems buggy and unreliable but the second _common_user_prefix seems sensible.

[No Comments]


Oracle Security Loop hole from Steve Karam

July 22nd, 2013 by Pete

I just saw a link to a post by Steve Karam on an ISACA list and went for a look. The post is titled "Password Verification Security Loophole". This is an interesting post discussing the fact that ALTER USER IDENTIFIED BY VALUES bypasses password management and specifically the password complexity function. Steves test shows this but to be honest I would say that this on balance is probably is not a security loophole and intended behaviour as it stands now as this is unsupported syntax so its hard to suggest that the password management should work when we are supposed to use the correct ALTER USER syntax or PASSWORD from SQL*Plus which uses the OCI function. It will be interesting to see if Oracle respond to Steves post and correct it or not. If we think about the logic of this, this is syntactic sugar for "update user$ set password='blah' where name='name'; should the password management also include this ALTER USER syntax and even UPDATE is another thing; for this second discussion "should" not "expected" is that it probably should include these two syntaxes also....but lets get to why it doesnt work....

To close the loop it may be possible to block this abuse with a DDL trigger, you could run the same veriifcation function in a DDL trigger that captures changes to the USER object but you would need the password first - i.e. the actual clear text password not the hash. This could work for simple passwords and my password cracker written in PL/SQL could help with this but it wont work for complex functions. Steve said:

"While Pete Finnigan has created a Password Audit tool to check for default passwords, I don’t believe there’s any method to check existing passwords for complexity."

The cracker doesn't just check default passwords, it aslo checks password=username, defaults (1475 defaults passwords known), a dictionary of words, brute force up to 2 characters and also it checks role passwords.

To be able to check an existing password for complexity you would need to crack that password first from its password hash so that you could analyse the clear text password. If the password were complex and 8 characters or longer that cannot be done with a simple password cracker written in PL/SQL or even in C in a reasonably short time - i.e. the time to run an ALTER USER IDENTIFIED BY VALUES command. This is why Oracle do not enforce the password management when this syntax is used; hence my statement that this is intended behaviour if not desirable. If the clear text password is known (which it is during a proper ALTER USER command to change clear text password) then a verification function can be used.

A program to test against complexity is only possible when you have the clear text password.

Interestingly there are two new password verification functions in 12c to choose from that are better than 11.2, one is more secure than the other. This is good but like 11.2 they are not enabled by default. Also it is interesting to think about a verify function that enforces complexity. This is worthless if the rest of the peofile is not set. i.e. a password complexity function where there is not life time is meaningless as the function may never fire. Or a user with a lifetime and no complexity function is also useless as when the password expires it can be reset to a weak value.

Nice post though Steve!
[No Comments]


Oracle Database 12c Security Auditing

July 8th, 2013 by Pete

I started to ask a question a few blog posts ago about how does the 12cR1 database affect database security audits. I have decided to come back to it now as it is a good chance to do so. I earn my daily crust by performing security audits of Oracle databases and also producing PFCLScan, our Oracle database security scanner so I am interested to understand how an audit may change. Currently when I do an audit I focus on the security of the data itself so I am interested in assessing the database, the server its hosted on and the Oracle networking. I am particularly interested in how and why people and job descriptions (i.e. all the real people who interactively access the data and also all the processes that access the data). Security of data falls into three main groups, 1) patching, 2) hardening and baseline security and the most important is 3) the security of the data itself. When we do an audit we use custom tools including PFCLScan to assess the database but also dig into the server and the database by hand to assess how and why the data can be accessed, leaked or be stolen. This is a very detailed Oracle database audit service and a lot of clients get a deep understanding if the true security of their data in their own databases. If you would like to know more about this service then please contact me.

In terms of doing that database audit service now on Oracle 10g, 11g or rarely earlier this is fine. We simply ask to assess a production database as looking at test systems is not realistic. If you wish to test simple baseline hardening then any system including a test system (suitably hardened) would be fine BUT the focus for your audits is to secure the actual data, therefore we need to see a live production system and ideally one used for some time to get some idea of exactly how it is used and abused and how real the threats are to data.
Bring in 12c and we now have the complexity of pluggable databases and how does this affect a security audit (The question I asked myself a few blog posts ago). In simple terms pluggable databases and containers and the ability to provision databases very quickly and remove them quickly gives a speedier version of what exists now. In the current time multiple databases may be hosted on the same server (same as 12c) and maybe they also share the same Oracle home (same as 12c). The current 11g and lower is more complex in terms of managing and patching as a shared SYSDBA account means that if database A is breached then database B is also breached. It is more complex because its separated. In 12c it is the same problem, there is now one database sharing pluggable databases and there is also one SYSDBA account the same as before (except that now it is truly shared across multiple databases) before it was shared at the OS and DB level if a shared home was used, if two databases on the same server had two Oracle homes then there was separation provided that the OSDBA Unix group (assuming Unix) was not shared.

The biggest issue is data and security of data and that is the same (as an issue) whether you have pluggable databases or not or consolidated databases or not. The application functionality and data and its access controls is the same whether it is deployed as a pluggable database or not. The biggest problems I come across from a security perspective is access to data by the wrong people or badly designed applications that assume that the application controls the security and not the database and worse applications and people that leak data to outside the database or DBA’s that copy databases into test systems and more (if pluggable architectures going to make this easier? " Yes I think so, that is not good). The fact that an application and its schemas is in a pluggable database or in a standalone database does not make a vast difference. For some reasons the pluggable database architecture will make the data less secure. It will promote database applications to run in the same servers, backups are likely to be held on the same servers, DBA’s are more likely to share data selected from the database into the same directories where they may not have done in the past. The biggest concern is the use of shared or common accounts and privileges. In one sense this could make privilege design and management simpler but on the other hand it will affect security. Whereas each separate database may have had “in effect” common accounts the passwords could be different, now they are truly shared; This could be better to manage but it means if the password is breached in one place it is breached in all places.

Shared or common privileges also don’t make sense in the application world, maybe for DBA bit not even for application administrators as each application is different. You could create a common account and grant it the lowest common denominator privileges in the root container and then add specific privileges for each application in each pluggable database but we all know that in reality that will never happen and the common account will become known to many groups and it will have excessive privileges. A common account approach is good in some senses but i feel separate accounts are always best at the pluggable level. Least privilege can never be achieved otherwise.

The bigger shared account issue is the issue that default schemas are shared; this means that they must be locked and protected and also that more fundamentally default schemas should not end up in pluggable databases again because of a lowest common denominator issue based around templates.
The mechanism that is part of 12c should also be considered, is the 12c mechanism better than two databases on the same server, same home? hard to argue without much more details on how exactly 12c works but it is likely to be code changes in the core kernel and also label security and DV, the fact that the DV/OLS like functions do protect data means possibly yes 12c is better, but shared operating system access means possibly no. This is a common issue i come across now before 12c, if consolidation has occurred it is important that shared databases are at the same functional and risk levels and that operating system access is controlled and locked down.

If 12c is broken, do we care? should we worry? Maybe? The fact of life is that someone will break it somehow but its not out concern as such, we must hope Oracle patches quickly and we must apply those. Its our responsibility to ensure that we use 12c securely and the biggest factor is that databases and applications should include security and whether you use 12c, 11g or consolidate really is a matter for the design stage and must be included. 12c Just makes the consolidation process easier but we should not assume secure! The cross over between pluggable databases has been considered well but its the cross over that we as customers create that is the problem and I do not see that changing just because 12c is used. We have to consider data, where is it, who can access it and create isolation.

My biggest worry would be sites using 12c to provide cloud like database services. Again this is not an issue of 12c per-se but an issue of the designers, developers and DBA’s. Provided proper separation is designed between pluggable databases there should not be an issue; so again fundamentally it comes down to shared or common accounts and privileges or even shared services.

In terms of a security auditing a 12c database it is really no different to auditing an 11g database except for shared accounts (essentially within one database " the problem still occurs in 11g except that as an auditor we may not see it if the sharing is across multiple databases that we have not seen). If we have separated databases then we can have separated accounts with different passwords. We will lose the fast deployment but gain a little security with isolation. We can do this with 12c to some extent; our own created shared accounts should not exist without very careful thought. We should create local DBA accounts and admin accounts, shared accounts mean that if the password is breached in one pluggable database the whole of the container and all other pluggable databases are breached. This is really no different to non-12c except that we have an opportunity as an auditor in 12c to actually see the problem by viewing common accounts and privileges in each container.

In terms of auditing production system A on 11g or production system A on 12c its really no different except as I say we can consider the common issues as they are now available in the plug database where they would not have been in 11g.

Oracle 12c looks great, the idea is great, in terms of security the biggest issues are still customer deployments and designs not 12c. 12c allows us to see the shared issue locally where we would not have done so with 11g. It is going to be fun to review production 12c databases. The biggest concerns i would have would be inappropriate consolidation and sharing of privilege and users.
[1 Comment]


Oracle 12cR1 Database Security - Default Users

July 5th, 2013 by Pete

Has the problem of default users got bigger or smaller in 12cR1. I have some figures that I have collected over the years from various versions of the Oracle database (these figures are for different versions of Oracle where I have taken them each from a seed database. This is to get consistency and also often reflects reality as customer systems tend to have either very few default schemas (rarer) or this sort of amount (more common)):

  • 9iR2 @ 30 by default

  • 10gR2 @ 27 by default

  • 11g R1 @ 35 by default

  • 11g R2 @ 36 by default

  • 12c R1 @ 35 by default


On the face of it 11.1, 11.2 and 12.1 all look pretty static. If I run my user analysis high level script against 11.2 then we can see:





SQL> @use_anl

use.sql: Release 1.0.2.0.0 - Production on Fri Jul 05 12:23:13 2013
Copyright (c) 2007, 2009 PeteFinnigan.com Limited. All rights reserved.

OUTPUT FLAG [A|O|C] [A]: A
NUMBER OF USERS [200]:

Typ Rol RSO Sys Ob Tab PL USER
================================================================================
ADM 53 200 9 954 1417 SYS
ADM 3 5 211 156 3 SYSTEM
DEF 1 3 1 3 1 OUTLN
DEF 0 1 0 0 0 DIP
DEF 0 1 4 0 6 ORACLE_OCM
DEF 1 4 4 25 9 DBSNMP
--- 0 3 8 2 0 APPQOSSYS
DEF 3 29 14 43 52 WMSYS
DEF 1 9 8 47 72 EXFSYS
DEF 2 7 52 47 139 CTXSYS
DEF 3 10 16 33 78 XDB
DEF 0 1 11 0 0 ANONYMOUS
DEF 1 1 1042 5 87 ORDSYS
--- 0 1 0 73 0 ORDDATA
DEF 0 0 2 0 10 ORDPLUGINS
DEF 0 1 0 0 0 SI_INFORMTN_SCHEMA
DEF 2 19 31 120 245 MDSYS
DEF 2 13 43 126 89 OLAPSYS
DEF 2 1 0 0 0 MDDATA
DEF 3 8 257 0 0 SPATIAL_WFS_ADMIN_USR
DEF 3 8 144 0 0 SPATIAL_CSW_ADMIN_USR
ADM 3 4 23 728 407 SYSMAN
DEF 1 0 4 0 0 MGMT_VIEW
APX 0 1 6 1 0 FLOWS_FILES
APX 0 1 10 0 0 APEX_PUBLIC_USER
--- 2 B,2,9,0 26 105 360 401 APEX_030200
DEF 10 22 44 1 0 OWBSYS
--- 0 2 0 0 0 OWBSYS_AUDIT
SAM 2 1 0 4 0 SCOTT
SAM 1 7 1 7 2 HR
SAM 2 7 14 10 1 OE
SAM 5 17 11 17 0 IX
SAM 3 12 4 17 0 SH
SAM 2 1 10 2 0 PM
SAM 1 9 23 0 0 BI
DEF 0 0 0 0 0 XS$NULL
Typ Rol RSO Sys Ob Tab PL USER
================================================================================

PL/SQL procedure successfully completed.

For updates please visit http://www.petefinnigan.com/use.sql

SQL>






11.2 shows 36 default users installed and compared to 11.1 that’s just an increase of 1 default account so not bad for an update. There was a drop of sorts for 10g from 9i so things went in the right direction. For 12cR1 it seems that we have dropped to 35 again from 36:





SQL> @use_anl

use.sql: Release 1.0.2.0.0 - Production on Thu Jul 04 11:02:55 2013
Copyright (c) 2007, 2009 PeteFinnigan.com Limited. All rights reserved.

OUTPUT FLAG [A|O|C] [A]: A
NUMBER OF USERS [200]: 200

Typ Rol RSO Sys Ob Tab PL USER
================================================================================
ADM 75 219 10 1221 1622 SYS
--- 0 1 0 1 0 AUDSYS
ADM 2 5 161 178 7 SYSTEM
DEF 1 3 1 3 1 OUTLN
--- 0 8 20 19 12 GSMADMIN_INTERNAL
--- 1 B,2,2,12 0 0 0 0 GSMUSER
DEF 0 1 1 0 0 DIP
DEF 0 4 9 0 6 ORACLE_OCM
DEF 3 5 7 20 7 DBSNMP
--- 0 3 12 4 0 APPQOSSYS
DEF 3 12 486 29 94 XDB
DEF 0 1 11 0 0 ANONYMOUS
--- 4 B,7,19,73 0 4 0 0 GSMCATUSER
DEF 1 37 13 40 47 WMSYS
--- 1 B,1,8,0 1 0 6 0 OJVMSYS
DEF 2 11 59 53 147 CTXSYS
DEF 1 2 1225 5 91 ORDSYS
--- 0 1 0 90 0 ORDDATA
DEF 0 1 6 0 10 ORDPLUGINS
DEF 0 1 0 0 0 SI_INFORMTN_SCHEMA
DEF 2 21 24 130 265 MDSYS
DEF 2 11 30 2 0 OLAPSYS
DEF 2 0 0 0 0 MDDATA
DEF 3 7 268 0 0 SPATIAL_WFS_ADMIN_USR
DEF 3 7 144 0 0 SPATIAL_CSW_ADMIN_USR
--- 2 B,2,8,1 20 42 22 77 LBACSYS
APX 0 1 6 1 0 FLOWS_FILES
APX 0 1 10 0 0 APEX_PUBLIC_USER
--- 2 B,2,10,0 26 154 453 545 APEX_040200
--- 1 B,1,2,0 1 0 0 19 DVF
--- 15 B,29,22,588 13 44 39 95 DVSYS
--- 1 B,2,0,3652 13 15 0 0 SYSBACKUP
--- 0 4 7 0 0 SYSDG
--- 0 1 13 0 0 SYSKM
DEF 0 0 0 0 0 XS$NULL
Typ Rol RSO Sys Ob Tab PL USER
================================================================================

PL/SQL procedure successfully completed.

For updates please visit http://www.petefinnigan.com/use.sql

SQL>





But that is not true as the seed database (for the better) has no sample accounts. The 11.2 database had 7 sample accounts such as SCOTT and SH and OE and BI etc but these are missing in 12cR1. So in 12cR1 we have a net increase of 6 new default accounts added to a seed database. Even this is not a good analysis of the default accounts added to 12cR1. If we compare the accounts side by side:






12cR1 11gR2
ANONYMOUS ANONYMOUS
APEX_040200 APEX_030200
APEX_PUBLIC_USER APEX_PUBLIC_USER
APPQOSSYS APPQOSSYS
AUDSYS .
. BI
CTXSYS CTXSYS
DBSNMP DBSNMP
DIP DIP
DVF .
DVSYS .
. EXFSYS
FLOWS_FILES FLOWS_FILES
GSMADMIN_INTERNAL .
GSMCATUSER .
GSMUSER .
. HR
. IX
LBACSYS .
MDDATA MDDATA
MDSYS MDSYS
. MGMT_VIEW
. OE
OJVMSYS .
OLAPSYS OLAPSYS
ORACLE_OCM ORACLE_OCM
ORDDATA ORDDATA
ORDPLUGINS ORDPLUGINS
ORDSYS ORDSYS
OUTLN OUTLN
. OWBSYS
. OWBSYS_AUDIT
. PM
. SCOTT
. SH
SI_INFORMTN_SCHEMA SI_INFORMTN_SCHEMA
SPATIAL_CSW_ADMIN_USR SPATIAL_CSW_ADMIN_USR
SPATIAL_WFS_ADMIN_USR SPATIAL_WFS_ADMIN_USR
SYS SYS
SYSBACKUP .
SYSDG .
SYSKM .
. SYSMAN
SYSTEM SYSTEM
WMSYS WMSYS
XDB XDB
XS$NULL XS$NULL







The difference between 11.2 and 12.1 on the surface seems to be one less default account in 12.1 but this is not true. If we look and compare 11.2 and 12.1 we can see that there are 22 differences in default installed accounts in 12c. 12c doesn’t include the sample accounts (6 of them) which is good but it also includes DV and OLS and GSM accounts that are not in 11.2. 12c also has the additional SYS accounts BUT it no longer installs SYSMAN or MGMT_VIEW. 12c also brings an audit user AUDSYS. Apex is also installed in both database versions; which for me is not good as I am not using it in this database.

So in simple terms 12c looked the same on the surface to 11gR2, if slightly better but in real terms even if you create a bare bones database in 12c it is likely that more default schemas are going to be needed and will remain in the 12c database such as the AUDSYS, the SYS??? Users and also the DV and OLS users.
A final point is the massive increase in PUBLIC privileges in 12cR1. For my simple seed database tests this number is massive:






SQL> select count(*) from dba_tab_privs
2 where grantee='PUBLIC';

COUNT(*)
----------
36866

1 row selected.

SQL>







This has grown from 28k in 11gR2.

[No Comments]



Oracle Database 12c Security - Privileges and users - The Beginning

July 5th, 2013 by Pete

In this blog post is to look at some high level things to do with database users (for me) in terms of planning security assessments in the 12c database. This is to get some ground rules down when considering looking at Oracle database 12c security; initially this is in release 1 but going forwards the ground rules will change from 12cR1 and later releases from now as the multitenant container/pluggable architecture means that any review of users and their roles and privileges must take into account the fact that some are now global (common) and some are specific to the pluggable database. What do I mean by the last section, well testing a database in 11g is simpler than 12c for users and security because in 11g we have no containers/pluggable databases/roots/con_ids and more.

When we consider Oracle database security in any database (I have been doing database audits of Oracle databases since Oracle 7.3 and Oracle 8.0 and more regularly from 8i then through 9i and then into 10g and 11gR1 and 11gR2. Audits tend to follow real life in that unless it is a government system or someone has just bought Exadata it is unlikely that I will audit many 12cR1 databases straight away. Companies tend to not have the latest bleeding edge databases in production straight away. So my bread and butter work will almost certainly still be 10.2.0.5, 11.1.0.7 and 11.2.0.3 for some time to come with most tending towards 11.2 before 12cR1 becomes the norm.

This tendency doesn’t stop me looking at 12cR1 now so that when we do them for real we have a really good grounding in 12c.

I installed 12cR1 Standard Edition One first as we have licenses for that version and it’s simpler; as soon as the Windows download appears we will update our personal Oracle installations from 11gR2 to 12cR1 and use 11gr2 on XE for simple testing. When I installed 12cR1 I chose "Standard Edition One" to have a seed database and in the installer it asked for the name of the pluggable database; or rather it defaulted the global database name to orcl.localdomain and the "create as container" database is checked and the name of the pluggable database is "pbdorcl" so the installer thinks it’s OK to create a single pluggable database in standard edition one. Also I found this page in a post by Hans Forbrich about personal edition Oracle on Linux on the oracle-l list ; scroll down to the feature by addition section and it says this:

"Extra cost option for up to 252 pluggable databases. The multitenant architecture with one pluggable database (single tenant) is available in all editions."

So it seems to me that container databases (pluggable database) - multi-tenant are fundamental to 12cR1 and Oracle want us all to use this architecture and as such they must allow at least one pluggable databases in all additions. It will be interesting if a 12c XE edition comes out as to whether that will also include multi-tenant architecture.

So if we audit a 12cR1 database for security and if it is a “non” container database then essentially the “views” of users and privileges are most likely similar to 11g and 10g (although I have not tested this yet " the views will still exist with con_id’s for instance but they should be “equal” as there are no containers).

In 12cR1 with containers enabled then it is not the same. Now we must contend with common users (global across all containers) and users in each pluggable database. Common users are known across the root container and also all pluggable databases. Their identity and password are the same across all but common users can have different privileges granted in different containers. This makes for complex analysis of privileges. Also if Database Vault (DV) is enabled in a pluggable container then it can block privileges if so configured; such as the restriction to stop SYSTEM from creating users for instance.

The common users created by Oracle itself are identified as “ORACLE_MAINTAINED” in the dba_users or cdb_users views. When a non-cdb database is plugged in to a CDB database as a PDB then it becomes complicated. Also if a CDB PBD is plugged into as CDB then also it becomes complex. There are rules to define what happens to a common users status (locked or not) and also its privileges and how they are merged into the CDB or PDB depending on whether common users are added to a new PDB or merged.

There are also “local users”, these are created or exist in a single pluggable container. These users can have system privileges but they apply only to the container that they exist in unlike global (common) users who can have privileges across all containers and also specifically in a container (pluggable). A local user can create other local users. A common user can create common users or local users or grant privileges to either (having the correct privileges itself of course). A local user cannot create common accounts or grant common privileges. Local users can have common roles such as DBA or RESOURCE but these apply only in the local users container. What is also strangest of all is that a local user is only unique in its own container. That is you can create a local user ERIC in one container and create another local user ERIC in another container they are not the same user but each pluggable container. It is also possible to grant access to common users objects to a local user.

Also editions can be enabled on a local user but not common users. In terms of creating users you still need to have the CREATE USER privilege as you did in all other releases of Oracle but this time the container comes into effect. To create common users you must have the common CREATE USER privilege. If you have the common CREATE USER privilege then you can create common or local users. To create a local user in a PDB you can have the privilege granted locally or commonly.

Back to the plot.... If we want to audit a database for security issues or potential issues in 12cR1 then we must understand how to view users and privileges across the common level and the local level as a whole.

There are some nuances and extras to consider in user analysis in 12cR1. Common users are identified by the new COMMON column on the views DBA_USERS, CDB_USERS, ALL_USERS or USER_USERS. The same information is available as flags in SYS.USER$ but access to SYS.USER$ is not available anymore to users with SELECT ANY DICTIONARY. This is a new security feature of 12cR1. If an account such as DIP, SYSTEM, DBSNMP or SYS is created by Oracle (the database) then it retains its name. If you create your own common user then the name must start with “C##” or “c##”. The Oracle created accounts are exempt from this of course. To create a common user you must also be connected to the root container CDB$ROOT. Also default tablespaces, temporary tablespaces and profiles all must exist in all containers. That means that profiles for instance can exist in different containers (pluggable databases). The final nuance related to users is the addition of a CON_ID to the user to show which container it exists for. This implies that a user is unique when combined with its container ID.

When you connect to the database the database session records your con_id in the session and this is linked to users/privileges/objects etc throughout the database. This certainly looks like a version of label security on some level.

So, that is enough background. In the next post I am going to create some users and have a dig around in the dictionary. Watch for more posts soon.
[No Comments]


Oracle 12c Security

July 4th, 2013 by Pete

Well it has been a long time to wait for 12cR1 to be released. The beta program has been on for a long time and I was not involved so I didnt get to see 12c until last week for the first time. It would have been nice to have seen 12c before now but it wasn't an option for me. I did get to hear a little about the new 12c features at the UKOUG conference at the end of last year from a couple of talks. Tom Kyte did a talk about the Oracle security features coming in 12c at that time.

I downloaded 12cR1 for Linux X64 last Tuesday just after it came out but i could not start to attempt to install it until later in the week as I am just too busy with paying work at the moment. I finally tried to install Oracle Linux 6.4 first in VMWare but had troubles. It installed properly but when at the end of the install it came to reboot the Linux kernel panicked. I tried again with Oracle Linux 6.3 and Oracle Linux 6.1 after speaking to Tim Hall who told me 6.4 worked for him on VMWare and also Virtual box on Windows and also linux. I was trying to install onto an external USB terabyte drive which was the only difference to Tim (in high level terms, PC's are obviously completely different under the skin). I then tried to install Oracle Linux 6.4 in VirtualBox and again got a very similar error; at the time of reboot after the last setup the VirtualBox itself crashed. So whilst the kernel didnt panic, it failed in the same place with a similar error.

Anyway Tim also said he had used Oracle Linux 5.9 so over the weekend I installed 5.9 and then finally got Oracle 12cR1 installed. Wow, its finally there.

The last few days I have been very busy with paid work so not had a lot of time to play with 12cR1 yet but i have done quite a bit of digging already into the database itself with SQL*Plus and also into the documentation to see whats changed, whats new in terms of Oracle security and also what new features that are not related to security may affect security. I have made a lot of notes on paper so plenty of fodder for blog posts. So expect more blogging than has been usual for the last couple of years. I saw Steve Karams post about Oracle 12c yesterday and the list of Oracle 12c posts by various blooggers shows that there is a lot more interest in this release than previous ones.

The biggest change of course is the multi-tenant or is it multitenant? addition to the database with root containers, seed containers, pluggable databases, CDB's PDBs and more. I have already had a play with my existing PL/SQL tools and also our scanner PFCLScan and have found out quite a lot so far. In terms of security, there are a lot of new features at the high level - i will discuss these in a future post but the biggest for security is the multi-tenant or pluggable databases as we now have local and global or common users and privileges and database objects and even parameters that are local or global. I will go into more details in a future post about that and its implications for security. I will also discuss the general new security features such as unified audit trails, redaction, PL/SQL privileges, authentication, SHA2 or SHA-2?, dbms_crypto and much more but also I will discuss all the smaller things I have seen so far, view changes, privilege changes, last login, view changes and much more.

As i said, I have made pages of high level notes so have plenty of material for blog posts so i hope to find enough time to discuss them all in some detail.

This new 12cR1 seems to be a good change, DV and OLS seem to be installed and I have always liked VPD, FGA, OLS and DV as concepts and features and indeed helped a number of customers design/implement and use VPD/OLS/FGA so its good to see some of them being used as core database features. I don't know how much of pluggable databases or multi-tenant are code changes to the core database and how much is implemented as DV or OLS policies (if any - maybe the presence of DV and OLS is for ease of build, to bind DV to the core to prevent the issue of turning it on and off or maybe its part of the functionallity) but to me the presence of these security functions in the core database being used to, clearly, protect the database is good. They are still cost options to EE but why not use them in all databases to implement or protect core database features.

Thats it for now, more to come soon with details!! on 12cR1 Oracle security
[No Comments]


July 2013
SMTWTFS
 123456
78910111213
14151617181920
21222324252627
28293031   

This is the weblog for Pete Finnigan. Pete works in the area of Oracle security and he specialises in auditing Oracle databases for security issues. This weblog is aimed squarely at those interested in the security of their Oracle databases.

Weblog Home
Weblog Archives


Home
Oracle Security Tools page
Oracle security papers
Oracle Security alerts

Web Development
SQL Server Security

RSS 1.0 FEED
RSS 2.0 FEED
Atom 0.3 FEED
Powered by gm-rss 2.0.0


Valid XHTML 1.0!