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:
Using: SQL Anywhere 11.0.1.2472 - Windows 2008. |
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 |