Desktop productivity for business analysts and programmers

passing private username and password from sas enterprise guide to oracle db

Reply
Occasional Contributor
Posts: 5

passing private username and password from sas enterprise guide to oracle db

Hi

SAS enterprise guide 5.1 is used by multiple users in our organization.  Currently all of them connect to ORACLE db via SAS with common connection (general user).

For security reasons we want to change that so every user of SAS will connect to the database with a personal db user created for him.

We don't want to duplicate objects in the SAS guide.

How can we pass as parameters the user and password for connection to the DB so it is different for each user?

PROC Star
Posts: 1,291

Re: passing private username and password from sas enterprise guide to oracle db

Occasional Contributor
Posts: 5

Re: passing private username and password from sas enterprise guide to oracle db

Hi,

Can any or all of these options work when working in the GUI environment?

(Users do not write scripts, just use direct access to tables from ORACLE exposed to the by the SAS admin in the GUI)

Valued Guide
Posts: 3,208

Re: passing private username and password from sas enterprise guide to oracle db

Yes they can work. The prerequisite is it should be supported by the SAS admin. He is the one responsible for the technical part (scripts instructions) and for the compliance part that are your mentioned requirements.

Would you say the SAS admin is the blocking person for not being able to solve that.  The waiver note for non-compliance?   

---->-- ja karman --<-----
Occasional Contributor
Posts: 5

Re: passing private username and password from sas enterprise guide to oracle db

Yes, The SAS admin refuses to adopt approach of using private oracle usernames claiming it will lead him to multiple configurations, and much more work everyday.

I am from the DB & security side of the organization and not familiar with all the SAS admin functionality,

The best thing would be if I can receive from experts here a link to a specific post/document describing exactly how to implement this in the easiest  way (supported by SAS EG 5.1), so I can sit with the Admin and help him implement this.

thank you very much for the help!

Respected Advisor
Posts: 4,137

Re: passing private username and password from sas enterprise guide to oracle db

In most organisations I've worked for so far the DBA's normally don't want to have an additional layer of user management and ask for access via generic users. You're obviously taking the opposite approach with individual users. This will add additional admin effort both for the Oracle and SAS Admin.

Here the links you've asked for:

SAS(R) 9.3 Intelligence Platform: Data Administration Guide, Second Edition

SAS(R) 9.3 Intelligence Platform: Security Administration Guide

From SAS(R) 9.3 Intelligence Platform: Security Administration Guide

In the server's authentication provider, identify or create accounts. Use any of the following approaches (here, Oracle is used as an example):

1 Create an individual Oracle account for each user. This provides the greatest accountability, but can also necessitate storing many Oracle user IDs and passwords in the metadata.

2 Create one Oracle account that all users will share. This greatly reduces the need to store Oracle user IDs and passwords, but also results in a loss of individual accountability.

3 Create a few Oracle accounts, each of which will be shared by several users. This middle-of-the-road approach enables you to make some access distinctions in Oracle and store only a few Oracle user IDs and passwords in the metadata.

I personally would consider the 2nd approach as I would assume that you'll have only a very limited number of different security/data access profiles. In such a setting users get access to a specific authentication domain via group membership - something you can centrally manage withing AD/LDAP and then synch to SAS metadata. To define the different auth domains in SAS metadata is this way a one-off task for the SAS Admin.

Valued Guide
Posts: 3,208

Re: passing private username and password from sas enterprise guide to oracle db

I have commented in that post. There are a lot of possible ways but just hardcoding that pwencode hash is a fake one (no 5).

If your libname statement is in your EG project (parallel code option disabled) you can use the EGuide prompts to set a macrovariable during the EG session.

When you are doing the Oracle libnames at startup and metadata bound (no batch access) you could use the metadata store.
Be prepared for questions of auditors wanting to know where the SMC stores passwords. It is the login table found in the metadata OS folder.

Needing a dedicated macro to isolate all that at each person personal OS folder, just give a note (...)

---->-- ja karman --<-----
Trusted Advisor
Posts: 2,114

Re: passing private username and password from sas enterprise guide to oracle db

If you don't have a metadata store, the macro approach I described on Chris' blog works well.  The SYMBOLGEN can't disclose the passwords by another user because the person running the program has to have access to the plain text passwords. It is also easy to explain to auditors.

Valued Guide
Posts: 3,208

Re: passing private username and password from sas enterprise guide to oracle db

Doc@duke, Yep same idea on my site isolation passwords in the OS user part. Is an easy underpinning.
Trusting that OS security is a common known practice. Put all the data in an encrypted SAS datasets and using the pwencode hash will overcome accidental browsing by admin people (eg backup/restore). In this case the coded read password is essential a salt not a password.   Accessing seeing something you are responsible for, is not the biggest problem, accessing using somebodies else or faked is the issue.  
It is the encryption of data on storage, getting more in the news. With 9.4 data libraries can get AES encrypted SAS(R) 9.4 Language Reference: Concepts, Second Edition (what is new)          

---->-- ja karman --<-----
Respected Advisor
Posts: 4,137

Re: passing private username and password from sas enterprise guide to oracle db

You need SAS/Secure licensed for any encryption. Without this module you can only encode passwords.

Valued Guide
Posts: 3,208

Re: passing private username and password from sas enterprise guide to oracle db

Sorry for hitting into that issue, possible some personal reconition.
It is the DB Oracle person getting all the work as he is the one adding all those users in his system and needed to be synchronized. The advantage for him is really getting to know the users, probably arlready knowing them when it are developers using SQLdeveloper.

Authdomain
The disadvantages are already mentioned. But the documentation is full done in SAS manuals and papers.
usage: SAS/ACCESS(R) 9.3 for Relational Databases: Reference, Second Edition (AUTHDOMAIN= LIBNAME Option)

smc to define: SAS(R) 9.3 Management Console: Guide to Users and Permissions (Adding a domain)

bisecag concepts: SAS(R) 9.3 Intelligence Platform: Security Administration Guide (Authentication Domains)
The final issue in this is the instruction how endusers can update their oracle-key / pswd.

They can do that using SMC or the personal login manager 9.3 (SAS(R) 9.3 Intelligence Platform: Security Administration Guide (what is new). That are java applications and having zero impact on Windows. (copy paste deploy possible). 

Eguide defining an encoded pw
Eguide is supporting prompts you could define a prompt and using a masked text.
What will happen is a sas-macro variable is defined (persistent during the session) containing the pwencode hash.

xkeypsw.jpg  

This one is using the sas002 (md5 and reversible at run time).

The libname nowcan be:

   libname <yourname> oracle schema=<yourschema> user=&sysuser psw=&wwsas_PASSW  ...  ;

This libname cannot be predefinied as only after the SAS-prompt in Eguide those value of the password will be known.

It will work fine in sas code using the explicit pass through method as those connections are setup at the moment of processing.

Advanced users are of the using that method of explicit pass through.

Using a macro and a pre-defined library.

Having that macro-variable and a EG project you can also define a user dataset (simple text-file or encrypted sas dataset).

The change in the libname is using a SAS macro for getting the text in. ( I think a %include of a text file will also do).

   libname <yourname> oracle schema=<yourschema> %xkeypsw defer=yes  ;  

That macro must become general available for the users at the SASapp level as autocall setting. (sas configuration usermods). That should be no big problem. It  does nothing when not used. Only being called when referenced.

There is nothing personal in the generic settings as the goal is solving that in the macro.
I used the &sysuser assuming that the personal user-key is kept the same for each users in all your systems. Each user-key is identifying to a  unique living being.  With this one the libname can be predefined again and would advice the external predefined approach so you can test that code with full logging on some impact.

The name xkeypsw is my personal name must be easy to find with google (full source),

By the way
-   I found EGuide having problems with encrypted SAS datasets. 
    It is corrupting those datasets when adding records (losing defined password as salt).     

    When you want to use encrypted datasets with Eguide that way.. be aware.

---->-- ja karman --<-----
Valued Guide
Posts: 3,208

Re: passing private username and password from sas enterprise guide to oracle db

There is a reason why risk managers are pushing the usage individual account. It is risk management and regulations. An example of what that is meaning can be read in https://www.cms.gov/Research-Statistics-Data-and-Systems/CMS-Information-Technology/InformationSecur.... This a detailed one sharing the fundamentals. The same ones can be found for financials.

Other technical processes that are related to that are RBAC Role-based access control - Wikipedia, the free encyclopedia not to be confused with some technical tools carrying the same name. There are many dogs called max. The requirement on auditing process with doing analytics on logs is also something to do. Sometimes to be found as SIEM (Security Information Event Monitoring). This big data analytics fields is getting conquered by tools like splunk (no not SAS).

The resistance of IT people and suppliers to do something with this guidelines coming from regulators is strange.

We like to compare IT with automotive world. Would you trust a car salesman knowing nothing about what to fulfill to drive a car on a public road? 

---->-- ja karman --<-----
Valued Guide
Posts: 3,208

Re: passing private username and password from sas enterprise guide to oracle db

The sas001 (base64) and sas002 (md5) both work without sas/Secure.

The sas003 is requiring sas/Secure using aes.

Encryption of sas datasets in propriety mode work without sas/Secure.  This has a long time available option. Sometimes it was used to avoid implementing real os security. Then the password got lost.... (real experienced)

Starting with sas 9.4 sas/Secure is included and not a separate license anymore.

The Libname statement at 9.4 is enhanced to support certificates with encryption. That is data (only sas datasets) can be encrypted on disk using that.

All documented.

Any links proving this, my list, is wrong?

---->-- ja karman --<-----
Ask a Question
Discussion stats
  • 12 replies
  • 2415 views
  • 0 likes
  • 5 in conversation