List:General Discussion« Previous MessageNext Message »
From:Baron Schwartz Date:April 14 2007 1:22pm
Subject:Re: a little user rights help?
View as plain text  
Hi,

Denise Wilson wrote:
> Hi.  I'm brand new at this so I'm looking for a little help.
> 
> I need to have two difference levels of access to a mysql database that 
> I am
> developing for our librarians to use to maintain the various research
> resources we have available in our library.  AdminType1 should have Delete,
> Insert, Select, and Update rights on all the tables in the Resources
> database.  AdminType2 will have the same rights on some of the tables in 
> the
> Resources database, but in other tables they should only have Select
> rights.  At the moment, my plan is to have a separate user database that
> will contain a table with a row for each librarian and a column that will
> hold information about whether the librarian is AdminType1 or 
> AdminType2.  I
> plan to set up 2 users in the grant tables of the mysql database.
> AdminType1 will be granted the more comprehensive rights to all the tables
> in the Resources database and AdminType2 will be granted the Delete, 
> Insert,
> and Update rights only on the appropriate tables.  After the user has 
> logged
> into my user database, they will be connected to the Resources database as
> either user AdminType1 or AdminType2.
> 
> My Question:
> Is this a good way to approach this or am I WAY off base?

I think it sounds reasonable.  I've built several web apps that work this way or 
similar to this.  I typically build all the logic into the structure of the 
database and then design queries that return, for a given user, everything s/he 
is allowed to do.  That makes it simple for the application to decide which 
buttons and pulldown menus to show: it just shows every action returned by the 
query(s).  I think it's a good design decision to keep this logic in one place, 
especially as the application gets more features.  In my case I make the 
application dumb and the queries smart.

I wrote more about this at 
http://www.xaprb.com/blog/2006/08/16/how-to-build-role-based-access-control-in-sql/

Baron
Thread
a little user rights help?Denise Wilson14 Apr
  • Re: a little user rights help?Mogens Melander14 Apr
  • Re: a little user rights help?Baron Schwartz14 Apr