New post
Question
· 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)1
Log in or sign up to continue
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 0
Mike Northrop · Nov 17, 2017

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 0