Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

The usage of Oracle PL/SQL procedures for controlling data access is often emphasized in PL/SQL books and other sources as being more secure approach. I'v seen several systems where all business logic related with data is performed through packages, procedures and functions, so application code becomes quite "dumb" and is only responsible for visualization part. I even heard some devs call such approaches and driving architects as database nazi :) because all logic code resides in database. I do know about DB procedure performance benefits, but now I'm interested in a "better security" when using thick client model and database procedures. I assume such design is mostly used when Oracle (and maybe MS SQL Server) databases are used.

I do agree such approach improves security but only if there are not much users and every system user has a database account, so we might control and monitor data access through standard database user security. However, how such approach increases the security for an average web system where thick client model is used: for example one database user with DML grants on all tables, and other users are handled using "users" and"user_rights" tables? We could use DB procedures, save usernames into context use that for filtering but vulnerability resides at the root - if the main database account is compromised than nothing will help. Of course in a real system we might consider at least several main users (for example frontend_db_user, backend_db_user).

So, does the usage of pl/sql stored procedures (all DML operations through procedures) help to increase security in thick client model systems (web systems)?

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

The theory behind using stored procedures for increased security is that you don't give DML access directly to users. The idea is that users never get anything other than execute permissions on the procedures. There's also a slightly less paranoid version of this approach where all writes are by stored procs, but table/view reads can be done by users.

The reasoning goes that if users only have execute permissions on stored procs and no direct DML on tables or views, then they can't manipulate data in any unpredicable ways.

To take an example, let's say you've got a bank account system and the only way to make a funds transfer is through a stored proc, you can have this proc audit log details about when it happened, where the money went and so forth. If you allowed direct DML access to the base tables then a compromised account could be used to take money without leaving an audit trail.

You are right that a compromised account is still a big security hole - big enough to drive a truck through. If all the compromised account's access is restricted to stored proc execution however then at least the hole will only fit a smaller truck.

share|improve this answer
    
Thanks. I liked the statement "then they can't manipulate data in any unpredicable ways.". Also liked the statement "There's also a slightly less paranoid version of this approach" :) –  Centurion Nov 11 '12 at 12:58
    
Also should note, audit trail of DML operations might also be enabled using triggers with custom log tables, so all needed DML actions would always be recorded, and an attacker will not be able to delete log table records if he will not have rights to log tables. However, if using procedures then an attacker will not be able to mess around in the database in any other manner than just calling procedures. –  Centurion Nov 12 '12 at 7:30
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.