dwhitney67
September 26th, 2010, 04:08 AM
It's been a few years since I dabbled with MySQL, and even then I interfaced to the Database using MySQL++ in a C++ application.
Anyhow, I am having trouble setting up a regular user to have permissions to create databases and tables.
As the MySQL root, I created a database, then a user account, and then I granted this user all privileges for this database.
# mysql -p
...
mysql> CREATE DATABASE dbname;
...
mysql> CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';
...
mysql> USE dbname;
...
mysql> GRANT ALL PRIVILEGES ON dbname TO 'user'@'localhost' identified BY 'password';
...
mysql> FLUSH PRIVILEGES;
...
All of the commands above succeeded without error.
Now, when I log into MySQL as the regular user, I am unable to create a table within a database:
$ mysql -p
...
mysql> USE dbname;
...
mysql> CREATE TABLE test (
-> columnOne int(11) NOT NULL,
-> columnTwo varchar(50) NOT NULL,
-> PRIMARY KEY (columnOne)
-> );
ERROR 1142 (42000): CREATE command denied to user 'user'@'localhost' for table 'test'
What am I missing? Is this a permissions issue with the actual database file in /var/lib/mysql? Does my regular user need to be made part of the mysql group?
P.S. All references above to "user" and "password" are not the actual values used.
Anyhow, I am having trouble setting up a regular user to have permissions to create databases and tables.
As the MySQL root, I created a database, then a user account, and then I granted this user all privileges for this database.
# mysql -p
...
mysql> CREATE DATABASE dbname;
...
mysql> CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';
...
mysql> USE dbname;
...
mysql> GRANT ALL PRIVILEGES ON dbname TO 'user'@'localhost' identified BY 'password';
...
mysql> FLUSH PRIVILEGES;
...
All of the commands above succeeded without error.
Now, when I log into MySQL as the regular user, I am unable to create a table within a database:
$ mysql -p
...
mysql> USE dbname;
...
mysql> CREATE TABLE test (
-> columnOne int(11) NOT NULL,
-> columnTwo varchar(50) NOT NULL,
-> PRIMARY KEY (columnOne)
-> );
ERROR 1142 (42000): CREATE command denied to user 'user'@'localhost' for table 'test'
What am I missing? Is this a permissions issue with the actual database file in /var/lib/mysql? Does my regular user need to be made part of the mysql group?
P.S. All references above to "user" and "password" are not the actual values used.