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


Home » Archives » July 2013 » Oracle 12c Security - SQL Translation and Last Logins

[Previous entry: "Hacking Oracle 12c COMMON Users"] [Next entry: "Oracle Security Training, 12c, PFCLScan, Magazines, UKOUG, Oracle Security Books and Much More"]

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.

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!