SitePoint Sponsor

User Tag List

Page 2 of 2 FirstFirst 12
Results 26 to 31 of 31
  1. #26
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,923
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Mittineague View Post
    Unless you can GRANT privileges, which is highly unlikely if you're on a shared host (you need to be the "super admin"),
    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

  2. #27
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,228
    Mentioned
    194 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    .....
    I believe there IS a way to grant User Privileges in MySQL
    .....
    Guess this part would be better suited in the MySQL Forum.
    .....
    There most certainly is a way - if you are the "super admin" - http://dev.mysql.com/doc/refman/5.1/...ing-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.

  3. #28
    Certified Ethical Hacker silver trophybronze trophy dklynn's Avatar
    Join Date
    Feb 2002
    Location
    Auckland
    Posts
    14,672
    Mentioned
    19 Post(s)
    Tagged
    3 Thread(s)
    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
    David K. Lynn - Data Koncepts is a long-time WebHostingBuzz (US/UK)
    Client and (unpaid) WHB Ambassador
    mod_rewrite Tutorial Article (setup, config, test & write
    mod_rewrite regex w/sample code) and Code Generator

  4. #29
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,923
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by dklynn View Post
    DD,
    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

  5. #30
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,068
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    The way I personally plan on doing it for an app that I'm writing:
    1. 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.
    2. 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)
    3. 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

    1. 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.
    2. 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.
    3. 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.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  6. #31
    Non-Member
    Join Date
    Jun 2012
    Posts
    88
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SpacePhoenix View Post


    • 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)
    This really good info might help with the red bit.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •