Security options at different SQL-Databases with ODBC - who is best?

Hello out there! As a database professional and journalist I am researching about security mechanism in the world of SQL-Databases. I do have experiences with DB2 on serveral platforms and found out that the "plan-security" offered by DB2 is a very useful thing. In a few words for those who are not familiar with DB2: Each program that is coded with Embedded SQL consists of the loadable Module on the client and "packages" (respectively a plan at DB2/MVS) in the server-database. The packages contain all information about the SQL-statements. Packages are created from the bindfiles with the bind-command. At bind-time the server can check whether or not the user who is actually binding does have all privileges neccessary for all the statements he wants to put into the plan. Later at execution time the user can be made independent from having those appropriate privileges himself, he just needs an EXECUTION-privilege on the package/plan. The abuse of packages by "foreign" programs is almost impossible since you need a very highly skilled programmer for coding all that stuff manually, and the the integrity between program and package is kept by a timestamp that references to the precompile-time. This is, of course, only possible for static SQL. Dynamic statements (PREPARE/EXECUTE) can't be checked in this manner because the tables accessed are not known at bind-time. Nevertheless each dynamic statement needs a package/plan, too. The user needs EXECUTE-privileges for it _and_ the access-privileges for the table he wants to access. So even query tools like QMF or environments like SAS and ODBC drivers do have packages/plans inside the database, but they are only "placeholders" for the statements generated at execution time. Unless you leave the mainframe you do not have a problem with this. A user can update any table from his application without being able to do the same update from the interactive query tool, because database changes are very unlikely coded with dynamic SQL. Unless the user has been granted with SELECT privilege explicitely he does not even have a chance to browse a table he must not browse. Now Client/Server is the breakthrough and lots of people are using lots of ways to create PC-software (mostly Windows 3.1/95/NT) to access databases. Those applications shall be portable between different SQL-databases, so they use ODBC. ODBC does not support static SQL in any way (except for a tool by NeonSoft I dislike for several reasons). On the one hand you have "grown" security policies on the mainframe that essentially depend on the difference between static/dynamic SQL, on the other hand there is the need to offer C/S-technologie yesterday. I have heard from several firms which want to sell us their applications that "only DB2 does have such problems with security". As for the standard SQL security mechanism I know I must disagree with them because the plan security DB2 offers is far beyond the standard. Who ist right? Do "all the other SQL databases" have a security mechanism that avoids my problem? Which mechanism does your favourite SQL database use to avoid it? Or do your user have the possibility to access the application's tables without using the application? A remark: Certainly DB2 does support creating views with seperate privileges and triggers and so on, and using hidden accounts on the database server to access the data is possible, too, but you can spy out all "secret" ODBC-calls using Software like ODBC-Agent by Leading Edge (this tool gives you even the database password as plain ASCII). So it is dangerous to call such workarounds "secure" as long as Lycos knows more than 1,400 links concerning "ODBC" and "SPY" Who can answer my question? Regards, Volker Koenig. http://www.Duesseldorf.netsurf,de/~vkoenig/