Grant does not work when adding a new user in mysql

[mathman540]

I have been able to create a user in mysql. I am not able to grant the user all privileges. Any ideas how I can do this?

 

[mathman540]

This is a screenshot. 

[kicken]

Use CREATE USER first to create the user account, then grant it the privileges it needs.

create user 'webuser' identified by 'abc123';
grant all on  wordpress.table to 'webuser';

Your first two grants failed because you are creating a new user. 'webuser'@'localhost' is different from 'webuser'@'host' specified in your create user statement.

Your second two failed because you didn't specify a valid privilege type.

Edited September 19, 2024 by kicken

[mathman540]

I would like to grant all privileges to the complete database. Is that possible?

[kicken]

Yes, See the documentation for the GRANT statement.  The privilege level can be specified as:

priv_level: {
    *
  | *.*
  | db_name.*
  | db_name.tbl_name
  | tbl_name
  | db_name.routine_name
}

So to set a privilege on an entire DB you would use the db_name.* syntax rather than the db_name.tbl_name syntax you are currently using.

 

[mathman540]

I tried the code you put above and find that when I put in the database name it is not working for me. The database name I used has been created in mysql. Also I don't know what db_name.routine_name is about. Am I suppose to put the splats in with the rest of the code? I have created a database called wordpress. I just can't create a single user to use with my word press. Your help is a bit confusing.

Edited September 19, 2024 by mathman540
still not clear

[kicken]

In the documentation, it shows you the various options for the privilege level that you can specify.  The | separates the possible options and can be read like or.  So for the priv_level portion of the GRANT statement you can use either "*" (meaning default database), "*.*" (meaning all databases), "db_name.*" (meaning the entire database db_name), "db_name.tbl_name" (meaning table tbl_name in database db_name), "tbl_name" (meaning table tbl_name in the default database), or "db_name.routine_name" (meaning the routine routine_name in database db_name).

 

Since you want to grant privileges on a specific database, you would use the "db_name.*" option when writing your grant statement. That means you'd write your grant statement like so:

grant all on wordpress.* to 'webuser'@'host'

 

[mathman540]

Hey kicken,

Thanks for the help. This works grant all on wordpress,* to 'username@'localhost'. Although when I installed mysql I added a user for the web and I think that it got all privileges,