I've never built a system where the database privs are the primary 
access control mechanism. Indeed, I've never really build an application 
(yet) where there's more than one completely independent application 
accessing the database. (At which point I'd imagine database-level privs 
would be more important.)

What's a good way to regularize the database privs with (say) what you 
show on the screen?

For example, I worked in one company where we had, essentially, a B2B 
store. Some employees could help customers, some could add inventory to 
the store, some could run reports, etc. Each of these groups got a 
different set of links on the main screen to take them to different 
functionality. The list of who could access what stored procedures was 
enforced in the application, not the database itself. Maintaining that 
list multiple times (who can see what on the screen, who can run which 
stored procedures, which tables each stored procedure can access) would 
have been very tedious and error-prone. (Plus, it started out all 
enforced by the app when I got there, only poorly, so it would have been 
difficult to cut it all over at once, given that it took a few weeks 
just to put all the inline SQL into stored procedures to start with.)

Anyway, what's a good way of synchronizing what a role can do at the 
application level, what stored procedures a roll can access, and what 
tables a role can access?  Is there anything unobvious and clever that 
doesn't require (say) parsing the stored procedure code to figure out 
what it does with which tables?


-- 
   Darren New / San Diego, CA, USA (PST)
     It's not feature creep if you put it
     at the end and adjust the release date.

Post a reply to this message