Oracle Security Preventing SQL Injection Attacks Oracle Security Tips by Burleson Consulting

This is an excerpt from the bestselling book "Oracle Privacy Security Auditing", a complete Oracle security reference with working Oracle security scripts.
 

From XKCD

---

How to Prevent SQL Injection Attacks

Bind Variables - The first one is rather simple – do not use dynamic SQL. If possible, use a construct like:

select field1, field2, field3
into :1, :2, :3
from table
where ….

and then pass parameters to it. This way, the hacker can't inject an extra string after the predicate of the query.

Special Characters - Filter out the special characters, such as quotation marks.

No Pass-through Queries - Do not pass the SQL as a pass-through, rather, use bind variables. Using concatenation (such as "&" in Visual Basic and "=+" in JavaScript) may be convenient, but allows hackers to inject.

No Sweeping Privileges - Carefully review the privileges granted to the users. Remove sweeping privileges such as ANY. Utilize the principle of least privileges – if a users does not need a privilege, don't grant it. Without required privilege, the hacker can't see much data.

Invoker Rights Model - Using the Invoker Rights model in procedures and functions makes sure the calling user's privileges are used, reducing the chances of the hacker getting into a user with large privileges.

Datatype - Use proper datatypes for the parameters. If the parameter is numeric, define a numeric value for it. Some developers tend to declare everything as character, because it makes coding easier, but this invites hackers.

DBSNMP Password - Change the default passwords for the DBSNMP user, a user with sweeping privileges such as SELECT ANY DICTIONARY. Better yet, use a different username for the Oracle Intelligent Agent user, following the technique described earlier in this chapter under DBSNMP User Security.

Function for Password Management - In the application password management, use a function described in the appropriate section to authenticate users, do not select directly from a table.

Encryption - Finally, encrypt the sensitive data. Even if the hacker gets through via SQL Injection, he or she will not be able to see anything of value.

* Review the chances of SQL Injection and take steps to prevent an attack. As we saw earlier, the technique is rather easy and often results from bad coding practice and poor privilege management.

SQL*Plus Security

The last frontiers of security, often neglected, lie in the domain of SQL*Plus, the ubiquitous tool used by DBAs and developers alike to interact with the database. Securing SQL*Plus is easy, the specific parts of SQL*Plus can be easily controlled using built in facilities. In this section, we will describe each of the security facets of the tool, and how to configure them to achieve compliance.

Product User Profile

This is a facility provided by Oracle itself to limit the abilities of its own tools to some extent. If you have used SQL*Plus for a while, you know that there are ways to produce reports using this tool, and in some cases these reports have become the mainstream reports of the organization. A typical script is invoked by, in the case of UNIX, in this manner.

sqlplus –s userid/password @report.sql

report.sql is the actual script run by the session. It may contain a SPOOL command that places the output in an output file, and then with an EXIT command, it exits the SQL*Plus environment.

What if the user, while the report is running, presses Control-C and breaks the report generation, i.e. the execution of the query? The SQL*Plus prompt comes up, aborting the query:

SQL>

The user can do anything here. Even though you may have forbidden the user from invoking SQL*Plus, this technique circumvents that. The SQL*Plus security feature called Product User Profiles protects against such a misuse.

The central object behind this security model is a table called sqlplus_product_profile, owned by the user SYSTEM, not SYS.

This table should have already been created in the database creation process. If it does not exist, you can create it by running the script pupbld.sql in $ORACLE_HOME/sqlplus/admin directory as user SYSTEM. Note: this must be run as SYSTEM, not SYS.

To prevent user JUDY from entering a HOST command at the SQL*Plus prompt, you would insert a record into this table

insert
    into sqlplus_product_profile
    (product, userid, attribute, char_value)
values
    ('SQL*Plus','JUDY','HOST','DISABLED')
/

After this is inserted, every SQL*Plus session of JUDY is scanned for a HOST command. If she issues that command, she gets an error as shown below.

SQL> host
SP2-0544: invalid command: host
SQL>

Note the error SP2-0544, an SQL*Plus error, not an Oracle error, which starts with ORA. For JUDY, the host command is gone, as if it never existed before. For other users, the host command works fine. This is how the security is controlled at the execution level.

Now let's examine the table sqlplus_product_profile in detail.

COLUMN	DESCRIPTION
PRODUCT	The name of the Oracle product, uses "SQL*Plus" as the only value. This table was designed for all types of products such as Forms and Reports, but only SQL*Plus stuck around.
USERID	The username you want to restrict.
ATTRIBUTE	The command you want to restrict, e.g. HOST.
CHAR_VALUE	Should contain the value DISABLED for the products that should be disabled for the user.

Table 4.9 Relevant Columns of sqlplus_product_profile Table

Other columns are neither relevant nor used by the present setup.

How can you restrict the host command for all users? Just enter a % sign for the USERID column. This will not, however, prevent SYS and SYSTEM from using HOST.

Now, let's see what commands can be restricted by this facility.

SQL*PLUS COMMAND	DESCRIPTION
COPY	Disables the COPY command in SQL*Plus
EDIT	Disables the user from entering the edit command to edit a script or the current SQL
EXECUTE	Disables the execute command, i.e. execution of procedures, packages, pl/sql blocks, etc.
EXIT	The user is disallowed to enter EXIT command
GET	GET is used to get a script to the SQL*Plus environment but not execute it. This disallows that command.
HOST	This command takes the user to the prompt of the host operating system, such as the shell in UNIX or the command prompt in Windows. Disabling this command also disables the shortcuts defined for that. For instance, the exclamation mark “!” is a shortcut for HOST in UNIX, and that is disabled too.
PASSWORD	This command is used by the user to change his or her own password without the ALTER USER command.
QUIT	Same as exit.
RUN	This disables running of an SQL script. This also disables the shortcuts '@" and "@@" used to run the scripts.
SAVE	This disallows saving the current SQL to a file.
SET	All SET commands as in SET PROMPT, SET ROLE, etc, are disabled.
SPOOL	Disallows saving the output to a file.
START	START is used to get an SQL script file and execute it. This disables that command.

Table 4.10 SQL*Plus commands that can be restricted by Product Profile Security

In addition, product profile security can also be used to disable SQL commands, not just SQL*Plus. Here are the SQL commands disabled by the facility.

ALTER

ANALYZE

AUDIT

CONNECT

CREATE

DELETE

DROP

GRANT

INSERT

LOCK

NOAUDIT

RENAME

REVOKE

SELECT

SET CONSTRAINTS

SET ROLE

SET TRANSACTION

TRUNCATE

UPDATE

Table 4.11 SQL Commands Restricted

Please note the important difference between this and system privileges. This facility merely disables the command in SQL*Plus. The user can execute the command in any other tool. To prevent the user from doing SELECT against a specific table in any tool, revoke the grant.

The following PL/SQL commands can also be restricted

* BEGIN – This disables a user from entering BEGIN as the first line of SQL*Plus. The user can enter EXECUTE to execute a pl/sql block, and it must be disabled separately.

* DECLARE – This disallows the user from entering DECLARE as the first line of a statement. By disallowing BEGIN, DECLARE, and EXECUTE, the user can be disallowed from running any kind of Pl/SQL in a SQL*Plus environment.

 

This is an excerpt from the book "Oracle Privacy Security Auditing". You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle security and auditing scripts.