How can you stop a db user from logging into Sybase Central?

[former_SQLA_member1694882]

During some discussions with a customers security expert, he asked whether we had the mechanism to stop the database user our applications use to interact with the database from logging into Sybase Central or isql or I suppose any application that can administer the database.

FYI - the database user our applications use is setup with the Resource authority.

So, is there? And if so, how?

Using: SQL Anywhere 11.0.1.2472 - Windows 2008.

[VolkerBarth]

Is the RESOURCE authority necessary? IMHO, that is way to powerful for users that usually will do DML but not DDL. - In case they will have to be able to do very limited DDL (say, to create custom fields or the like), I would build a STP to do so and give the users permission to use that STP. They still won't need RESOURCE then. So I would prefer to follow the least priviledge pattern.

[former_SQLA_member1694882]

@Volker Barth - No, in hindsight it doesn't need Resource authority as we could use sprocs as you suggested. What we currently require resource authority for is to create logging functionality on tables which creates a corresponding log_table and trigger on the primary table.

[reimer_pods]

We don't want users to directly connect to our application's database using ODBC or Sybase tools. When creating user logins for our application the specified password will be modified by an internal algorithm before the corresponding database user is created. The password the user enters when logging in to our applictaion is treated the same way before using the credentials to connect to the database.

So the combination of login name and password will not be valid for connections made outside our application.

[Former Member]

Cool. Double cool. I was never a fan of the "one database user id and application mimics the login security". So I always create actual db users. But this approach would give me the best of both worlds. Thanks Reimer.

[former_SQLA_member1694882]

Reimer - thanks for the answer. Good approach however it won't work for us as we could have 1000's of users (we have our own internal user management system) connecting to the database via desktops or web-applications so currently we only connect with one or a couple db users.

[VolkerBarth]

@Reimer: Yeah, and now we would like to get the sample-code...:)

[reimer_pods]

@Nick: the number of users doesn't seem to be a problem to me, just for fun I wrote a 3-line-script to create 2000 users. No problem, took some 30 seconds. So IMHO you could grant group to the account you're currently using to connect to the and make the newly created users members of that group.

[VolkerBarth]

You could use a login procedure in combination with the APPINFO connection property to reject connections with inappropriate APPINFO values.

You could thereby require that the EXE value is restricted to your EXE name, or exclude particular tools like Sybase Central, or could add an APPINFO=MySecret connection parameter to the connection string.

(Note: APPINFO is both the connection_property including several entries like API=, EXE=, and a connection parameter that builds the APPINFO= entry in the connection_property. Somewhat confusing, methinks.)

But note that won't prevent advanced attacks: In case you use a "magic" APPINFO that will be stored somewhere and could by used by Joe Hacker, too, and if you restrict to a particular EXE name, well, I guess one could rename Sybase Central to that, too...

---

A very simple (and untested) sample based on the login procedure sample from the docs - it should prevent any client connection unless APPINFO=MySecret is specified in the connection string (or the exe's path contains that string...):

CREATE PROCEDURE DBA.login_check( )
   BEGIN
      DECLARE INVALID_LOGON EXCEPTION FOR SQLSTATE '28000';
      DECLARE MagicValue VARCHAR(100) = 'MySecret';
      IF (CONNECTION_PROPERTY('AppInfo') NOT LIKE '%' || MagicValue || '%') THEN
          SIGNAL INVALID_LOGON;
      ELSE
          CALL sp_login_environment;
      END IF;
   END
go

GRANT EXECUTE ON DBA.login_check TO PUBLIC
go

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