HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Can't get access to other database

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
candatabasegetotheraccess

Problem

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.

Solution

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'

Code Snippets

select user();
select current_user();
mysql> SELECT host,user,password,Grant_priv,Super_priv FROM mysql.user
UPDATE mysql.user 
SET Grant_priv='Y', Super_priv='Y' 
WHERE User='me'@'localhost';
FLUSH PRIVILEGES;
GRANT ALL ON *.* TO 'me'@'localhost'

Context

StackExchange Database Administrators Q#111045, answer score: 3

Revisions (0)

No revisions yet.