svn 管理 mysql_svn项目使用svnAdmin管理，通过mysql查询项目权限情况

最新推荐文章于 2021-03-23 08:22:31 发布

WWF世界自然基金会

最新推荐文章于 2021-03-23 08:22:31 发布

阅读量176

点赞数

文章标签： svn 管理 mysql

本文链接：https://blog.csdn.net/weixin_26861329/article/details/113904858

版权

本文介绍如何通过svnAdmin工具结合MySQL查询和管理svn项目权限。内容包括使用SQL查询组权限和用户权限，解析了不同权限级别，并提出了在用户姓名不明确时创建新表m2username来存储真实姓名的解决方案。

摘要由CSDN通过智能技术生成

svn项目使用svnAdmin管理，通过mysql查询项目权限情况

sql语句如下 fzsg为项目名，需替换为相应的项目名

SELECT

t1.NAME,

t1.access,

t1.path,

t2.username

FROM

(

SELECT

groups.`name`,

groups.id,

groupprivileges.access,

groupprivileges.path

FROM

groupprivileges,

groups

WHERE

groups.id = groupprivileges.groupid

AND groupprivileges.repositoryid = ( SELECT id FROM repositories WHERE NAME = "fzsg" )

) AS t1,

(

SELECT

users.NAME AS username,

usersgroups.groupid

FROM

usersgroups,

users

WHERE

users.id = usersgroups.userid

AND usersgroups.groupid IN (

SELECT

groups.id

FROM

groupprivileges,

groups

WHERE

groups.id = groupprivileges.groupid

AND groupprivileges.repositoryid = ( SELECT id FROM repositories WHERE NAME = "fzsg" )

)

) AS t2

WHERE

t1.id = t2.groupid

UNION ALL

SELECT

"fzsg",

access,

path,

users.NAME AS username

FROM

userprivileges,

users

WHERE

users.id = userprivileges.userid

AND repositoryid = ( SELECT id FROM repositories WHERE NAME = "fzsg" )

第1部分为组权限，项目组下面所有用户及权限情况

第2部分为用户权限，项目下用户权限情况

项目名权限路径 svn用户名

fzsg 3 / yuting

权限说明

access 0禁止读写，1只读 2【应该是只写，但现实中基本不会用到这个】 3=可读写

svn用户名取名时，如果没有规划好！找不到用户的真实姓名

原来想在users表中，增加字段来处理，但测试发现，修改时会异常，只能新增一个表来处理

CREATE TABLE m2username (

id int(11) NOT NULL AUTO_INCREMENT,

name varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,

rolename varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,

PRIMARY KEY (id) USING BTREE,

UNIQUE INDEX name(name) USING BTREE

) ENGINE = InnoDB AUTO_INCREMENT = 256 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Compact;

新表很简单，id，svn帐号名，真实姓名

select t1.name,t1.access,t1.path,t2.username,t2.rolename

from

(select groups.`name`,groups.id,groupprivileges.access,groupprivileges.path from groupprivileges,groups where groups.id=groupprivileges.groupid and groupprivileges.repositoryid = (select id from repositories where name="fzsg")) as t1 ,

(select t.*,m2username.rolename from (select users.name as username,usersgroups.groupid from usersgroups,users where users.id = usersgroups.userid and usersgroups.groupid in (select groups.id from groupprivileges,groups where groups.id=groupprivileges.groupid and groupprivileges.repositoryid = (select id from repositories where name="fzsg"))) as t,m2username where t.username=m2username.name) as t2

where t1.id=t2.groupid

union all

select R.*,m2username.rolename from (

select "fzsg",access,path,users.name as username

from userprivileges,users where users.id=userprivileges.userid and repositoryid = (select id from repositories where name="fzsg")

) as R,m2username where R.username=m2username.name