use PHP login info to access mysql database

Question

I have built a custom CMS for updating my website. Normally what I do is, I mention database login info (like username-host-password) to a php file and include that file into all the php script files having database syntax. By doing this I access database through that specific database user.

What I am trying to do is to replace that particular database user with the logged in user. More clearly if user John is logged in to the CMS, he will access the database using the username john. And if user Doe is logged in, he will access the database using the username joe.

Main motive for this is to track the user who has updated a table in mysql database.

Can you guys please provide me with a concept of how to do this.

Thank You!

Answer 1

Actually i can do it here. I have a php file that i use require_once in all my pages. It is called logs and has a function in it here:

require_once("db_connection.php");

date_default_timezone_set('America/Chicago');

function logIt($msg,$tablename="",$application="" )
{
    global $connection;
    $cleanedMsg = remove_single_quotes($msg);
    $userID = "0";
    if ( isset($_SESSION['USERID']))
    {
        $userID = $_SESSION['USERID'];
    }

    $postDate = addHoursToTime(0);
    $query = "insert into web_log_tab (message, userid, post_Date, app, tablename) values ('{$cleanedMsg}',{$userID},'{$postDate}','{$application}','{$tablename}')";
    odbc_exec($connection,$query);
}

then after every query that I call in my php, i just call the logIt fufnction and pass in the query, tablename, and application. In my case, multiple applications are accessing the database so this way I can filter out by application, user, and even tablename. I hope this makes a little more sense and is helpful. When the user is logging into my application via the login page, I am putting their username, userid, userlevel in their private session so I can access anytime and from anywhere in the application.

$webPassword = $row['password'];
if ( $webPassword == $hashedPassword)
{
    $_SESSION['USERID'] = $row['id'];
    $_SESSION['FIRSTNAME'] = $row['first_name'];
    $_SESSION['USER_LEVEL'] = $row['user_level'];
    echo '{success:true,error:0,id:'.$row['id'].'}';
}

Of course you can store the users username and use that insead, I just prefer the id and I'll join whatever information that I need at a later date.

Answer 2

I always store the username/userid in their session and I keep a table in my database called logs. I log every query, the time, and the userid and username so I might have an entry that looks like this: 'select * from foo','1','mike','7/1/2014 1:45 pm'

it gets pretty large so I have to purge it every once in a while, but for most of these apps, just our techs are using the apps so its at most around 20 people so its not that bad. It also helps to troubleshoot when someone says your app did a boo-boo. You can go back and look at what they were doing and see what server queries were executed.