vsbabu.org

Stored procedures for security

If you don’t trust your web server’s security (reveal-source-bugs for example), you might have a concern like "what if someone gets the Oracle connection and password from the code; and uses SQL*Plus to cause problems?" Oracle stored procedures can be used to beef up your site's security. Here’s how.

Let us assume you’ve a web application running on Oracle backend. I’m going to assume your application follows the classic two or three tier models where the web processing code does not happen within Oracle. You get the parameters from web, query database and write back to web.

In such a situation, you usually will end up making SQL query strings in your application and then run it. This will require the SELECT - and possibly - INSERT, UPDATE, DELETE permissions on the tables for the Oracle user used by the web application. What if you want this user to have no free-form data browsing (or modification) permission on the database? One situation is where you want to store sensitive information inside the database. Even if someone is able to figure out the web application's connection string - which is usually available in the code - and use SQL*Plus to login to the database, you don’t want that user to see sensitive data.

1. Create two users, one that owns the application schema and one that web application uses to connect. Let us call these APPOWNER and WEBUSER.
2. Setup the database schema under APPOWNER. Don’t grant any permission on any object to any other user. If in doubt, write a revoke script that revokes permissions on all objects to all other users.
3. Write stored procedures using PL/SQL or Java that encapsulates your business logic. Depending upon your requirement, the procedures might even return a record set. This requires you to have a real good idea about what kind of database interaction your application needs to perform beforehand. So, design first, then code.
4. Grant EXECUTE on these procedures to WEBUSER. If you would like to, you might even create synonyms so that WEBUSER does not need to call these as APPOWNER.procedure_name. In short, WEBUSER will only have execute permissions on procedures, which, because being owned by APPOWNER, can manipulate data. Also, don’t write a procedure that takes a SQL query as the parameter and executes it - kind of beats the purpose
5. For WEBUSER, disable SQL*Plus access. You can do that by setting PRODUCT_USER_PROFILE. More information.
6. Now, from your code, execute these procedures and process the output. Returning record sets and processing these in ASP/VBScript is an experience I’d like to forget. Other coding technologies might be more helpful.
7. For added security, you can obfuscate the stored code using DBMS_OBFUSCATE - but do that only after you are sure you’ve a readable copy of the code safely saved somewhere.

Obviously, this doesn’t bullet-proof your data. It just adds one layer of protection.

1. Excelent! thank you!

   Posted by: Toro verde on May 27, 2003 02:35 PM

2. You have said that one might use the DBMS_OBFUSCATE. How is this used?

   Posted by: Hunny on December 4, 2003 01:12 PM