Am I being role happy?

[Me]

I'm working on the security portion of this vb app. In the application the
user is presented with a list of commands that can be executed on a piece of
data.

My client wants to be able to block certain users from seeing certain
commands on their screen.

The commands are loaded into a listview from a table.

I've created a role for each combination of command/province(A user may be
able to execute a command on an Ontario piece of data but not a Quebec
piece).

I just modified the query that loads the list box so that it includes an
IS_Member with the command/province combination.

The roles were easy to create(sql query to create the sp_addrole commands).
And the query modifications were easy as well. Is_Member barely affects
query times(very fast).

I've also created roles that contain these roles so a user only has to be
added to one role to get access to large groups of commands.

I may have one problem though. I've created hundreds of roles on the
server. Is that a problem? Can anyone tell me if there's a better way of
doing this?

Thanks in advance.
J
P.S. The application has just under 500 users and is going to hit 1000 in a
couple of years.

[Erland Sommarskog]

Me ([e-mail]) writes:

I've created a role for each combination of command/province(A user may be
able to execute a command on an Ontario piece of data but not a Quebec
piece).

I just modified the query that loads the list box so that it includes an
IS_Member with the command/province combination.

The roles were easy to create(sql query to create the sp_addrole
commands).
And the query modifications were easy as well. Is_Member barely affects
query times(very fast).

I've also created roles that contain these roles so a user only has to be
added to one role to get access to large groups of commands.

I may have one problem though. I've created hundreds of roles on the
server. Is that a problem? Can anyone tell me if there's a better way of
doing this?

The one thing that I don't really like this design is that you use
SQL Server's own mechanisms to control data access within the
application. I would prefer to have my own authorization tables.
This design can confuse a DBA who thinks roles are for control access
to tables and T-SQL commands.

Then again, if it ain't broke...
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp