Mssp299 2015/08/26 12:15

Oracle OLAP applications that use DBMS_AW, OLAP_TABLE, or any OLAP* function face a new type of injection threat. Ultimately, this is due to syntax differences between SQL and OLAP DML. The end result is that an attacker can take advantage of this to perform arbitrary SQL operations with high privileges.

0 x01 introduction

Online analytical processing (OLAP) is often used to query multidimensional data. In Oracle, an Analytic Workspace can be created to store data to be analyzed, calculation objects such as formulas and models, and various programs to perform analysis. The computed objects and programs here are written using OLAP DML. It is important to note that OLAP DML is different from SQL because they have different syntax.

For example, in SQL,– is used for single-line comments and /**/ is used for multi-line comments. Comments in OLAP DML are represented by double quotes. A semicolon (;) Can be used to separate OLAP DML commands on a single line, and when a command is split into two lines, a single minus sign is used as a line continuation.

OLAP DML can be executed from SQL, but with the help of an interface that receives OLAP DML. This includes the DBMS_AWPL/SQL package, the OLAP_TABLE function, and other OLAP functions such as OLAP_CONDITION and OLAP_EXPRESSION.

In addition, many OLAP DML commands and functions, as well as several SQL command families, can also be executed from OLAP DML.

0x02 OLAP DML Injection Attack

The new injection attack described in this article occurs when user input is passed to an OLAP function or DBMS_AW package. This risk exists even if the input is legal for SQL and even if constraint variables are used. Basically, an attacker can embed any SQL statement in an OLAP DML statement and execute it with high privileges.

Here is a practical example. DROP_AW_ELIST_ALL is a stored procedure provided by Oracle. The corresponding code is shown as follows:

As you can see here, DBMS_ASSERT is used to ensure that there is no embedded SQL in the user-provided parameters “MYSCHEMA” and “AWNAME”. Once authenticated, they are passed to the stored procedure dbMS_AW.execute and the OLAP DML command “AW ATTACH” is executed.

But we can still “entrap” any OLAP DML command into this call by enclosing a forged AWNAME in double quotes and enclosing another command after a semicolon. In the following example, we execute OLAP DML SQL PROCEDURE along with a PL/SQL stored PROCEDURE, in this case dbMS_output.put_line.

Notice SYS in the output above.

Another example is found in the dbMS_aw.aw_ATTACH stored procedure. In fact, most of DBMS_AW’s stored procedures and functions have this security vulnerability. After dbMS_AW.aw_ATTACH gets the AW name, it is passed to GEN_DBNAME(). The GEN_DBNAME() function checks the AW name with dbMS_assert.qualified_SQL_name () to verify the validity of the input.

Here, too, an attacker can entrap any OLAP DML and execute SQL from there.

In the above attack, the attacker was able to bypass the input validation of DBMS_assert.qualified_SQL_name by using double quotes. Don’t forget that OLAP DML also sees the double quotation mark and treats it as an annotation. An attacker can then provide a hyphen that separates the OLAP DML command AW ATTACH into two lines. It is followed by a semicolon, which enables the attacker to execute the OLAP DML command (in this case, SQL PROCEDURE) that follows, and is then closed with double quotes. Not only does the user’s input bypass dbMS_assert.qualified_SQL_NAME, OLAPDML will treat it as a comment symbol.

When processing the OLAP_TABLE function, if any user input is passed to a third parameter, which is supposed to receive an OLAP DML command, or to a fourth parameter, LIMIT_MAP, the attacker can execute arbitrary OLAP DML.

Let’s use a specially designed example to illustrate. The first few lines below do some simple setup work to show the security problem:

Here, we want to use OLAP_TABLE in the view and read LIMIT_MAP from an analysis workspace variable named XLNAME. Even if users do not have permission to write AW, they can still modify their own private copies. This private copy can be used to access AW objects. Therefore, if user DAVID connects and sends the following, he can override XLNAME, which directly affects the LIMIT_MAP parameter of OLAP_TABLE. Using the keyword PREDMLCMD, DAVID can execute any OLAP DML command.

Note that the output of the SYS_CONTEXT(‘USERENV’,’CURRENT_USER’) functions above is DAVID. This indicates that the OLAP DML and subsequent SQL commands are executed as the current user, not as the owner of the view. To take advantage of this to gain higher privileges, user DAVID needs to pass the view to a PL/SQL package with definer privileges or to a stored procedure that can manipulate arbitrary data tables. There are many practical examples, but for illustration purposes, the one we designed specifically involves ¬SELECT_FROM_TABLE, where the owner of the stored procedure is SYS:

0 x03 summary

If a developer uses DBMS_AW in a PL/SQL package, a stored procedure or function uses definer privileges, and the user’s input is passed to DBMS_AW, an attacker can still execute arbitrary OLAP DML commands, even if the input passes SQL-level validation, or even if constraint variables are used. Execute as PL/SQL package owner from arbitrary SQL. Similarly, if a developer uses OLAP_TABLE or any other OLAP function in a PL/SQL package that uses definer permissions, an attacker can use user input to launch a similar attack. Similar injection attacks can occur if OLAP_TABLE is used in a view that allows subsequent processing, as in the above example, and can be accessed through PL/SQL packages.

The developer of an OLAP application must double-check all user input to ensure that no OLAP DML commands are “ensnared” in the user input. To do this, it is generally necessary to reject anything that contains hyphens, double quotes, or semicolons, depending on the specific circumstances of your application.