How to give user privilages for a db. Login as root. Switch to the MySQL db. Grant privs. Update privs

by

# mysql -u root -p

# mysql -u root -p

mysql> use mysql;

mysql> INSERT INTO user

(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES (‘%’,’databasename’,’username’,’Y’,’Y’,’Y’,’Y’,’Y’,’N’);

mysql> flush privileges;

or

mysql> grant all privileges on databasename.* to username@localhost;

mysql> flush privileges

To give user privileges for a specific database in MySQL, you would typically follow these steps:

  1. Login as root: Use the mysql command-line tool to log in as the root user, providing the appropriate credentials.
    bash
    mysql -u root -p
  2. Switch to the MySQL database: Once logged in, switch to the mysql system database which holds user account information and privileges.
    sql
    USE mysql;
  3. Grant privileges: Use the GRANT statement to assign specific privileges to a user for a particular database. Replace username with the username you’re granting privileges to, and dbname with the name of the database.
    sql
    GRANT privileges ON dbname.* TO 'username'@'localhost';

    Replace privileges with the specific privileges you want to grant, such as SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALL, etc.

  4. Update privileges: After granting privileges, you may need to update the changes with the FLUSH PRIVILEGES command to ensure that the changes take effect immediately.
    sql
    FLUSH PRIVILEGES;

Here’s an example of granting SELECT, INSERT, UPDATE, and DELETE privileges on a database called mydatabase to a user named myuser with a password ‘mypassword’:

sql
GRANT SELECT, INSERT, UPDATE, DELETE ON mydatabase.* TO 'myuser'@'localhost' IDENTIFIED BY 'mypassword';

Make sure to replace 'mypassword' with the actual password you want to set for the user.