Security Report with SAS-Autocall Macros mdsec**

[FK1]

Hi Everyone,

among other Autocall-Macros, SAS offers a couple of macros, let's call them mdsec-framwork (see: https://go.documentation.sas.com/doc/en/bicdc/9.4/bisecag/n0l1mpdt430djgn1bl1c3euei85w.htm ), which rely on each other to generate datasets with information from the SAS metadata model (see: https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lrmeta/titlepage.htm ), that can be used to create a securtiy report. They are bundeled in the wrapper-macro "mdseecds":

%macro mdsecds (
                folder="/", 
                includesubfolders=YES,
                membertypes="*",
                memberfilter="",
                perms="",
                IdentityTypes="",
                IdentityNames="",
                outdata=work.mdsecds,
                includeTableComponents=YES,
                includeCubeComponents=YES,
                includeSecuredTables=YES
                );

       %let outdata=%str(&outdata);

       %if %quote(&outdata) =  %then %do;
          %put ERROR: OutData dataset name must be specified.;
         %return;
         %end;

       /* Call to the mdsecgo macro  */
       %mdsecgo( 
                folder=&folder,
                includesubfolders=&includesubfolders,
                membertypes=&membertypes,
                memberfilter=&memberfilter,
                objdata=&outdata._objs,
                includeTableComponents=&includeTableComponents,
                includeCubeComponents=&includeCubeComponents,
                includeSecuredTables=&includeSecuredTables
                );

       /* Call to the mdsecgp macro  */
       %mdsecgp( 
                objdata=&outdata._objs,
                permdata=&outdata._permsl,
                Perms=&perms,
                IdentityTypes=&IdentityTypes,
                IdentityNames=&IdentityNames,
                conddata=&outdata._pconds 
                );

      /* Call to the mdsectr macro  */
       %mdsectr( 
                permdata=&outdata._permsl,
                trandata=&outdata._permsw 
                );

      /* Call to the mdsecvw macro  */
       %mdsecvw( 
                objdata=&outdata._objs,
                trandata=&outdata._permsw,
                targdata=&outdata._join  
                );

%mend mdsecds;

Some of the called macros in the wrapper-macro were written back in 2012 for SAS 9.3M2 or even older, eg. 2007 for SAS 9.2.

I presume, as a consequence of the non-existence of the metadata data step functions back in 2007(see: https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lrmeta/p02e3ara4110hcn1dei1tun4smmu.htm ), the implemented method of retrieving metadata was via XML-formatted SAS Open Metadata Interface method calls with PROC METADATA which return XML output that mirrors the (xml)input, except the requested values are filled in. However, the returnd xml had to be "converted" to a SAS dataset by using the XML LIBNAME engine, having defined an XML map beforehand.

My question is as follows:

is there a SAS-coding-version ot the mdsec-framework, in particular of the coding that generates the "permissions data" dataset that lists the URIs of objects in the metadata server, that contains the authorization information that is currently defined for each object, which relies on the metadata data step functions, therefore avoiding the detour coding of xml request ?

(The information is essentially the same as what is displayed on the authorizations tab of the SAS Management Console.)

The reason I am aksing this is, that I get error messages when executing mdsecds like this:

mdsecds (
                folder="/", 
                includesubfolders=YES,
                membertypes="*",
                memberfilter="",
                perms="",
                IdentityTypes="",
                IdentityNames="",
                outdata=work.mdsecds,
                includeTableComponents=YES,
                includeCubeComponents=YES,
                includeSecuredTables=YES
                );

meaning, that I want to retrieve all information by starting on the very top of the metadata tree ("/")

The error i get ( i assume due to the fact that the macros are coded using the xml-way of retrieving the metadata information):

ERROR: IOM call failed because of a data conversion error.
ERROR: Failed to transcode data from U_UTF8_CE to U_LATIN1_CE encoding because it contained characters which are not supported by 
your SAS session encoding.  Please review your encoding= and locale= SAS system options to ensure that they can accommodate the 
data that you want to process.  A portion of the source string, in hex representation is:
NOTE: 1137a6bd8: 3c 47 65 74 4d 65 74 61 64 61 74 61 3e 3c 4d 65 |<GetMetadata><Me|
NOTE: 1137a6be8: 74 61 64 61 74 61 3e 3c 50 68 79 73 69 63 61 6c |tadata><Physical|
ERROR: Some code points did not transcode.

ERROR: Physical file does not exist, /work_p/SAS_workB72C0161002E_macdb001/#LN00062.
ERROR: Encountered during XMLInput parsing at or near line 1, column 1.
ERROR: The definition for the "COLUMNS" table is not well-formed or is corrupt.

Is there someone out there, who might be able to help?

Thank's a lot!

FK1

[_Dan_]

Your issue is that you're using a (likely) LATIN1 SASApp against a Metadata Server where its contents contain a UTF8 character. This is likely a formatted 'dash', quotes, £ or % that has been stored in UTF8 encoding.

You need a UTF8 App Server, and this code can help you see some of the characters that are giving you issues:
prxchange('s/[\x20-\x7F]//', -1, name) as UTF
'name' should be the name of the column where the object name is supplied, as it's likely to be an object name, and not a Tree folder that'll have the issue.

[FK1]

"Your issue is that you're using a (likely) LATIN1 SASApp against a Metadata Server where its contents contain a UTF8 character. This is likely a formatted 'dash', quotes, £ or % that has been stored in UTF8 encoding." '>>> You nailed it!
This is exactly the issue I have.

Besides the solution of using you supplied regular expression function, do you know, wheter there is another way of creating a list/data set of authorization settings for a given set of metadata objects?

[_Dan_]

No, sorry, just wanted to provide the fix for the facility that you're trying to use since it does the job, but breaks when using LATIN environments.

[FK1]

Never mind! It definitely helps.

Meanwhile, I found out, that in the macros "mdugfldr" and "mdsecgo" several filename statements exist, which use the filename option statement "encoding="UTF-8" (see: https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/nlsref/n0utx4w7x4exijn1wt7pk0srrxz5.htm ), for example:

   filename _reposmp temp lrecl=1024 encoding="UTF-8" ;

The encoding option documentation site states:

When you read, write, copy, or save data using an external file, SAS transcodes the data from the session encoding to the specified encoding.

So, I guess the issue arises, as the (external) xml request file(s), as well as their populated version which is sent back by PROC METADATA are encoded in utf-8, whereas my session encoding is LATIN1. 

The only thing I might try out, is to modify the statements in the above mentioned macros like this:

filename _reposmp temp lrecl=1024 encoding="wlatin1" ;

[_Dan_]

You could try that, however, whilst the App Server encoding being in LATIN1 would limit the ASCII characters you send/receive through SAS, Metadata is less picky and if someone's using (for example) Visual Analytics and pastes a 'bad' character into the report filename box, Metadata will happily eat that character up as it's running in UTF8.

If you then force the queries to Metadata to transcode to LATIN1, you may observe odd effects, and in fact the data may not populate for any affected row. You'll also get issues with character truncation, multi-byte characters will play havoc with any string functions you try to use, like 'cat' for example. Using cat with the field that contains a multi-byte character would result in the string being created as one byte/character less than it should be, and will simply return an empty observation. You can of course avoid this by forcing the length to be a set figure, and much longer than you'd realistically need.

This is no different to connecting to a UTF8 Oracle database via a LATIN1 SASApp. Despite all of the options available for transcoding and session encoding, ultimately the best option is just to set the Application Server to the right encoding.

[FK1]

True, I absolutely agree with you:
"Despite all of the options available for transcoding and session encoding, ultimately the best option is just to set the Application Server to the right encoding."

However, I'm uncertain about the side-effects that might occure when modifying the encoding of the SASApp-Server-Context.

Do you know: could I change the encoding, by using Deployment Manager?

[_Dan_]

I can't help with that, sorry. I can tell you though that we have 50+ App Servers deployed, three of which are UTF, and interoperability between those two encodings is fine so far.

[jklaverstijn]

It is no small decision to change the encoding for a deployment that is servicing a large population and has many SAS datasets. The impact is considerable. You can however start a separate SAS session in its own ad-hoc encoding. I have run into the same issue, largely due to DI Studio jobs with "Em dashes" in descriptions and user written code. I have taken a laborious detour by creating the datasets in a UTF-8 session, spitting them out into CSV files and scanning them for "illegal" symbols like the Em dash and the curly quotation marks you get from MS Office products. You can then weed them out of your metadata. That may be more of a challenge if your metadata originates in an external source like Active Directory.

What realy bugged me here is that this can easily happen when you use a Windows client (DI Studio, Management Console) but your SAS server is on Linux or Unix. Any XML based interface like the macros you mention can then trip over them when encoding is anything other than UTF-8. One thing to be aware of is that, despite popular belief, WLATIN1 on Windows is not the same as LATIN1 (aka ISO-8859-1) on Linux/Unix. SAS is somewhat lenient towards those differences but you still run into issues as the one you describe.

I highly recommend UTF-8 for new SAS deployments but warn against just flipping the switch on an existing one. You will end up in a world of hurt if you do.

Hope this helps,

- Jan.

[FK1]

Thank you @jklaverstijn  and @_Dan_  for your highly valuable remarks!