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

Darren New wrote:
> 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?
>

As dirty as it sounds (and is), most systems I've seen handle the actual
db with full access (or at least with permissions that are "enough") and
the frontend takes care of the permissions.

:(

-- 
Eero "Aero" Ahonen
   http://www.zbxt.net
      aer### [at] removethiszbxtnetinvalid

Post a reply to this message

Eero Ahonen wrote:
> As dirty as it sounds (and is), most systems I've seen handle the actual
> db with full access (or at least with permissions that are "enough") and
> the frontend takes care of the permissions.

That's what I've usually done, but I've so far written systems where 
only one application accesses the DB.

I saw a lecture about a tool set one person built (Wyatt Erp or some 
such name) (in Tcl) that let you build enterprise-level systems. One 
application took orders, another did the accounting, one did the 
scheduling, one sent the parts list to the assembly automation, and so 
on. So you really wouldn't want the guy running the application that ran 
the machine tools to have access to the same stuff the guy running the 
sales management software to have.

I just haven't figured out an especially clean way of separating it out. 
Maybe I need to look at the Wyatt Erp source code. :-)

-- 
   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

Darren New wrote:
> 
> That's what I've usually done, but I've so far written systems where
> only one application accesses the DB.

Actually so have I. OTOH, I usually haven't done actually finished
frontends to write the data to db, just to access it (darn, it's clearly
too easy to login to the db and write pure SQL to push data at home db's
:p).

> I saw a lecture about a tool set one person built (Wyatt Erp or some
> such name) (in Tcl) that let you build enterprise-level systems. One
> application took orders, another did the accounting, one did the
> scheduling, one sent the parts list to the assembly automation, and so
> on. So you really wouldn't want the guy running the application that ran
> the machine tools to have access to the same stuff the guy running the
> sales management software to have.

Actually I know couple of systems, where different applications and
users deal with different things and via one application one user can do
different things than another user. I'm not sure if I can say enterprise
-level, but pretty major systems for one firm.

And yet even these systems do exactly as I told - full access to db,
limited access to frontends. As I said, dirty :/.

-- 
Eero "Aero" Ahonen
   http://www.zbxt.net
      aer### [at] removethiszbxtnetinvalid

Post a reply to this message