Originally posted by Paul
What's that all about then? Don't views offer the same thing
I think so; but this is more of an integrated "no work on my end" sort of gig I think:
Row-Level Access Control
Data is becoming an increasingly valuable commodity in our economy. Protecting data at the table level isn't always enough. Fortunately ASE 12.5 now supports security mechanisms associated with the data stored. It provides a finer granularity of access control the allows users to retrieve only portions of a table based on who the user is. The solution is completely contained within the server, so it is unnecessary to write any filtering code at the application level to use this row-based security.
The solution involves creation of an access rule by the user or application. This rule is bound to a data type, which when used as a column type, applies the rule to all operations on the table containing the column. This storage level security is not limited to a specific SQL table, but can be applied to all tables using the data type. In addition, default rules can be described for server-wide enforcement.
So, it is sort of like an updateable view except it limits you to specific rows no matter what the query is. I guess that allows you to do something like this:
SELECT * FROM user;
and depending on the user it will give you certain rows. e.g. an admin would see all users, moderators would see everyone but admins..
That's a bad example but you get my drift. I guess it's a time-saver when you don't want to write customized views based upon the user. For a web-app like this where you have a single login (e.g. webuser) there's no real need for this but in an enterprise situation you can say 'ok DBA has access to everything', 'managers have access to XYZ rows', etc. That could provide useful; I know where I work we all have unfettered access to everything but if we had contractors all we would have to do is assign them to the 'restricted' group who has access to table XYZ but not 'special' rows.
I’m trying to think of how Oracle would do it; since you have a tablespace per user you can set up a view for the users table and assign it to the usergroup. I’m not sure what would be easier;
CREATE VIEW user
SELECT * FROM user WHERE usergroup != ‘ADMIN’
Bind to user/group.
CREATE RULE user
DISALOW FROM user WHERE usergroup = ‘ADMIN’