Hi!
On Mon 2003-03-10 at 16:44:40 -0500, [EMAIL PROTECTED] wrote:
[...]
> - Given a system user 'junk' I would like to create a "master" user
> 'junk' that can have all permissions (including grant?) on all 'junk_*'
> databases so that he can create and manage his own databases
>
> - Given a "master" user 'junk' I would like for that user to be able to
> create other users 'junkNN' or or 'junk-*' or whatever so that my good
> friend junk is not always acting as "root".
>
> I have tried
>
> mysql> grant all privileges on junk_* to [EMAIL PROTECTED] identified by 'junk';
> ERROR 1064: You have an error in your SQL syntax near '* to [EMAIL PROTECTED]
> identified by 'junk'' at line 1
> mysql> grant all privileges on junk_.* to [EMAIL PROTECTED] identified by 'junk';
> Query OK, 0 rows affected (0.24 sec)
>
> and so I guess the way to specify database matching is with pure regexp
> notation of '.*' for 'any character any number of times',
No, it does not use REGEXPs at all. The second statement was
interpreted as 'databasename_'.'*', i.e. '.' is the seperator between
database name and table name. Here, '*' is not really a joker
(although it has some of its effects), but simply a placeholder
specific to the GRANT command. That means you have granted user junk
access to all tables within the database 'junk_' (where '_' is a joker
meaning any single char, like '.' in REGEXP).
I am not sure from my mind whether SQL jokers ('%') work within GRANT,
but if they do, the first statement should have looked something like
that:
GRANT ALL PRIVILEGES ON 'junk\\_%'.* TO [EMAIL PROTECTED] IDENTIFIED BY 'junk';
(maybe more or less backslashes needed)
> mysql> select * from mysql.db where user = 'junk' ;
> | localhost | junk_ | junk | Y | Y | Y | Y
> | Y | Y | N | Y | Y | Y |
> mysql> select * from mysql.user where user = 'junk' ;
> | localhost | junk | 372b3ff6545565e4 | N | N | N |
> N | N | N | N | N | N |
> N | N | N | N | N |
[...]
> but I can't create a database
>
> mysql> create database junk_testing ;
> ERROR 1044: Access denied for user: '[EMAIL PROTECTED]' to database 'junk_testing'
Aside from the name mismatch, creating databases is a global
privilege, i.e. database privileges are not evaluated for a
not-yet-existing database. Someone correct me, if I am wrong.
> bash-2.05a$ mysql -ujunk -pjunk
> Welcome to the MySQL monitor. Commands end with ; or \g.
> mysql> use junk_test ;
> ERROR 1044: Access denied for user: '[EMAIL PROTECTED]' to database 'junk_test'
Jupp, see above.
> Maybe I have to re-grant, too. Grrr...
No, grants are evaluated when they are needed, not before. I.e. a
database/table whatever has not to exist at GRANT-time for the
privileges to work later.
> OK, now I'm just mad :-) Let's try granting to this specific database
> just to get things going.
>
> bash-2.05a$ mysql -u droot -p
> Enter password:
> Welcome to the MySQL monitor. Commands end with ; or \g.
> mysql> grant all privileges on junk_test to junk ;
> ERROR 1046: No Database Selected
> mysql> use mysql ;
> Database changed
That should have got you thinking. :-)
Of course, the grant command implicitly changes the mysql tables, but
you don't have to use them in order to use GRANT. GRANT is an
abstract, generic interace to whatever privilege system an RDBMS has.
I bet the error you get is due to 'junk_test' having no database
specifier ("junk_test" instead of "junk_test.*" and therefore MySQL is
trying to use junk_test as table name with the default database
currently used. I.e. I guess you just changed access rights for
"mysql.junk_test". Try to see what mysql.db contains now...
> mysql> grant all privileges on junk_test to junk ;
> Query OK, 0 rows affected (0.02 sec)
> mysql> quit
> Bye
> bash-2.05a$ mysql -ujunk -pjunk
> Welcome to the MySQL monitor. Commands end with ; or \g.
> mysql> use junk_test ;
> ERROR 1044: Access denied for user: '[EMAIL PROTECTED]' to database 'junk_test'
... and it would explain that error perfectly. Btw, there is a nice
tool, mysqlaccess, which tells you a bit about the privileges a user
has.
> Not only am I a little confused about having had to use a database before
Seems you were not confused enough to see. :-)
> I could grant privs, but it didn't work anyway!
>
> I give up. Haaaalp! :-)
I hope the above hints get you going.
Bye,
Benjamin.
--
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
