USE Tempdb
SET ANSI_NULL_DFLT_ON OFF
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].Usr_Role') AND OBJECTPROPERTY(id, N'IsUsrTable') = 1)
DROP TABLE dbo.Usr_Role
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].Role_Page') AND OBJECTPROPERTY(id, N'IsUsrTable') = 1)
DROP TABLE dbo.Role_Page
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].Usr') AND OBJECTPROPERTY(id, N'IsUsrTable') = 1)
DROP TABLE dbo.Usr
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].Role') AND OBJECTPROPERTY(id, N'IsUsrTable') = 1)
DROP TABLE dbo.Role
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].Page') AND OBJECTPROPERTY(id, N'IsUsrTable') = 1)
DROP TABLE dbo.Page
GO
CREATE TABLE dbo.Page(
PageID BigInt Identity(7000,1) PRIMARY KEY,
PageName Varchar(128)
)
GO
CREATE TABLE dbo.Role(
RoleID BigInt Identity(10,1) Primary Key,
RoleName Varchar(255)
)
GO
CREATE TABLE dbo.Usr(
UsrID BigInt Identity(100,1) Primary Key,
UsrName Varchar(255),
Password Varbinary(256)
)
GO
CREATE TABLE dbo.Usr_Role(
Usr_RoleID BigInt Identity(1000,1) Primary Key,
UsrID BigInt REFERENCES Usr,
RoleID BigInt REFERENCES Role
)
GO
CREATE TABLE dbo.Role_Page(
Role_PageID BigInt Identity(10000,1) Primary Key,
RoleID BigInt REFERENCES Role,
PageID BigInt REFERENCES Page
)
GO
/*
INSERT
*/
INSERT INTO Role(RoleName) VALUES('Administrator')
INSERT INTO Role(RoleName) VALUES('Public')
GO
INSERT INTO Page(PageName) VALUES('Index.cfm')
INSERT INTO Page(PageName) VALUES('PageView.cfm')
INSERT INTO Page(PageName) VALUES('PageCreate.cfm')
INSERT INTO Page(PageName) VALUES('PageUpdate.cfm')
INSERT INTO Page(PageName) VALUES('PageDelete.cfm')
GO
INSERT INTO Usr(UsrName,Password) VALUES('Phillip.Senn',CONVERT(varbinary(256), pwdEncrypt('mypassword')))
INSERT INTO Usr(UsrName,Password) VALUES('Peter.Bell',CONVERT(varbinary(256), pwdEncrypt('password goes here')))
GO
DECLARE @UsrID BigInt,@RoleID BigInt
SELECT @UsrID=UsrID FROM Usr WHERE UsrName='Phillip.Senn'
SELECT @RoleID=RoleID FROM Role WHERE RoleName='Administrator'
INSERT INTO Usr_Role(UsrID,RoleID) VALUES(@UsrID,@RoleID)
SELECT @UsrID=UsrID FROM Usr WHERE UsrName='Peter.Bell'
SELECT @RoleID=RoleID FROM Role WHERE RoleName='Public'
INSERT INTO Usr_Role(UsrID,RoleID) VALUES(@UsrID,@RoleID)
GO
DECLARE @PageID BigInt, @RoleID BigInt
SELECT @PageID=PageID FROM Page
SELECT @RoleID=RoleID FROM Role WHERE RoleName='Administrator'
INSERT INTO Role_Page(RoleID,PageID) 
SELECT @RoleID,PageID FROM Page
SELECT @RoleID=RoleID FROM Role WHERE RoleName='Public'
SELECT @PageID=PageID FROM Page WHERE PageNAME='Index.cfm'
INSERT INTO Role_Page(RoleID,PageID) VALUES(@RoleID,@PageID)
SELECT @PageID=PageID FROM Page WHERE PageNAME='PageView.cfm'
INSERT INTO Role_Page(RoleID,PageID) VALUES(@RoleID,@PageID)
/*
DECLARE @PassPhrase Varchar(128)
SET @PassPhrase = 'OBAFGKMRN'
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'plugh'
INSERT INTO Usr(UsrName,Password) VALUES('Phillip.Senn',EncryptbyPassPhrase(@Passphrase,'mypassword1'))
INSERT INTO Usr(UsrName,Password) VALUES('Peter.Bell',EncryptbyPassPhrase(@Passphrase,'mypassword2'))
DECLARE @PassPhrase Varchar(128)
SET @PassPhrase = 'OBAFGKMRN'
SELECT UsrName,CONVERT(VARCHAR(20),DecryptByPassPhrase(@Passphrase,Password))
FROM Usr
*/
GO
/* 
SELECT
*/
-- DECLARE @Test Varchar(128)
-- SET @Test='mypassword'
-- SELECT UsrName,Password,pwdCompare(pwdEncrypt(@Test),Password) FROM Usr
-- SELECT UsrName,Password,pwdCompare(Password,pwdEncrypt(@Test)) FROM Usr
SELECT * FROM Page
SELECT * FROM Role
SELECT * FROM Usr
SELECT * FROM Usr_Role
SELECT * FROM Role_Page
GO
/*
Index
*/
CREATE INDEX Usr_UsrName ON Usr(UsrName)
CREATE INDEX Page_PageName ON Page(PageName)
CREATE INDEX Role_RoleName ON Role(RoleName)
CREATE INDEX Role_Page_RoleID ON Role_Page(RoleID)
CREATE INDEX Role_Page_PageID ON Role_Page(PageID)
CREATE UNIQUE INDEX Role_Page ON Role_Page(RoleID,PageID)
CREATE INDEX Usr_Role_UsrID ON Usr_Role(UsrID)
CREATE INDEX Usr_Role_RoleID ON Usr_Role(RoleID)
CREATE UNIQUE INDEX Usr_Role ON Usr_Role(UsrID,RoleID)
GO
/* 
Views
*/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'vPage') AND OBJECTPROPERTY(id, N'IsView') = 1)
DROP View vPage
GO
CREATE View vPage AS
SELECT * FROM Page
GO
SELECT * FROM VPage
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'vRole') AND OBJECTPROPERTY(id, N'IsView') = 1)
DROP VIEW vRole
GO
CREATE VIEW vRole AS
SELECT * FROM Role
GO
SELECT * FROM vRole
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'vUsr') AND OBJECTPROPERTY(id, N'IsView') = 1)
DROP View vUsr
GO
CREATE VIEW vUsr AS
SELECT * FROM Usr
GO
SELECT * FROM vUsr
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'vUsr_Role') AND OBJECTPROPERTY(id, N'IsView') = 1)
DROP VIEW vUsr_Role
GO
CREATE VIEW vUsr_Role AS
SELECT 
UsrName,RoleName
FROM Usr_Role
INNER JOIN Usr
ON Usr_Role.UsrID = Usr.UsrID
INNER JOIN Role
ON Usr_Role.RoleID = Role.RoleID
GO
SELECT * FROM vUsr_Role
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'vRole_Page') AND OBJECTPROPERTY(id, N'IsView') = 1)
DROP VIEW vRole_Page
GO
CREATE VIEW vRole_Page AS
SELECT 
RoleName,PageName
FROM Role_Page
INNER JOIN Page
ON Role_Page.PageID = Page.PageID
INNER JOIN Role
ON Role_Page.RoleID = Role.RoleID
GO
SELECT * FROM vRole_Page
GO
-- Every Page this Usr has rights to
SELECT UsrName,PageName
FROM Usr_Role
INNER JOIN Usr
ON Usr_Role.UsrID=Usr.UsrID
RIGHT OUTER JOIN Role_Page
ON Usr_Role.RoleID=Role_Page.RoleID
INNER JOIN Page
ON Role_Page.PageID=Page.PageID