BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bheinsius
Lapis Lazuli | Level 10

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
JBailey
Barite | Level 11

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

View solution in original post

13 REPLIES 13
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

bheinsius
Lapis Lazuli | Level 10

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.

SuryaKiran
Meteorite | Level 14

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. 

 

 

Thanks,
Suryakiran
bheinsius
Lapis Lazuli | Level 10

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.

SASKiwi
PROC Star

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.

Patrick
Opal | Level 21

@bheinsius

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

bheinsius
Lapis Lazuli | Level 10
22         libname wikdimon list;

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

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.

Check out SAS Innovate on-demand content! Watch the main stage sessions, keynotes, and over 20 technical breakout sessions!
bheinsius
Lapis Lazuli | Level 10

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?

JBailey
Barite | Level 11

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

bheinsius
Lapis Lazuli | Level 10

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

SASKiwi
PROC Star

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. 

JBailey
Barite | Level 11

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 13 replies
  • 1849 views
  • 4 likes
  • 7 in conversation