List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:June 14 2005 8:11pm
Subject:Re: discuss: user management
View as plain text  
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
>>
>>
>>
>> ------------------------------------------------------------------------
>>
>>
>
>
>

Attachment: [text/html]
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
Thread
discuss: user managementDanny Stolle14 Jun
  • Re: discuss: user managementPeter Brawley14 Jun
    • Re: discuss: user managementDanny Stolle14 Jun
      • Re: discuss: user managementPeter Brawley14 Jun
  • Re: discuss: user managementScott Gifford14 Jun
  • Re: discuss: user management: conclusionDanny Stolle15 Jun
RE: discuss: user managementTheRefUmp14 Jun
  • Re: discuss: user managementDanny Stolle14 Jun
RE: discuss: user managementGordon Bruce14 Jun