Page 1 of 1

DB Security Question

Posted: Thu Mar 04, 2004 4:13 pm
by evangelinux
I'm developing a church admin app, and am setting up the db_users table. I anticipate that most end-users would use this app on a stand-alone system, or in a LAN environment. I'm curious whether it makes sense to use a similar approach to security as Linux & MySQL themselves.

I.e., the db_user table might include a db_group column, which my scripts can check to ensure that a user actually has access rights to a particular part of the app. We wouldn't necessarily want the youth leader to have access to the payroll records, after all! I just wander if this scheme makes sense, and is pretty secure. Could be a big security hole to do it that way, and I just can't see it.

Thanks for the input.

Posted: Thu Mar 04, 2004 7:09 pm
by Stoker
..depends on environment I guess..

for simplicity, I think all access control should be done by the application, making sure no-one knows any access credentials is likely better security, maintaining user/table access as well as application level access can be cumbersome and could, as say, open up other potential risks..

For complex situations where other 3rd party tools may connect to the data via ODBC and such, using a more application-functionality RDBMS like PostGreSQL and managing users on the table/column (or even row) level may be useful, this stuff can get fairly complex, all restratints and much functionality/code need to be within the rdbms, triggers etc..

DB Security Question

Posted: Fri Mar 05, 2004 1:42 am
by evangelinux
Might should have mentioned that I am using MySQL. Is PostGreSQL better suited for such a purpose? The book I'm using to learn PHP (Wrox) does suggest establishing a separate script for admins in their case study (a Yahoo clone that allows users to add URL's, but only admins can delete). The admin script would be placed in a password-protected directory. I shouldn't think that a typical church office would bog down MySQL if I use a user table for access control.

In my scheme, there would still be two or three levels of users (root, admin, user), depending on the user level set at setup time. All users would have to be created by the application's root user initially (I'm assuming the root user will do the install). After the initial setup, an admin might be able to create a new user, and grant privileges. I recognize that I will need to check the user's access level througjout the script. Is that why it's better/easier to run a separate admin script?

Also, does anyone know of any opensource Payroll apps? or just some source code I could view to see how payroll is done? I need to understand how to build payroll functionality into this thing eventually.

Posted: Fri Mar 05, 2004 10:18 am
by Stoker
that makes sense, if someone hacks or cracks your user-application they will hav elimited rights... That sort of access management is easy to set up and needs no maintenance, and as long as the various parts of the applications are physically separated ito the end user it will enhance your security yeah..

Posted: Fri Mar 05, 2004 11:15 am
by Pyrite
Hey man, check out phpSecurePages.com . Also, you can make MySQL User's that have certain permissions to the databases and tables you want. So differnet scripts can connect to MySQL using different users that have different permissions.

Also, there is another php application out there to manage churches. It is called InfoCentral and can be found at:

http://www.infocentral.org/

Posted: Fri Mar 05, 2004 12:46 pm
by Stoker
..actually, your seconds paragraph was exactly what he suggested himself..