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 commonimport ("context""github.com/neo4j/neo4j-go-driver/v5/neo4j""log"
)var DBName string
var Neo4jCtx = context.Background()
var Driver neo4j.DriverWithContextfunc initNeo4jConfig(c neo4jConfig) {var err error// Driver is thread safe: can be shared by multiple threadsDriver, 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.Neo4jCtxsession := 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) * pageSizeparams["size"] = pageSizeres, 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.Deptfor res.Next(ctx) {m := res.Record().AsMap()var d model.Depterr = 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 neoimport ("commerce/common""commerce/model""fmt""github.com/mitchellh/mapstructure""github.com/neo4j/neo4j-go-driver/v5/neo4j""strings""sync"
)var permissionLock sync.Mutexfunc PagePermission(pageNo, pageSize int, name string) (*common.Page, error) {var ctx = common.Neo4jCtxsession := 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) * pageSizeparams["size"] = pageSizeres, 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.Permissionfor res.Next(ctx) {m := res.Record().AsMap()var r model.Permissionerr = 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.Recordsif records == nil || len(records) == 0 {return nil, err}m := records[0].AsMap()var r model.Permissionerr = 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.Recordsif 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.Recordsvar ps = make([]model.Permission, len(records))for i, r := range records {var p model.Permissionerr = 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.Recordsvar ps = make([]model.Permission, len(records))for i, r := range records {var p model.Permissionerr = mapstructure.Decode(r.AsMap(), &p)if err != nil {return nil, err}ps[i] = p}return ps, nil
}
角色操作neo4j dao:
package neoimport ("commerce/common""commerce/model""fmt""github.com/mitchellh/mapstructure""github.com/neo4j/neo4j-go-driver/v5/neo4j""strings""sync"
)var roleLock sync.Mutexfunc PageRole(pageNo, pageSize int, name string) (*common.Page, error) {var ctx = common.Neo4jCtxsession := 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) * pageSizeparams["size"] = pageSizeres, 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.Rolefor res.Next(ctx) {m := res.Record().AsMap()var r model.Roleerr = 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.Recordsif records == nil || len(records) == 0 {return nil, err}m := records[0].AsMap()var r model.Roleerr = 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.Recordsif 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 stringif 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 runwind $permissionIdList as pIdmatch (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
}