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
