Querying SQL Repository

unknown

Hello,

I am currently reviewing users, roles, and permissions for a repository that I have inherited.

I would like to create a few SQL views to produce a quick overview of repository users with their corresponding groups, roles, permissions, etc.  I have found several tables in the SQL repository that contain this data, but it's a little awkward to query without a bit of trial and error.

Does anyone have a database diagram that I could use, or is there a more efficient way to do this?  I do not want to use the Repository Administration software to manually check through users.

Kinds regards,
Carl

MikeLewis

Here are some queries that I use.

select
auth.name as GroupName,
r.name as Roll
from dAlea olap --Repository
inner join CCombModelRole r
on olap.IDALEA = r.IDCOMBMODEL --Rollen
inner join cCMRoleAuthObj cmroles --Verknüpfung über ModelRole ID
on r.idcombmodelrole = cmroles.idcombmodelrole
inner join cAuthObj auth
on cmroles.idauthobj = auth.idauthobj

Select
U.Name as UserName,
U.Description as UserDescription,
G.Name as GroupName,
G.Description as GroupDescription
from oManualUser U
join oMUserMGroup UG ON U.IDManualUser=UG.IDManualUser
join oManualGroup G ON UG.IDManualGroup=G.IDManualGroup

With UserGroups as
(
Select
U.Name as UserName,
U.Description as UserDescription,
G.Name as GroupName,
G.Description as GroupDescription
from oManualUser U
join oMUserMGroup UG ON U.IDManualUser=UG.IDManualUser
join oManualGroup G ON UG.IDManualGroup=G.IDManualGroup
)
select
OLAP.Name as Repository,
AO.Name as GroupName,
UG.GroupDescription,
R.Name as Roll,
R.Description,
UG.UserName,
UG.UserDescription
from dAlea OLAP
Join CCombModelRole R on OLAP.IDAlea = R.IDCombModel
Join cCMRoleAuthObj CMR on R.IDCombModelRole = CMR.IDCombModelRole
Join cAuthObj AO on CMR.IDAuthObj = AO.IDAuthObj
Join UserGroups UG on UG.GroupName=AO.Name