BookmarkSubscribeRSS Feed
eparson
Fluorite | Level 6

I am looking to create a regular script or macro allowing me to:

 

  • Run additional oracle library scripts giving the first one connects
  • Reason:  I tend to run 3 at a time and when my password changes at time I still run the the 3 scripts with the old password which then ends up locking my account since after 3 incorrect login attempts my oracle username will lock. 

 

 

Using SAS/CONNECT: libname MAIN odbc user=***** password=*****dsn= myoracle schema=Table1 preserve_tab_names=yes;

 

 

Pseudo code:

 

If username connects to table then do;

Run oracle script 1;

Run oracle script 2;

 

End;

2 REPLIES 2
ChrisBrooks
Ammonite | Level 13

Try the Libref function - this checks whether a libref has been assigned and returns 0 if it has and a non-zero value if not - the full documentation is here

Patrick
Opal | Level 21

@eparson

If you issue the libname statement only once then only one attemt gets made to connect to the database. Just using the libref downstream won't create another connection attempt (especially as you're not using option defer=yes).

 

If your code tries to connect multiple times then you must have code with multiple libname statements.

 

In case you run all these 3 SQL's in the same session then just have a single libname statement on top of your script.

 

What at @ChrisBrooks proposes would allow you to check if a connection could get established and lets you implement code that ends in a controlled manner if something goes wrong. It wouldn't protect you though from SAS making a connection attempt first and your account would still get locked out after 3 such unsuccessful attempts.

 

One way to get around this:

Have a SAS Admin talk with the Oracle DBA's so he/she can define an authentication domain in SAS Metadata using a generic account where the password never expires or where admins managed the generic account and keep passwords in sync becomes an IT function. With such a domain defined you won't need to pass credentials in your libname statements anymor, you just need metadata read access to the definition in SAS Metadata.

http://support.sas.com/kb/38/204.html

http://support.sas.com/documentation/cdl/en/bidsag/69848/HTML/default/viewer.htm#p0g4zazv5evotnn1db3...

 

 

If that's not possible then another option I can think of:

1. Implement a SAS Autocall macro

2. Have the macro issue the libname statement

3. Build in the check as proposes by @ChrisBrooks

4. Maintain a permanent control table in SAS where you write an entry in case you couldn't establish a connection

    If possible: Only write this entry if the reason was an expired password. Not sure though if you get a specific return code for this.

5. In your macro always check first your control table for an entry of failed connection attempt and only issue the libname statement if there hasn't been such a failed attempt.

 

...and just as an idea: You could also add a timestamp to the entries for failed attempts and only consider entries not older than 30 minutes or so. Or you just have an adhoc script which cleans out the control table once you've changed your password.

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 666 views
  • 0 likes
  • 3 in conversation