Re: Help with using bits in an integer


Harry,

Is your database schema already decided? I had a look at a similar system
and I found myself becomming tied up in knots with my stored procedures.
Eventually I went for a table with one bit column for each permission in any
given row. Then I explicitly loaded them into boolean variables from the
database. My enumeration doesn't have to worry about bit manipulation and
neither do my stored procedures. Of course the first idea is always to
store a group of permissions in a single integer, but then what happens if
you need more than 32 or 64 bits of information here? You will need two,
then three, etc. Much better to store this information in a database table
explicitly, because you can mix and match types - if for example instead of
a permissions bit, you want some number (MAX or MIN) or some other data
types.

( Assume an enumeration called PermissionsEnum, that stores the column index
for each item in the set of permissions.
Also assume I have just executed a "GetPermissions ( name )" stored
procedure against the database. Finally, assume
a class called DataPermissions that exposes boolean properties for each
permission ).


If DataReader.HasRows = True Then

DataReader.Read()

thePermissions = New DataPermissions
thePermissions.ID =
DataReader.GetInt32(DataPermissions.PermissionsEnum.ID)
thePermissions.Name =
DataReader.GetString(DataPermissions.PermissionsEnum.Name)
thePermissions.CanReadCritical =
DataReader.GetBoolean(DataPermissions.PermissionsEnum.CanReadCritical)
thePermissions.CanAddIM =
DataReader.GetBoolean(DataPermissions.PermissionsEnum.CanAddIM)
thePermissions.CanAddIP =
DataReader.GetBoolean(DataPermissions.PermissionsEnum.CanAddIP)
thePermissions.CanAddAP =
DataReader.GetBoolean(DataPermissions.PermissionsEnum.CanAddAP)
thePermissions.CanAddLP =
DataReader.GetBoolean(DataPermissions.PermissionsEnum.CanAddLP)
thePermissions.CanDeleteIM =
DataReader.GetBoolean(DataPermissions.PermissionsEnum.CanDeleteLP)
thePermissions.CanDeleteIP =
DataReader.GetBoolean(DataPermissions.PermissionsEnum.CanDeleteIP)
thePermissions.CanDeleteAP =
DataReader.GetBoolean(DataPermissions.PermissionsEnum.CanDeleteAP)
....
....
Else

' Failed....

End If


.

Relevant Pages

  • Re: Execute Persmission denied on object sp_OACreate
    ... SQL Server doesn't check permissions on indirectly referenced objects as ... You can prevent ad-hoc execution of powerful master database procs while ... >I have a user who has execute permissions on a store procedure in a>database> which in turns executes 4 stored procedures in the master database. ...
    (microsoft.public.sqlserver.security)
  • Re: Security: Accessing data in another db
    ... If your objects are owned by 'dbo', the database owners need to be the same ... those users that have permissions to create dbo-owned objects. ... which only has execute permissions on the stored procedures. ... > permissions from AppUser and then grants execute permissions for AppUser ...
    (microsoft.public.sqlserver.security)
  • Re: application roles
    ... The best way is to take advantage of parameterized stored procedures, ... stored procedures and denying all permissions to the base tables to ... >What is the recommended approach to prevent users from accessing database ... >> your client code. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: permissions with sql server tables and access
    ... >>maintainable permissions on my tables, views, etc. ... >>access to my database. ... Only views and stored procedures can be used. ...
    (microsoft.public.sqlserver.security)
  • Re: User Permissions
    ... You can certainly store User/Group and Object permissions, but I can't really advise you how to do this, since I don't ... I'm not sure what you mean by "not permissions of the database". ...
    (microsoft.public.access.security)