golang结合neo4j实现权限功能设计

neo4j 是非关系型数据库之图形数据库，这里不再赘述。
传统关系数据库基于rbac实现权限, user ---- role ------permission,加上中间表共5张表。
如果再添上部门的概念：用户属于部门，部门拥有角色，则又多了一层：
user------ dept *-------- * role *------*permission，
如果再引入子部门概念。。。

1.权限设计

1.1 关系

user ---*-----> role --- * --> permission
user ------> dept [-->父dept -->父dept --->父dept]  ---可让子部门继承*-> role --- * --> permission
user ------> dept ---不允许子部门继承*-> role --- * --> permission

1.2 图

用户和部门之间的关系：
在这里插入图片描述

部门和子部门之间的关系：
在这里插入图片描述

部门和角色关系：
在这里插入图片描述
角色和权限关系：

后台配置界面：

1.查询一个用户拥有的权限集：

match paths=(admin:Admin{name:'zs'})-[:HAS_ROLE]->(:Role)-[:HAS]->(p:Permission)
return p.id as id, p.name as name, p.url as url
union
match (admin:Admin{name:'zs'})
match paths=(admin)-[:BELONG_TO]->(:Dept)-[:CHILD_OF*0..3]->(d:Dept)-[:ALLOW_INHERIT]->(:Role)
-[:HAS]->(p:Permission)
return p.id as id, p.name as name, p.url as url
union
match (admin:Admin{name:'zs'})
match paths=(admin)-[:BELONG_TO]->(d:Dept)-[:ALLOW_NO_INHERIT]->(:Role)
-[:HAS]->(p:Permission)
return p.id as id, p.name as name, p.url as url

在这里插入图片描述
查询用户权限集：链路：

match paths=(admin:Admin{name:'xiaolan'})-[:HAS_ROLE]->(:Role)-[:HAS]->(p:Permission)
return paths
union
match (admin:Admin{name:'xiaolan'})
match paths=(admin)-[:BELONG_TO]->(:Dept)-[:CHILD_OF*0..3]->(d:Dept)-[:ALLOW_INHERIT]->(:Role)
-[:HAS]->(p:Permission)
return paths
union
match (admin:Admin{name:'xiaolan'})
match paths=(admin)-[:BELONG_TO]->(d:Dept)-[:ALLOW_NO_INHERIT]->(:Role)
-[:HAS]->(p:Permission)
return paths

在这里插入图片描述

2.判断一个用户是否可访问特定资源（url, 通过权限体现此概念）：

match c=(admin:Admin{name:'xiaoQ'})-[:HAS_ROLE]->(:Role)-[:HAS]->(p:Permission{url:'/api/v2/goods/list'})
return count(c) as accessCount
union
match c=(admin:Admin{name:'xiaoQ'})-[:BELONG_TO]->(:Dept)-[:CHILD_OF*0..3]->(d:Dept)-[:ALLOW_INHERIT]->(:Role)
-[:HAS]->(p:Permission {url:'/api/v2/goods/list'})
where not ((admin)-[:BELONG_TO]->(:Dept)-[:CHILD_OF*0..3]->(d:Dept)-[:DENY]->(:Role))
return count(c) as accessCount
union
match c=(admin:Admin{name:'xiaoQ'})-[:BELONG_TO]->(d:Dept)-[:ALLOW_NO_INHERIT]->(:Role)
-[:HAS]->(p:Permission {url:'/api/v2/goods/list'})
return count(c) as accessCount

在这里插入图片描述

3.查看谁拥有指定资源(url) 的权限：

match (p:Permission{url:'/api/v2/admin/list'})
match (admin:Admin)-[:HAS_ROLE]->(:Role)-[:HAS]->(p)
return admin.id as id, admin.name as name
union
match (p:Permission{url:'/api/v2/admin/list'})
match (admin:Admin)-[:BELONG_TO]->(:Dept)-[:CHILD_OF*0..3]->(d:Dept)-[:ALLOW_INHERIT]->(:Role)-[:HAS]->(p)
where not ((admin)-[:BELONG_TO]->(:Dept)-[:CHILD_OF*0..3]->(d:Dept)-[:DENY]->(:Role))
return admin.id as id, admin.name as name
union
match (p:Permission{url:'/api/v2/admin/list'})
match (admin:Admin)-[:BELONG_TO]->(d:Dept)-[:ALLOW_NO_INHERIT]->(:Role)-[:HAS]->(p)
return admin.id as id, admin.name as name

在这里插入图片描述
附上完整关系图：

下面介绍golang代码整合处理：

先上成型图：
在这里插入图片描述

1.启动项目时读取配置，初始化neo4j driver:

package common

import (
	"context"
	"github.com/neo4j/neo4j-go-driver/v5/neo4j"
	"log"
)

var DBName string
var Neo4jCtx = context.Background()
var Driver neo4j.DriverWithContext

func initNeo4jConfig(c neo4jConfig) {
	var err error
	// Driver is thread safe: can be shared by multiple threads
	Driver, err = neo4j.NewDriverWithContext(c.DbUri, neo4j.BasicAuth(c.DbUser, c.DbPwd, ""))
	if err != nil {
		log.Println("new neo4j driver with context failed:", err.Error())
		return
	}

	err = Driver.VerifyConnectivity(Neo4jCtx)
	if err != nil {
		log.Printf("init neo4j failed:%s\n", c)
		return
	}
	log.Println("neo4j connection established...")

	DBName = c.DBName
}

2.neo4j列表分页查询数据

func PageDept(pageNo, pageSize int, name string, queryParentOnly string, parent uint64) (*common.Page, error) {

	var ctx = common.Neo4jCtx
	session := common.Driver.NewSession(ctx, neo4j.SessionConfig{DatabaseName: common.DBName})
	defer session.Close(ctx)
	tx, err := session.BeginTransaction(ctx)
	if err != nil {
		return nil, err
	}
	defer tx.Rollback(ctx)

	whereSql, params := composeDeptSearchQuerySql(name, queryParentOnly, parent)

	res, err := tx.Run(ctx, whereSql+` return count(d.id) as c`, params)
	if err != nil {
		return nil, err
	}
	record, err := res.Single(ctx)
	if err != nil {
		return nil, err
	}
	var c = int64(0)
	if r, flg := record.Get("c"); flg && r != nil {
		c = r.(int64)
	}
	// 没有数据
	if c == int64(0) {
		return common.NewPage([]model.Dept{}, pageNo, pageSize, 0), nil
	}

	params["s"] = (pageNo - 1) * pageSize
	params["size"] = pageSize

	res, err = tx.Run(ctx, whereSql + ` return `+row+` order by d.id SKIP $s limit $size`, params)
	if err != nil {
		return nil, err
	}
	var ds []model.Dept

	for res.Next(ctx) {
		m := res.Record().AsMap()
		var d model.Dept
		err = mapstructure.Decode(m, &d)
		if err != nil {
			return nil, err
		}
		d.CreatedTimeStr = d.CreatedTime.Format(time.DateTime)
		ds = append(ds, d)
	}

	return common.NewPage(ds, pageNo, pageSize, int(c)), nil
}

func composeDeptSearchQuerySql(name string, only string, parent uint64) (string, map[string]any) {

	var params = map[string]any{}
	sb := strings.Builder{}
	sb.WriteString("match (d:Dept) ")

	// 没有条件查询
	if name == "" && only == "" && parent == 0 {
		return sb.String(), params
	}
	// 只查询父分类
	if only == "on" {
		sb.WriteString(" where d.parent = 0")
		return sb.String(), params
	}

	// 查询指定的父分类
	if parent > 0 {
		sb.WriteString("-[:CHILD_OF]->(:Dept{id: $parent})")
		//sb.WriteString(" where d.parent = $parent")
		params["parent"] = parent
	}

	// 有部门名称的模糊查询
	if len(name) > 0 {
		sb.WriteString(" where d.name CONTAINS $name")
		params["name"] = name
	}
	return sb.String(), params
}

权限permission dao for neo4j操作：

package neo

import (
	"commerce/common"
	"commerce/model"
	"fmt"
	"github.com/mitchellh/mapstructure"
	"github.com/neo4j/neo4j-go-driver/v5/neo4j"
	"strings"
	"sync"
)

var permissionLock sync.Mutex

func PagePermission(pageNo, pageSize int, name string) (*common.Page, error) {

	var ctx = common.Neo4jCtx
	session := common.Driver.NewSession(ctx, neo4j.SessionConfig{DatabaseName: common.DBName})
	defer session.Close(ctx)
	tx, err := session.BeginTransaction(ctx)
	if err != nil {
		return nil, err
	}
	defer tx.Rollback(ctx)

	whereSql, params := composePermissionSearchQuerySql(name)

	res, err := tx.Run(ctx, whereSql+` return count(p.id) as c`, params)
	if err != nil {
		return nil, err
	}
	record, err := res.Single(ctx)
	if err != nil {
		return nil, err
	}
	var c = int64(0)
	if r, flg := record.Get("c"); flg && r != nil {
		c = r.(int64)
	}
	// 没有数据
	if c == int64(0) {
		return common.NewPage([]model.Permission{}, pageNo, pageSize, 0), nil
	}

	params["s"] = (pageNo - 1) * pageSize
	params["size"] = pageSize

	res, err = tx.Run(ctx, whereSql+` return p.id as id, p.name as name, p.priority as priority, p.status as status, p.public_res_flg as public_res_flg order by p.id SKIP $s limit $size`, params)
	if err != nil {
		return nil, err
	}
	var rs []model.Permission

	for res.Next(ctx) {
		m := res.Record().AsMap()
		var r model.Permission
		err = mapstructure.Decode(m, &r)
		if err != nil {
			return nil, err
		}
		rs = append(rs, r)
	}

	return common.NewPage(rs, pageNo, pageSize, int(c)), nil
}

func GetPermissionById(id uint64) (*model.Permission, error) {

	sqlTpl := `match (p:Permission {id: $id}) return p.id as id, p.name as name, p.priority as priority, p.status as status, p.public_res_flg as public_res_flg`

	res, err := neo4j.ExecuteQuery(common.Neo4jCtx, common.Driver, sqlTpl, map[string]any{
		"id": id,
	}, neo4j.EagerResultTransformer,
		neo4j.ExecuteQueryWithDatabase(common.DBName),
		neo4j.ExecuteQueryWithReadersRouting(),
	)
	if err != nil {
		return nil, err
	}

	records := res.Records
	if records == nil || len(records) == 0 {
		return nil, err
	}

	m := records[0].AsMap()
	var r model.Permission
	err = mapstructure.Decode(m, &r)
	if err != nil {
		return nil, err
	}
	return &r, nil
}

func AddPermission(c model.Permission) (uint64, error) {

	permissionLock.Lock()
	defer permissionLock.Unlock()

	res, err := neo4j.ExecuteQuery(common.Neo4jCtx, common.Driver, `match (p:Permission {name: $name}) return p.id as id limit 1`, map[string]any{"name": c.Name},
		neo4j.EagerResultTransformer, neo4j.ExecuteQueryWithDatabase(common.DBName),
		neo4j.ExecuteQueryWithReadersRouting())

	if err != nil {
		return 0, err
	}
	if res.Records != nil && len(res.Records) > 0 {
		return 0, fmt.Errorf("%s 已存在，不允许创建", c.Name)
	}

	id := common.IdGenerator.Generate()

	sqlTpl := `create (p:Permission {id: $id, name: $name, priority: $priority, status:$status, 
		public_res_flg: $publicResFlg, created_time: datetime({timezone: 'Asia/Shanghai'}), 
		updated_time: datetime({timezone: 'Asia/Shanghai'})})`

	_, err = neo4j.ExecuteQuery(common.Neo4jCtx, common.Driver, sqlTpl, map[string]any{
		"id":           id,
		"name":         c.Name,
		"priority":     c.Priority,
		"status":       c.Status,
		"publicResFlg": c.PublicResFlg,
	}, neo4j.EagerResultTransformer, neo4j.ExecuteQueryWithDatabase(common.DBName))

	if err != nil {
		return 0, err
	}
	return id, nil
}

func UpdatePermission(c model.Permission) (bool, error) {

	permissionLock.Lock()
	defer permissionLock.Unlock()

	res, err := neo4j.ExecuteQuery(common.Neo4jCtx, common.Driver, `match (p:Permission {name: $name}) return p.id as id limit 1`, map[string]any{"name": c.Name},
		neo4j.EagerResultTransformer, neo4j.ExecuteQueryWithDatabase(common.DBName),
		neo4j.ExecuteQueryWithReadersRouting())

	if err != nil {
		return false, err
	}
	records := res.Records
	if records != nil && len(records) > 0 {
		r, _ := records[0].Get("id")
		dbId := uint64(r.(int64))

		if dbId != c.Id {
			return false, fmt.Errorf("%s 已存在，不允许更新部门名称为此值", c.Name)
		}
	}

	sqlTpl := `match (p:Permission {id: $id}) set p.name=$name, p.priority=$priority,
		p.public_res_flg=$publicResFlg, p.updated_time=datetime({timezone: 'Asia/Shanghai'})`

	res, err = neo4j.ExecuteQuery(common.Neo4jCtx, common.Driver, sqlTpl, map[string]any{
		"id":           c.Id,
		"name":         c.Name,
		"priority":     c.Priority,
		"publicResFlg": c.PublicResFlg,
	}, neo4j.EagerResultTransformer, neo4j.ExecuteQueryWithDatabase(common.DBName))

	if err != nil {
		return false, err
	}
	return res.Summary.Counters().ContainsUpdates(), nil
}

func composePermissionSearchQuerySql(name string) (string, map[string]any) {

	var params = map[string]any{}
	sb := strings.Builder{}
	sb.WriteString("match (p:Permission) ")

	// 没有条件查询
	if name == "" {
		return sb.String(), params
	}

	// 有权限名称的模糊查询
	if len(name) > 0 {
		sb.WriteString(" where p.name CONTAINS $name")
		params["name"] = name
	}
	return sb.String(), params
}

func UpdatePermissionStatus(id uint64, status int8) error {

	sqlTpl := `match (p:Permission {id: $id}) set p.status = $status, p.updated_time=datetime({timezone: 'Asia/Shanghai'})`

	_, err := neo4j.ExecuteQuery(common.Neo4jCtx, common.Driver, sqlTpl, map[string]any{
		"id":     id,
		"status": status,
	}, neo4j.EagerResultTransformer, neo4j.ExecuteQueryWithDatabase(common.DBName))

	if err != nil {
		return err
	}

	return nil
}

func ListAllPermission() ([]model.Permission, error) {

	res, err := neo4j.ExecuteQuery(common.Neo4jCtx, common.Driver,
		`match (p:Permission) where p.status = $status return p.id as id, p.name as name order by p.priority`,
		map[string]any{"status": 1},
		neo4j.EagerResultTransformer, neo4j.ExecuteQueryWithDatabase(common.DBName),
		neo4j.ExecuteQueryWithReadersRouting())

	if err != nil {
		return nil, err
	}
	records := res.Records
	var ps = make([]model.Permission, len(records))

	for i, r := range records {
		var p model.Permission
		err = mapstructure.Decode(r.AsMap(), &p)
		if err != nil {
			return nil, err
		}
		ps[i] = p
	}

	return ps, nil
}

func ListPermissionByRoleId(roleId uint64) ([]model.Permission, error) {

	res, err := neo4j.ExecuteQuery(common.Neo4jCtx, common.Driver,
		`match (p:Permission {status: $status})<-[:HAS]-(r:Role {id: $roleId}) 
				return distinct p.id as id, p.name as name`,
		map[string]any{"status": 1, "roleId": roleId},
		neo4j.EagerResultTransformer, neo4j.ExecuteQueryWithDatabase(common.DBName),
		neo4j.ExecuteQueryWithReadersRouting())

	if err != nil {
		return nil, err
	}
	records := res.Records
	var ps = make([]model.Permission, len(records))

	for i, r := range records {
		var p model.Permission
		err = mapstructure.Decode(r.AsMap(), &p)
		if err != nil {
			return nil, err
		}
		ps[i] = p
	}

	return ps, nil
}

角色操作neo4j dao：

package neo

import (
	"commerce/common"
	"commerce/model"
	"fmt"
	"github.com/mitchellh/mapstructure"
	"github.com/neo4j/neo4j-go-driver/v5/neo4j"
	"strings"
	"sync"
)

var roleLock sync.Mutex

func PageRole(pageNo, pageSize int, name string) (*common.Page, error) {

	var ctx = common.Neo4jCtx
	session := common.Driver.NewSession(ctx, neo4j.SessionConfig{DatabaseName: common.DBName})
	defer session.Close(ctx)
	tx, err := session.BeginTransaction(ctx)
	if err != nil {
		return nil, err
	}
	defer tx.Rollback(ctx)

	whereSql, params := composeRoleSearchQuerySql(name)

	res, err := tx.Run(ctx, whereSql+` return count(r.id) as c`, params)
	if err != nil {
		return nil, err
	}
	record, err := res.Single(ctx)
	if err != nil {
		return nil, err
	}
	var c = int64(0)
	if r, flg := record.Get("c"); flg && r != nil {
		c = r.(int64)
	}
	// 没有数据
	if c == int64(0) {
		return common.NewPage([]model.Role{}, pageNo, pageSize, 0), nil
	}

	params["s"] = (pageNo - 1) * pageSize
	params["size"] = pageSize

	res, err = tx.Run(ctx, whereSql+` return r.id as id, r.name as name, r.priority as priority, r.status as status order by r.id SKIP $s limit $size`, params)
	if err != nil {
		return nil, err
	}
	var rs []model.Role

	for res.Next(ctx) {
		m := res.Record().AsMap()
		var r model.Role
		err = mapstructure.Decode(m, &r)
		if err != nil {
			return nil, err
		}
		rs = append(rs, r)
	}

	return common.NewPage(rs, pageNo, pageSize, int(c)), nil
}

func GetRoleById(id uint64) (*model.Role, error) {

	sqlTpl := `match (r:Role {id: $id}) return r.id as id, r.name as name, r.priority as priority, r.status as status`

	res, err := neo4j.ExecuteQuery(common.Neo4jCtx, common.Driver, sqlTpl, map[string]any{
		"id": id,
	}, neo4j.EagerResultTransformer,
		neo4j.ExecuteQueryWithDatabase(common.DBName),
		neo4j.ExecuteQueryWithReadersRouting(),
	)
	if err != nil {
		return nil, err
	}

	records := res.Records
	if records == nil || len(records) == 0 {
		return nil, err
	}

	m := records[0].AsMap()
	var r model.Role
	err = mapstructure.Decode(m, &r)
	if err != nil {
		return nil, err
	}
	return &r, nil
}

func AddRole(c model.Role) (uint64, error) {

	roleLock.Lock()
	defer roleLock.Unlock()

	res, err := neo4j.ExecuteQuery(common.Neo4jCtx, common.Driver, `match (r:Role {name: $name}) return r.id as id limit 1`, map[string]any{"name": c.Name},
		neo4j.EagerResultTransformer, neo4j.ExecuteQueryWithDatabase(common.DBName),
		neo4j.ExecuteQueryWithReadersRouting())

	if err != nil {
		return 0, err
	}
	if res.Records != nil && len(res.Records) > 0 {
		return 0, fmt.Errorf("%s 已存在，不允许创建", c.Name)
	}

	id := common.IdGenerator.Generate()

	sqlTpl := `create (r:Role {id: $id, name: $name,
		priority: $priority, status:$status, created_time: datetime({timezone: 'Asia/Shanghai'}), 
		updated_time: datetime({timezone: 'Asia/Shanghai'})})`

	_, err = neo4j.ExecuteQuery(common.Neo4jCtx, common.Driver, sqlTpl, map[string]any{
		"id":       id,
		"name":     c.Name,
		"priority": c.Priority,
		"status":   c.Status,
	}, neo4j.EagerResultTransformer, neo4j.ExecuteQueryWithDatabase(common.DBName))

	if err != nil {
		return 0, err
	}
	return id, nil
}

func UpdateRole(c model.Role) (bool, error) {

	roleLock.Lock()
	defer roleLock.Unlock()

	res, err := neo4j.ExecuteQuery(common.Neo4jCtx, common.Driver, `match (r:Role {name: $name}) return r.id as id limit 1`, map[string]any{"name": c.Name},
		neo4j.EagerResultTransformer, neo4j.ExecuteQueryWithDatabase(common.DBName),
		neo4j.ExecuteQueryWithReadersRouting())

	if err != nil {
		return false, err
	}
	records := res.Records
	if records != nil && len(records) > 0 {
		r, _ := records[0].Get("id")
		dbId := uint64(r.(int64))

		if dbId != c.Id {
			return false, fmt.Errorf("%s 已存在，不允许更新部门名称为此值", c.Name)
		}
	}

	sqlTpl := `match (r:Role {id: $id}) set r.name=$name, r.priority=$priority, r.updated_time=datetime({timezone: 'Asia/Shanghai'})`

	res, err = neo4j.ExecuteQuery(common.Neo4jCtx, common.Driver, sqlTpl, map[string]any{
		"id":       c.Id,
		"name":     c.Name,
		"priority": c.Priority,
	}, neo4j.EagerResultTransformer, neo4j.ExecuteQueryWithDatabase(common.DBName))

	if err != nil {
		return false, err
	}
	return res.Summary.Counters().ContainsUpdates(), nil
}

func composeRoleSearchQuerySql(name string) (string, map[string]any) {

	var params = map[string]any{}
	sb := strings.Builder{}
	sb.WriteString("match (r:Role) ")

	// 没有条件查询
	if name == "" {
		return sb.String(), params
	}

	// 有角色名称的模糊查询
	if len(name) > 0 {
		sb.WriteString(" where r.name CONTAINS $name")
		params["name"] = name
	}
	return sb.String(), params
}

func UpdateRoleStatus(id uint64, status int8) error {

	sqlTpl := `match (r:Role {id: $id}) set r.status = $status, r.updated_time=datetime({timezone: 'Asia/Shanghai'})`

	_, err := neo4j.ExecuteQuery(common.Neo4jCtx, common.Driver, sqlTpl, map[string]any{
		"id":     id,
		"status": status,
	}, neo4j.EagerResultTransformer, neo4j.ExecuteQueryWithDatabase(common.DBName))

	if err != nil {
		return err
	}

	return nil
}

func AttachRolePermissionList(roleId uint64, permissionIdList []uint64) error {

	var sqlTpl string
	if len(permissionIdList) == 0 {
		sqlTpl = `match (:Role {id: $roleId})-[rel:HAS]->(:Permission) delete rel`
	} else {
		sqlTpl = `match (r:Role {id: $roleId})
				CALL {match (r:Role {id: $roleId})-[rel:HAS]->(:Permission) delete rel }
				with r
				unwind $permissionIdList as pId
				match (p:Permission {id: pId})
				merge (r)-[:HAS]->(p)`
	}
	_, err := neo4j.ExecuteQuery(common.Neo4jCtx, common.Driver, sqlTpl, map[string]any{
		"roleId":           roleId,
		"permissionIdList": permissionIdList,
	}, neo4j.EagerResultTransformer, neo4j.ExecuteQueryWithDatabase(common.DBName))

	if err != nil {
		return err
	}

	return nil
}