Like the title says, I've been asked to come up with an estimate for retrofitting an existing asp application.
就像标题所说,我被要求提出改造现有asp应用程序的估计。
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.
当前的安全机制控制对应用程序不同部分的访问(页面级限制),但没有将单个记录标记为受限制的机制。为用户分配权限(使用现有的自定义访问管理代码)是没有问题的,但强制执行权限是另一回事 - 每个asp页面都嵌入了sql - 没有使用存储过程,对象等。
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.
这是在IIS6上针对oracle数据库运行的经典asp。
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.
我们有用户,经理,董事和副总裁。管理人员可以查看向其报告的用户创建的数据,但不能查看向其他经理报告的用户。用户无法查看任何经理创建的数据。董事也是如此 - 他们可以看到,但他们的报告却看不到。
2 个解决方案
#1
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.
这听起来像是实现行级安全性的理想时间。 Oracle有一个包DBMS_RLS,允许您定义可应用于一个或多个表的任意访问策略,这些表限制允许特定用户查看哪些行。从概念上讲,当用户在受保护的表上发出没有过滤器的查询时,即
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.
Oracle自动且透明地插入由安全策略定义的WHERE子句,该子句限制结果集。您不应该对应用程序正在执行的SQL进行任何更改。
#2
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".
将其重命名为tablename +“_ base”。
-
create a many-to-many table that associates that table's id with a user id, called tablename + "allowed_user".
创建一个多对多表,将该表的id与用户ID相关联,称为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."
创建一个视图,其名称表名将tablename_base连接到table_name_allowed_user,其中select * from tablename_base,user_id来自tablename_allowed_user。这种观点应该符合Oracle的要求,因为它“具有内在的可更新性”。
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.
现在来了困难的部分。您需要为每个查询添加“and user_id = $ user_id”。找到您用于进行查询的各种功能。将这些函数包装在从会话中获取用户ID并添加该谓词的函数中。
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.
一种可行的方法是读取选择字符串,找到所有“where”s(对于子查询可能有更多),并将其替换为“where(user = $ user)and”。对于没有where的查询,您需要在任何“group by”或“order by”之前插入。这很脆弱,所以很明显你会测试这适用于所有页面(你有一个自动测试所有页面,对吗?),并添加黑客以涵盖特殊情况。
"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.
“更新”声明不必改变; “插入”可能会插入到视图中,然后对表的“allow_user”表执行单独插入以及插入用户的ID,以自动授予插入用户对其插入内容的访问权限。
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.
如果您的用户数量更受限制,或者您要限制用户类型,则可以采用为用户或类型命名的多个视图策略;然后你用相应的视图替换查询中的表名。
#1
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.
这听起来像是实现行级安全性的理想时间。 Oracle有一个包DBMS_RLS,允许您定义可应用于一个或多个表的任意访问策略,这些表限制允许特定用户查看哪些行。从概念上讲,当用户在受保护的表上发出没有过滤器的查询时,即
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.
Oracle自动且透明地插入由安全策略定义的WHERE子句,该子句限制结果集。您不应该对应用程序正在执行的SQL进行任何更改。
#2
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".
将其重命名为tablename +“_ base”。
-
create a many-to-many table that associates that table's id with a user id, called tablename + "allowed_user".
创建一个多对多表,将该表的id与用户ID相关联,称为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."
创建一个视图,其名称表名将tablename_base连接到table_name_allowed_user,其中select * from tablename_base,user_id来自tablename_allowed_user。这种观点应该符合Oracle的要求,因为它“具有内在的可更新性”。
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.
现在来了困难的部分。您需要为每个查询添加“and user_id = $ user_id”。找到您用于进行查询的各种功能。将这些函数包装在从会话中获取用户ID并添加该谓词的函数中。
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.
一种可行的方法是读取选择字符串,找到所有“where”s(对于子查询可能有更多),并将其替换为“where(user = $ user)and”。对于没有where的查询,您需要在任何“group by”或“order by”之前插入。这很脆弱,所以很明显你会测试这适用于所有页面(你有一个自动测试所有页面,对吗?),并添加黑客以涵盖特殊情况。
"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.
“更新”声明不必改变; “插入”可能会插入到视图中,然后对表的“allow_user”表执行单独插入以及插入用户的ID,以自动授予插入用户对其插入内容的访问权限。
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.
如果您的用户数量更受限制,或者您要限制用户类型,则可以采用为用户或类型命名的多个视图策略;然后你用相应的视图替换查询中的表名。