topic Re: As which database user am I connected to my RDBMS? in SAS Data Management

As which database user am I connected to my RDBMS?

bheinsius — Fri, 15 Jun 2018 11:41:25 GMT

I have a Postgres libname assigned from metadata.

How can I see with which database user I am connected to the database?

libname wikdimon list;

only shows this:

22         libname wikdimon list;

NOTE: Libref=   WIKDIMON
      Scope=    Object Server
      Engine=   POSTGRES
      Physical Name= mydb.example.com
      Schema/Owner= public

Re: As which database user am I connected to my RDBMS?

RW9 — Fri, 15 Jun 2018 12:35:47 GMT

Probably a generic read only account. Best to ask you SAS or Database admin.

Re: As which database user am I connected to my RDBMS?

bheinsius — Fri, 15 Jun 2018 13:03:53 GMT

I am a developer, i am trying to debug a problem with RDBMS access and I have multiple groups that I a m a member of and I am ytrying to find out which database user is being used in my case.

I know I can find it from Management Console by logging into it with my metadata credential, switch off the auto-assign option and select Display libname statement, but there must be an easier way than that.

Re: As which database user am I connected to my RDBMS?

SuryaKiran — Fri, 15 Jun 2018 13:06:46 GMT

If your libraries are pre-assigned then, your SAS admin might created authentication domain definitions while creating a user definition with the user manager in SAS Management Console. That means your credentials will be added to metadata repository and your libnames might be pre-assigned using external configuration or auto execute file. Simply if the Library is assigned to you then your the user.

AUTHDOMAIN=option enables you to avoid exiplicitly specifying a database user ID and password in a SAS Program. You can manually connect using the authdomain. Contact your SAS admin for the authdomain details.

Re: As which database user am I connected to my RDBMS?

bheinsius — Fri, 15 Jun 2018 14:27:55 GMT

Thank you but that is not my question.

My question is that I want to submit a line of SAS code, like:

LIBNAME WIKDIMON LIST;

that will list which user is used to connect to the database.

I mean for real - not what is listed in metadata.

Re: As which database user am I connected to my RDBMS?

SASKiwi — Fri, 15 Jun 2018 22:57:46 GMT

So what are the user credentials being supplied in the metadata definition? You should be able to figure it out by looking through the Management Console data library properties. I'm not aware of anyway of doing this in code.

Re: As which database user am I connected to my RDBMS?

Patrick — Sat, 16 Jun 2018 08:18:51 GMT

@bheinsius

So what does the libname ... list; command return in your case?

Re: As which database user am I connected to my RDBMS?

bheinsius — Sat, 16 Jun 2018 13:13:33 GMT

22         libname wikdimon list;

NOTE: Libref=   WIKDIMON
      Scope=    Object Server
      Engine=   POSTGRES
      Physical Name= mydb.example.com
      Schema/Owner= public

Re: As which database user am I connected to my RDBMS?

ChrisHemedinger — Sat, 16 Jun 2018 14:08:13 GMT

Hi @bheinsius - I know you just want the current user info. Maybe there is a passthrough method to get it, or it can be inferred by querying the INFORMATION_SCHEMA (supported in Postgres, I think). I have a blog post here that has a MySQL example.

@JBailey might have a more definitive answer.

Re: As which database user am I connected to my RDBMS?

bheinsius — Sat, 16 Jun 2018 15:10:06 GMT

Thanks @ChrisHemedinger I will look into that.

Actually obtaining the user was only part of my question, as I would also like to obtain the options that my library was assigned with.

From metadata I see that it will be assigned as follows:

LIBNAME wikdimon POSTGRES  PRESERVE_COL_NAMES=NO  PRESERVE_TAB_NAMES=NO  
DATABASE=dimon  SERVER="mydb.example.com"  SCHEMA=public  
USER=my_user  PASSWORD="{SAS002}xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" ;

where, like I posted before, libname list i only gives me this:

22         libname wikdimon list;

NOTE: Libref=   WIKDIMON
      Scope=    Object Server
      Engine=   POSTGRES
      Physical Name= mydb.example.com
      Schema/Owner= public

The only way I know to obtain this information is to go to SAS Management Console, disable the advanced option "Library is pre-assigned" option from the library, select the "Display LIBNAME statement" option from the library's context menu, then revert the "Library is pre-assigned" setting. But I am not allowed to do this in our production environment.

Do you have suggestions how to get that information as well?

Re: As which database user am I connected to my RDBMS?

JBailey — Mon, 18 Jun 2018 17:33:08 GMT

Hi @bheinsius

This may be what you are looking for...

libname pglib postgres user=myuser pw=Mypass1 server="postgres.mycompany.com" schema=myschema db=mygpdb
                      conopts="UseServerSidePrepare=1";

proc sql;
   connect using pglib;
   select * from connection to pglib (select current_user, current_schema(), current_database(););
quit;

The semicolon inside the explicit pass-through is optional but useful if you cut-and-paste between SAS and pgsql.

Best wishes,

Jeff

Re: As which database user am I connected to my RDBMS?

bheinsius — Mon, 18 Jun 2018 19:24:28 GMT

Hi @JBailey

Thanks.

From the answers so far I assume there is no way to get this information from SAS, including the connection options I listed before?

Bart

Re: As which database user am I connected to my RDBMS?

SASKiwi — Mon, 18 Jun 2018 21:10:20 GMT

All of the data source setup is stored in SAS metadata. So in theory it should be possible to extract this with SAS metadata functions. However the metadata model is complicated and very hard to unpick. I've never seen an example metadata read program for data sources but I've seen some for other types.

Re: As which database user am I connected to my RDBMS?

JBailey — Tue, 19 Jun 2018 12:29:31 GMT

Hi @bheinsius

At one point I actually requested your request as a feature. It didn't go anywhere. I will take a look at it again. I doubt it will be implemented...

For right now, the solution to your problem lies in the code I posted.

Best wishes,

Jeff