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?

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

DotNetNewbie wrote:

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]

On Dec 28, 9:46*pm, Arne Vajhøj <[e-mail]:

DotNetNewbie wrote:

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

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]
- mv*@spam.guard.caspershouse.com

"DotNetNewbie" <sn***********@yahoo.comwrote in message
news:7e**********************************@d21g2000 prf.googlegroups.com...
On Dec 28, 9:46 pm, Arne Vajhøj <[e-mail]:

DotNetNewbie wrote:

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

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]

On Dec 29, 9:55 am, "Nicholas Paldino [.NET/C# MVP]"
<[e-mail]:

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]

"DotNetNewbie" <[e-mail] in message

news:7e**********************************@d21g2000 prf.googlegroups.com...
On Dec 28, 9:46 pm, Arne Vajhøj <[e-mail]:

DotNetNewbie wrote:

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

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).

[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]

On Jan 7, 3:23 am, "Marc Gravell" <[e-mail]:

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

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?