How do I correctly set ownership and privileges in MariaDB (MySQL)?

mysql privileges mariadb grant
Hajo Lemcke picture Hajo Lemcke · Oct 25, 2014 · Viewed 19.2k times · Source

I'm trying to setup a database schema on MariaDB which originally comes from PostgreSQL.

What is the correct syntax in MariaDB for:

ALTER DATABASE mydbname OWNER TO someuser

and similar for granting privileges:

GRANT ALL PRIVILEGES ON DATABASE mydbname TO someotheruser

Both works on PostgreSQL and is valid SQL-99 syntax. MariaDB (the PHPMyAdmin frontend) gives me: #1064 - You have an error in your SQL syntax;

Answer

Yorick de Wid picture Yorick de Wid · Oct 25, 2014

Often privileges are not part of the SQL standard because every database does this there own way. MySQL and MariaDB do not have database owners like postgres. They do have a privilege systems to allow or deny accounts certain rights. The second would look like:

GRANT ALL PRIVILEGES ON database.table TO 'user'@'host' [IDENTIFIED BY 'password']

Where the IDENTIFIED is optional. If you want to grant access to all tables, as most do, you can use the asterisk. Setting the GRANT ALL on a specific database effectively prevents the 'user' from accessing other schema objects. The same can be accomplished by creating a GRANT USAGE ON ..

Related questions

How to start MySQL with --skip-grant-tables?

I locked my root user out from our database. I need to get all privileges back to the root user. I have my password and I can log in to MySQL. But the root user has no all privileges.

Read More
I can not find my.cnf on my windows computer

My computer is Windows XP. I need to find my.cnf to get all privileges back to the root user. I accidentally removed some privileges of the root user. I still have the password and there is no problem for …

Read More
How can I restore the MySQL root user’s full privileges?

I accidentally removed some of the privileges from my MySQL root user, including the ability to alter tables. Is there some way I can restore this user to its original state (with all privileges)? UPDATE mysql.user SET Grant_priv = …

Read More