Login SQL-Script

[Former Member]

Hi.

I am in a situation in which I would like DB-users to automatically run a specific SQL-script every time they connect to a specific database. Some kind of login-script which I define on the server.

Any creative minds out there who could lead me in the right direction?

Best regards

[former_SQLA_member1694880 · Accepted solution]

You do not need any creativity, just some RTFM on login_procedure option ;). In short, you need to do the following.

Create a procedure to do whatever you want.

CREATE PROCEDURE "DBA"."CheckLogin"() 
begin 
  -- do whatever you want 
  ... 
  -- don't forget to call sp_login_environment()
  call sp_login_environment()
end ;

Allow anyone to use it.

grant execute on DBA.CheckLogin to PUBLIC;

Make server call it during login.

set option PUBLIC.Login_procedure='DBA.CheckLogin';

[Former Member · Accepted solution]

Hi.

Thanks a lot. Yes I did think it was a RTFM issue 🙂 I just needed the keyword to search for: Login_Procedure.

Thanks I will continue this route and I am convinced this is the what I will need.

Thanks again.

[Former Member]

Hi again.

After I have used this info I will have to open the thread again. Note that the answer from Dmitri was not the general answer, I have just run in to a specific problem regarding my login-script.

What I am trying to do, is that I would like to authenticate against a OEM-database, like specified here: http://dcx.sybase.com/index.html#1201/en/dbadmin/running-s-4314706.html But in stead of modifying my app to connect using this Auth string every time:

SET TEMPORARY OPTION connection_authentication='company = company-name;
  application=application-name;signature=application-signature';

I would like to make the required OEM-authentication a part of a loginscript for the DBA, so that every time I use the DBA to connect to the DB I get authenticated automatically.

But, when I do this I get this error:

The option 'CONNECTION_AUTHENTICATION' cannot be set from within a procedure.

So are there anyone who know a way around this - making this sort of auth run as part of a loginscript?

[Breck_Carter]

That would completely defeat the whole idea behind authentication, wouldn't it?

[VolkerBarth]

I'd agree with Breck. What exactly are you trying to achieve?

IMHO, OEM authentication is meant to authenticate your app with your database so that your app is allowed to modify data, not to authenticate particular users from any app connecting to your database.

You may have a look at the INITSTRING connection parameter...

[Former Member]

You are both right. The reason why I would do this, is because I have several application which I use to connect to these OEM db's. The servers are only used by my db's and no one besides me (and my apps) have access to them. So the need for me to use this Auth in "the right way" will just make my usage more complex here and now.

Anyway, if this is the only way, I will off course have to go down that route.

(The funny thing is that when I was presented by the usage of OEM servers by Sybase their own Technician mentioned the usage of LoginScript as a way to work around this problem)

[VolkerBarth]

Anyway, if this is the only way, ...

Just to clarify: I'm not at all in the position to claim that there are no other ways, I was just spending my thoughts ...