MySQL Privileges

Discussion in 'Server Operation' started by mcfly, May 22, 2005.

  1. mcfly

    mcfly New Member

    I've a little problem: I have a script that writes the privileges of new MySQL users into the mysql.db table. Now it seems as if this table has changed from MySQL 3.23 to MySQL 4.0, i.e., there are now more privileges columns than before.

    Now for MySQL 3.23 I'm running this command:

    Code:
    INSERT INTO `db` (`Host`, `Db`, `User`, `Select_priv`, `Insert_priv`, `Update_priv`, `Delete_priv`, `Create_priv`, `Drop_priv`, `Grant_priv`, `References_priv`, `Index_priv`, `Alter_priv`) VALUES ('localhost', 'database_name', 'database_user', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y');
    but for MySQL 4.0 I'd have to change it. But it's bothersome to program a routine that finds out which MySQL version is used or which privileges columns are available for the database in question in order to form the right SQL INSERT statement.
    So my question is: is there an easier way to do this?
     
  2. peter

    peter New Member

    You can use MySQL's GRANT command:

    Code:
    GRANT ALL PRIVILEGES ON test_db.* TO 'test_db_user'@'localhost';
    GRANT ALL PRIVILEGES ON test_db.* TO 'test_db_user'@'%';
    GRANT ALL PRIVILEGES ON test_db.* TO 'test_db_user'@'localhost' IDENTIFIED BY 'password';
    GRANT ALL PRIVILEGES ON test_db.* TO 'test_db_user'@'%' IDENTIFIED BY 'password';
    You can find details about it here: http://dev.mysql.com/doc/mysql/en/grant.html

    Peter
     
  3. mcfly

    mcfly New Member

    What's the
    Code:
    IDENTIFIED BY 'password'
    for?
     
  4. joe

    joe New Member HowtoForge Supporter

    It creates a password for the user you are granting privileges to. Just like normal shell accounts use passwords to protect privileges, the same goes for database users.
     
  5. peter

    peter New Member

    if you use the
    Code:
    IDENTIFIED BY 'password'
    clause, and the user doesn't exist in the user table, it will be created with the specified password. If the user is already existing, its password will be changed.

    Peter
     

Share This Page