John Hoover <[EMAIL PROTECTED]> wrote on 01/06/2006 10:21:40 AM: <<snipped>> > >For users that won't connect to the database directly, you probably > >don't want to create individual accounts - as if the user connects > >directly they can perform operations outside the bounds of your > >application (where you may implement business logic). > > You're the second person to mention users who won't connect directly > to the database and I'm not sure that I understand what you mean. I > THINK you mean "connect via the command line tools and bypass my application." > > I suppose that I could have "hidden" accounts (and passwords) that > my application uses behind the scenes. In this scenario, my users > would "login" with their name and password, but the connection to > the DB would use some secret account name and password. After the > connection was made, the application would query some > table (private to my application) to find out the actual privileges > for that person. The application would then enforce those > privileges. Nobody could bypass the application because they > wouldn't know the hidden usernames or passwords. > > My concern with this scheme is that the "secret" usernames and > passwords would either be set in a preferences file, in which case > they wouldn't really be secret, or they'd be coded in the > application, in which case I couldn't change them (we're supposed to > change our passwords at least once a year - more often for some systems). <<snipped>>
I agree with Chander and still recommend application-level database accounts, not one or more direct MySQL logins per user. That's how most databases storing data with complex business rules maintain their data integrity. The SQL data definition language is usually not complex enough to enforce certain common business relationships so we rely on the application the users interact with to do it for us. That means that most users DO NOT have their own private accounts with MySQL. The "permissions" are enforced programmatically by the application (usually with the help of one or more private tables, not actually part of the business data you are trying to protect). Certain users either are or feel that they are 'privileged' and need direct access to the underlying data. In those cases, I will create personal accounts for them in the database but I will only grant them read-only access. That way if they wanted to connect using their favorite data tool (mysql client, charting, reporting, whatever) they still could but they won't be able to break anything. They can 'look' but they cannot 'touch'. Most applications require periodic updates anyway (new rules, new screens, requested changes, etc). It should be possible to enforce consistent upgrades by changing the application passwords during each update cycle. That way, you can make sure that v1.1.13 no longer connects while v1.1.15 or better still can. Depending on the complexity of your business rules and the ever-changing nature of business, having version specific application accounts may be the easiest option to ensure that the "rules-du-jour" are being properly followed. There are various ways to obfuscate the passwords you use in various versions of each application. Do not leave your passwords in plain-text in any application leaving your direct supervision (as in a desktop rollout). Also, if the application and the database are using a networked connection on an open network, you should probably encrypt the link (SSL is built into the MySQL protocols). Even with that said I know that No program is un-crackable. All you need to achieve is reasonable security for the application and the sensitivity of your data. > >-- > >Chander Ganesan > >Open Technology Group, Inc. > >One Copley Parkway, Suite 210 > >Morrisville, NC 27560 > >Phone: 877-258-8987/919-463-0999 > >http://www.otg-nc.com > > > > -- > John Hoover > [EMAIL PROTECTED] > 301-890-6932 (H) > 202-767-2335 (W) > Shawn Green Database Administrator Unimin Corporation - Spruce Pine
