How can I use same Auth domain for different DB schemas

[VLanka]

Hi experts, I am struggling to understand how SAS metadata deals with AuthDomains. Any help is appreciated. 

SAS version - 9.4M3.

We have an Oracle DB with 5 schemas and some generic code that is supposed to execute against different schemas. 

All DB schemas have different passwords. 

To deal with multiple schemas, 

a) unix shell script passes oracle DB schema name to sas

b) autoexec.sas reads parameters and dynamically assigns Oracle libraries pointing to appropriate oracle schema.

    The logic roughly looks like 

          libname ora user=&param_schema schema=&param_schema password=&param_schema_password path=xxx;     

    when &param_schema =schema1, the above statement resolves to 

          libname ora user=schema1 schema=schema1 password=schema1_password path=xxx;     

c) generic sas code runs using the libname assigned by autoexec. 

This works fine so far. 

To avoid defining schema passwords in macro variables, 

(d) I created an authdomain OraAuth and added 5 userid/passwords to it, corresponding to 5 DB schemas. 

(e) Changed autoexec.sas to 

          libname ora user=&param_schema schema=&param_schema authdomain=OraAuth path=xxx;     

At runtime, 

when &param_schema =schema1, I was expecting the above statement fetching schema1_password from OraAuth and define the libname properly. 

when &param_schema =schema2, I was expecting the above statement fetching schema2_password from OraAuth and define the libname properly. 

However, in reality, no matter which DB user/schema combination is used in the libname statement, OraAuth is only resolving schema1/schema1_password. 

Is the above approach correct and valid ? SAS metadata obviously allowed multiple DB logins attached to the same OraAuth. Why doesn't it fetch the expected login object using the DB user/schema details from libname ?

[Sajid01]

I suggest creating separate authdomian for each schema. 

[gwootton]

You would need to create a different authentication domain for each schema if there are different credentials for each. When SAS requests a credential from metadata for an authdomain there is no way for it to specify which credential it wants, so it returns the first one it finds if there is more than one a given user has access to.

[VLanka]

Thanks @gwootton , @Sajid01, I will most likely end up doing this. 

However,  I am still curious, if SAS allows creating multiple logins per Authdomain, why would it select only the first credential for DB logins Authdomain? 

Is this a known bug/limitation of SAS ? 

For DefaultAuth, it does lookup the metauser against the user in all logins under DefaultAuth, fetches relevant credentials for the metauser. 

[Sajid01]

My understanding is that this is how SAS works.

[VLanka]

Thanks sajid

[gwootton · Accepted solution]

This is by design.

When you use an authentication domain for outbound authentication, SAS calls the Metadata Server and says "give me the login and password for the authentication domain X."

If you have access to multiple, like one in your user and one in a group you are a member of, or you are a member of multiple groups, this does not change the question to the Metadata Server. The request made is for the first result, and that is what the Metadata Server returns.

DefaultAuth is the default authentication domain, but it behaves the same as any other. In most cases you only store the user ID for DefaultAuth, not the password. When you log in to the Metadata Server (in most cases) it takes the credentials you supply and passes them to the host OS for authentication. The host will either respond with authenticated or not authenticated. If it responds as authenticated, then the Metadata Server will look up the user ID supplied against its defined users to see if it exists in Metadata (under any authentication domain not defined as "outbound only"). It is not possible to define the same user ID for multiple identities for any authentication domain not defined as "outbound only". If the user ID used is found in Metadata, you are logged in as that Metadata identity. If it does not find any login defined you are still authenticated but as the identity PUBLIC which has limited access by default to Metadata.
Were you to store a password under DefaultAuth and use it as an outbound authentication domain the behavior would be the same as any other authentication domain.