Retrofitting record-level access restrictions in classic asp applications

Question

Like the title says, I've been asked to come up with an estimate for retrofitting an existing asp application.

The current security mechanism controls access to different parts of the application (page-level restrictions), but has no mechanism for flagging individual records as restricted. Assigning rights to a user (using the existing, custom access management code) is no problem, but enforcing the rights is a different matter - each asp page has embedded sql - there's no use of stored procs, objects, etc.

Is the only solution to modify each table and query, or is there a better way? Any pointers, suggestions or prayers would be welcome.

This is classic asp, running on IIS6, against an oracle database.

Update: Here's a user scenario.

We have users, managers, directors, and VPs. The managers can see data created by users who report to them, but not users who report to other managers. Users can't see data created by any managers. Same thing with directors - they can see down, but their reports can't see up.

Answer 1

Assuming you need maximum granularity, the ability to "grant" each and any row to any of very many users, then you have a many-to-many relation, yes?

So apply the following pattern:

Add a tables of users.

Then, for each restricted table, so the following:

• Rename it tablename + "_base".

• create a many-to-many table that associates that table's id with a user id, called tablename + "allowed_user".

• create a view with the name table name that joins tablename_base to table_name_allowed_user, with a select* from tablename_base and user_id from tablename_allowed_user. This view should meet Oracle's requirements rto be "inherently updatable."

Now comes the hard part. You need to add "and user_id = $user_id" to every query. Find the various functions you're using to make queries. Wrap those function(s) in ones that gets the user id from the session and add that predicate.

One passable way to do this is to read select string, find the all "where"s (for subqueries there may be more that one), and replace it with "where (user = $user) and ". For queries that don't have a where, you'll need to insert this before any "group by" or "order by". This is fragile, so obviously you'll test that this works for all pages (you have an automated test for all pages, right?), and add hacks to cover special cases.

"update" statements won't have to change; "inserts" will presumably insert both to the view and then do a separate insert to the table's "allow_user" table with the id of the inserting user, to automatically grant teh inserting user acces to what he inserted.

If your number of users is more limited, or you're restricting types of users, you can go with a strategy of multiple views named for the user or type; then you'd replace tables names in the queries with the appropriate views.

Answer 2

This sounds like an ideal time to implement row-level security. Oracle has a package DBMS_RLS that allows you to define arbitrary access policies that can be applied to one or more tables that limit what rows a particular user is allowed to see. Conceptually, when a user issues a query with no filters on a protected table, i.e.

SELECT *
  FROM my_table

Oracle automatically and transparently inserts a WHERE clause defined by your security policy that limits the result set. You shouldn't need to make any changes to the SQL your application is executing.