The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

how to control database access per user

We have a problem in dealing with a situation where every user will need to be limited to access a subset of data in a database. This can easily be done by adding logic in query generation logic to append conditions by looking at user/role. But we are looking for a way that does not involve programming if possible. I'm rusty on database. Is there any specific features, standard or non-standard, that can be used in the database itself for this purpose?

Creating a different view for each access right is not an option since there may be hundreds of different combinations.
blablabla
Tuesday, November 15, 2005
 
 
Most databases support some method of per-object access control. Depending on what you're trying to accomplish, that may or may not work for you.

"Creating a different view for each access right is not an option since there may be hundreds of different combinations."

That could be a problem. If you're accessing data through views, then it's views that you need to set permissions on.
comp.lang.c refugee
Tuesday, November 15, 2005
 
 
Why not just make use of the DBMS grant tables? You can limit users down to a single field of one table if you wanted to.
CIFChamp
Tuesday, November 15, 2005
 
 
Is that a mySql only feature? How about oracle?
blablabla
Tuesday, November 15, 2005
 
 
How are you identifying the users?
Anonymous Coward
Tuesday, November 15, 2005
 
 
Roles and the GRANT command are unavailable?
MBJ Send private email
Tuesday, November 15, 2005
 
 
What database?

Oracle has a feature, variously called "fine-grained access control", "row level security" or "virtual private database" designed for exactly this use. Unfortunately, Oracle Enterprise Edition also costs $$$$, and VPD is not available in Standard Edition or Standard Edition One.

If you have stored procedures available, and your database lets you use them in queries, you could conceivably roll your own with them, e.g. have a pgsql function in Postgres returning a predicate based on the current user.
George Jansen Send private email
Tuesday, November 15, 2005
 
 
Thanks! This is exactly what I'm looking for.
blablabla
Tuesday, November 15, 2005
 
 
+1 Oracle VPD (but still a lot of work to do yourself)

If I read the OP correctly he's not interested in restricting a user's access to particular tables or to particular subsets of columns in tables - he's interested in restricting the access to subsets of ROWS in tables because he says "This can easily be done by adding logic in query generation logic to append conditions by looking at user/role"
Abstract Typist
Tuesday, November 15, 2005
 
 
I am concerned that since creating additional views is discouraged, maintaining a list of 100+ users with their respective access levels, is also discouraged.

Generally, I find programatic approaches to be better in the long run, but it's also possible and recommended that you build a user interface such that a properly trained manager can simply assign names and roles on the fly.
Anonymous Coward
Tuesday, November 15, 2005
 
 
==>Why not just make use of the DBMS grant tables? You can limit users down to a single field of one table if you wanted to.

That works for column level (fields), but most of the times I see this requirement it's for a row level restriction on data -- e.g. Bob can update *his* clients, but not Betty's. Jim, the MidWest regional manager, has full access to both Bob's and Betty's customer data, but not to the clients out on the LeftCoast.

Not do-able with column level security.
Sgt.Sausage
Tuesday, November 15, 2005
 
 
Yeah I've never seen much need for column-level security.  It isn't often you have graduations of read that are not related to the same graduateions of write.

But row-level security is where it is at.  I've built little databases with little datasets on little (jet, oracle*) etc where the row-level has been by doing stuff with an access control table of my own rolling.  This seems fairly common in my experience.  And performance sucks.  Database engines ought to special-case it.

*Oracle used to have a 'web cartridge'.  Try authoring an interactive website in plsql yeah!  Unfortunately the web cartridge would log into the database as a single user, rather than each web client having their own corresponding username and access control .... Lets hope that has changed in the last five years.
new nick, new rep
Wednesday, November 16, 2005
 
 
you can create views for and then grant permissions on these views.
Viestards Send private email
Wednesday, November 16, 2005
 
 
In our application, this issue is one of the hugest complications of all and there is no totally good answer to it in my opinion. It is especially grotesque when you have Transaction tables that grow forever combined with arbitrary combinations of user permission levels (eg. Bob can see Utah and Idaho, Mary can see Idaho and Texas, Jane can see Utah and Texas and Oklahoma, etc.)
NetFreak Send private email
Wednesday, November 16, 2005
 
 
>> Try authoring an interactive website in plsql yeah! <<

Like this? http://www.oracle.com/technology/products/database/htmldb/index.html

Pure PL/SQL, baby.
David Aldridge Send private email
Wednesday, November 16, 2005
 
 
If your database supports them, consider views. Some views are read only, etc. etc.
sometimes unprepared
Saturday, November 19, 2005
 
 
I suppose that a table that needs graded access control will have a field that you an key on. So if Bob can only see Bob's data then the table will have a field that identifies the data belonging to Bob.
 So you would create an extra table with Bob's id and the id of the records that Bob could update; in this case only Bob. If Bob could also update Carol's records then the table would also contain a record with Bob's id and Carol's id.
 Your queries/updates would then link that table to the data table.

 Am I missing something?
Philip Madams Send private email
Tuesday, November 29, 2005
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz