how to grant privileges to all columns except one?

Question

I want to grant user an update privilege to update all columns except one - Id in my case (changing Id could make a mess in data, existing URLs etc. - foreign keys are not a solution). Is it possible in mysql without listing all the other columns? E.g. somehow by saying:

grant update on db.table to user;
NOT grant update id on db.table to user;

or something like that?

Reasons why I don't want to list all the columns:

it is more robust - I often add some columns to tables later and I would probably forget that I must also update this rule,
I don't have the grant permission so I must ask my hosting provider to run every grant command, so I want to minimize these requests as it is an above-standard service (it's their grace to me).

Particularly for mysql, but I'm also interested in gereric sql solution.

Unfortunately it cannot be done the way you want it to work. — kapa, Jan 26 '12 at 08:23

score 2 · Answer 1 · edited May 23 '17 at 12:27

2

You should learn more about database integrity, just about foreign keys.

Foreign Keys are the way that Dabase Relational system keep integrity with out artifices. From mySql FK documentation:

"RESTRICT: Rejects the delete or update operation for the parent table."

Avoid corruption with Foreign key has some advantages:

Is a built in functionality: performance will be better than a trigger or other systems.
Is the standard way to do this: other developers can understand what are you doing.
Reporting solution and other application can learn about relation ships database dictionary.

EDITED due OP comments

It is not possible to grant a high level privilete over an object and then revoke a low level one, take a look to selective privileges in MySQL (and vote up the answer).

That means that you should grant insert privileges over table, select privileges over all fields and update only over non id fields. You can write a script that do this for you.

I hope this finally answer your question.

edited May 23 '17 at 12:27

Community

1
1

answered Jan 26 '12 at 08:21

dani herrera

48,760
8
117
177

you all hear the buzzword "database consistency" and automatically come up with foreign keys :-) How would foreign key solve the issue? ) there's not necessarily a foreign key; the consistency of ids is just the simple fact they don't change, 2) the foreign key would not stop the user from changing the id to another valid one, but it would still make a mess. – Tomas Jan 26 '12 at 08:54
@TOmas: You say that Foreign keys is not a solution, yet you also say that *"changing Id could make a mess in data, existing URLs etc"*. How come? How could a change of an id cause such trouble? – ypercubeᵀᴹ Jan 26 '12 at 09:17
@ypercube, isn't that clear why changing ids is evil and should never happen? http://stackoverflow.com/a/7860111/684229 – Tomas Jan 26 '12 at 09:27
@danihp, again, your solution is not an answer to my question as defining the id as foreign key doesn't prevent the user from changing it. – Tomas Jan 26 '12 at 09:30

score 1 · Accepted Answer · edited May 23 '17 at 10:32

1

It is not possible - for more details see this question.

edited May 23 '17 at 10:32

Community

1
1

answered Jun 15 '13 at 19:22

Tomas

57,621
49
238
373

score 1 · Answer 3 · answered Jan 26 '12 at 08:28

1

AFAIK it is not possible, if you are afraid of database consistency, you should use foreign key constraint to keep the database consistency.

answered Jan 26 '12 at 08:28

xdazz

158,678
38
247
274

1) there's not necessarily a foreign key; the consistency of ids is just the simple fact they don't change, 2) the foreign key would *not* stop the user from changing the id to another valid, but it would still make a mess. – Tomas Jan 26 '12 at 08:52
@Tomas If you don't provide the function to change the id, how can the user change the ids? – xdazz Jan 26 '12 at 09:08

score 1 · Answer 4 · answered Jan 26 '12 at 08:31

1

I guess this might works :-

revoke update (id) on db.table from user;

answered Jan 26 '12 at 08:31

ajreal

46,720
11
89
119

I cannot test it now, but it would be interesting what `show grants` would show then (if it would show the revoke command too). – Tomas Jan 26 '12 at 09:00
That doesn't work. If the `grant update on db.table from user`(all columns grant) is done, you'll have update privileges for all columns. Your suggestion would not remove any privilege from the all columns grant. It would only work if the grant was for particular columns: `grant update(id,name,other) on db.table from user`. – Steve Chavez Nov 10 '20 at 14:12
For this type revoke to work you should probably first enable partial_revokes and you should have a minimum version of mysql as stated here: https://stackoverflow.com/a/63481197/839332 – Tommy Bravo Feb 26 '21 at 09:23

score 1 · Answer 5 · answered Jan 26 '12 at 09:20

1

Create stored procedures that do (Select, Insert, Update, Delete) operations on the data.

Give to the user access to the procedures only, not to the tables.

answered Jan 26 '12 at 09:20

ypercubeᵀᴹ

113,259
19
174
235

thanks. Would that work over ODBC and be accessible with Microsoft Access? That's what the user will do. I'm afraid not, that's why I want to use the simplest means possible... – Tomas Jan 26 '12 at 09:34

how to grant privileges to all columns except one?

5 Answers5