Design Question - Implementing User Privileges

I have the following skeleton DDL and need to do the following:
*Ability to override a users privileges of the 'Role' that he belongs to.
For e.g. if a user belongs to Role 1 that has Privileges 1, 2, and 3 then I
want the ability to override this users privileges such that he can be
assigned, say, privileges 1, 2, and 4 though he still belongs to Role 1. I a
m
unable to make much headway into this. Any help is appreciated.
-- table Roles
CREATE TABLE [dbo].[Roles] (
[RoleID] [int] IDENTITY (1, 1) NOT NULL ,
[RoleDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
) ON [PRIMARY]
GO
--table Privileges
CREATE TABLE [dbo].[Privileges] (
[PrivilegeID] [int] IDENTITY (1, 1) NOT NULL ,
[PrivilegeDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL
) ON [PRIMARY]
GO
--table RolePrivilege
CREATE TABLE [dbo].[RolePrivilege] (
[RoleID] [int] NOT NULL ,
[PrivilegeID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Users] (
[UserID] [int] NOT NULL ,
[RoleID] [int] NOT NULL ,
[Username] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Password] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FirstName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Email] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
Thanks,
NaveenYou need to modify your schema to do this...
Although there are several ways to implement such a pattern one way is to
add another table, similar to your RolePrivileges table, called UserPrivilig
es
--table UserPrivilege
CREATE TABLE [dbo].[UserPrivilege] (
[UserID] [int] NOT NULL ,
[PrivilegeID] [int] NOT NULL,
[Allow] TinyInt Not Null Default 1
) ON [PRIMARY]
GO
Then have your application code construct actual privileges from Both
UserPrivilege and RolePrivilege, with UserPrivileges taking Precedence, (or
Overriding) Role Provileges when there was a conflict i.e. If a User's role
Allows Privledge 10, but UserPrivileges has Deny for Prifiledge 10, th3en
Priviledge 10 would be denied...
"Naveen" wrote:

> I have the following skeleton DDL and need to do the following:
> *Ability to override a users privileges of the 'Role' that he belongs to.
> For e.g. if a user belongs to Role 1 that has Privileges 1, 2, and 3 then
I
> want the ability to override this users privileges such that he can be
> assigned, say, privileges 1, 2, and 4 though he still belongs to Role 1. I
am
> unable to make much headway into this. Any help is appreciated.
> -- table Roles
> CREATE TABLE [dbo].[Roles] (
> [RoleID] [int] IDENTITY (1, 1) NOT NULL ,
> [RoleDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL
> ) ON [PRIMARY]
> GO
> --table Privileges
> CREATE TABLE [dbo].[Privileges] (
> [PrivilegeID] [int] IDENTITY (1, 1) NOT NULL ,
> [PrivilegeDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL
> ) ON [PRIMARY]
> GO
> --table RolePrivilege
> CREATE TABLE [dbo].[RolePrivilege] (
> [RoleID] [int] NOT NULL ,
> [PrivilegeID] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Users] (
> [UserID] [int] NOT NULL ,
> [RoleID] [int] NOT NULL ,
> [Username] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Password] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [FirstName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [LastName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Email] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> Thanks,
> Naveen|||Thanks. I think I got a good idea on what to do.
"CBretana" wrote:
> You need to modify your schema to do this...
> Although there are several ways to implement such a pattern one way is to
> add another table, similar to your RolePrivileges table, called UserPrivil
iges
>
> --table UserPrivilege
> CREATE TABLE [dbo].[UserPrivilege] (
> [UserID] [int] NOT NULL ,
> [PrivilegeID] [int] NOT NULL,
> [Allow] TinyInt Not Null Default 1
> ) ON [PRIMARY]
> GO
>
> Then have your application code construct actual privileges from Both
> UserPrivilege and RolePrivilege, with UserPrivileges taking Precedence, (o
r
> Overriding) Role Provileges when there was a conflict i.e. If a User's rol
e
> Allows Privledge 10, but UserPrivileges has Deny for Prifiledge 10, th3en
> Priviledge 10 would be denied...
> "Naveen" wrote:
>