trying to understand (my)sql user rights/levels and advanced features

[parasew]

i am trying to create a quite secure web-portal with a database backend. (mysql)
i was wondering if it is possible somehow to map the users that would be created to use the system, to "real" mysql users -- the idea was to have security from the backend:
the main "surfer" would connect to the db via a user that can only read all data.
system users would connect with their username (+level) so they have read and write access to several parts.
as far as i found out, users in mysql are only used for access_control, there is generally no information which user put which entries to the DB (please correct me, if i am wrong).
is there some way to restrict the DB-users to just be able to modify and delete content they added to the db (from the backend side)?
this would bring a really good access-by-level aproach:
-websurfers connect to the db with a user that can just read
-system users connect with their account, can write into some parts (tables) but can just delete/mod their own added content (or maybe group content)

[phydeauxarff]

Yes you can do this but you are going to have to sit down, pull up your sleeves, and start writting some code.

First, you will need an method for authentication against your mySQL data.

A quick search provides Apache authentication with Mysql ...the examples given there will get you started down the path of getting your site to authenticate against the users in your database.

You can use the above to provide read-only access to unauthenticated users by simply changing your forms based on whether the user has logged in.

Next, you need to have the ability to have users only edit the data they enter, or have permission to edit...this will take some creativity. Off the top of my head the approach I would try is to add an extra table to show who is the owner and/or the security level of each record.

This would be used as a flag to determine if the user logged in has the appropriate security level edit the record they retrieved, or if it would be read-only.

This is just a start, but it should get you running down the road....good luck.

[parasew]

thanks for the hint with the Apache authentication with Mysql, i was just wondering why in general databases don't have the approach of bringing the entered data in a relation to the users, as this would bring up totally different and (in my opinion) better security from the backend.

you would not need to put any database-user-password somewhere in your .pl or .cgi script (except the www-user that can only read).
thanks again,
parasew

[adrianh]

is there some way to restrict the DB-users to just be able to modify and delete content they added to the db (from the backend side)?

MySQL allows you to control user access at the database, table and column level. See Chapter 4 of the manual for full details.

Depending on your application you might be able to use this to enable the kind of access control you want.

For example, you might create a table for each user, and only give the appropriate user insert/update permissions. You could then create a global view of all of those tables so everybody can read them.

Without some more details it's hard to tell. It might be easier to roll your own access control system.

With PostgreSQL, of course, you could add a triggers on the database side to roll your own access control system on the database side :-)

[parasew]

>For example, you might create a table for each user,(..)

hihi, that's a nice idea ;)

but you recommended PostgreSQL and Triggers.
as far as i found out, postgreSQL is a ORDBMS, i am wondering how to effectively use the functionality that comes with the object oriented approach such as
inheritance

quoting the definition of an ORDBMS:
The main benefit to this type of database lies in the fact that the software to convert the object data between a RDBMS format and object database format is provided. Therefore it is not necessary for programmers to write code to convert between the two formats and database access is easy from an object oriented computer language.

i was wondering if anyone has hints on how to use that functionality from the perl side...
for example with Class::DBI...

[adrianh]

i am wondering how to effectively use the functionality that comes with the object oriented approach such as inheritance

Reading the manual would seem a good starting point.

i was wondering if anyone has hints on how to use that functionality from the perl side... for example with Class::DBI..

Class::DBI is neutral as to the way the database represents tables, so you'd just reference the tables in the same way as normal. Whether Class::DBI is an appropriate base for your database work depends on the application.

[Abigail-II]

What was your Perl question again?

Abigail