Thread

Hi,
I’m trying to create a business application based on CI. I have a question how can I select a database user/pass depending on the data in session.
For example: I use basic/anonymous access to allow loging in, then I use different user/pass to get data in mysql (securing database)
Another requirement is the “history of changes” in database. I would like to do it via triggers in mysql. I would like to add record to special table with information about the actual user login used in web app. How can I make sure that each user has it’s own connection to db, and that at when the session is established the special mysql session variable is set. Or how can I set such variable before each add,update,delete query? (is this concurrently safe?)

thanks,
cogito

Hi, I will share my approach to this problem.

Every monitored table needs several things: 1) additional “user_id” column for storing last activity user 2) log table with some columns like “date”, user_id, “operation_type” 3) triger before insert, update or delete on that table. Though, you might have one “special” log table for all monitored tables.

During every insert, update or delete on table, you just append logged app user id into sql statement just like ordinary column. The log into a special log table will be made through the trigger of the table that will perform insert to the log table (date, user_id, operation_type). In this approach you do not need additional db user for each app user.