0
517
Question Mike Northrop · Nov 16, 2017

SQL Privileges Export Script by User or Role

#Caché

I'm trying to write a method to Export SQL Privileges from an instance by namespace, user, or roles. I've found two classes that might work:

  • ##class(Security.Users).Export
  • ##class(Security.SQLPrivileges).Export

One (Security.Users) exports an XML file and the other (Security.SQLPrivileges) exports an SQL file.

Neither of these options allows me to specify specific users or roles to export so I'll have to use them in tandem with something else. Is there something else that I should be using or do I need to find a way to filter on the backend with regular expressions or the like?

Discussion (6)0
Log in or sign up to continue

Comments

Pete Greskoff · Nov 16, 2017

Security.Users Export method is what you need. It does allow you to specify usernames or roles to export. From the docs:

 classmethod Export(FileName As %String = "UsersExport.xml", ByRef NumExported As %Integer = 0, Usernames As %String = "*", Roles As %String = "*", SQLPrivileges As %Boolean = 0, ByRef NumSQLPrivilegesExported As %Integer)as %Status

This method exports User records to a file in xml format.
Parameters:
Filename - Output file name
NumExported (byref) - Returns number of records exported.
Usernames - Comma separated list of Usernames to export, "*" = All
Roles - Comma separated list of Roles, "*" = All. Export Users containing only these roles
SQLPrivileges - 1/0 flag. If 1, export all SQL Privileges from all namespace on this system that have been directly granted to this Role
NumSQLPrivilegesExported *byref) - Returns number of SQL Privileges and SQL Admin Privilege Set records exported

0
John Murray  Nov 17, 2017 to Mike Northrop

Thanks Mike. Knowing your version lets us confirm that the Export method has the capabilities Pete described. Here's a version-specific link.

0
Mike Northrop  Nov 17, 2017 to Pete Greskoff

Thanks, Pete. I thought of this one too, only this will only return if the user has a Cache account. I need to find users that may only have SQL access also. For example:

d ##class(Security.Users).Export("UsersExport.xml",0,"SupportCenter","*",1,)  --> returns an empty XML file. The user "SupportCenter" doesn't have a Cache user account. Now, if I run it again with a user that has a Cache user account, I get the appropriate information.  

0
John Murray · Nov 16, 2017

What Caché version are you working with?

0