topic How to NOT use user credentials when accessing a database? in Administration and Deployment

How to NOT use user credentials when accessing a database?

FrankPoppe — Fri, 05 Feb 2021 16:29:05 GMT

Hi,

I am trying to establish something I always thought is possible (and I have said so 😐). But it proves to be more difficult than I thought.
I have a set of Stored Processes, to be used interactively from the browser. They maintain an administration in a set of tables, to store who is doing what and when, etc. (The users are DI Studio developers, and the Stored Processes enable them to define and export metadata packages and promote them by importing in the next environments and redeploying jobs there, in a kind of DevOps way.).

The administration tables are in database, and we always use libname statements with the AuthDomain option, meaning that there are no passwords in the code, but that credentials are collected from the metadata at runtime.

Stored Processes run under a service account, and when asking the metadata server for credentials the current user's identity is used. This is (I think) a fairly normal setup, and normally just what one wants.

But it means that all users have to be member of the metadata group where the outbound login for the database is stored.

And that means that they can write to the the administration tables without using the Stored Processes.
But I want the tables to be shielded away from these users; they may read them but changing data should be done only through the Stored Process.

I naively though it should be possible to create code that should get the credentials for the database on the basis of the account that runs the Stored Process (of maybe some other service account), but not the user's credentials.
But I do not see how to get there. Except by creating a libname statement with user= and password=, but that would be against policy. And I would have to make sure in some way that the users cannot see it by getting access to the log.

Any better suggestions?

Re: How to NOT use user credentials when accessing a database?

Jlochoa — Fri, 05 Feb 2021 17:26:47 GMT

Use password encode functionality?

Re: How to NOT use user credentials when accessing a database?

JackHamilton — Fri, 05 Feb 2021 18:06:38 GMT

If you mean PROC PWENCODE, that's really the illusion of security rather than actual security. If your actions are entirely in SAS, there's not much you can do with the real password that you can't also do with the encrypted password.

I wonder if creating an encrypted compiled view with the password embedded would work. Or something with a view in a metadata-bound library?

Re: How to NOT use user credentials when accessing a database?

SASKiwi — Fri, 05 Feb 2021 21:24:15 GMT

An alternative to using individual user access is to use token-based database access via a database service account that is only stored in SAS metadata. The way to do this is to set up a unique metadata Authdomain for this type of access, then create a metadata user group for all of the users who are going to use this approach. Then you tag the user group with the Authdomain, database service account and password.

Whenever the Authdomain is referenced in a LIBNAME, as long as the user is a member of the user group created for this purpose the database connection will use the stored service account.

Re: How to NOT use user credentials when accessing a database?

FrankPoppe — Sat, 06 Feb 2021 08:54:55 GMT

Thanks for the suggestions. Short reactions.

@Jlochoa PWENCODE indeed is just a suggestion of security.

@SASKiwi I know how to set up AuthDomain's. But as I see it, once a user is member of that group, that user can execute any code, without using my Stored Processes. Just what I do not want.

@JackHamilton The same goes, I am afraid, for something with meta bound libraries.

That leaves the compiled view option. The idea had crossed my mind, but I was hoping for something more based on metadata. I am not sure if @JackHamilton is referring to a compiled macro or a compiled SCL-entry, but both options seem doable. Of course, then those things have to be hidden, but I think that can be done best on OS-level: just make the physical location only readable for the StoredProcess-account.