HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Grant access to a table to all users

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
allgrantaccessuserstable

Problem

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?

Solution

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

Code Snippets

CREATE DATABASE specialdata;
CREATE TABLE specialdata.specialtable LIKE mydb.mytb;
INSERT INTO specialdata.specialtable SELECT * FROM mydb.mytb;
INSERT INTO mysql.db
SET host='%',user='',db='specialdata',
Select_priv='Y',Insert_priv='Y',Update_priv='Y',Delete_priv='Y';
FLUSH PRIVILEGES;

Context

StackExchange Database Administrators Q#43614, answer score: 5

Revisions (0)

No revisions yet.