11

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;

Jemolah
  • 1,962
  • 3
  • 24
  • 41

2 Answers2

14

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

Yorick de Wid
  • 859
  • 11
  • 19
  • Thanks for the hint. Are you aware of some good documentation about this handling in MariaDB? – Jemolah Oct 25 '14 at 13:33
  • As far as I know, the storage engine doesn't do anything with permissions, users and roles in MySQL. This is the task of the database management layer. The same goes for Percona, MariaDB and other derivatives of MySQL. That means you can just use the MySQL manual. – Yorick de Wid Oct 25 '14 at 13:37
4

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.

Julian Ladisch
  • 1,367
  • 9
  • 10