Sean's Oracle Share

March 16, 2010

conditional login.sql in the SQLPATH

Filed under: sqlplus — Sean Molloy @ 3:57 am

 

Further to my post on Self-inflicted unable to startup or shutdown database from OEM, I wondered if there might be a way to make the login.sql conditional. 

Background: 

The windows registry HKey_Local_Machine\ORACLE\Key_OraDb11g_home1 key has a Value field named SQLPATH 

When you run a file from insied sqlplus using @ or “start”, sqlplus will search any folders listed in sqlpath for the file. 

Also on starting sqlplus, it will check folders listed in sqlplus for any file named “login.sql”. If it finds a file, then this gets run before a prompt is presented. Also if any “connect /” is done in sqlplus, then a login.sql is again run. 

Problem: 

If my pc does a sqlplus/nolog then I do not want my login.sql to do anything (because there is no connection) 

Likewise once someone is connected, if they do a “connect sys/password as sysdba”, I do not want my login.sql to do anything (namely because this blocks OEM from starting/shutting down my database) 

Otherwise for any non-sys/non-system connect, I do want a login.sql to be run. 

Solution: 

In sqlplus there are some predefined variables. 

You can see these by typing “define”. The following is what you get after a sqlplus/nolog 

SQL> define
DEFINE _DATE        = "15-MAR-10" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "" (CHAR)
DEFINE _USER        = "" (CHAR)
DEFINE _PRIVILEGE      = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1101000700" (CHAR)
DEFINE _EDITOR        = "Notepad" (CHAR) 

 

After connect as test, you get

SQL> define _user
DEFINE _USER           = "TEST" (CHAR)  

So I created the following files: 

“login.sql” with one line:

@start_&_user..sql

“start_.sql”  and “start_SYS.sql” and “start_SYSTEM.sql” are empty files 

start_TEST.sql with any number of lines to do something useful such as


@@init.sql

This works as planned for “sqlplus/nolog”, “connect sys…” and “connect test/…”
Of course, a big downside here is that for each username you need a file named “start_username.sql”

Danger: 

With any login.sql script you need to worry about someone adding lines to your start_SYS.sql such as

grant dba to bad_guy;

About these ads

3 Comments »

  1. Hi Sean,

    This is an awesome idea. Thanks for sharing! Found my way here via the OraNA aggregator, will be back later via the RSS feed. :-)

    Regards,

    John P.

    Comment by jpiwowar — March 16, 2010 @ 5:19 am | Reply

  2. nice indeed to use _USER (10g upwards)!

    to get one file for everything else than “SYS”, “SYSTEM”, “” it is a bit more complex indeed, you could go for a batch job

    kind of

    unix:

    ! if [ x != "" -a x != system -a x != sys ]; then echo @start_default > /tmp/startup.sql;else : > /tmp/startup.sql; fi
    @ /tmp/startup

    windows:

    $ echo @startup_default > c:\temp\startup.sql
    $ if x&_user == xsys echo prompt > c:\temp\startup.sql
    $ if x&_user == xsystem echo prompt > c:\temp\startup.sql
    $ if x&_user == x echo prompt > c:\temp\startup.sql
    @ c:\temp\startup

    Comment by Laurent Schneider — March 16, 2010 @ 2:24 pm | Reply

    • Laurent,
      Thanks for the solution to my downside.

      This is what worked for me.
      login.sql
      host if 1 == 1 echo @start.sql > c:\temp\temp_start.sql
      host if x&_user == xSYS echo prompt > c:\temp\temp_start.sql
      host if x&_user == xSYSTEM echo prompt > c:\temp\temp_start.sql
      host if x&_user == x echo prompt > c:\temp\temp_start.sql
      @c:\temp\temp_start.sql

      Two comments:
      1. SYS/SYSTEM were case sensitive.
      2. Weirdly “host echo @start.sql > c:\temp\temp_start.sql” resulted in “@start.sql > c:\temp\temp_start.sql” being echo’d to the console. When I added the “if 1==1″ then the contents were written to the temp_start.sql

      Thanks very much for your input.

      Comment by Sean Molloy — March 17, 2010 @ 2:57 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: