topic Re: Get authorization level from library tables in SAS Enterprise Guide

Get authorization level from library tables

Jose6 — Sat, 07 Sep 2019 01:07:13 GMT

Hi Experts,

How can i get the names of the tables from a library and the authorization group level that each table has ?

for example i have the "customer" table and this table can only be read by "group 1" and other table can only be read by "group 2" ......

Re: Get authorization level from library tables

Patrick — Sat, 07 Sep 2019 01:42:01 GMT

"How can i get the names of the tables from a library...."

%let lref=sashelp;
proc sql;
/*  create table want as*/
  select *
  from dictionary.tables
  where libname="%upcase(&lref)"
  order by memname
  ;
quit;

"....and the authorization group level that each table has"

That depends.

- If it's physical SAS tables then security is applied on OS level on folder and files and you would have to query the OS plus eventually AD/LDAP to retrieve such information. That could become quite intense.

- If it's a database then you need to retrieve this information from some database system tables

- If it's SAS Metadata then you also need to query the SAS Metadata Repository as for such tables who gets access can be a combination of SAS Metadata and OS level or database security.

If you're not an admin then also be aware that your user might not have the necessary privileges to retrieve such security information from the OS/LDAP/AD or a database.

Re: Get authorization level from library tables

Jose6 — Tue, 10 Sep 2019 01:20:53 GMT

Thanks Patrick, in this case i want the authorization level assigned in metadata by management console for each table.

A few days ago i found this query to obtain the user en its group level and now instead of the user i want the tables

data users_grps;

/* The LENGTH statement defines variables for function arguments and
assigns the maximum length of each variable. */

length uri name dispname group groupuri $256
id MDUpdate $20;

/* The CALL MISSING routine initializes output variables to missing values.*/

n=1;
call missing(uri, name, dispname, group, groupuri, id, MDUpdate);

/* The METADATA_GETNOBJ function specifies to get the Person objects
in the repository. The n argument specifies to get the first Person object that is
returned. The uri argument will return the actual uri of the Person object that
is returned. The program prints an informational message if no Person objects
are found. */

nobj=metadata_getnobj("omsobj:Person?@Id contains '.'",n,uri);
if nobj=0 then put 'No Persons available.';

/* The DO statement specifies a group of statements to be executed as a unit
for the Person object that is returned by METADATA_GETNOBJ. The METADATA_GETATTR
function gets the values of the object's Name and DisplayName attributes. */

else do while (nobj > 0);
rc=metadata_getattr(uri, "Name", Name);
rc=metadata_getattr(uri, "DisplayName", DispName);

/* The METADATA_GETNASN function gets objects associated via the IdentityGroups
association. The a argument specifies to return the first associated object for
that association type. The URI of the associated object is returned in the
groupuri variable. */

a=1;
grpassn=metadata_getnasn(uri,"IdentityGroups",a,groupuri);

/* If a person does not belong to any groups, set their group
variable to 'No groups' and output their name. */

if grpassn in (-3,-4) then do;
group="No groups";
output;
end;

/* If the person belongs to many groups, loop through the list
and retrieve the Name and MetadataUpdated attributes of each group,
outputting each on a separate record. */

else do while (grpassn > 0);
rc2=metadata_getattr(groupuri, "Name", group);
rc=metadata_getattr(groupuri, "MetadataUpdated", MDUpdate);
a+1;
output;
grpassn=metadata_getnasn(uri,"IdentityGroups",a,groupuri);
end;

/* Retrieve the next person's information */

n+1;
nobj=metadata_getnobj("omsobj:Person?@Id contains '.'",n,uri);
end;

/* The KEEP statement specifies the variables to include in the output data set. */

keep name dispname MDUpdate group;
run;

/* Display the list of users and their groups */
proc report data=users_grps nowd headline headskip;
columns name dispname group MDUpdate;
define name / order 'User Name' format=$30.;
define dispname / order 'Display Name' format=$30.;
define group / order 'Group' format=$30.;
define MDUpdate / display 'Updated' format=$20.;
break after name / skip;
run;

Re: Get authorization level from library tables

Patrick — Tue, 10 Sep 2019 03:38:30 GMT

I don't have ready-made code available for what you're after but the SAS provided %mdsecds() macro should give you a good starting point.

https://go.documentation.sas.com/?docsetId=bisecag&docsetTarget=n0l1mpdt430djgn1bl1c3euei85w.htm&docsetVersion=9.4&locale=en#p1c938it9pojnen1vwjal9cans9z

You are aware that if you don't have a metadata-bound library then querying the metadata alone will only tell you who can access registered table metadata objects. It won't tell you if such users also have the necessary privileges to access the underlying physical data when using the metadata objects nor will it tell you who else can use SAS to access the physical data simply by issuing a libname statement in code.

Re: Get authorization level from library tables

Jose6 — Wed, 11 Sep 2019 00:15:21 GMT

Thks for the link patrick, it's a great help