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
