Currently when our applications connect to SQL Anywhere, we connect using a database user with the Resource authority. The db user password is encrypted (synchronously) and decrypted by the application using a secret key known by the application.

However, it wouldn't be too difficult for a user to figure out how to get access to the password and then log into the database using our application db user.

So is there a way of either:

  • Elevate the db user privileges once the application has proven itself to the database?

  • Or connecting as a low security db user, then once the application has proven itself, switching to a higher privileged db user?

Using: SQL Anywhere 11.0.1.2472 - Windows 2008.

asked 16 Dec '10, 06:37

Nick%20Brooks's gravatar image

Nick Brooks
513172031
accept rate: 50%


I am sure there are a number of different answers that people can/will provide. I have three suggestions.

The first is to consider using a userid that has no password. If the userid has no password, then you cannot log in using that userid. You can then grant that userid resource authority and and create stored procedures owned by that userid. When that stored procedure is executed, the procedure will execute "as the owning userid" but once the procedure exits, the effective user will revert back to the logged in userid. By encapsulating all the "resource" specific SQL in stored procedures, you have full control over what SQL gets executed with the higher privileges and let all the normal stuff happen as userids with lower privileges.

If you do not like the stored procedure approach (although that tends to be the most used and most preferred approach), then you might consider using the SetUser statement. Be aware though that in order for a userid to execute the SetUser statement, that userid must be granted DBA authority, so this second approach is really scary.

My third suggestion is to see if using Windows Integrated Login will get around the password problem completely. Note that in the case of Windows Integrated Login, your application will need to run on a Windows network and may need to impersonate a Windows User context; so there could be some high hurdles to jump over in that case.

Those three suggestions amount to my two cents worth, hopefully others will jump in and provide additional suggestions. Like I said though, the stored procedure approach is probably the more widely used (and safest) approach.

Karim

permanent link

answered 16 Dec '10, 13:16

Karim%20Khamis's gravatar image

Karim Khamis
5.6k53870
accept rate: 40%

edited 16 Dec '10, 13:30

If you're worried about someone reverse engineering your application to get the username and password from it, there's not much you can do. From the server's point of view, a client is a client is a client - there's nothing that your application can do that Joe Hacker's application can't do if Joe Hacker has your username and password. So if we add the ability for your application to log in as a user and then "elevate" your login to have more permissions, there's nothing stopping Joe Hacker's application from doing the same thing.

We have something called the authenticated server, which only allows databases that have been authenticated to be run, and only allows applications which have been authenticated to connect. In order for an application to authenticate itself, it does a SET OPTION statement using a particular string, and the server validates the string. You may see the problem already - the string has to be stored somewhere that the application can read, and whether it's hard-coded in the application itself or stored in a file somewhere, a hacker with access to the application can get access to the string, and then his application is "authenticated".

The most secure way to get around this is simply not to store the password in your application, though this is admittedly inconvenient.

permanent link

answered 17 Dec '10, 03:30

Graeme%20Perrow's gravatar image

Graeme Perrow
8.5k371109
accept rate: 52%

The same applies to the key (DBKEY) for strongly encrypted databases. I would really like to know whether customers are usually asked to insert both a DBKEY and a PWD (in order not to store them) or if the DBKEY is "hidden" somewhere. - I know, for setups with a real administrator, dbsrvX -ep is a solution, but that won't fit to the usual embedded application...

(17 Dec '10, 09:35) 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:

×37

question asked: 16 Dec '10, 06:37

question was seen: 741 times

last updated: 17 Dec '10, 03:30