Re: REVOKE GRANT OPTION - is that possible?

[Imran Chaudhry]

> # mysqlmgr add database www1
> # mysqlmgr add user www1 password xxx
> # mysqlmgr allow user www1 database www1 read write define

I suppose in some cases this is OK, and I'm sure you will test thoroughly! :-)

One thing to bear in mind is that if you define a user and then revoke
his privileges, his account details details will still persist in the
user Grant table. This may not be a worry to you since he cannot get
at any database resource. To remove any trace of him, you must delete
him from the user table. (if you're playing with MySQL 5.0 then DROP
USER is the ticket).

Final thing, as you're a MySQL admin type writing an access control
app, you want to know when the priv changes take effect. You will
probably find this info useful:
http://dev.mysql.com/doc/refman/5.0/en/privilege-changes.html

Hope that helps,
Imran Chaudhry

--
http://www.EjectDisc.com
Get your Digital Identity - Domain Names, Web Space, E-mail & More!

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: REVOKE GRANT OPTION - is that possible?

[Yves Goergen]

On 02.01.2006 13:41 (+0100), Imran Chaudhry wrote:
> GRANT and REVOKE are essential to your database security, I would
> think twice before adding a "layer" on top of them. There is potential
> for error and you dont want that where security is related. It is
> probably worth the "pain" of learning the ins and outs of
> GRANT/REVOKE.

I wanted to create a tool that manages these privileges with a simple
interface. To me, GRANT is a privilege like others are. I know that
GRANT is a very powerful privilege but it's still a privilege and I
don't want to handle same things differently, that's just stupid as for
the application design. Not sure what the SQL inventors thought about it
some decades ago, where computers didn't have to be easy to handle.

I won't make much usage of that special privilege anyway, I'll mostly
allow single users read/write/definition access to single databases. But
when I want to revoke all privileges from a user, I also want the GRANT
privilege to be revoked, and for this I currently need a second command.
Well, doesn't matter, SQL is complicated and that's what my tool works
around:

# mysqlmgr add database www1
# mysqlmgr add user www1 password xxx
# mysqlmgr allow user www1 database www1 read write define

;)

-- 
Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]>
"Does the movement of the trees make the wind blow?"
http://newsboard.unclassified.de - Unclassified NewsBoard Forum

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: REVOKE GRANT OPTION - is that possible?

[Imran Chaudhry]

Yes, and from database and table level too, but you must revoke GRANT
OPTION seperately from all other privs like this:

REVOKE GRANT OPTION ON .FROM user

GRANT and REVOKE are essential to your database security, I would
think twice before adding a "layer" on top of them. There is potential
for error and you dont want that where security is related. It is
probably worth the "pain" of learning the ins and outs of
GRANT/REVOKE.

Hope that Helps,

Imran Chaudhry




--
http://www.EjectDisc.com
Get your Digital Identity - Domain Names, Web Space, E-mail & More!

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: REVOKE GRANT OPTION - is that possible?

[Dan Fulbright]

> How can I revoke a GRANT OPTION on a database or table from a user?
> 
> The manual  says
> something about
> 
> REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...
> 
> but this can only revoke the GRANT OPTION on a global level as it seems,
> not on a certain database or table. Trying to use this query
> 
> REVOKE ALL PRIVILEGES, GRANT OPTION ON `test`.* FROM 'test'@'localhost'
> 
> fails with the error message
> 
> You have an error in your SQL syntax; check the manual that
> corresponds to your MySQL server version for the right syntax to use
> near 'ON `test`.* FROM 'test'@'localhost'' at line 1

It works as two separate queries:

REVOKE ALL PRIVILEGES ON `test`.* FROM 'test'@'localhost';
REVOKE GRANT OPTION ON `test`.* FROM 'test'@'localhost';

-- 
Dan Fulbright

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

REVOKE GRANT OPTION - is that possible?

[Yves Goergen]

Hi,

I'm currently creating a PHP commandline tool to manage MySQL databases
and user privileges easily since the whole thing is way to complex to do
it by hand with SQL queries and you have nothing else right after
installing the MySQL server on an SSH-only machine where the webserver
depends on the database. So I'm close to finished now, but there's one
major point that seems to be impossible or at least undocumented:

How can I revoke a GRANT OPTION on a database or table from a user?

The manual  says
something about

REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...

but this can only revoke the GRANT OPTION on a global level as it seems,
not on a certain database or table. Trying to use this query

REVOKE ALL PRIVILEGES, GRANT OPTION ON `test`.* FROM 'test'@'localhost'

fails with the error message

You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'ON `test`.* FROM 'test'@'localhost'' at line 1

I could of course modify the relevant part of the mysql.db table myself
but I felt this important task should be possible the standard way just
as I can grant the GRANT OPTION directly on a database or table.

PS: The whole thing must be compatible with MySQL 4.0 through 5.0.

-- 
Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]>
"Does the movement of the trees make the wind blow?"
http://newsboard.unclassified.de - Unclassified NewsBoard Forum

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]