Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2004
    Posts
    57

    Unanswered: Control user right

    Hi,

    I would like to control my table for member is only able to insert by user "A"
    but not edit or delete, do i able to set this control in database side?

    Thank is advance.

  2. #2
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Are you talking about creating a database user that only has INSERT privileges? Or are you talking about creating a database table called "members" which contains a list of people who can log into a CMS and change content, but you only want to be able to allow them to add new users, not edit nor delete them... The first can be accomplished using MySQL CREATE USER or GRANT syntax. The second option is up to you, but in your members table you could have the following columns :
    Code:
    CREATE TABLE members (
     id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
     username VARCHAR(50),
     insert BOOLEAN NOT NULL DEFAULT 0,
     edit BOOLEAN NOT NULL DEFAULT 0,
     delete BOOLEAN NOT NULL DEFAULT 0
    );
    In your program you need to test for those fields whenever you are allowing access to particular parts of the application. i.e. those without DELETE = 1 cannot delete items.

    Another way to do it instead of creating individual columns is to use binary addition, and have it store a single integer of permissions. (very much alikened to the chmod principle)
    e.g.
    Code:
    CREATE TABLE members (
     id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
     username VARCHAR(50),
     permissions INT UNSIGNED NOT NULL DEFAULT 0
    );
    And accordingly in your program you need constants to define what bit refers to which permission.
    i.e. (PHP)
    Code:
    define('INSERT',1);
    define('EDIT',2);
    define('DELETE',4);
    So if you have the number 7 in your permissions column you know the user has INSERT, EDIT and DELETE permissions.

    You can test for individual permissions using the following (PHP):
    Code:
    $sql = 'SELECT permissions FROM members WHERE id=34';
    $result = mysql_query($sql);
    while($row = mysql_fetch_assoc($result)){
      $permissions = $row['permissions'];
    }
    
    if(($permissions & INSERT) = INSERT){
     echo "You have insert permission";
    }
    
    if(($permissions & EDIT)=EDIT){
     echo "You have edit permission";
    }
    
    if(($permissions & DELETE) = DELETE){
     echo "You have delete permission";
    }

    Hope this helps outline what you are trying to achieve.

  3. #3
    Join Date
    Jun 2004
    Posts
    57
    actually what i want is the first one. anyway to do in phpmyadmin?

  4. #4
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Only if the MySQL user you are logging in with has the WITH GRANT (or SUPER) option. If they do you should be able to do
    Code:
    CREATE USER '<username>'@'localhost' IDENTIFIED BY '<password>';
    GRANT SELECT,UPDATE,DELETE ON <database>.<table> TO '<username>'@'localhost';
    There are a whole host of other options for GRANT. Check mysql.com syntax to find them. Usually most users get the ALL privilege, but by specifying exact privileges you can get your desired effect.

Posting Permissions

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