create a mysql user .....

who is able to create and drop its own databases, but no access to other db

[st2xo]

Is there any possibilty to create a non-root mysql user who has:

- the right to create dbs
- browse and drop its own dbs
- no access to dbs from other users

I read almost 3 hours in docs, made some changes in the user and db tables from the mysql database
with only one result: no result

Already an answer of my question whether there is a possibility or not would help me very much.

Or is there is anybody who knows a way to create such a user?

thanks, Stefan

[jatar_k]

st2xo,

The only answer I would have is to look through the documentation and it seems you've done that quite thoroughly already.

Anyone else have any insight on this?

[andreasfriedrich]

Creating a user [mysql.com] with
- the right to create dbs
- no access to dbs from other users
is easy. This user should have no super-user privileges.

select * from user where User='apache';
Host = localhost
User = apache
Password = I ainīt tellinī
Select_priv = Y
Insert_priv = Update_priv = Delete_priv = Create_priv = Drop_priv = Reload_priv = Shutdown_priv = Process_priv = File_priv = Grant_priv = References_priv = Index_priv = Alter_priv = N

As you can see all apache is allowed is to run SELECT queries. Even that might be too much on this global level.

Creating a user with the right to
- browse and drop its own dbs
is a bit harder but can be done as well as a careful read of the documentation reveals:

The wildcard characters '%' and '_' can be used in the Host and Db fields of either table.

[...]

The server looks in the db table for a match on the Host, Db, and User fields. The Host and User fields are matched to the connecting user's hostname and MySQL user name. The Db field is matched to the database the user wants to access. If there is no entry for the Host and User, access is denied.
If there is a matching db table entry and its Host field is not blank, that entry defines the user's database-specific privileges.
If the matching db table entry's Host field is blank, it signifies that the host table enumerates which hosts should be allowed access to the database.

[mysql.com ]

With this knowledge we insert a new row into the db table:

insert into db set Host='localhost', Db='aaron%', User='apache', Select_priv ='Y', Insert_priv ='Y', Update_priv ='Y', Delete_priv ='Y', Create_priv ='Y', Drop_priv ='Y', Grant_priv ='Y', References_priv ='Y', Index_priv ='Y', Alter_priv='Y';

As you can see the Db field contains the percent sign wildcard character. This row will apply to all databases whose name begins with aaron. Letīs see this setting in action (Be sure to flush privileges before testing this!):

mysql> create database apachetest;
ERROR 1044: Access denied for user: 'apache@localhost' to database 'apachetest'
mysql> create database aarontest;
Query OK, 1 row affected (0.02 sec)
mysql> drop database aarontest;
Query OK, 0 rows affected (0.00 sec)

As you can see now even the powerless apache user may create a new database.

There might be more elegant ways to do just that but my approach works. So I guess it is ok.

Hope this helps.

Andreas

[st2xo]

hi,

The hint with the wildcards works fine, thanks so far for the detailed description! Iīd posted messages in some other forums, with the same hints. Of course it works - except of browsing the databases.

In Mysql (3.23.39, Red Hat) only root can browse the databases ("show databases"). Other users will get an error: "ERROR 1045: Access denied for user: 'foo@localhost' (Using password: YES"

In [e-mail] somebody posted a mysql 4 command "--safe-show-database". With this privilege the user see his own, and only his own databases (search for topic "found bug" - this topic is related to phpmyadmin, not to my origin question).

But the privilige to create and drop databases for some users is enough for me. I can wait for mysql 4 running on my machine ;)

Stefan