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