Querying the metadata for a list of users and groups

[DonH]

This is probably more a metadata server question. But since there is no metadata group, and the context of what I need to do is the Stored Process server, here is my question.

I need to create a program that queries the metadata to provide a complete list of all the users and their assigned groups. The output data set should contains three variables:

• the group name
• the user display name
• the user metadata id (i.e., the value that is &_metauser in a stored process).

I have found a bunch of examples on support.sas.com that give me the group and display name; or the group name and external login. I have tried to reconcile the various programs to give me a data set with all three of the above variables.

Note that this program will be run on a nightly basis to just extract what groups a user belongs to. I then need to use that in a stored process where I know who the current user is (i.e., &_metauser).

Does anyone know of a way to do this?

[PaulHomes · Accepted solution]

Hi Don,

I think the SAS supplied %MDUEXTR macro should give you everything that you need.  The docs are here and there's a sample here.  The user's memberships are just the direct groups so if you need the indirect nested groups too then there will be a bit of tree walking involved. We had to do something similar in our Metacoda Security Plug-ins but, since we were running as a plug-in inside the SAS Management Console, we did it in Java which made the tree walking, recursion and infinite loop detection a bit simpler.  You might be able to do something similar with PROC FCMP perhaps.

A new metadata group in the communities would get my vote

Cheers

Paul

[DonH]

Thanks Paul. That macro certainly creates a lot of output information. I thiink I have figured out what datasets I need to use.

However I am frankly making a guess at the the value that will become _metauser and would apprreciate your input. I think that I need the value of Name from logins_info (actually a parsed value because the format appears to be be "Login.&_metauser.xx" where xx is a number). I would need to use the ID field to match to the logins table (on objid) to get the keyid field that I can then match to the grpmems table (which appears to be the crosswalk of id of groups/users) on memkeyid. Then I would have to navigate comparably to get the group name.

If you can confirm that the Name field in logins_info is where I will get the value that the stored process will see as _metauser, I'd appreciate it.

And I agree on a metadata forum. I will figure out where/how to suggest that here.

Thanks,

Don

[DonH]

Assuming my assumption about where to get the value that becomes the value of the _metauser macro variable is correct, I think it is simpler that what I described above. Here is a SQL query that I think gives me what I need:

proc sql;

create table metaUser_Groups_Crosswalk as

select scan(logins_info.Name,2,'.') as MetaUser,

        groupmempersons_info.memName as UserName,

        groupmempersons_info.Name as GroupName

from logins_info, logins, groupmempersons_info

where logins_info.id = logins.objid

     and logins.keyid = groupmempersons_info.memid

;

quit;

This does drop users with no external id, but for my current use case that is not an issue.

So Paul, can you comment on whether this looks right to you?

[PaulHomes]

Hi Don,

It looks like we were both replying at around the same time before.

As I mentioned, I would personally use the _METAPERSON value and the Person table.  I don't know how consistent _METAUSER is with regard to formatting and I am not sure how reliable the Login object name will be with respect to the user id in _METAUSER.  Here is some sample code using _METAPERSON and the Person table:

%let _METAPERSON=Bob Baxter; 

proc sql; 
create table work.DirectGroups as 
select p.Name, p.ObjId as PersonId, gmpi.Name as GroupName, gmpi.Id as GroupId 
from work.Person p join work.GroupMemPersons_Info gmpi 
on p.ObjId = gmpi.MemId 
where p.Name="&_METAPERSON" 
; 
quit;

If you do go down the Logins path, with an nightly %MDUEXTR extract, you will want to make sure the person running %MDUEXTR is a user administrator or unrestricted user in order to get all of the Logins.  That is unless you run it in the stored process (might be a bit slow though) and only get the logins that the currently logged in user has access to (including their own).  I'm assuming you don't have any groups in any custom repositories (it's highly unlikely and not recommended) but if you do then you'll need to do some more work to get those too.

Cheers

Paul

[DonH]

Paul,

Thanks for the reply and the followup.

Thanks for the _metaperson reminder. I will check into it. Not sure however that it is an option since, I believe, two users can have the same value. In answer to your other question, this is code that will be run nightly (as part of a job stream that will have the credentials defined) and it is used as the input to a process to build a complex table structure for a work flow process that will be managed by a set of stored processes. So I really need a unique key. But perhaps one of the object id values can be used. Definitely something for me to check into.

And there are no custom repositories and I don't need to get indirect groups. So this should work (I've marked you answer above as correct, BTW).

Also, good point about the variability of _metauser. The current project is Unix, so it is not an issue. I usually handle this by defining a very simple utility macro to handle this, e.g.,:

%macro metaUser;

%lowcase(%sysfunc(coalesceC(%scan(&_metauser,2,\),&_metauser)))

%mend metaUser;

and I simply use %metauser instead of &_metauser.

[PaulHomes]

You shouldn't be able to have 2 users with the same Name, they can only have the same DisplayName (though not recommended). The _METAPERSON macro var is the Name so you should be ok with that.

One test I would do to verify the Login Name/UserId is to see what happens when the Login is edited after it has been created and change the user id (to simulate a typo correction or userid change) and see if the Login Name attribute follows the change too.

Best of luck with the project.

[DonH]

Many thanks Paul. This has been a great exchange and you've given me all the info I will need.

The point about _metaPerson being unique is a nice nugget that I will have to stash away. I have a couple of tools that use _UserName (for descriptive info)  and I suspect that I should be using _metaPerson instead.

[PaulHomes]

Hi Don,

I would suggest using _METAPERSON rather than _METAUSER.  It will save you 1 join and I am not sure if _METAUSER might be a bit variable depending on the credentials that were used to login (e.g. bob, Bob, domain\bob etc).  I am testing a Linux server so can't play around with domain prefixes and case changes but if you are on Windows then you might want to try it and see what values you get for _METAUSER.  The _METAPERSON macro will have the same value as the Name attribute for the Person object in metadata for the logged in user.

Here's a walkthrough with one of my demo users. His identity (Person object) in metadata has a Name attribute value of "Bob Baxter", and no DisplayName attribute value (so Name is used).  His Linux user id is "demobob".  When he runs a stored process _METAPERSON is "Bob Baxter" and _METAUSER is "demobob".  The screenshot below shows all of the groups he is a member of.  He is only a direct member of "Vegas Enterprises: Executives" but is an indirect/implicit member of 4 other groups.

After running %MDUEXTR(libref=work) if I filter the work.Person table where Name="&_METAPERSON"  (i.e. Name="Bob Baxter") I get the record for Bob with an ObjId value of "A5J40VK3.AN000008". 

I take this ObjId value and use it to filter the work.GroupMemPersons_Info table where MemId="A5J40VK3.AN000008".  That will show me which groups he is a direct member of.  In this case I get a single record showing the group with Name="Vegas Enterprises: Executives" and Id="A5J40VK3.A300001J".

If you only need the direct groups then that should do it.  If you need the nested groups too then that's where the fun starts.  Taking the first level group ids as a starting point you'll need to do repeated lookups on the work.GroupMemGroups_Info for each level until you exhaust the nested group memberships.  You'll also need to consider the implicit PUBLIC and SASUSERS groups too.

I hope this helps.

Cheers

Paul