Keeping users out ...

The application will give users access to the database and grant them rights as appropriate to their known roles, but I want to ensure they have no rights whatsoever as users who connect from outside the application.

So, the application goes through a login process (username & password) to allow the user to be identified and granted use of the application according to its already-defined menu system. The application will also connect them to the database (essential for the user to actually use the application) via ODBC. Some users will need read-only access, some others will need insert/update/delete. All their needs are known. I'm expecting to be able to give all users execute rights to a stored procedure which itself has dba rights so that it can grant/revoke access levels according to the user's profile. The initial actions of the application will be to call that SP for the user. Any comments?

If the above works OK, there is one (perhaps more?) other consideration -- I don't want them to be able to connect to the database from outside the application and have any rights whatsoever. I'm assuming that the rights they'd be granted when connecting through the application could be session-limited - i.e. temporary and available only through that instance of a connection. If that's so, then their user-id could be set to have a default of no access at all, and that would probably work ok. Except they'd still have execute rights for the SP ...

I'd like to be able to connect them to the database (when via the application) using a dummy id, perhaps their real id + a random suffix, so that they'd not have any notion of a useable login id ... perhaps a little paranoid ... perhaps not actually achievable.... (The app. would first have to connect as dba, add the dummy user-id, disconnect, connect as dummy user-id, allow user normal use, disconnect, connect as dba, erase dummy user-id ... but if the scheme fails somwhere, there may be potential for said user to end up connected as a dba ... and for dummy user-ids to persist because the dba housekeeping didn't complete)