How to remove Vault Repository Access

[btd]

I am using Version 5.1.1.19215 so its browser based.

I had a user retire from the company, so I made the user inactive.
However, the user still shows up on our Vault Report that shows this user having access to repositories prior to retiring.

So I used the Admin tool (web based) and drilled down to each Source Conrol Repository that this user had access to. I removed any Repository Access and Folder Security. When I ran the report again, it still shows the this user having access.

What am I doing wrong or what steps do I need to do to remove all access to all respositories for this retired user?

Thanks
BTD

[lbauer]

A user who is inactive cannot log in, so that would be the first line of defense. You could also change the password of the inactive user.

Are you using the Security Reporter Power toy for your Vault Report? If so, I'll investigate to see why it might be reporting old information.

[btd]

Yes, an inactive user can't login. But even after I remove access to all repositories and folders, my Vault Access Reporting Services report still shows this user as having access.

I am not familiar with the Security Reporter Power toy or how it works.

Is there some kind of delay in processing?

[lbauer]

If you look at an overview of the user's rights in the Vault Admin Web Client, are settings changed?

You could try restarting IIS, to flush the Server in-memory cache.

Vault Access Reporting Services report

Is this a custom query you've written?

[btd]

I figured it out. Thanks for your help.
When I clieck Overview and scrolled all the way down, I see where the access rights were still lingering.

Thanks for your help.

BTD

[lbauer]

Thanks for the update. That's good news. : )

[btd]

I thought doing what I said (making sure each user's Overview's -->Folder Security Rights Assignment is empty)fixed the problem, but after a closer look, I see where I have three DIFFERENT inactive users, where they show up on the report. Two of three users show (N N N) and one user shows Y for Read access (karajagiaa) but accordign to the Vault GUI, karajagiaa's Overview's -->Folder Security Rights Assignment is empty!

Here is the SQL.

SELECT
DISTINCT sgmaster..users.name,
tblrepositories.name AS Repository,
CASE WHEN tblsecurityassignments.securityrights = 1 OR
tblsecurityassignments.securityrights = 3 OR
tblsecurityassignments.securityrights = 7 THEN 'Y' ELSE 'N' END AS ReadRights,
CASE WHEN tblsecurityassignments.securityrights = 3 OR
tblsecurityassignments.securityrights = 7 THEN 'Y' ELSE 'N' END AS CheckOutInRights,
CASE WHEN tblsecurityassignments.securityrights = 7 THEN 'Y' ELSE 'N' END AS AddRemoveDeleteRights
FROM tblsecurityassignments INNER JOIN
sgmaster..users ON tblsecurityassignments.userid = sgmaster..users.userid INNER JOIN
tblrepositories ON tblsecurityassignments.repid = tblrepositories.repid
WHERE sgmaster..users.active = 0 AND (tblsecurityassignments.securityrights >= 0)
ORDER BY sgmaster..users.name, Repository


Can you look at the SQL and tell me what I am doing wrong if anything.
Oh yeah, I did reset IIS on the web server where Vault was runnign and it did not help.

Inactive User alshamif still shows up on my report like this
user name Repository Name R Check in/Out Add/Remove/Delete
======= ============= = ======== =============
alshami Extranet N N N
gallawayge CRM N N N
karajagiaa CRM Y N N


Note: All three users are inactive. And when I click on "overview" for each user and scroll all the way down to the bottom, Folder Security Rights Assignment is empty.

Please advise.

Thansk

Tom

So I am stumped.

[lbauer]

Could you send me screenshots of karajagiaa's Overview page?

It could be there is a combination of rights that that cause this behavior. Could be a bug.

Send the image(s) to support at sourcegear.com, Attn: Linda. Please include a link to this forum post.

[btd]

Sorry for the delay.
We went ahead and changed the underlying SQL and it works now.

But I now have a different question.
In the sgmaster..users table, fot the admin row, the value of sgmaster..users.securityrights = 7!

So... I launched the admin tool, and added myself to the "admin" group, but my sgmaster..users.securityrights = 0. How do I get this value to change. Or rather, how can I query the Vault database for "admin's?


Thanks
BTD

[lbauer]

I believe the security rights column in sgmaster are for default rights, and don't reflect what group you're in.

A person with R default rights would be 1.
RC rights would be 3
RCA rights would be 7.

I'm not sure why you're getting a 0, though. Are your default rights None?

[btd]

Yes my default rights are NONE and I am assigned to Global:Admin.
Yet when I run theis query, the value of sgmaster..users.securityrights =0.
For the "admin" account, the valu of sgmaster..users.securityrights = 7


SELECT
sgmaster..users.securityrights
FROM sgmaster..users
WHERE (sgmaster..users.name= 'dariatj')


Why is this so?

[lbauer]

The "securityrights" column in sgmaster dbo.users is for default rights, same as the rights you see in the Vault Admin Web Client under Server Settings->Users.

A person with R default rights would be 1.
RC rights would be 3
RCA rights would be 7.

So Admin has RCA default rights and has 7 and you have no default rights, so you have 0. The number 7 does not mean the person is an administrator.

See example, attached.

[btd]

OK then please tell me how I query (SQL) vault to find a list of "Administrators" please? And is there a way in the UI to set users as Adminstrators?

Thanks

BTD

[lbauer]

Here's a query you can run to determine what security rights specific users have for each repository.

First, you would need to determine the repository ID and the user ID by running these queries:

Select * from sgmaster.dbo.users


Select * from sgvault.dbo.tblrepositories

Next, run this query:

Then run the following:
SELECT * FROM sgvault.dbo.ufngetusersecurityrights(<REPID>, <USERID>, -1 )

For security rights, this is a numeric integer ranging from 0 to 7. The
numbers are 1 = Read, 2 = Checkin, 4 = Add. Other numbers in the range
are made from adding multiple numbers together. For instance, Read + Checkin (RC rights) would be 3.

When you run the queries, the security right will come back as type "U"
(user), "G" (group), or "D" (default rights). What this means is the
data for user's assigned right returned from the query originated from a
User, Group, or Default assignment.

To find out what group the user is in, find the usergroupid in the query results. Then look in sgmaster.dbo.groups for the name of the group. The Global Administrator group is groupid "1."

Also, the table sgmaster.dbo.groupmembers will have the groupid and the userid. So any userid that is in groupid 1 is in the global Admin group.

You can assign users to the Admin group from the Vault Admin Web Client. Under Server Settings->Users, click on the Username for user details. In the <user> Details->Security Rights section, you'll see available groups. just select Global: Admin and the ">" to move it to the "Assigned to Groups" box.

[btd]

Thank you very much. I got the SQL written to return all users (inc. the Administrator) who are members of the global:admin group.

Have a nice day.