Anyway to get Data directly from db

If you are having a problem using Vault, post a message here.

Moderator: SourceGear

Post Reply
jstarbird
Posts: 146
Joined: Wed Jul 22, 2009 11:49 am

Anyway to get Data directly from db

Post by jstarbird » Wed Aug 19, 2009 3:05 pm

Vault Version 5.

We get audited twice a year and part of that is Users access rights within Source Control. I know I can get this by going into each individual User or see a list per repository but is there a way I can extract from the DB a list of all users with all of their access rights listed?
Do you make available the db schema or a means to run reports? History view does provide some of this but not across repositories which would be useful.


Thanks,
J

jeremy_sg
Posts: 1821
Joined: Thu Dec 18, 2003 11:39 am
Location: Sourcegear
Contact:

Re: Anyway to get Data directly from db

Post by jeremy_sg » Thu Aug 20, 2009 7:16 am

Please email support at sourcegear.com ATTN: jeremy with a link to this thread, and I can send you a SQL query to get this information.
Subscribe to the Fortress/Vault blog

IrishManInUSA
Posts: 6
Joined: Fri Aug 21, 2009 7:06 am

Re: Anyway to get Data directly from db

Post by IrishManInUSA » Fri Aug 21, 2009 7:36 am

I was wondering if it would be possible to get this query as well, since I am in the same boat.

jeremy_sg
Posts: 1821
Joined: Thu Dec 18, 2003 11:39 am
Location: Sourcegear
Contact:

Re: Anyway to get Data directly from db

Post by jeremy_sg » Fri Aug 21, 2009 8:13 am

The query I provided jstarbird only gave repository access permissions. He needed Folder Security information as well, which is a more complicated query. I plan to put out a power toy to print that information in the next couple of weeks. If you only need repository access, I can provide that now, but if you need more, you will have to wait for a little while. Is that Ok?
Subscribe to the Fortress/Vault blog

IrishManInUSA
Posts: 6
Joined: Fri Aug 21, 2009 7:06 am

Re: Anyway to get Data directly from db

Post by IrishManInUSA » Fri Aug 21, 2009 8:40 am

if you could give me the resposity one that would be great, and looking forward to the power tool.

jeremy_sg
Posts: 1821
Joined: Thu Dec 18, 2003 11:39 am
Location: Sourcegear
Contact:

Re: Anyway to get Data directly from db

Post by jeremy_sg » Fri Aug 21, 2009 8:48 am

Please email support at sourcegear.com ATTN: jeremy with a link to this thread, and I'll send it to you.
Subscribe to the Fortress/Vault blog

muppal
Posts: 22
Joined: Sat Jan 31, 2009 12:18 pm

Re: Anyway to get Data directly from db

Post by muppal » Thu Mar 04, 2010 9:13 am

Hello, I need to report the User Rights/Access for a Corporate Security Audit. I am running Vault 4.1.4. Is there a simple way I can do this?? --muppal

tjruska
Posts: 59
Joined: Tue May 13, 2008 7:35 am
Location: Milwaukee, WI
Contact:

Re: Anyway to get Data directly from db

Post by tjruska » Fri Mar 05, 2010 6:51 am

I also have had this need previously and couldn't wait. So I have developed some SQL that provides this information for v4.1.4. However, I will wait for permission to post it from either Beth or Jeremy as I don't want to violate anything or get them mad in case I need help from them in the future :wink:

Tom

Beth
Posts: 8550
Joined: Wed Jun 21, 2006 8:24 pm
Location: SourceGear
Contact:

Re: Anyway to get Data directly from db

Post by Beth » Mon Mar 08, 2010 8:31 am

The current version of Vault has a power toy to create a security report. The latest power toys are posted here: http://www.sourcegear.com/vault/vaultpowertoys.html. Of course, that doesn't help you with Vault 4.1.4. I would recommend upgrading to Vault 5.0.3.

tjruska: You can post what you wrote. Other users just need to be aware that SourceGear can't make any guarantees on what you wrote, but I'm sure others will greatly appreciate it.
Beth Kieler
SourceGear Technical Support

tjruska
Posts: 59
Joined: Tue May 13, 2008 7:35 am
Location: Milwaukee, WI
Contact:

Re: Anyway to get Data directly from db

Post by tjruska » Tue Mar 09, 2010 7:11 am

Fair enough. Here is the SQL that works for version 4.1.4 with no guarantees but provided to the community as a courtesy and a guide.
Fire up your favorite SQL tool and run the attached SQL. It only reports user and repository access with access rights. I didn't drill down to folder level as our auditors are satisfied with this top-level report.
Here is a sample of what the output looks like:

Code: Select all

ID      Repository   Rights										          Status
admin	Admin-Test	READ & CHECKOUT/CHECKIN & ADD/RENAME/DELETE	ACTIVE
v4 Vault security report.zip
v4 Vault security report
(706 Bytes) Downloaded 99 times
Enjoy, Tom

Beth
Posts: 8550
Joined: Wed Jun 21, 2006 8:24 pm
Location: SourceGear
Contact:

Re: Anyway to get Data directly from db

Post by Beth » Wed Mar 10, 2010 11:42 am

Thanks for the update.
Beth Kieler
SourceGear Technical Support

Post Reply