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

Question

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 1

You may use this grant statement in MariaDB:

GRANT ALL PRIVILEGES ON mydbname.* TO someotheruser

Please find MariaDB's manual about the grant statement: https://mariadb.com/kb/en/mariadb/documentation/sql-commands/account-management-sql-commands/grant/

Please find SQL-99's grant statement syntax: https://mariadb.com/kb/en/sql-99/15-authorizationids/grant-statement/ https://mariadb.com/kb/en/sql-99/15-authorizationids/privilege/

MariaDB und MySQL do not have a database owner, instead database privileges are assigned using grant as shown above.

Answer 2

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 ..