Thread: Views and security
05-25-04, 20:53 #1Registered User
- Join Date
- May 2004
- All over (Houston, Nashville, Alabama)
Unanswered: Views and security
I am working on a project, and here is the is the basics that I need to acomplish...
Background: It is an oracle 8i database, and I am developing a web-based application in PHP.
There are a few different classifications of users. Each have varying levels of permissions. However, to give each user a static "role" that defines what he/she can and cannot do is too rigid. I am currently working on implementing a security/permissions table and have it set so that only administrators can update that table (real-time from the site).
See if this would make sense and is a permissible security schema. There is a security/permissions table that contains all of the different priviledges that users could have within the system. Within the application, the permission definitions held within this table would be actually implemented. Thus the only oracle roles that would need to be created would be a user and an administrator and the only difference being that the administrator would have the ability to edit the security/permissions table.
If that is a viable approach, then great...however, it would still allow for programmming slip-ups that would allow the users to possibly query directly off of the base tables and get to information that they were not supposed to have access to. Therefore, a potentially better solution would be for the only interactions with the database to be from views that would implement the all of the checks of the security/permissions table. However...I don't know Oracle (at least its implemetation of views, etc) that well, so does anyone know where I could find how to implement something like this:
From the application:
Within Oracle the view contains the logic something like the following:
SELECT (Viewing Priviledges)
FROM (the security/permissions table)
WHERE username = [logged in user]
-- the part I don't know how to do
if (Viewing priviledge is one way)
if (Viewing priviledge is another way)
RETURN something else or nothing
If that doesn't make sense, then let me know and I will try to clarify, but really what I am trying to find out is how to make the views implement the logic (using something like if-statements or an equivalent approach). Any help, suggestions, or resources would be greatly appreciated.
Note: One of the reasons behind having this implemented by a security/permissions table is that the Administrator may not be able to grant different roles within the on-line system since we do not have direct access to the underlying database, making users wait for the database owners to grant them new priviledges via roles could be a slow and probably unacceptable solution.
Also, if there is a different/better way to implement this security schema, then let me know as well.
05-25-04, 22:06 #2Registered User
Provided Answers: 1
- Join Date
- Aug 2003
- Where the Surf Meets the Turf @Del Mar, CA
If you really want to get anal WRT security, then implement Virtual Private Databases.
If you doubt that you can make ROLES secure "enough",
what make you convinced that you could make VIEWs more sercure?
The problem might be more easily 'solved' by having a policy that anyone
caught circumventing application security, is subject to immediate dismissal.
I "technology solution" is not always the "best" solution.You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
Good judgement comes from experience. Experience comes from bad judgement.
05-26-04, 13:44 #3Registered User
- Join Date
- Sep 2003
- Virginia, USA
Views can be bypassed by DBAs and other high priv users. (Such as users with the "select any table" privilege.) VPD can be bypassed by any user with SYSDBA privs. Therefore, VPD is "more" secure than views.
Consider that a local sys admin (root) can reconfigure your server to allow them to log into SQL*Plus as "SYS AS SYSDBA" without a password. (No, I won't say how as I don't like to encourage such behavior.) Therefore, you can have degrees of protection but not absolute protection.
For sensitive data I like VPD. It's trickier to setup and a whole lot trickier to manage than views, and I hear customers say it's slower than views for some reason. However, sometimes you gotta pay the piper if you want to maximize security.MarkRem
Author, Oracle Database 10g: From Nuts to Soup