Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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.

asked 16 Dec '10, 06:27

Nick%20Brooks's gravatar image

Nick Brooks
668212537
accept rate: 33%

edited 16 Dec '10, 06:36

3

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.

(16 Dec '10, 11:53) Volker Barth

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

(16 Dec '10, 21:21) Nick Brooks

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.

permanent link

answered 16 Dec '10, 11:31

Reimer%20Pods's gravatar image

Reimer Pods
4.5k384891
accept rate: 11%

1

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.

(16 Dec '10, 16:52) Bill Aumen

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.

(16 Dec '10, 21:38) Nick Brooks

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

(17 Dec '10, 09:40) Volker Barth
1

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

(17 Dec '10, 17:37) Reimer Pods

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

answered 17 Dec '10, 09:58

Volker%20Barth's gravatar image

Volker Barth
40.2k361550822
accept rate: 34%

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:

×48

question asked: 16 Dec '10, 06:27

question was seen: 2,375 times

last updated: 17 Dec '10, 09:58