Passing Arguments to Login-Procedure

[Former Member]

Hi,

i was trying to pass some own/more connection infos to our login procedure.

I used the "CON=" parameter.

CON=PROGRAM_USER=test_user@

PROGRAM_PASSWORD=pasword_hash@PROGRAM_CON_NAME=Test Connection

Is there a better way to pass such infos?

First I thought about the connectstring (e.g. ConnectString='DSN=myDatabase;UserDefinedOption=ABC') but i didn't find a way to access these infos in my login procedure.

How do i change the connection-name property in my login procedure?

set option name=Test Connection; -- does not work

Regards

Stefan

[jeff_albion · Accepted solution]

Hi Stefan,

See the following sqlanywhere-forum thread for more details about changing the connection string after login - it can't be done (and really isn't intended for that purpose):

http://sqlanywhere-forum.sap.com/questions/8730/is-there-a-way-to-set-con-property-connection-apart-...

So in summary, no, there isn't a way to change the connection name, and as Arcady points out, it isn't a very secure mechanism to transfer passwords. Arcady is also correct in that you should call the login procedure directly from your application.

There are two issues here:

1) Changing connection name information after login / passing information around on your connection

Instead of storing the information in the 'Name' connection property, you probably just want to use a connection-level variable or a customer user-specified database option (created by 'DBA' authority in SQL Anywhere 12 and lower or with the SET ANY USER DEFINED OPTION privilege in SQL Anywhere 16 ) to pass along information from the login procedure:

    CREATE VARIABLE myVar VARCHAR(100);
    SET myVar = 'Hello, World!';

    ...

    SELECT myVar;

or

    SET OPTION PUBLIC.custom_option = 'default'
    ...
    SELECT * FROM SYS.SYSOPTION;

2) Creating a secure, custom, login procedure against another table

If you're going to be using just one database user with one set of permissions, you should look to pass the custom authentication information after the database connection has been made (hopefully over a strongly encrypted connection, for maximum security), and ideally using a prepared statement with bound parameters from the programming API you're using. Your application can then handle any SQL exceptions that are thrown from the login procedure statement and log-out the user from the application at that time.

You are correct in that this will need to be done outside of the original login procedure (that should really be verifying the supplied database username and password).

e.g. in Java:

       ==================
       Connection con = DriverManager.getConnection( "jdbc:sqlanywhere:uid=DBA;pwd=sql" );

       String sqlStr = "CALL sp_custom_login( ?, ? ) ";

       // Prepare the statement

       PreparedStatement stmt = con.prepareStatement( sqlStr );

       // Set values

      stmt.setString( 1, 'userName' );

      stmt.setString( 2  'password' );

       // Execute the statement

       try {

           int iRows = stmt.executeUpdate();

       } catch (SQLException ex) {

             // Can't log in - log user out of application...
             ex.printStackTrace();
       }

==================

Regards,

Jeff Albion

SAP Active Global Support

[former_member329524]

Hello, Stefan

You will need to elaborate on the basics: What do you mean by "login procedure"?

Do you mean connection to db? Or logging into your application?

BTW, it is a really bad idea passing such arguments in the connection name. It will be visible for everyone, who launches sybase central or runs sa_conn_info procedure. that mean everyone will be able to see passwords of your user.

[Former Member]

Hello,

i want to do additional checking if the user is in our "PROGRAM_USER_TABLE'.

We want to change how our application uses the database.

Today we're using one database user for each user that uses our program. We want to change it, so we have only one database user and do our own authentication.

It should look something like this:

CREATE PROCEDURE DBA.login_check( )
   BEGIN
      DECLARE INVALID_LOGON EXCEPTION FOR SQLSTATE '28000';
       DECLARE sAdditionalLoginData varchar(255);
       DECLARE sProgramUser             varchar(255);
       DECLARE sProgramPwd             varchar(255);
       DECLARE sConnectionName        varchar(255);
       
       SELECT connection_property('Name') INTO sAdditionalLoginData FROM SYS.DUMMY;
       
       SET sProgramUser         = sf_getValue (sAdditionalLoginData, 'PROGRAM_USER');
       SET sProgramPassword = sf_getValue (sAdditionalLoginData, 'PROGRAM_PASSWORD');
       SET sConnectionName  = sf_getValue (sAdditionalLoginData, 'PROGRAM_CON_NAME');
       
       IF EXISTS (SELECT 1 FROM DBA.PROGRAMM_USER_TABLE WHERE USERNAME = sProgramUser AND PASSWORDHASH = sProgramPassword) THEN
               CALL sp_login_environment;
       ELSE
               SIGNAL INVALID_LOGON;
       END IF;
       
       --set option name=sConnectionName ; -- does not work 
   END;

GRANT EXECUTE ON DBA.login_check TO PUBLIC;

SET OPTION PUBLIC.login_procedure='DBA.login_check';

Regards

Stefan

[former_member329524]

The obvious question would be: why not just call this procedure with the login/password as parameters?

[Former Member]

Hey,

I thought it would be easier, when the database does this, when i log in with a specific user.

I would change the Login-Procedure of that user, to my login_check()

SET OPTION program_user.login_procedure='DBA.login_check';

I could do this manuelly: connect, call procedure from my program, if return value = 0 then disconnect.

But that's not the question 😉 I asked if it's possible to do this, the way i described above.

I already tried it, but as you wrote, it's a bit risky to use the CON= parameter. Thats why i asked if i can change the Connection-Name in the login_check() procedure.

Stefan

[former_member329524]

Even if it were possible to change the connection name after the connection has been made, it is still just as risky and open for all to see.

I strongly suggest you call the login procedure from the program.

Arcady