Depends on where the info is. Options include creating user roles in the database and/or assigning assigning access levels to each user and then check if the user has the required access rights before letting them query/see data from the database.
We are talking about two different things…
Of course I test my code.
And of course I step through every path to make sure my code does what it should do.
And, yes, most stuff on my website requires a Member is logged in since most things a Account-driven.
But I was talking about Access-Control from a backend database standpoint.
I thought there were ways to make sure a MySQL User has restricted access (e.g. can only perform actions on own records, can only Read, cannot perform DCL, cannot Delete, etc)
I thought that was what you originally meant.
I haven’t done anything with my Database Connection or backend MySQL to prevent an Anonymous User or logic_earth from running the gamut on database operations…
Debbie
DD,
As stated by someone else way above, I do that with PHP which pulls the “permissions” from the database.
Regards,
DK
Sorry, I don’t know what you are referring to…
Can you please explain here?
Thanks,
Debbie
Unless you can GRANT privileges, which is highly unlikely if you’re on a shared host (you need to be the “super admin”), I don’t think there’s a way to set privileges per user in MySQL.
The common way is to have a “privileges” table eg.
Userr_Id … Role
123 … 1
234 … 1
432 … 2
etc.
Then you do a SELECT and if the user has an adequate role level you continue else no go.
Who is using a Shared Host???
I have a VPS with Root access.
I don’t think there’s a way to set privileges per user in MySQL.
I believe there IS a way to grant User Privileges in MySQL, and that is what I have been asking about. (I know that my PHP properly handles Logging In/Out and prohibiting Users unless they are accessing their own Account, but I believe there is an additional layer of security here on the backend.)
The common way is to have a “privileges” table eg.
Userr_Id … Role
123 … 1
234 … 1
432 … 2
etc.
Then you do a SELECT and if the user has an adequate role level you continue else no go.
This thread has morphed and I’m a bit lost.
Guess this part would be better suited in the MySQL Forum. Oh well.
Debbie
There most certainly is a way - if you are the “super admin” - http://dev.mysql.com/doc/refman/5.1/en/adding-users.html
And yes, it is more secure. If your db connection allows you to do things like create users and DROP, you shouldn’t be using that for code others will use. Much safer to limit their capabilities.
And yes, this has strayed from passwords. Although this topic is security related, I too think a thread in the database forum would probably get better results.
DD,
You’re quite correct. I created a website for a local org in which I allowed different “officers” to modify pages under their control (but no others). All that takes is a field in the user’s record and a check in the PHP script which is used to modify records. Mitt’s suggested that you might allow others to modify the database which we all agree is not what you want to do.
IMHO, this is not a db question but one for the PHP board. Okay, it could go either way but allowing others to manipulate your database is not advisable.
Regards,
DK
That is the point of this tangent, though…
From my understanding, you want to set up your database connection in PHP so that Visitors and Members are logging in with restricted rights.
Right now on my laptop, I am logging in as root to the database.
I would think that I will want to create MySQL Database Roles and create one called something like “member” which prohibits said role from: Deleting Users, Dropping Tables, Changing Rights, etc and basically restricts things - at a database level - to where anyone accessing my site can only do basic CRUD on their own records and account.
But I am rusty on this and no expert.
Nonetheless, I think there is a large “database dimension” here that I need to address.
Debbie
The way I personally plan on doing it for an app that I’m writing:
- I create a new user to use when directly accessing MySQL (in my case via PHPMyAdmin), this acts as a “super-administrator” and the old root user gets deleted.
- Each app has it’s own database and user which it uses to access MySQL, that user is only given access-rights to the database belong to that app and it’s denied any access whatsoever to any other apps (also it’s not allowed drop or truncate at all)
- The app will have tables:
- User: Essential user information only
- User Info: “Optional” fields like instant messaging contacts, avatars etc
- Rights: Each possible action is given it’s own row
- Group Rights: Records what rights each group has
- Group Membership: Records what groups a user is a member of
- Groups: Names of groups and things like PM limits
- Session: User sessions are stored in the database
You’ve probably got similar tables in your app’s database.
The user groups/permissions bit is not complete but when a user logs into the site
- the credentials they provide are checked against the users table and there should be a single match, if there’s more then 1 match then there’s a problem.
- They will then get checked (eventually) against a list of banned users and if they are banned then they get shown a “unable to login you’re banned” message.
- Their right to view the page they are on is checked (via their group memberships), if they are allowed they view the page, if they aren’t they get sent back through the hierarchy of pages until they reach one they have permission to view.
Like has already been said you should:
- use sha1 at the minumum for hasing passwords use a salt with each one (I’ve got to look into the use of using a different salt for each user)
- Use prepared statements to protect against sql injection.
- Have a read of this wikipedia article about password strength
- You might want to consider adding a password strength indicator on you site’s signup page.
- IANAL but you might want to add to your site’s T&Cs something about a user being responsible for all actions attempted by their account and that it’s their responsibility to keep their login details safe and also something about their registration address to always be a valid address
Any web app in whatever language using whatever database server should not be verifying users directly against the database server’s user table, they should verify a user against the user’s table stored in that apps database. That app should use a single MySQL user to interact with it’s database only.
I hope that helps a bit Debbie, if I had a diagram which could visually show it I would add it.