Vault Reports

[btd]

Are there any reports out of Vault I can get?

I need to a report of what repositores each user has access to.

Is there any way to get this information?

Thanks

Tom

[ian_sg]

Tom,

There is no report per se, but you can easily grab this information from vault's database. This query will get you a basic list:

Code: Select all

SELECT r.[name] AS repository, u.login, u.[name], ISNULL(u.email,'') as email,
	CASE securityrights & 1 WHEN 1 THEN 'Y' ELSE 'N' END AS ReadRights, 
	CASE securityrights & 2 WHEN 2 THEN 'Y' ELSE 'N' END AS ChangeRights, 
	CASE securityrights & 4 WHEN 4 THEN 'Y' ELSE 'N' END AS AdminRights
FROM tblusers u
	INNER JOIN tbluserrepositories ur ON u.userid = ur.userid
	INNER JOIN tblrepositories r ON ur.repid = r.repid
ORDER BY r.[name], u.login

[btd]

Thanks for SQL.

One more question. If I wanted to add a trigger so to track when a Repository user right was added or changed, which tables are involved?

Thanks

Tom

[ian_sg]

You'd want to watch for inserts, deletes, and updates affecting the securityrights column in tbluserrepositories.

[ian_sg]

Tom,

A colleague who knows the security system better than I has pointed out that these tables are strictly the "default rights." Hold off on using this info, I'll correct it and post back.

Sorry about the misinformation!

[btd]

Will do! Thanks for the heads up.

I'll hold off until I hear from you.

Thanks

Tom

[btd]

Dear Ian:

Did you forget about me yesterday?

I wish to be notified (via triggers) whenever a status gets changed but I need your help in telling me what tables are involved.

Also, on a more general scale, does your compnay have a schema diagram and documentation on the database desgin so I can reference this first?

Thanks

Tom

[ian_sg]

Tom,

There is no documentation of the database schema available for public consumption at this time.

Unfortunately this has turned out to be significantly more involved than I first thought. Because we track security through time (a user may have rights to some versions of files and not others, depending on when the rights were assigned), rights are inherited down the tree, and there are both user and group rights to be considered, querying this information from the database is quite complex.

Based on the way your organization uses Vault you may be able to consider only a subset of those possibilities. If you want to give it a go, the core tabes to consider are tblsecurityassignments and tblsecurityassignmentspaths.

[ian_sg]

This ought to be a better starting point for you to get this working. There's a function, ufngetusersecurityrights that you can run to get a list of a user's rights for a particular repository:

dbo.ufngetusersecurityrights(@repid, @userid, @txid)

@repid identifies the repository, corresponding with tblrepositories.repid
@user identifies the user, corresponding with tblusers.userid
@txid identifies the moment in time, corresponding with tbltransactions.txid. Specifying DEFAULT or -1 for this will give you the current rights.

After talking it over with Jeff, I should also caution you against using triggers to accomplish. There are some security-related triggers that already exist that shouldn't be changed.

Instead, perhaps you can schedule a query that uses ufngetusersecurityrights to accomplish your goal.