3

Is it possible to assign a grant to a table for all users, or a default permission so that when new users are created they will have the specific grants for that table to SELECT, UPDATE, INSERT and DELETE?

5

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

Give it a Try !!!

  • @RolandoMySQLDBA: I think without entry in mysql.user table it should not work. Please correct me if I am wrong. – Zafar Malik Oct 20 '15 at 9:07
  • Nice hack! This seems to work well – Shlomi Noach Feb 8 '16 at 9:11
0

MySQL does not support wildcards in user names.

This is from mysql 5.7 manual. But you may use a special user (or anonymous user), grant that user the permissions you want everybody to have. And then you could make a separate connection with that user to access the table. Not sure it makes a great sense though. It's perhaps easier to just grant these privileges to new users when creating them.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy