Design Question - Suggestions Please

3 Apr 2006 12:05 PM

S Chapman

We are designing a security model for our application. The security is
for controlling access to various functions(essentially buttons) on
various screens per user group. The problem we are facing is that
different screens support different functions and it is proving hard to
come up with one security table for the whole application. We have two
designs on mind:

1. Security table will have the most common functions (like Save,
Delete etc) as fixed columns and UserDef1, UserDef2 etc for non
standard functions. This is a good model but the system admin will have
problems guessing what UserDef1 means when administering rights.

2. Security table will have all the columns as Function1, Function2
etch (up to a max of say 15 columns) and a seperate table holds the
mapping to say what Function1 means on the screen.

Security
---------------------------------------------
Screen | User Group | Function 1 | Function 2 | Function3 .....

Screen Function Map
---------------------------------------------
Screen | Function | Name

Ex. 100, Function1, 'Save'
100, Function2, 'Delete' and so on.

This model will enable easy administration and also is extensible.

Please let me know your thoughts on this design.

Thanks for reading the post

3 Apr 2006 12:10 PM

Will

will it not be possible to have a table of users, a separate table of
rights, and a table UserRights which links users to what they can do
(perhaps with additional columns for Read, Update, Delete.

Much like you see in the SQL server permissions.

3 Apr 2006 12:25 PM

Uri Dimant

Hi
I guess you would want something like that
CREATE TABLE Users
(
Userid INT NOT NULL PRIMARY KEY,
Uname VARCHAR(n) NOT NULL
Email...
.....
)
CREATE TABLE Functions
(
FunctionId INT NOT NULL PRIMARY KEY,
Ftype VARCHAR(n) NOT NULL...
.......
)

CREATE TABLE Screens
(
ScreenId INT NOT NULL PRIMARY KEY,
Scereen_Descr VARCHAR(n) NOT NULL
........
)

Now ,it is interesting , how to build relationship between those tables . I
don't really know your business requirenments, so based on your narrative
i'd guess

CREATE TABLE Sequrity_System
(
UserID ----> Foreign Key to users
FunctionId ------>....
ScreenId --------->
........
)

Is these combinations of columns unique?If it is make it as Primary Key

Show quoteHide quote

"S Chapman" <s_chapma***@hotmail.co.uk> wrote in message
news:1144065933.043085.21470@i40g2000cwc.googlegroups.com...
> We are designing a security model for our application. The security is
> for controlling access to various functions(essentially buttons) on
> various screens per user group. The problem we are facing is that
> different screens support different functions and it is proving hard to
> come up with one security table for the whole application. We have two
> designs on mind:
>
> 1. Security table will have the most common functions (like Save,
> Delete etc) as fixed columns and UserDef1, UserDef2 etc for non
> standard functions. This is a good model but the system admin will have
> problems guessing what UserDef1 means when administering rights.
>
> 2. Security table will have all the columns as Function1, Function2
> etch (up to a max of say 15 columns) and a seperate table holds the
> mapping to say what Function1 means on the screen.
>
> Security
> ---------------------------------------------
> Screen | User Group | Function 1 | Function 2 | Function3 .....
>
> Screen Function Map
> ---------------------------------------------
> Screen | Function | Name
>
> Ex. 100, Function1, 'Save'
> 100, Function2, 'Delete' and so on.
>
> This model will enable easy administration and also is extensible.
>
> Please let me know your thoughts on this design.
>
> Thanks for reading the post
>

3 Apr 2006 12:51 PM

S Chapman

Uri Dimant, thanks, your solution seems like the way foward. More
elegant and simple than our earlier design.

3 Apr 2006 12:50 PM

Omnibuzz

Are the function1,2,3,4.. columns in the security table of boolean datatype?

Think of these issues before you freeze on the design:
1.This will not work if your screen has more than 15 functions. So
extensibility might be a problem.
2. Say you have a requirement like you want to add a new function to all the
screens. What would you do about the update. It can't be an update of one
column because each screen will have different functions.

The way I see it, the approach given by Uri Dimant is the best. Though its a
bit too normalized, its seems to be excellent for extensibility.

4 Apr 2006 9:28 AM

S Chapman

Yes, I agree with you, Uri's idea is good and we might probaly do
something on similar lines. Just as an aside - we need to add an extra
table to Uri's design called [Screen Functions] that captures the
relation between screens and implemented functions. Without this the
system Admin will be presented with a list of ALL functions (from
Functions table) for a screen even if some or many may not be
appilcable to the screen being administered.