MySQL User Account Management

[jordanpope]

I am attempting to set up a user account (that can be used from a php application) that has SELECT privileges on all tables of a given database, and CREATE, DROP, and INSERT on only those tables that the user actually creates (ie. the user can CREATE new tables, and INSERT and DROP on those tables - but not INSERT and DROP on the other tables).

How can I grant INSERT and DROP on tables that don't yet exist?

Thanks

 

[sleipnir214]

You can't. MySQL doesn't support the concept of table ownership.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[lebisol]

"Want the best answers? Ask the best questions"
I wonder..........IF this should be the other way arround!

A: Of Course u can

To set up this user's privileges using GRANT statements, run these commands: 

shell> mysql --user=root mysql mysql> GRANT ALL PRIVILEGES ON *.* TO monty@localhost IDENTIFIED BY 'some_pass' WITH GRANT OPTION; mysql> GRANT ALL PRIVILEGES ON *.* TO monty@"%" IDENTIFIED BY 'some_pass' WITH GRANT OPTION; mysql> GRANT RELOAD,PROCESS ON *.* TO admin@localhost; mysql> GRANT USAGE ON *.* TO dummy@localhost; 

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON bankaccount.*
TO custom@localhost
IDENTIFIED BY 'stupid';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON expenses.*
TO custom@whitehouse.gov
IDENTIFIED BY 'stupid';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON customer.*
TO custom@'%'
IDENTIFIED BY 'stupid';



THEN flush the privs for them to take effect

Posted by Daniel Seltzer at December 1, 2001 10:37 PM 
[code]
or if ur a GUI lover (*hint hint)
[URL unfurl="true"]http://ems-hitech.com/mymanager/[/URL]

All the best!

> need more info? 
:: don't click HERE ::

 

[jordanpope]

Thanks for your response. Hopefully you can give me a little bit of an explanation for each of these commands ... I'm at a bit of a loss for how this solves my problem. Thanks again for your help.

 

[lebisol]

LOL...this was a snip from someone elses post.
u asked:
I am attempting to set up a user account (that can be used from a php application) that has SELECT privileges ....
this post
-----------
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON bankaccount.*
TO custom@localhost
----------
priviledges(see above) granted TO user CUSTOM on server LOCALHOST on databse bankaccount


I HATE shell commans so I use:

http://mysqlfront.sstienemann.de/

how to use it?
=

http://www.splatt.it/phpWebNotes/EN-Book/mysql-frontend.php

or EMS Manager

http://ems-hitech.com/mymanager/

I hope this helps!
All the best!

> need more info?
:: don't click HERE ::

 

[jordanpope]

I understand the individual commands, but I don't see how they work together to grant insert and drop only on tables that the user has created ... and I would prefer to work from the command line.

 

[lebisol]

hello Jordan!
Yes, you are right commands are much better and faster to execute...unfortunately my skills are not there yet (my production server is hosted...so u can imagine the limitations....hence my love for GUI)
anyhow, try to follow this post and the links suggested by the users:

http://forums.devshed.com/t45363/s.html

All the best!

> need more info?
:: don't click HERE ::

 

[lebisol]

please note yet antoher freeware ;-)
picture is worth a million words they say....
have fun!

http://mostarnet.com/MySQL.asp

All the best!


> need more info?
:: don't click HERE ::

 

[lebisol]

correction....not free as it used to be:

http://www.webyog.com/index.html

> need more info?
:: don't click HERE ::