topic Re: DBPROMPT for SAS Studio in SAS Studio

DBPROMPT for SAS Studio

KalebF — Thu, 14 Dec 2023 20:01:58 GMT

Hello,

My organization mostly uses the old school SAS Windowing Environment for running programs. A lot of these programs that connect to ORACLE DB have a prompt window that allows the user to type in the name and password and then after successfully entered, the program runs.

Is there a solution for SAS Studio to display a window prompt so a user can type in their username / pw to connect to Oracle?

DBPrompt=yes; works for SAS Windowing Environment.

DBPrompt=yes; doesn't work for SAS Studio.

Is there a solution for SAS Studio to also display a window prompt for username and password?

Thanks for any input!

Kaleb

Re: DBPROMPT for SAS Studio

sbxkoenk — Fri, 15 Dec 2023 10:52:29 GMT

Hello,

Are you using SAS Studio to submit code on a SAS 9.4 server?
Or are you using SAS Studio to submit code on a SAS Viya server?

The DBPROMPT= LIBNAME Statement Option
is not applicable to SAS Viya.

Koen

Re: DBPROMPT for SAS Studio

Patrick — Fri, 15 Dec 2023 15:36:07 GMT

The way I've seen that done normally:

DB access is via a functional user
The credentials are stored in a SAS Authdomain (SAS Metadata)
Users use the authdomain instead of the userid/pw in the libname (if not pre-assigned)
The SAS metadata security model needs to be such that only users in defined groups get read access to the authdomain

Normally DBA's prefer this approach as it removes the need for user management/sync with SAS on the DB side.

If you really need individual user credentials with a Studio version where prompts are not available then one option would be for users to create environment variables with their credentials in their .profile.
In SAS code you then could use these environment variables instead of the SAS macro variables from the prompt. Or you could amend the autoexec and look for such environment variables (which will exist and have values set depending on the user who initiates the SAS session) and populates the macro variables as previously the prompt did.

....just giving you some ideas.

Re: DBPROMPT for SAS Studio

KalebF — Fri, 15 Dec 2023 14:37:23 GMT

We are using SAS Studio 9.4 server.

Re: DBPROMPT for SAS Studio

KalebF — Fri, 15 Dec 2023 14:44:44 GMT

Thank you for your post.
The macro variable idea works and what i'm using now. It seems like SAS studio 9.4 isn't able to prompt a user like SAS windowing environment 9.4. Rather, they'd have to enter it into a macro statement. I was hoping for a prompt approach so that when saving programs someone doesn't inadvertently save their passwords in the program.

Re: DBPROMPT for SAS Studio

Patrick — Fri, 15 Dec 2023 14:53:10 GMT

The most common approach is using an authdomain instead of explicit user credentials.

Using an authdomain in SAS 9.4 will also allow for an uncomplicated migration path to SAS Viya and Cloud.
https://blogs.sas.com/content/sgf/2017/08/17/sas-viya-sharing-credentials-for-database-access/

Re: DBPROMPT for SAS Studio

Tom — Fri, 15 Dec 2023 16:47:01 GMT

You should be able to make a TASK in SAS/Studio that allows entry of information. Just be careful as I am not sure if there are ways to make the password secure.

Here is a picture of an example:

Re: DBPROMPT for SAS Studio

sbxkoenk — Fri, 15 Dec 2023 17:06:02 GMT

Good idea from @Tom .

Here's how to do that:

SAS® Studio 5.2: Developer’s Guide to Writing Custom Tasks
- https://go.documentation.sas.com/api/docsets/webeditordg/5.2/content/webeditordg.pdf
- https://go.documentation.sas.com/doc/en/webeditorcdc/5.2/webeditordg/titlepage.htm
Paper CT13 (from phuse-connect)
Next generation collaboration: using tasks and snippets in SAS Studio
Fatima Kassim, OCS Life Sciences, ’s-Hertogenbosch, The Netherlands
https://www.lexjansen.com/phuse/2019/ct/CT13.pdf

Tasks are based on the common task model (CTM), which consists of XML (metalanguage that allows for custom mark-up language) and Velocity Template Language (VTL or Apache Velocity language). Each task, SAS-supplied or custom, is based on such a CTM file.

By the way ... I also don't know about the security.

Something I tried out of curiosity --> the macro windowing environment (%window and %display).

Test Result was negative ... these do not work in SAS Studio!

Koen

Re: DBPROMPT for SAS Studio

Nigel_Pain — Fri, 15 Dec 2023 17:18:54 GMT

I don't know how well it would work but there's this from the SAS Github repository:

https://sassoftware.github.io/sas-studio-tasks/prompts/

I can't try it myself as our servers are blocked from going through our firewalls and proxies.

Re: DBPROMPT for SAS Studio

KalebF — Tue, 19 Dec 2023 15:16:46 GMT

This is probably about as close to a solution to what I had in mind. But due to security issues, I most likely won't be able to implement it in good conscience. But thank you Tom and to all others who posted. I got some ideas.. and learned the limitations. Thanks!