Problem revoking all privileges

[Twinkletoes]

I'm using MySQL 4.1 (I think... a late 4 anyway) on a Gentoo box.  It's only a semi-live situation and no need for security just yet.

After messing with privileges a bit, I decided to revoke whatever privileges are already applied and specifically grant only the ones I need (using a shell script).  To revoke, the obvious choice was to use (if I can remember it correctly):

mysql> revoke ALL on *.* from '%'

However, I'm getting an error about that privilege not applied, and I've read it's because ALL isn't an alias for all privileges, it's a privilege in itself.  I think this is the reason I can't remove it!  So, the question is, how can I revoke all the permissions that have been applied to a database (recursing into table/columns etc.) ?

Many thanks,
Steve :)

[SGreen]

Twinkletoes <nabble@...> wrote on 02/24/2006 04:18:18 PM:

>
> I'm using MySQL 4.1 (I think... a late 4 anyway) on a Gentoo box.  It's
only
> a semi-live situation and no need for security just yet.
>
> After messing with privileges a bit, I decided to revoke whatever
privileges
> are already applied and specifically grant only the ones I need (using a
> shell script).  To revoke, the obvious choice was to use (if I can
remember
> it correctly):
>
> mysql> revoke ALL on *.* from '%'
>
> However, I'm getting an error about that privilege not applied, and I've
> read it's because ALL isn't an alias for all privileges, it's a
privilege in
> itself.  I think this is the reason I can't remove it!  So, the question
is,
> how can I revoke all the permissions that have been applied to a
database

> (recursing into table/columns etc.) ?
>
> Many thanks,
> Steve :)
> --
> View this message in context: http://www.nabble.com/Problem-
> revoking-all-privileges-t1183423.html#a3115859
> Sent from the MySQL - General forum at Nabble.com.
>
>

If you had checked the manual, you would have seen that your statement's
syntax is missing the name of the user whose privileges you are trying to
revoke.

REVOKE ALL ON *.* FROM 'username'@'%';

is what you are looking for.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

[Twinkletoes]

I appreciate your attempt to get me to RTM, but I think you'll find the user@host syntax is correct.  It works if I list a specific privilege to revoke.

The problem is that ALL is itself a specific privilege.  You can grant/revoke using it but it doesn't replace all the other specific privileges - it's just another one and happens to mean everything.  I can grant/revoke ALL, but it leaves all those other specific privileges intact.

As far as I see it, the problem lies with how to remove all those specific privileges easily.  I can blindly list all privs to remove, but this is not ideal.  I suppose I could export host_privs, do some jigery-pokery with sed/awk and formulate all the possible privs to remove, but that just seems too long-winded.

I hope this explains the problem better,

Thanks,
Steve :)