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;
Often privileges are not part of the SQL standard because every database does this their 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 ..
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.
