MySQL – Grant Database Access to All Users

MySQLmysql-5.5permissionsusers

I have a global database, as I all users should have access to it. Is it possible to grant permission on a database to all users? something like

GRANT ALL ON my_database.* TO *@localhost;

Even (for security reason), it can be read permission only.

Best Answer

Yes, this is possible, though not recommended. You can create a user with an empty string as username, also known as the anonymous user. It matches any username. This is the only possibility, wildcards like in the hostname (i.e. 'user'@'%.any.domain.de') are not allowed.

GRANT ALL ON my_database.* TO ''@'localhost';

Related Solutions

PostgreSQL – Created User Can Access All Databases Without Grants

At the SQL level, every user can indeed connect to a newly created database, until the following SQL command is issued:

REVOKE connect ON DATABASE database_name FROM PUBLIC;

Once done, each user or role that should be able to connect has to be granted explicitly the connect privilege:

GRANT connect ON DATABASE database_name TO rolename;

Edit: In a multi-tenant scenario, more than just the connect privilege would be removed. For multi-tenancy tips and best practices, you may want to read on the postgresql public wiki: Shared Database Hosting and Managing rights in PostgreSQL.

Mysql – Grant access to a table to all users

What I am about to propose would be considered by many to be a hack, but will get you what you want.

STEP 01 : Create a special database called specialdata

CREATE DATABASE specialdata;

STEP 02 : As root@localhost, place the table you want into the specialdata database

CREATE TABLE specialdata.specialtable LIKE mydb.mytb;
INSERT INTO specialdata.specialtable SELECT * FROM mydb.mytb;

STEP 03 : Create anonymous user access to the specialdata database with the needed grants

INSERT INTO mysql.db
SET host='%',user='',db='specialdata',
Select_priv='Y',Insert_priv='Y',Update_priv='Y',Delete_priv='Y';
FLUSH PRIVILEGES;

STEP 04 : There is no STEP 04

Every user should have access to any table in specialdata

Best Answer

Related Solutions

PostgreSQL – Created User Can Access All Databases Without Grants

Mysql – Grant access to a table to all users

Give it a Try !!!

Related Question