Search code examples
mysqlprivilegesmariadbsql-grant

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


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;


Solution

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