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

asked 27 Oct '11, 05:19

JorgenD's gravatar image

JorgenD
36114
accept rate: 0%

edited 02 Nov '11, 04:46

Volker%20Barth's gravatar image

Volker Barth
30.3k300452659


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';
permanent link

answered 27 Oct '11, 05:37

Dmitri's gravatar image

Dmitri
1.5k41132
accept rate: 11%

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.

(28 Oct '11, 06:54) JorgenD

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?

permanent link

answered 01 Nov '11, 03:34

JorgenD's gravatar image

JorgenD
36114
accept rate: 0%

edited 01 Nov '11, 12:36

Volker%20Barth's gravatar image

Volker Barth
30.3k300452659

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

(01 Nov '11, 05:01) Breck Carter
Replies hidden

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...

(01 Nov '11, 12:48) Volker Barth

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)

(02 Nov '11, 04:12) JorgenD
Replies hidden

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 ...

(02 Nov '11, 04:45) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×22
×13

question asked: 27 Oct '11, 05:19

question was seen: 1,375 times

last updated: 02 Nov '11, 04:46