Danny,

>that is an interesting formulation which you gave. can you please comment
>that with an example? would you create roles for users who are assigned the
>same privileges and individual users who have multiple roles?

In this fragment from an example, a role has a name (roles.name), eg 'payments clerk', a role is referenced in one or more rows in usecases (eg usecases.name='enter/edit payments'), so a role defines access to multiple jobs (the list of usecases rows which reference that roleID), a users.username must exist as a mysql.user.User, and a user may have multiple roles, whence the users-roles bridge table userroles.

CREATE TABLE IF NOT EXISTS roles(
roleID INT NOT NULL AUTO_INCREMENT,
name CHAR(50) NOT NULL,
roleRank SMALLINT NOT NULL,
PRIMARY KEY (roleID),
UNIQUE UC_roleRank (roleRank));

CREATE TABLE IF NOT EXISTS usecases(
usecaseID INT NOT NULL AUTO_INCREMENT,
name CHAR(50) NOT NULL,
roleID INT NOT NULL,
PRIMARY KEY (usecaseID),
INDEX roleID (roleID)) ENGINE=InnoDb;

CREATE TABLE IF NOT EXISTS users(
userID INT NOT NULL AUTO_INCREMENT,
username CHAR(16) NOT NULL,
status SMALLINT NOT NULL,
date_created DATETIME NOT NULL,
date_edited DATETIME,
entered_by INT,
PRIMARY KEY (userID)
);

CREATE  TABLE  IF  NOT  EXISTS userroles(
 userroleID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 roleID INT NOT NULL ,
 userID INT NOT NULL ,
 INDEX ur_roleID( roleID ) ,
 INDEX ur_userID( userID ) ,
);

For more flexibility, define metaroles as collections of roles, and/or model the hierachy as nodes and edges.

PB

-----

Danny Stolle wrote:
Peter,

that is an interesting formulation which you gave. can you please comment that with an example? would you create roles for users who are assigned the same privileges and individual users who have multiple roles?

Best Regards,

Danny Stolle
Netherlands

Peter Brawley wrote:
Danny

/>1. Giving the user a Single user ID and assign the privileges to that user ID
 >2. Create role-bases users and have different people share the same user ID
 >for a given role.
 >3. Create multiple user IDs for each role played by each user (dannys_arch
 >as an architect, dannys_dev as a developer).
/
#2 has a name (role-based user access, RBAC) and is widely used, but its formulation above needs a correction: create roles, and users who can be assigned different and possibly multiple roles.

PB

-----

Danny Stolle wrote:

hi,

i would like to discuss 'user management' in mysql. Working with Oracle you can assign users to roles giving them privileges provided by that role. MySql doesn't have Roles. I have read (Managing and Using MySql, O'Reilly) 3 options on managing users having multiple roles in a MySql environment:
1. Giving the user a Single user ID and assign the privileges to that user ID
2. Create role-bases users and have different people share the same user ID for a given role.
3. Create multiple user IDs for each role played by each user (dannys_arch as an architect, dannys_dev as a developer).

Which of these 3 options is the most preferable one or are there more options which you can use. What are the advantages and disadvantages on working with one of these 3 options? how do you handle hostnames when working with random ip-addresses on your site.

Or just plain simple (or stupid) what are your experiences on user management in a MySql environment.

Best regards,

Danny Stolle
Netherlands


------------------------------------------------------------------------

No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.7.2 - Release Date: 6/14/2005



------------------------------------------------------------------------