I'm wondering what various solutions people know about for this problem of user security with client apps connected to database servers. The most common form of "security" is to store one single database password in a local app or in a config file and login that way. Individual users would be then granted privileges based on a secondary table in the database that the server login user has privileges on. I know that's basically not a great way.

I guess a better way would have each client user have his own password in the database. This seems a bit messy for most database systems though because users and roles are maintained at a global level to the database server. There are bound to be collisions, especially if you're using an app that shares a database server with other users.

You could solve the problem with encoding user names with something like myapp_tom for user tom and database myapp. Is that something that people do?

I'm just wondering how people get around this issue. The SQL standard seems clumsy in that it doesn't have a simple mechanism for user logins on a per database level.

Thanks.

It really depends on your DBMS what you want and can do. Oracle manages users at the DBMS level. Thus, your last sentence is not true there. DB2 allows security plugins with which you can do whatever you want. So you could manage users at the OS level, DBMS level, or use other tools like Kerberos, NIS, or RACF.