Limiting User Access

[Joseph Witchard]

Let's say you have a Users table that contains people's username, user id#, and password. If the user logs in and wants to change his/her password, how do you make sure that they're only able to change THEIR password? Won't the username that logs into the database that allows them access to the database have powers to edit all of the user information?

[chazzy]

unfortunately you can't. nothing that i know has row-level restrictions, and even then the app user concept has no impact on server users (the db user, for example)

[legendx]

Forgive me if I totally misunderstood what you are asking...

If you're using something like phpMyAdmin or MSSQL Server Manager as the sole interface for storing, retrieving, and updating information then you are misunderstanding what that application was intended for. phpMyAdmin is an administration tool for use only by the highest level developers. Users should never be given access to such a powerful interface.

[ripcurlksm]

Joe, this can definitely be done, but from the previous post it looks like you are using PHP MyAdmin? Yes? No?

For your question- yes you can do what you are asking, to allow a user to change only their password, but you have to setup a front end script which lets users change their password.

For a user that is logged in, you can put a "change password" link which sends them to a new page where they can enter in a new password and submit it.

If you post more details.. I can help more

[chazzy]

of course all this can be done, don't let my post imply that it can't. what i'm simply stating is that this level of control can't be done from the database, it has to be done from the application/middleware.

[Joseph Witchard]

Well they've got to have enough access to be able to write and edit to the database if you're creating something for your users to use. I'm not saying for them to go through phpMyAdmin, but you'll need a database user that has permissions to connect, write, and update in order for the user to be able to do anything for the example I just gave, won't you? Through a server-side script, of course.

[oldcat]

Let's say your front end is in something like PHP or ASP (it really doesn't matter which one, the same principles apply). What you want to do is to have a variable that keeps track of the username information (can be done via a cookie). For the query, you will execute something like

update users set password = password('$password') where user = '$user';

where $user is the username of the person currently logged in. This way, the user can only change his or her own password, and not someone else's.

------------------------------------------------

SQL Tutorial (http://www.1keydata.com/sql/sql.html)

[Joseph Witchard]

Could this be done with sessions instead of cookies?

[chazzy]

session data is generally tracked via cookies. PHP, for exaple, keeps the session id in a cookie.

[Joseph Witchard]

So it's pretty much the same thing?

[legendx]

Sessions last until you browse away from the website. Cookies can last a set amount of time. In PHP, session_ids are sometimes stored in a cookie but if the user has cookies disabled PHP will automatically stick the session_id onto the url.

http://www.mikebernat.com/blog/PHP_Cookies_vs_Sessions_-_The_Breakdown

[chazzy]

Sessions last until you browse away from the website.

That's very much not true. Session duration is managed either by php.ini or by your web server. I can browse a site, go somewhere else, and go back to the first site, all in a single session, provided I return quick enough (usually 15 minutes).

In PHP, session_ids are sometimes stored in a cookie but if the user has cookies disabled PHP will automatically stick the session_id onto the url.

Unless, of course, session.use_only_cookies is enabled


session.use_only_cookies boolean
session.use_only_cookies specifies whether the module will only use cookies to store the session id on the client side. Enabling this setting prevents attacks involved passing session ids in URLs. This setting was added in PHP 4.3.0. Defaults to 1 (enabled) since PHP 6.0.

[ripcurlksm]

Joe, are you familiar with PHP?

[Joseph Witchard]

I'm not going to win any PHP contests, but yeah, I'm familiar with it. My site pages end with the .php extension. So far, the only PHP I've really worked with is include files and email forms, but I've been experimenting with sessions and connecting to MySQL.

[ripcurlksm]

Would you like for me to post a simple working PHP/MySQL example for you to try with Login, Change Password and Logout?

[Joseph Witchard]

That would be so helpful. Thanks:)

[ripcurlksm]

I sent you a private message

[Joseph Witchard]

Really appreciate it:)