Permissions, should I use an Int, long or binary datatype?

[DotNetNewbie]

Hello,

in my web application, I have to create permissions for each user. So
what I am doing is that for each role (using sqlmembership in .net) I
am creating a column in the database to hold a group of permissions
which I will then do some 'bit banging' in my web application to see
if the permission is set or not.

An int is 32 bits, and a long is 64.

I guess making the data type a long is smarter since it doesn't take
that much more space in the database, and it gives me 64 instead of 32
possible permissions correct?

What datatype in sql server would a long map too? And int is an int,
but long?

 

[Arne Vajhøj]

in my web application, I have to create permissions for each user. So
what I am doing is that for each role (using sqlmembership in .net) I
am creating a column in the database to hold a group of permissions
which I will then do some 'bit banging' in my web application to see
if the permission is set or not.

An int is 32 bits, and a long is 64.

I guess making the data type a long is smarter since it doesn't take
that much more space in the database, and it gives me 64 instead of 32
possible permissions correct?

What datatype in sql server would a long map too? And int is an int,
but long?

A C# long matches a SQLServer bigint.

You could use bitmaps as described.

But it is not a very relational way of storing data.

Arne

 

[DotNetNewbie]

A C# long matches a SQLServer bigint.

You could use bitmaps as described.

But it is not a very relational way of storing data.

Arne

Yeah, that is the downside.

For example, if I wanted to do a SQL query for all users with a
particular permission, I'm guessing it would result in a complete
table scan.

ie. select * from users where permissions = (permissions & 0x02) (or
something like that).

One option would be to store each permission in a seperate table, and
then create a 'summary column' in the users table, this way I don't
have to do seperate queries for each user in my web application.

 

[Nicholas Paldino [.NET/C# MVP]]

It probably will result in a table scan, which isn't going to help you
much.

Why not have one table which has the definitions of the permissions, and
then another which has the permissions that are assigned to the appropriate
user? That way, you can have as many permissions as you need, and to be
quite honest, it's much easier to query and process in querying the
permissions (it's just two joins, and you just cycle through the permissions
in the client side code).


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

A C# long matches a SQLServer bigint.

You could use bitmaps as described.

But it is not a very relational way of storing data.

Arne

Yeah, that is the downside.

For example, if I wanted to do a SQL query for all users with a
particular permission, I'm guessing it would result in a complete
table scan.

ie. select * from users where permissions = (permissions & 0x02) (or
something like that).

One option would be to store each permission in a seperate table, and
then create a 'summary column' in the users table, this way I don't
have to do seperate queries for each user in my web application.

 

[DotNetNewbie]

It probably will result in a table scan, which isn't going to help you
much.

Why not have one table which has the definitions of the permissions, and
then another which has the permissions that are assigned to the appropriate
user? That way, you can have as many permissions as you need, and to be
quite honest, it's much easier to query and process in querying the
permissions (it's just two joins, and you just cycle through the permissions
in the client side code).

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

A C# long matches a SQLServer bigint.

You could use bitmaps as described.

But it is not a very relational way of storing data.

Arne

Yeah, that is the downside.

For example, if I wanted to do a SQL query for all users with a
particular permission, I'm guessing it would result in a complete
table scan.

ie. select * from users where permissions = (permissions & 0x02) (or
something like that).

One option would be to store each permission in a seperate table, and
then create a 'summary column' in the users table, this way I don't
have to do seperate queries for each user in my web application.

Nicholas,

The problem with that approach is that if I applying/checking for
permissions from INSIDE a stored procedure, it makes the subquery much
more combersome (as oppose to it being a simple bit check).

 

[Lew]

Nicholas,

The problem with that approach is that if I applying/checking for
permissions from INSIDE a stored procedure, it makes the subquery much
more combersome (as oppose to it being a simple bit check).

Your "simple bit check" will work out to be much more "cumbersome" in the end,
due to the "simple" solution's bit twiddling, poor query plans, people
forgetting that bit *thirteen* means "fubar-read", and bit *seventeen* means
"baz-buzz read-write", not bits twelve and eighteen, and such problems.

When you coerce a database engine like MS SQL Server into not using its own
highly-engineered optimizations, but instead using a programmer hack, most of
the time you wind up with a maintenance problem that has performance problems
and cannot be expanded, as for example when you decide direct permissions are
too inflexible and switch to an Access-Control List (ACL) approach based on roles.

Too many programmers get caught up in premature optimizations and (too-too)
clever, but extremely rococo coding idioms that hurt maintenance. A system
that makes it into production spends far more time and money in maintenance
phase than in development phase; plan accordingly.

You should seriously consider Nicholas's advice, or at least using a more
"relational", database-typical approach.

 

[DotNetNewbie]

Your "simple bit check" will work out to be much more "cumbersome" in the end,
due to the "simple" solution's bit twiddling, poor query plans, people
forgetting that bit *thirteen* means "fubar-read", and bit *seventeen* means
"baz-buzz read-write", not bits twelve and eighteen, and such problems.

When you coerce a database engine like MS SQL Server into not using its own
highly-engineered optimizations, but instead using a programmer hack, most of
the time you wind up with a maintenance problem that has performance problems
and cannot be expanded, as for example when you decide direct permissions are
too inflexible and switch to an Access-Control List (ACL) approach based on roles.

Too many programmers get caught up in premature optimizations and (too-too)
clever, but extremely rococo coding idioms that hurt maintenance. A system
that makes it into production spends far more time and money in maintenance
phase than in development phase; plan accordingly.

You should seriously consider Nicholas's advice, or at least using a more
"relational", database-typical approach.

Lew,

I respect all the advice I can get, I just want to make sure I start
off on the right foot

In terms of programmers forgetting what each bit position represents,
a simple Enumeration would solve that no?

ie.

public enum Permissions: long
{
xxx-read = 0,
xxx-write = 0x0000000000000001,
xxx-update = 0x0000000000000002,
yyy-read = 0x0000000000000004

}

 

[Marc Gravell]

In terms of programmers forgetting what each bit position represents,

a simple Enumeration would solve that no?

Yes, an enumeration would be a good step if you went down that route
(it would allow an enum-based IsInRole method, for example); some
feedback, though:

* mark the enum with [Flags] to tell the system that this is what you
are doing (then "parse" etc will allow composite values)
* you would need None = 0, xxx-read = 1, etc - unless everyone has
"xxx-read" automatically
* unless you have well-defined roles from the start, you might find
you run out of bits very quickly

As a counter-point, note that the runtime itself has good support for
name-based roles (via [PrincipalPermission] etc) - allowing you to
enforce these very easily (for instance, on whether a user can call
certain methods without any extra code).

With the combination of these factors...
* normalisation in the database (who has role x?)
* runtime support for role-based security
* concerns about number of roles
....I've often plumped for the "keep it simple" approach, and then just
linked it in the app with (for example):

static class Role {
public const string XXX_READ = "something", XXX_WRITE= "something
else"; // etc
}
class SomeClass {
[PrincipalPermission(SecurityAction.Demand, Role=Role.XXX_READ)]
void SomeMethod() {...}
}
....
(for non-throwing checks)
bool canWrite = Thread.CurrentPrincipal.IsInRole(Role.XXX_WRITE);

All you need to do then is setup your principal; for a winform you can
do this at login (after checking the roles the user is in) via
GenericIdentity/GenericPrincipal; for ASP.NET I suspect the membership
provider is the way to go...
In my smart-client code I generally use a lazy-loading principal -
i.e. the first time a role is queried it asks the server, then caches
the result - but this requires writing a custom principal.

Marc

 

[Nicholas Paldino [.NET/C# MVP]]

(This isn't directed at Marc, but the OP)

The thing is, this advice goes out the window when you are writing a
T-SQL stored procedure, as you won't be able to access the enumeration
values in that. So you go back to having to remember what the values are
for the enumeration, which makes the relational approach better.

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

In terms of programmers forgetting what each bit position represents,
a simple Enumeration would solve that no?

Yes, an enumeration would be a good step if you went down that route
(it would allow an enum-based IsInRole method, for example); some
feedback, though:

* mark the enum with [Flags] to tell the system that this is what you
are doing (then "parse" etc will allow composite values)
* you would need None = 0, xxx-read = 1, etc - unless everyone has
"xxx-read" automatically
* unless you have well-defined roles from the start, you might find
you run out of bits very quickly

As a counter-point, note that the runtime itself has good support for
name-based roles (via [PrincipalPermission] etc) - allowing you to
enforce these very easily (for instance, on whether a user can call
certain methods without any extra code).

With the combination of these factors...
* normalisation in the database (who has role x?)
* runtime support for role-based security
* concerns about number of roles
...I've often plumped for the "keep it simple" approach, and then just
linked it in the app with (for example):

static class Role {
public const string XXX_READ = "something", XXX_WRITE= "something
else"; // etc
}
class SomeClass {
[PrincipalPermission(SecurityAction.Demand, Role=Role.XXX_READ)]
void SomeMethod() {...}
}
...
(for non-throwing checks)
bool canWrite = Thread.CurrentPrincipal.IsInRole(Role.XXX_WRITE);

All you need to do then is setup your principal; for a winform you can
do this at login (after checking the roles the user is in) via
GenericIdentity/GenericPrincipal; for ASP.NET I suspect the membership
provider is the way to go...
In my smart-client code I generally use a lazy-loading principal -
i.e. the first time a role is queried it asks the server, then caches
the result - but this requires writing a custom principal.

Marc

 

[Marc Gravell]

So you go back to having to remember what the values are for the

enumeration,

Just an aside; when you *do* have bitwise flags columns (which should
be rare), I find that the extended properties in SQL Server (such as
MS_Description) are a good place to note down what each bit means...
that way it will show automatically in a number of tools, and is
queryable via TSQL

Marc

 

[DotNetNewbie]

Just an aside; when you *do* have bitwise flags columns (which should
be rare), I find that the extended properties in SQL Server (such as
MS_Description) are a good place to note down what each bit means...
that way it will show automatically in a number of tools, and is
queryable via TSQL

Marc

Ok if I store the permissions in a normalized database way, how will I
be referencing the columns in my code.

ideally I want to do something like:


Permissions[userRoleID].AllowRead

Will I just have to map my class to the permissions table, or is there
a dynamic way of doing things? I could use a hashtable, that way I
won't have to keep updating my codebase when a new permission is
created...thoughts?

 

[Marc Gravell]

By name... as per an earlier post:
Thread.CurrentPrincipal.IsInRole(Role.XXX_READ);

Seriously, look into GenericPrincipal, GenericIdentity, IsInRole and
[PrincipalPermission]; this will not only allow you to re-use an
existing (and commonly understood) framework (with runtime-level
support at the method-invoke level), but it also means that 3rd-party
code can consume your roles without having to know about the
implementation.

Simply (at login) list the roles the user has, and feed them (and the
username) into GenericPrincipal / GenericIdentity. Job done. Now all
your code knows (via the principal) who you are and what you can do.

// login
string cn = "marc.gravell";
// list roles (for this user) from database
string[] roles = {"FOO", "BAR"};
// setup identity
Thread.CurrentPrincipal = new
GenericPrincipal(new GenericIdentity(cn), roles);

Marc

 

[DotNetNewbie]

By name... as per an earlier post:
Thread.CurrentPrincipal.IsInRole(Role.XXX_READ);

Seriously, look into GenericPrincipal, GenericIdentity, IsInRole and
[PrincipalPermission]; this will not only allow you to re-use an
existing (and commonly understood) framework (with runtime-level
support at the method-invoke level), but it also means that 3rd-party
code can consume your roles without having to know about the
implementation.

Simply (at login) list the roles the user has, and feed them (and the
username) into GenericPrincipal / GenericIdentity. Job done. Now all
your code knows (via the principal) who you are and what you can do.

        // login
        string cn = "marc.gravell";
        // list roles (for this user) from database
        string[] roles = {"FOO", "BAR"};
        // setup identity
        Thread.CurrentPrincipal = new
            GenericPrincipal(new GenericIdentity(cn), roles);

Marc

Well I wanted to group the permissions together, basically creating
permissions for each role.