Can't get access to other database

Question

I am working on a project for my university. They gave me a login username and password for MySQL database they've setup on the server.

With that username and password, I'm able to login to the server. I ran the following query on it:

SHOW GRANTS;

And got following output

GRANT USAGE ON *.* TO 'me'@'localhost' IDENTIFIED BY PASSWORD '*mypass'

Now if I am trying to create a database with following code

CREATE DATABASE test;

I am getting following error

Error: 1044
Access denied for user 'me'@'localhost' to database 'test'

As far as I know, . means access to all tables of all databases. Where is the problem. Please help me to rectify me.

Answer 1

Please RUN the following:

select user();
select current_user();

The former is who you are attempting to login as; the second is what the database sees as connected. You will need to run grant_priv to user.

Here is how to verify:

mysql> SELECT host,user,password,Grant_priv,Super_priv FROM mysql.user

If the grant_priv is set to 'N' for your user, this needs to be 'Y'.

To grant access you can run:

UPDATE mysql.user 
SET Grant_priv='Y', Super_priv='Y' 
WHERE User='me'@'localhost';
FLUSH PRIVILEGES;
GRANT ALL ON *.* TO 'me'@'localhost'

Answer 2

As the MySQL's documentation says:

The USAGE privilege specifier stands for “no privileges.” It is used at the global level with GRANT to modify account attributes such as resource limits or SSL characteristics without affecting existing account privileges.

Test:

mysql> GRANT USAGE ON test.* TO 'me'@'localhost' IDENTIFIED BY 'mypass';
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| me@localhost   |
+----------------+
1 row in set (0.00 sec)

mysql> CREATE DATABASE test2;
ERROR 1044 (42000): Access denied for user 'me'@'localhost' to database 'test2'

Answer 3

In addition to what you did, you should run the following:

GRANT ALL ON *.* TO 'me'@'localhost'

More about GRANT

Creating users and granting permissions can be done in more one way. One of them is GRANT USAGE..... If you instead to create a user using CREATE USER Syntax, and then run SHOW PRIVILEGES FOR 'user'@'host', you will notice that the user has USAGE ON *.*.

All that to say that you need to grant permissions after adding the user, regardless of how the user was added.