Role-based permission management database design (RBAC)

This article comes from the design of my database for 'Role-based access control' permission management.

Please send an email to the freeget.one@gmail.com for strong software.
*/
Use [Master]
Go

-- Check whether the database [RBAC] exists. If yes, delete the database (for test only, otherwise data will be lost .)
-- Search from the sysdatabase to see that if the [RBAC] database exist.
-- If exists then drop it else create it.

If exists (select * From sysdatabases where name = 'rbac ')
Drop database [RBAC]
Go

-- Create a database [RBAC]
-- Create the database named by '[RBAC]'.
Create Database [RBAC]
Go

-- Use Database [RBAC]
-- Use the database of '[RBAC]'.
Use [RBAC]
Go

-- Create a "user" data table [rbac_user]
-- Create the datatable named by '[rbac_user]' to save users.
Create Table [rbac_user]
(
-- User ID
[User_id] int primary key not null,
-- User Name
[User_name] varchar (20) not null,
-- User Password
[User_password] varchar (20) not null,
-- User status
[User_lock] bit not null
)
Go

-- Add Test Data
-- Add data for test
Insert into [rbac_user] values (1, 'fightingyang', 'Password', 0 );
Go
Insert into [rbac_user] values (2, 'supper3000 ', 'teacher', 0 );
Go
Insert into [rbac_user] values (3, 'jianzhongli', 'teacher', 1 );
Go

Select * from [rbac_user]
Go

-- Create a "group" data table [rbac_group]
-- Create the datatable named by '[rbac_group]' to save groups.
Create Table [rbac_group]
(
-- Group ID
[Group_id] int primary key not null,
-- Group name
[Group_name] varchar (20) not null
)
Go

-- Add Test Data
-- Add data for test
Insert into [rbac_group] values (1, 'programmer hobby ');
Go
Insert into [rbac_group] values (2, 'msdn instructor ');
Go

Select * from [rbac_group]
Go

-- Create a "role" data table [rbac_role]
-- Create the datatable named by '[rbac_role]' to save roles.
Create Table [rbac_role]
(
-- Role ID
[Role_id] int primary key not null,
-- Role name
[Role_name] varchar (20) not null
)
Go

-- Add Test Data
-- Add data for test
Insert into [rbac_role] values (1, 'admin ');
Go
Insert into [rbac_role] values (2, 'user ');
Go

Select * from [rbac_role]
Go

 

-- Create a "resource" data table [rbac_resource]
-- Create the datatable named by '[rbac_resource]' to save resources.
Create Table [rbac_resource]
(
-- Resource ID
[Resource_id] int primary key not null,
-- Resource Name
[Resource_name] varchar (20) not null
)
Go

-- Add Test Data
-- Add data for test
Insert into [rbac_resource] values (1, 'audios ');
Go
Insert into [rbac_resource] values (2, 'video ');
Go

Select * from [rbac_resource]
Go

 

-- Create an "operation" data table [rbac_operate]
-- Create the datatable named by '[rbac_operate]' to save operates.
Create Table [rbac_operate]
(
-- Operation ID
[Operate_id] int primary key not null,
-- Operation name
[Operate_name] varchar (10) Not null
)
Go

-- Add Test Data
-- Add data for test
Insert into [rbac_operate] values (1, 'add ');
Go
Insert into [rbac_operate] values (2, 'read ');
Go
Insert into [rbac_operate] values (3, 'write ');
Go
Insert into [rbac_operate] values (4, 'delete ');
Go

Select * from [rbac_operate]
Go

 

-- Create a "permission" data table [rbac_privilege]
-- Create the datatable named by [rbac_privilege] To save privileges.
Create Table [rbac_privilege]
(
-- Permission ID
[Privilege_id] int primary key not null,
-- Resource ID
[Resource_id] int foreign key references [rbac_resource] ([resource_id]) not null,
-- Operation ID
[Operate_id] int foreign key references [rbac_operate] ([operate_id]) not null
)
Go

-- Add Test Data
-- Add data for test

-- The First permission is to add the "permission" to "audio ".
Insert into [rbac_privilege] values (1, 1 );
Go
-- The second permission is the "read" permission for "audio ".
Insert into [rbac_privilege] values (2, 1, 2 );
Go
-- The third permission is the "write" permission for "audio ".
Insert into [rbac_privilege] values (3,1, 3 );
Go
-- The fourth permission is the "delete" permission for "audio ".
Insert into [rbac_privilege] values (4,1, 4 );
Go
-- The Fifth permission is the "read" permission for "video ".
Insert into [rbac_privilege] values (5, 2, 1 );
Go
-- The sixth permission is the "read" permission for "video ".
Insert into [rbac_privilege] values (6, 2 );
Go
-- The Seventh permission is the "write" permission for "video ".
Insert into [rbac_privilege] values (7,2, 3 );
Go
-- The eighth permission is the "delete" permission for "video ".
Insert into [rbac_privilege] values (8, 2, 4 );
Go

Select * from [rbac_operate]
Go

 

-- Create an "authorize" data table [rbac_impower]
-- Create the datatable named by [rbac_impower] To save impower.
Create Table [rbac_impower]
(
-- Authorization number
[Impower_id] int primary key not null,
-- Role ID
[Role_id] int foreign key references [rbac_role] ([role_id]) not null,
-- Permission ID
[Privilege_id] int foreign key references [rbac_privilege] ([privilege_id]) not null
)
Go

-- Add Test Data
-- Add data for test

-- The first authorization content "admin" has the 'Add "permission to" audio'
Insert into [rbac_impower] values (1, 1 );
Go
-- The second authorization content "admin" has the "read" permission on "audio'
Insert into [rbac_impower] values (2, 2 );
Go
-- The third authorization content "admin" has the "write" permission for "audio'
Insert into [rbac_impower] values (3, 3 );
Go
-- Article 4 the authorized content "admin" has the "delete" permission on "audio'
Insert into [rbac_impower] values (4, 4 );
Go
-- The Fifth authorization content "admin" has the "add" permission "for" video'
Insert into [rbac_impower] values (5, 5 );
Go
-- Article 6 The authorized content "admin" has the 'read "permission on" video'
Insert into [rbac_impower] values (6, 6 );
Go
-- Article 7 The authorization content "admin" has the "write" permission for "video'
Insert into [rbac_impower] values (7, 7 );
Go
-- Article 8 "admin" has the "delete" permission on "video'
Insert into [rbac_impower] values (8, 8 );
Go
-- The ninth authorization content "user" has 'read "permission for" audio'
Insert into [rbac_impower] values (9, 2 );
Go
-- Article 10 the authorized content "user" has the "read" permission on "video'
Insert into [rbac_impower] values (10, 5 );
Go

Select * from [rbac_impower]
Go

 

-- Add Test Data
-- Add data for test

-- The first part of the data for the group's roles is "msdn instructor" with "admin ".
Insert into [rbac_grouprole] values (1, 2, 1 );
Go
-- The second content of the data of the group's roles is "programmer" with "user ".
Insert into [rbac_grouprole] values (2, 1, 2 );
Go

Select * from [rbac_grouprole]
Go

-- Create a "User Group" data table [rbac_usergrouprole]
-- Create the datatable named by '[rbac_usergrouprole]' to save usergrouproles.
Create Table [rbac_usergrouprole]
(
-- User Group ID
[Usergroup_id] int primary key not null,
-- User ID
[User_id] int foreign key references [rbac_user] ([user_id]) not null,
-- Group ID
[Group_id] int foreign key references [rbac_group] ([group_id]) not null,
-- Role ID
[Role_id] int foreign key references [rbac_role] ([role_id]) not null
)
Go

-- Add Test Data
-- Add data for test

-- The first user group data is "fightingyang", which belongs to the "programmers" group. The role in the group is "admin"
Insert into [rbac_usergroup] values (1, 1, 1 );
Go
-- The second user group data is "supper3000" belonging to the "msdn instructor" group, and the role in the group is "admin"
Insert into [rbac_usergroup] values (2,2, 2,1 );
Go
-- The third user group data is "jianzhongli" belonging to the "msdn instructor" group. The role in the group is "user"
Insert into [rbac_usergroup] values (3,3, 2,2 );
Go

Select * from [rbac_usergrouprole]
Go