SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

As which database user am I connected to my RDBMS?

Accepted Solution Solved
Reply
Regular Contributor
Posts: 203
Accepted Solution

As which database user am I connected to my RDBMS?

[ Edited ]

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

 

 


Accepted Solutions
Solution
‎06-19-2018 12:37 PM
SAS Employee
Posts: 293

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

Posted in reply to bheinsius

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


All Replies
Super User
Super User
Posts: 9,813

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

Posted in reply to bheinsius

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

Regular Contributor
Posts: 203

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

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.

PROC Star
Posts: 624

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

Posted in reply to bheinsius

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
Regular Contributor
Posts: 203

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

Posted in reply to SuryaKiran

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.

Super User
Posts: 4,016

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

Posted in reply to bheinsius

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.

Respected Advisor
Posts: 4,779

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

Posted in reply to bheinsius

@bheinsius

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

Regular Contributor
Posts: 203

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

22         libname wikdimon list;

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

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

Posted in reply to bheinsius

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.

Regular Contributor
Posts: 203

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

Posted in reply to ChrisHemedinger

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?

SAS Employee
Posts: 293

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

[ Edited ]
Posted in reply to bheinsius

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

Regular Contributor
Posts: 203

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

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

Super User
Posts: 4,016

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

Posted in reply to bheinsius

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. 

Solution
‎06-19-2018 12:37 PM
SAS Employee
Posts: 293

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

Posted in reply to bheinsius

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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