Can I allocate an aggregated libref that is partially readonly using metadata?

[ScottBass]

Hi,

I'm pretty sure the answer to this is "No", but thought I'd check anyway.

In our development (Lev2) environment, we have an aggregated libref to particular directories.  For example:

libname foo ("C:\Temp\test" "C:\Temp\prod");

I'd like the prod directory to be readonly in this scenario.  (Yes, an accident occurred recently, and some production files got overwritten when a development job was tested using the wrong userid).

All our libraries are pre-assigned in our metadata, and this affects both EG, DMS, and batch processing (-metaautoresources "SASApp").

In base SAS, I can "trick" SAS into allocating a portion of the aggregated libref as readonly with the following approach:

* create a dataset in prod ;

libname foo "C:\Temp\prod";

data foo.test;prod=1;run;

* allocate an aggregated libref, where the prod library is readonly ;

libname tmp "C:\Temp\prod" access=readonly;

libname foo ("C:\Temp\test" tmp);

libname tmp;

* check this:  foo is still an aggregated libref even after tmp is deallocated ;

libname foo list;

* create datasets in test (since prod is readonly) ;

data foo.test;set foo.test;test=2;run;

data foo.test2;x=1;run;

* check the results ;

libname prod "C:\Temp\prod";

libname test "C:\Temp\test";

* now execute this, then check foo.test again.  this time it will be the prod version ;

proc delete data=test.test;run;

Can this be done using metadata?

[A nice enhancement to base SAS would be the ability to add library options directly in the aggregated allocation.  For example, something like:

libname foo ( ("C:\Temp\test" compress=yes) ("C:\Temp\prod" access=readonly) );

or something like this mythical syntax.]

[Patrick]

I can think of 2 ways to go.

A. Secure the readonly folder on OS level.

B. Define libref "foo" in metadata, then do what you do in Base SAS in the autoexec, so "libname tmp '...' readonly; libname foo (foo tmp); libname tmp;"

For approach B:

I'm not 100% sure which autoexec gets executed when using "register tables" in SMC or DIS. So eventually when doing this you will only get the tables which are stored in the "foo" folder under the path as defined in Metadata.

By the way: I believe the terminology is "concatenated libraries" and not "aggregated libraries".

[ScottBass]

Thanks Patrick.

A.  Well I thought we'd done that, but a new service account that was an administrator submitted jobs in batch (testing from a scheduler) that incorrectly overwrote production datasets.  My intent is to add an additional fail safe to the mix.  (Yes, we can fiddle with the permissions on the libraries, but it gets a bit messy.  The additional fail safe would be useful.)

B.  I need the process to be dynamic, so I wrote this code:

%let options=%sysfunc(getoption(notes)) %sysfunc(getoption(source)) %sysfunc(getoption(nosource));

options nonotes nosource nosource2;

proc sql noprint;

  create table work._concat_libs_ as

    select libname, path, engine, level

  from

    dictionary.libnames

  where

    level > 0 and libname ne "SASHELP"

  ;

run;

filename temp temp;

data _null_;

  set work._concat_libs_;

  file temp;

  by libname notsorted;

  if (level eq 1) then

    put "libname tmp" level '"' path +(-1) '";';

  else

    put "libname tmp" level '"' path +(-1) '" access=readonly;';

  if last.libname then do;

    put "libname " libname engine "(" @;

    do i=1 to level;

      put "tmp" i @;

    end;

    put +(-1) ");";

    do i=1 to level;

      put "libname tmp" i "clear;";

    end;

    put;

  end;

run;

proc delete data=work._concat_libs_;

run;

%include temp / nosource nosource2;

filename temp;

options &options;

However, I'm running into a timing issue that I can't seem to fix.  The order of initialization seems to be:

1. autoexec
2. initstmt
3. metaautoresources

If there is a way I can force the metadata library allocation (pre-assigned) to occur before either the autoexec or an initstmt, please let me know.

[Patrick]

Hi Scott

I've overwritten/extended library definitions from metadata via autoexec already in several implementations so your findings about the initialization sequence worried me a bit. I tried to find some documentation about the sequence but couldn't really find "the one". What's documented under "metaautoresources" is kind of confusing for me but there is a clear example under SAS(R) 9.3 Intelligence Platform: Data Administration Guide, Second Edition, section: "Pre-assigning Libraries in an Autoexec File".

I believe the sequence is:

1. metaautoresources

2. autoexec

3. initstmt

I didn't fully dive into your code so can't tell you what might go wrong. What I've done as a test is to define in an autoexec a libref like "libname test (lib1 lib2);" with "lib1" and "lib2" defined in metadata as pre-assigned libraries. This worked so I would assume that in this case "metaautoresources" executed before the autoexec (and this is also in-line with the docu link posted above).

Another thought:

You could also lock down the access via Metadata. I believe what could work is to have a single concatenated library defined in Metadata but then for example the tables separated into 2 metadata folders. For the tables in the more restricted folder simply deny write/create/delete.

If you want even more control and safety then you could consider using a Metadata bound library and tables SAS(R) 9.3 Guide to Metadata-Bound Libraries

As I understand it this basically uses the good-old password protection for SAS tables but then stores these passwords in metadata. In doing so no one will be able to simply define another libname in code and then access your tables in a way you don't want to.

Cheers

Patrick

[ScottBass]

Hi Patrick and Tom,

Thanks for the replies.  Much appreciated.  Let me also take this opportunity to publicly thank you for your contributions to this forum.  I always learn a lot from your posts.

We have a pretty standard managed environment/planned deployment, i.e. Lev1, Lev2, and Lev9 (test of metadata security).

In Lev2, we have a number of concatenated libraries defined in metadata, as "test", then "prod".  All our libraries are pre-assigned, so they get allocated whether from EG, Base SAS (DMS), or batch jobs.

In E:\SAS\Config\Lev2\SASApp\appserver_autoexec_usermods.sas:

%put AUTOEXEC;

libname usr          "R:\tier0\usr\&sysuserid";

libname usrspde spde "R:\tier0\usr\&sysuserid";  * wish we could use a macro variable in metadata defined libraries ;

In E:\SAS\Config\Lev2\SASApp\sasv9_usermods.cfg:

/* Run initstmt to reallocate concatenated libraries. */

-initstmt '%put INITSTMT;%realloc_concat_libs;'

I've defined the previously posted library reallocation code as a macro for easier inclusion in an initstmt.

Here is a partial log when I launch E:\SAS\Config\Lev2\SASApp\sas.bat:

NOTE: AUTOEXEC processing beginning; file is E:\SAS\Config\Lev2\SASApp\appserver_autoexec.sas.

AUTOEXEC

NOTE: Libref USR was successfully assigned as follows:

      Engine:        V9

      Physical Name: R:\tier0\usr\sbass

DEBUG=CACHE_DISABLE option set.

NOTE: Libref USRSPDE was successfully assigned as follows:

      Engine:        SPDE

      Physical Name: R:\tier0\usr\sbass\

NOTE: AUTOEXEC processing completed.

INITSTMT  (>>> No changes to the library allocations since the metadata library assignment has not taken place yet <<<)

NOTE: Libref claims successfully assigned from logical server.

...

NOTE: Libref clminfo successfully assigned from logical server.

NOTE: Libref SASDATA successfully assigned from logical server.

(End of library allocations from metadata.  About 30 librefs are defined.)

I then submitted this from DMS:

1    libname claims list;

NOTE: Libref=   CLAIMS

      Scope=    DMS Process

      Levels=   2

        -Level 1-

      Engine=   BASE

      Physical Name= S:\tier2\test\claims

      Filename= S:\tier2\test\claims

        -Level 2-

      Engine=   BASE

      Physical Name= S:\tier2\prod\claims

      Filename= S:\tier2\prod\claims

Now I explicitly call the macro that was in the INITSTMT.  options nonotes nosource nosource2 nomprint, so no visible changes in the log.

2    %realloc_concat_libs;

And now the libraries have been reallocated, but the *source* of the library reallocation was from the metadata.  IOW

129  libname claims list;

NOTE: Libref=   CLAIMS

      Scope=    DMS Process

      Levels=   2

        -Level 1-

      Engine=   V9

      Physical Name= S:\tier2\test\claims

      Filename= S:\tier2\test\claims

        -Level 2-

      Engine=   V9

      Access=   READONLY

      Physical Name= S:\tier2\prod\claims

      Filename= S:\tier2\prod\claims

So, it appears to me that the order of initialization is autoexec, initstmt, metaautoresources.  I certainly wish it was metaautoresources, autoexec, initstmt, since I have more control with good-old handwritten SAS code (i.e. autoexec).

I know we can tighten the NTFS permissions, and that may solve the issue.  But, I'd like to put as many fail-safe measures in as possible to ensure that our production libraries are safe from being accidentally overwritten.  I'll investigate the links you posted to see if there are additional steps I can take.

(BTW, the problem occurred when we were testing a new scheduler, and the service account had been created incorrectly - too high privileges, which bypassed the NTFS permissions on the libraries.  Additional fail safes within SAS would have prevented the incorrect overwriting of the production data.)

Thanks,

Scott

[Patrick]

Hi Scott

I've done some more testing on my Win7 SAS9.3 environment and things worked for me as expected.

You could try and run the following as this might help you in narrowing down where to look for the issue.

-initstmt '%put you have used the initstmt; libname mywork (work); proc sql; create table mywork.libs as select * from dictionary.libnames; quit;'

I've used this command in my environment under "...Lev1\SASApp\sasv9_usermods.cfg" and got all pre-assigned libraries.

I needed "libname mywork (work);" to be able to see the result in EG - it appears EG (or the object spawner?) "does something" with "work" as when running running a Proc Contents out of EG the paths for "work" and "mywork" differ.

When running out of PC SAS the paths are the same.

[Tom]

Sounds like you are trying to re-create Tom Hoffman's reporting environments.

What we did for those was to use separate librefs for write access versus read access.

libname foo ('dev' 'prod') access=readonly;

libname wfoo ('dev');