权限管理系统（偏重于数据库设计）

--用户登陆

select * from Accounts_Users where username='admin' and [password] ='admin'

--1 用户角色表中的用户ID=登陆的用户ID

--用户角色中的角色ID=角色许可表中的角色ID

--角色许可表中的模型ID=模型ID

 

--最终取出模型表中的相关数据

 

 

 

数据库

--作者：张涛

use master

go

create database AccountsRole

go

use AccountsRole

go 

create table userLog --用户登陆表

(

  userId int primary key,

  userName nvarchar(20),

  userPwd nvarchar(20),

  isSuper bit   --是否是管理员

)

go

insert into userLog values(1,'admin','admin',0)

insert into userLog values(2,'user','user',1)

go

create table Roles  --角色表

(

  roleId int primary key,  --角色编号

  roleName nvarchar(50), --角色名称

  texts text

)

go

insert into roles values(1,'系统管理员','s')

insert into roles values(2,'管理员','s')

insert into roles values(3,'科长','s')

insert into roles values(4,'科员','s')

go

create table menu  --菜单表

(

  menuId int primary key, --菜单编号

  menuValue nvarchar(50),  --菜单名称

  menuUrl nvarchar(50)

)

go

insert into menu values(1,'签发文件','http://baidu.com')

insert into menu values(2,'职称修改','http://baidu.com')

insert into menu values(3,'文件修改','http://baidu.com')

insert into menu values(4,'未发','http://baidu.com')

insert into menu values(5,'已发','http://baidu.com')

insert into menu values(6,'所有计划','http://baidu.com')

go

 

create table userRole --用户角色表

(

  id int primary key identity(1,1),

  userId int references userLog(userId), --用户编号

  roleId int references Roles(roleId),  --角色编号

)

go

insert into userrole values(1,1)

insert into userrole values(2,2)

go

create table RolePermission

(

  id int primary key identity(1,1),

  roleId int references Roles(roleId),  --角色编号

  menuId int references menu(menuId),  --角色编号

)

go

insert into RolePermission values(1,1)

insert into RolePermission values(1,2)

insert into RolePermission values(1,3)

insert into RolePermission values(1,4)

insert into RolePermission values(2,1)

insert into RolePermission values(2,2)

insert into RolePermission values(2,3)

go

select * from userLog

select * from Roles

select * from userRole

select * from menu

select * from RolePermission

 

 

--用户登陆

select * from userLog where username='admin' and userpwd='admin'and issuper=0

--按照用户名查找用户角色 根据用户角色查找菜单项 管理员登陆

select roleid from userrole where userid=1 --根据角色的个数循环执行下面的sql语句

select distinct m.menuvalue,m.menuurl from userRole as r ,menu as m ,RolePermission as p

where r.userid=1 and r.roleId=p.roleId and p.menuId=m.menuId

go

--用户登陆

select roleid from userrole where userid=2 --根据角色的个数循环执行下面的sql语句

select distinct m.menuvalue,m.menuurl from userRole as r ,menu as m ,RolePermission as p

where r.userid=2 and r.roleId=p.roleId and p.menuId=m.menuId

go

 

select m.menuvalue,m.menuurl from userRole as r ,menu as m where r.userid=1 and r.roleId=1 and r.menuId=m.menuId

 

--登陆验证

select * from userlog where username='admin' and userpwd='admin' and issuper=0

 

--根据用户具有的角色角色许可 对应的菜单

select distinct m.menuvalue,m.menuurl from userRole as u,RolePermission as p,menu as m

where u.userid=1 and u.roleId=p.roleid and p.menuid=m.menuid

go

--用户登陆

select distinct m.menuvalue,m.menuurl from userRole as u,RolePermission as p,menu as m

where u.userid=2 and u.roleId=p.roleid and p.menuid=m.menuid

go

--添加菜单

insert into menu values(7,'测试菜单','http://www.google.com')

 

--给角色添加权限

insert into RolePermission values(1,5)

insert into RolePermission values(1,6)

insert into RolePermission values(1,7)

insert into RolePermission values(2,4)

go

 

 

代码：

protected void Page_Load(object sender, EventArgs e)

    {

        //判断用户的权限  根据登陆的用户名的编号 查询权限并得到相应的结果 r.userid=1

        string sql = "select distinct m.menuvalue,m.menuurl from userRole as r ,menu as m ,RolePermission as p where r.userid=1 and r.roleId=p.roleId and p.menuId=m.menuId";

        SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=AccountsRole;Integrated Security=True");

        SqlDataAdapter sqldad = new SqlDataAdapter(sql, conn);

        DataTable dt = new DataTable();

        sqldad.Fill(dt);

        this.GridView1 .DataSource=dt;

        this.GridView1 .DataBind();

 

        //普通用户登陆  r.userid=2

        //string sql = "select distinct m.menuvalue,m.menuurl from userRole as r ,menu as m ,RolePermission as p where r.userid=2 and r.roleId=p.roleId and p.menuId=m.menuId";

        //SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=AccountsRole;Integrated Security=True");

        //SqlDataAdapter sqldad = new SqlDataAdapter(sql, conn);

        //DataTable dt = new DataTable();

        //sqldad.Fill(dt);

        //this.GridView1.DataSource = dt;

        //this.GridView1.DataBind();

} //  管理员登陆                                                  用户登陆