In SAS Viya domains and credentials can be used to connect to external databases. By using domains you do not need to have userid and password in clear text in your SAS program. See SAS Viya sharing credentials for database access for background information and how this can be done using the SAS Environment Manager.
This article will show you how to create a domain and add the corresponding credentials using the sas-viya CLI (Command Line Interface). This domain is then used to connect to an Oracle DBMS.
The following topics are discussed:
- Create a Password Domain
- Add Credentials to the Domain for a group/user
- Use the Domain to access the DBMS
- Other use cases where Domains are used
- Useful to know
To be able to add credentials to a domain you need at least version 1.2.7 of the credentials plugin, this version was provided with the Stable 2023.09 release. To see the version of your current plugin use this command:
$ sas-viya credentials --version
sas-credentials version 1.2.10
To update to the latest version of the plugin you can use this command:
$ sas-viya plugins install --repo SAS credentials
Installing the credentials plugin...
Retrieving source file from the SAS repository...
Plugin credentials v1.2.10 is already installed and up to date. Use --force to overwrite.
Create a Password Domain
First we list the domains that are already defined, the list at your site might be different. Please note some columns have been deleted to make the list more readable here. See Predefined Authentication Domains for a list of predefined domains.
$ sas-viya credentials domains list
ID Description Type
DefaultAuth The default authentication domain for SAS Viya password
EsriAuth Esri authentication credential domain password
EsriPortalAuth Local Esri portal authentication credential domain password
EsriPortalRefreshToken Esri portal OAuth2 refresh credential domain token
kerberos Kerberos credentials captured by logon and cached for delegation GSSCredential
ListDataRedis External Redis server associated with List Data password
For our use case we will create a new password domain to be used for accessing an Oracle DBMS.
$ sas-viya credentials domains create --domain-id OraAuth --type password --description "Provide access to Oracle"
The domain "OraAuth" was created.
The domain is now ready to add credentials to it. Please keep in mind, that domain names are case sensitive. Adding a description, if possible , will help you later, it is "free" documentation.
Add Credentials to the Domain for a group/user
When using "technical users" to access the DBMS, you will typically create a custom group to be used as the identity for which we enter the credentials. In our example the custom group OracleUsers does already exist. In case you need to create a new custom group, the sas-viya identities plugin provides the means to create custom groups and add members to it.
Looking at the help of the sas-viya credentials groups create command we can see that we need the following information:
--domain-id Specifies the unique ID associated with the domain.
--identity-id Specifies the SAS Viya group id to which this credential will be available.
--user Specifies the user to authenticate with the external system.
--password Specifies the password to authenticate with the external system.
In our case the DBMS user/password is STUDENT/Metadata0, so our command looks like this:
$ sas-viya credentials groups create --domain-id OraAuth --identity-id OracleUsers --user STUDENT --password Metadata0
The credential "OracleUsers" was created.
In case you want to add credentials for an individual user use: sas-viya credentials users create using the same options as with the command above. If a user has individual credentials for a domain, they take precedence before any credential available through group membership.
Please note: group memberships are only read once at login time. So for testing purposes you might need to logout and log back in again to have the proper group memberships.
Use the Domain to access the DBMS
First we are going to test the domain and credentials using a LIBNAME statement.
libname aora oracle
path="server.demo.sas.com/ORCL"
authdomain="OraAuth"
;
NOTE: Credential obtained from Viya credentials service.
NOTE: Libref AORA was successfully assigned as follows:
Engine: ORACLE
Physical Name: server.demo.sas.com/ORCL
We can see in the log, that the credentials used to connect to Oracle are read from the credentials service. Please note domain names are case sensitive.
In order to test with which user we are connected we can use the code below. This code works for oracle, for other DBMS you might need to adapt accordingly.
proc sql;
connect using aora as oracle;
select * from connection to oracle (
select user from dual
);
quit;
The result of running the above code is:
User
-------
STUDENT
The same domain can also be used when creating a new caslib pointing to the DBMS. The following command will create a new caslib and use the new domain created.
$ sas-viya cas caslibs create oracle --caslib oralib --server cas-shared-default --schema STUDENT --oracle-path "server.demo.sas.com/ORCL" --authentication-domain OraAuth
The requested caslib "oralib" has been added successfully.
To verify that we can access the caslib and see any data we use this command:
$ sas-viya cas tables list -caslib oralib --server cas-shared-default --all
Name Source Table Name Scope State
CONTACTS CONTACTS None unloaded
ORDERS ORDERS None unloaded
We can use additional groups and credentials to specify different "technical users" to access the DBMS. In some cases each user might have its individual credentials to access the DBMS. For this case, the individual user can use the Credentials page in the SAS Environment Manager to set his credentials.
Other use cases where Domains are used
Accessing a DBMS from a SAS program is not the only use case for domains. Here are examples for other use cases:
Useful to know
Here a few things that might be useful to know.
- Only SAS Administrators can create domains
- All users can add their own credentials to a domain either using SAS Environment Manager or the sas-viya credentials users create command
- If you delete a domain, caslibs using this domain will no longer work. Use this command to get a list of all caslibs, the third column will contain the domain name:
sas-viya --output json cas caslibs list --server cas-shared-default --all --superuser | jq -r '.items[] | "\(.name),\(.type),\(.attributes.authenticationDomain)" '
Summary
As the example has shown, you can use the sas-viya CLI (Command Line Interface) to create a domain and corresponding credentials to access data in a DBMS. So this process can be fully automated using scripts. Beside using a domain to access DBMS data, there are other use cases where domains are used to store credential information. Some of them will provide their own sas-viya CLI to create the necessary domain and credentials.
