in mysql, how can a user create owner database and write in?

Question

i all

i was able to assign privilege to create new database to a user with

root> GRANT CREATE ON *.* TO 'newuser'@'localhost';
newuser> create database newdb;
newuser> Query OK, 1 row affected (0.00 sec)

now, i would that the newdb just created by 'newuser' was writable by newuser itself.

newuser> CREATE TABLE newtable ( id INT );
Query OK, 0 rows affected (0.00 sec)
newuser> INSERT INTO newtable (id) VALUES (1);
ERROR 1142 (42000): INSERT command denied to user 'offique'@'localhost' for table 'newtable'

i try to set privileges (with same user...) without solution :-(

newuser> GRANT ALL PRIVILEGES ON newdb.* TO 'newuser'@'localhost';
ERROR 1044 (42000): Access denied for user 'newuser'@'localhost' to database 'newdb'

any idea?

many thanks!

Did you FLUSH PRIVILEGES after using the GRANT command? dev.mysql.com/doc/refman/5.0/en/flush.html — dakdad
– dakdad, Commented Nov 25, 2011 at 0:48
thanks dak but it would be unhelpful... GRANT command cames out not well... (ERROR 1044 (42000): Access denied) — Roberto
– Roberto, Commented Nov 25, 2011 at 0:59
With GRANT CREATE ON *.* TO 'newuser'@'localhost'; you give the CREATE privilege to newuser when he connects from localhost. Have you tried granting him all the privileges? — Dor
– Dor, Commented Nov 25, 2011 at 1:03
You need to be root when adding privileges to newuser - because newuser only has the CREATE right. So, root> GRANT ALL PRIVILEGES ON newdb.* TO 'newuser'@'localhost' WITH GRANT OPTION; should have given the newuser all privileges on newdb — dakdad
– dakdad, Commented Nov 25, 2011 at 1:13
then... is impossible to a new user create database himself and write in without passing for root user (that giving privileges)? — Roberto
– Roberto, Commented Nov 25, 2011 at 6:28

Devart · Accepted Answer · 2011-11-25 07:03:50Z

1

now, i would that the newdb just created by 'newuser' was writable by newuser itself.

In this case it is enought to give an INSERT privilege on database level -

GRANT INSERT ON *.* TO 'newuser'@'localhost';

You should grant it from the 'root' account, because your new user has no rights to do it itself.

GRANT INSERT ON newdb.* TO 'newuser'@'localhost';

edited Nov 25, 2011 at 7:03

answered Nov 25, 2011 at 6:54

Devart

120k23 gold badges172 silver badges189 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Roberto Over a year ago

thanks Devart. But in this case newuser have access to all databases. I would that newuser have access only to database create itself.

Devart Over a year ago

@Roberto Oh, sorry. I missed it. I changed the answer.

Collectives™ on Stack Overflow

in mysql, how can a user create owner database and write in?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related