Clever Geek Handbook

How to properly set ownership and privileges in MariaDB (MySQL)?

I am trying to set up a database schema on MariaDB that 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 work in PostgreSQL and are SQL-99 syntax. MariaDB (PHPMyAdmin frontend) gives me: # 1064 - You have an error in your SQL syntax;

+3


source to share


2 answers


Privileges are often not part of the SQL standard because each database does it differently. MySQL and MariaDB do not have database owners like postgres. They have privileged systems to allow or deny certain privileges to accounts. The second will look like this:

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


If the IDENTIFIED parameter is optional. If you want to give access to all tables, like most others, you can use an asterisk. Setting GRANT ALL on a specific database effectively prevents the user from accessing other schema objects. The same can be done by creating GRANT USAGE ON ..

+6


source


You can use this MariaDB grant application:

GRANT ALL PRIVILEGES ON mydbname.* TO someotheruser

Please refer to the MariaDB manual for a grant application: https://mariadb.com/kb/en/mariadb/documentation/sql-commands/account-management-sql-commands/grant/



Please find the syntax for the SQL-99 statement: https://mariadb.com/kb/en/sql-99/15-authorizationids/grant-statement/ <a2>

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

+1


source






More articles:


All Articles