Re: Grants for own objects ?
Lentes, Bernd skrev: hello ML, i'm new to MySQL, so i have a very basic question. I have to install a database server for about 15 persons. The server is intended for testing and evaluating. The users should be able to create their own databases and tables.. And they should be able to give grants on their own objects to other users. Following the recommendation in a MySQL-Book, i inserted the following line in the db table: [...] localhost | lentes\_% | lentes | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y| Y| Y | Y | Y | Y [...] That means that the user lentes can create databases, where the name begins with lentes_ . This works. Being logged in as lentes, i'm able to create a databse called lentes_1. But i'm not able to give grants to other users: mysql grant select on lentes_1.* to 'eitz'@'localhost'; ERROR 1044 (42000): Access denied for user 'lentes'@'localhost' to database 'lentes_1' Uer lentes has no global privileges. How can i achieve that users are able to create their own databases/tables and to assign grants for their own databases/tables ? First, don't mess around with the grant tables. Many years ago, that was indeed the way to control user access, but things have progressed since then. How old is that MySQL book? Remove the manual edits you have made to the grant tables, and use only GRANT and REVOKE. I believe this will do what you want: CREATE USER lentes@localhost; GRANT ALL ON lentes_%.* TO lentes@localhost WITH GRANT OPTION; Hth, / Carsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Grants for own objects ?
Carsten Pederseb wrote:
First, don't mess around with the grant tables. Many years
ago, that was indeed the way to control user access, but
things have progressed since then. How old is that MySQL book?
It's from 2005 and about MySQL-version 5.
Remove the manual edits you have made to the grant tables,
and use only GRANT and REVOKE. I believe this will do what you want:
I did.
CREATE USER lentes@localhost;
GRANT ALL ON lentes_%.* TO lentes@localhost WITH GRANT OPTION;
Using the GRANT, i get an error:
GRANT ALL ON lentes_%.* TO lentes@localhost WITH GRANT OPTION;
ERROR 1064 (42000): 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
'lentes_%.* TO lentes@localhost WITH GRANT OPTION' at line 1
The book says it's not possible to use metacharacters like % with GRANT.
If i insert the line manually in the db-table, it works:
insert into db (host, db, User, select_priv, insert_priv, update_priv,
delete_priv, create_priv, drop_priv, grant_priv, references_priv, index_priv,
alter_priv, create_tmp_table_priv, lock_tables_priv, create_view_priv,
show_view_priv, create_routine_priv, alter_routine_priv, execute_priv) values
('localhost', 'lentes%', 'lentes', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y',
'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y');
This should be the same as using grant ?
As user lentes, i'm now able to create a database called e.g. lentes_1 :
mysql create database lentes_1;
Query OK, 1 row affected (0.01 sec)
And now i can assign grants on this database to other users:
mysql grant select on lentes_1.* to 'eitz'@'localhost';
Query OK, 0 rows affected (0.00 sec)
Bernd
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Grants for own objects ?
On Wed, Apr 21, 2010 at 2:39 PM, Lentes, Bernd bernd.len...@helmholtz-muenchen.de wrote: The book says it's not possible to use metacharacters like % with GRANT. The book is wrong. You have to use backticks to quote the lentes_% part, though - it's an annoying quirk. Please ritually burn the book and provide humorous pictures of the event in order to receive further support :-) -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
RE: grants
Grant user for slave: GRANT REPLICATION SLAVE, REPLICATION CLIENT, RELOAD, SUPER on *.* TO 'username'@'IP Slave' IDENTIFIED BY 'password'; ToanDA -Original Message- From: Ananda Kumar [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 03, 2007 8:59 PM To: MySQL General Subject: grants Hi All, We have setup replication for our production database. We need to do monitoring of the slave and master. I created a user with only SELECT privileges, and when i do show master status on master db, its saying Access denied; you need the SUPER,REPLICATION CLIENT privilege for this operation. Is it necessary to grant SUPER privilages, or can i do it in some other way, without super privilages. This user is just for monitoring. regards anandkl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Grants for mysqlbackup
Hi i am using mysql-4.1.3-beta version in mysq server my concern is that i want to create a user(minimal grant) that should able to take backup using mysqldump ...can any one help me out .. i tried giving the following grants but it is not works grant file,SHOW DATABASES,select,lock tables on *.* to [EMAIL PROTECTED] by 'pass'; regards Anand
Re: GRANTS for tables - why is create possible?
Hello. In my opinion, it means that user [EMAIL PROTECTED] is able to create table address in your current database (SHOW GRANTS shows that the name of the database is implicitly added to the table name). Martijn Tonies [EMAIL PROTECTED] wrote: Hi there, What is the purpose of this GRANT statement? GRANT CREATE ON tablename TO [EMAIL PROTECTED]; eg: GRANT CREATE ON address TO [EMAIL PROTECTED]; It's possible, but what is it supposed to do? I can understand this grant on a global (server) and database level, but on a table level? With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Grants not entirely propagated to slaves?
Atle Veka wrote: What version of MySQL are you using? Also, are you issuing only GRANT .. statements or modifying the privilege tables manually as well? Search for 'GRANT': http://dev.mysql.com/doc/mysql/en/replication-features.html Atle - Flying Crocodile Inc, Unix Systems Administrator On Fri, 1 Apr 2005, Nico Sabbi wrote: hi, my mysql is a 4.0.21. After a flush privileges I can see all granted accesses. Thanks for you help! Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Grants not entirely propagated to slaves?
Hello. If you replicate the privilege tables in the mysql database and update those tables directly without using the GRANT statement, you must issue a FLUSH PRIVILEGES statement on your slaves to put the new privileges into effect. Nico Sabbi [EMAIL PROTECTED] wrote: Hi, it seems my Grants are not entirely propagated from the master to the slave (some are active, some are not). The slave is configured to replicate all databases, and the replication client has all privileges on the master. What is necessary to propagate every single grant? Thanks, -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Grants not entirely propagated to slaves?
What version of MySQL are you using? Also, are you issuing only GRANT .. statements or modifying the privilege tables manually as well? Search for 'GRANT': http://dev.mysql.com/doc/mysql/en/replication-features.html Atle - Flying Crocodile Inc, Unix Systems Administrator On Fri, 1 Apr 2005, Nico Sabbi wrote: Hi, it seems my Grants are not entirely propagated from the master to the slave (some are active, some are not). The slave is configured to replicate all databases, and the replication client has all privileges on the master. What is necessary to propagate every single grant? Thanks, -- Nico Sabbi - Officine Digitali - Bologna Tel. 051 - 4187565 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Grants
Well i think i found a bug or a change un behavior between mysql 5.0.0 ans mysql 5.0.1 The behavior i describe does not happen. Can anybody else confirm this ? Thanks Yann On September 22, 2004 21:51, Yann Larrivée wrote: Hi, I juste installed mysq. 5.1 (alpha) and i guess i am facing some really stupid issue :) I logged in as root without probleme create a database xoops which shows when i do a show databases Now i try to give grants to a user on that datase via grant all on xoops.* to [EMAIL PROTECTED] identified by 'something'; But i keep getting this error ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'xoops' Usually this works well, unless i am too sleepy to see my mistake :) What could be the probleme ? Thanks Yann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: grants to multiple DBs at once
Hi Michael,
Thanks for the reply.
It doesn't work, at least not with 4.0.18. I also tried without
the _ to simplify the case, but the grant statement isn't wild about
the wildcard. :)
The docs you list are for 4.1. I guess I will have to wait
until 4.1. :)
In the meantime, this works well:
SYSTEM rm /var/mysql/tmp/grants-tmp.sql;
SELECT DISTINCT
concat('GRANT SELECT ON ',db,'.* TO [EMAIL PROTECTED]'%\' IDENTIFIED BY \'abc\';'),
concat('GRANT SELECT ON ',db,'.* TO [EMAIL PROTECTED]'%\' IDENTIFIED BY \'def\';'),
concat('GRANT SELECT ON ',db,'.* TO [EMAIL PROTECTED]'%\' IDENTIFIED BY \'ghi\';')
FROM db WHERE db LIKE 'dev\_%' INTO OUTFILE '/var/mysql/tmp/grants-tmp.sql';
SOURCE /var/mysql/tmp/grants-tmp.sql;
Brian
==
On Thu, Sep 16, 2004 at 05:28:51PM -0400, Michael Stassen wrote:
This is documented in the manual:
Note: the '_' and '%' wildcards are allowed when specifying database
names in GRANT statements that grant privileges at the global or
database levels.
http://dev.mysql.com/doc/mysql/en/GRANT.html
So, if dev is the prefix, you need:
GRANT SELECT ON 'dev%'.* TO [EMAIL PROTECTED]
If dev_ is your prefix, the next line from the manual is relevant:
This means, for example, that if you want to use a '_' character as
part of a database name, you should specify it as '\_' in the GRANT
statement, to prevent the user from being able to access additional
databases matching the wildcard pattern; for example,
GRANT ... ON 'foo\_bar'.* TO
Then you would need
GRANT SELECT ON 'dev\_%'.* TO [EMAIL PROTECTED]
Michael
Brian C. Hill wrote:
I have about 20 DB's with the same prefix.
How can I do something like
GRANT SELECT ON dev_*.*
I have seen examples for the _other_ DB software, like msql,
that does something like:
SELECT 'GRANT SELECT ON '+name+' TO webuser;'
from sysobjects
where type = 'U'
(which generates the grant statements to run)
Is this possible in mysql? Maybe something like:
SELECT 'GRANT SELECT ON '+name+' TO webuser;'
from `show databases like 'dev_%'`
I know that sub-queries aren't possible, but does anyone have
any suggestions that don't involve weighty shell scripts?
Is there anyway to write show databases to file
without the bordering box?
Brian
--
_
/ Brian C. Hill [EMAIL PROTECTED] http://brian.bch.net\
| UNIX Specialist BCH Technical Services http://www.bch.net |
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: grants to multiple DBs at once
That's my fault. It does work with 4.0.18. (The manual is usually pretty
good at specifying when something is 4.1 only, by the way.) You need to
quote the db string with backticks, not single quotes (as I did in my
example). So, you would use
GRANT SELECT ON `dev\_%`.* TO [EMAIL PROTECTED]
Sorry to have led you astray.
Michael
Brian C. Hill wrote:
Hi Michael,
Thanks for the reply.
It doesn't work, at least not with 4.0.18. I also tried without
the _ to simplify the case, but the grant statement isn't wild about
the wildcard. :)
The docs you list are for 4.1. I guess I will have to wait
until 4.1. :)
In the meantime, this works well:
SYSTEM rm /var/mysql/tmp/grants-tmp.sql;
SELECT DISTINCT
concat('GRANT SELECT ON ',db,'.* TO [EMAIL PROTECTED]'%\' IDENTIFIED BY \'abc\';'),
concat('GRANT SELECT ON ',db,'.* TO [EMAIL PROTECTED]'%\' IDENTIFIED BY \'def\';'),
concat('GRANT SELECT ON ',db,'.* TO [EMAIL PROTECTED]'%\' IDENTIFIED BY \'ghi\';')
FROM db WHERE db LIKE 'dev\_%' INTO OUTFILE '/var/mysql/tmp/grants-tmp.sql';
SOURCE /var/mysql/tmp/grants-tmp.sql;
Brian
==
On Thu, Sep 16, 2004 at 05:28:51PM -0400, Michael Stassen wrote:
This is documented in the manual:
Note: the '_' and '%' wildcards are allowed when specifying database
names in GRANT statements that grant privileges at the global or
database levels.
http://dev.mysql.com/doc/mysql/en/GRANT.html
So, if dev is the prefix, you need:
GRANT SELECT ON 'dev%'.* TO [EMAIL PROTECTED]
If dev_ is your prefix, the next line from the manual is relevant:
This means, for example, that if you want to use a '_' character as
part of a database name, you should specify it as '\_' in the GRANT
statement, to prevent the user from being able to access additional
databases matching the wildcard pattern; for example,
GRANT ... ON 'foo\_bar'.* TO
Then you would need
GRANT SELECT ON 'dev\_%'.* TO [EMAIL PROTECTED]
Michael
Brian C. Hill wrote:
I have about 20 DB's with the same prefix.
How can I do something like
GRANT SELECT ON dev_*.*
I have seen examples for the _other_ DB software, like msql,
that does something like:
SELECT 'GRANT SELECT ON '+name+' TO webuser;'
from sysobjects
where type = 'U'
(which generates the grant statements to run)
Is this possible in mysql? Maybe something like:
SELECT 'GRANT SELECT ON '+name+' TO webuser;'
from `show databases like 'dev_%'`
I know that sub-queries aren't possible, but does anyone have
any suggestions that don't involve weighty shell scripts?
Is there anyway to write show databases to file
without the bordering box?
Brian
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: grants to multiple DBs at once
- Original Message - From: Brian C. Hill [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, September 16, 2004 4:22 PM Subject: grants to multiple DBs at once I have about 20 DB's with the same prefix. How can I do something like GRANT SELECT ON dev_*.* I have seen examples for the _other_ DB software, like msql, that does something like: SELECT 'GRANT SELECT ON '+name+' TO webuser;' from sysobjects where type = 'U' (which generates the grant statements to run) Is this possible in mysql? Maybe something like: SELECT 'GRANT SELECT ON '+name+' TO webuser;' from `show databases like 'dev_%'` I know that sub-queries aren't possible, but does anyone have any suggestions that don't involve weighty shell scripts? Is there anyway to write show databases to file without the bordering box? I don't know what you mean by weighty but this code excerpt is from a Bash shell script that I use for taking database backups. The 'for' line can probably be adapted to do what you want; it writes the names of all of the databases to the variable ONE_DBNAME: USERID=foo; #The userid to use for creating the backup PASSWORD=bar; #The password to use for creating the backup #For each database currently in MySQL, take a database-level backup. for ONE_DBNAME in `echo show databases | mysql -s -u $USERID -p$PASSWORD` do echo echo Backing up database $ONE_DBNAME; # do whatever you want to do done Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: grants to multiple DBs at once
This is documented in the manual: Note: the '_' and '%' wildcards are allowed when specifying database names in GRANT statements that grant privileges at the global or database levels. http://dev.mysql.com/doc/mysql/en/GRANT.html So, if dev is the prefix, you need: GRANT SELECT ON 'dev%'.* TO [EMAIL PROTECTED] If dev_ is your prefix, the next line from the manual is relevant: This means, for example, that if you want to use a '_' character as part of a database name, you should specify it as '\_' in the GRANT statement, to prevent the user from being able to access additional databases matching the wildcard pattern; for example, GRANT ... ON 'foo\_bar'.* TO Then you would need GRANT SELECT ON 'dev\_%'.* TO [EMAIL PROTECTED] Michael Brian C. Hill wrote: I have about 20 DB's with the same prefix. How can I do something like GRANT SELECT ON dev_*.* I have seen examples for the _other_ DB software, like msql, that does something like: SELECT 'GRANT SELECT ON '+name+' TO webuser;' from sysobjects where type = 'U' (which generates the grant statements to run) Is this possible in mysql? Maybe something like: SELECT 'GRANT SELECT ON '+name+' TO webuser;' from `show databases like 'dev_%'` I know that sub-queries aren't possible, but does anyone have any suggestions that don't involve weighty shell scripts? Is there anyway to write show databases to file without the bordering box? Brian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: grants for non-root
I have MySQL 4.0.18-standard on RH 9 and it works fine + mysql grant all on *.* to 'mysqladmin'@'localhost'; Query OK, 0 rows affected (0.06 sec) mysql show grants for 'mysqladmin'@'localhost'; +-+ | Grants for [EMAIL PROTECTED] | +-+ | GRANT ALL PRIVILEGES ON *.* TO 'mysqladmin'@'localhost' | +-+ 1 row in set (0.00 sec) + I would recommend checking the user table in mysql database and see what change your GRANT query did, if any. Let us see what it looks like, if there is any entry for the user you are GRANTing privileges. Regards Aman On Fri, 2004-07-23 at 09:50, Wolfgang Riedel wrote: I try to set *all* privileges to a user, which should not called 'root' but should have its privileges (I'm connected as root): mysql grant all on *.* to 'mysqladmin'@'localhost'; Query OK, 0 rows affected (0.00 sec) But now I get mysql show grants for 'mysqladmin'@'localhost'; ERROR 1141: There is no such grant defined for user 'mysqladmin' on host 'localhost' What does it mean? What's wrong? The problem occurs with MySQL-4.0.20 at Linux Fedora, it does not occur with MySQL-3.23. Thanks for any help. Regards, Wolfgang -- Aman Raheja Linux+ Certified [EMAIL PROTECTED] Brainbench Certified Linux (General) Admin www.TechQuotes.comBrainbench Certified Linux (RedHat 9) Admin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: grants for START/STOP SLAVE
At 11:31 -0500 1/15/04, Mike Johnson wrote: This is in reference to 4.0.16, FWIW... I can't find any direct references to the actual grant permission for controlling a slave thread in the online docs. The closest I saw was the 5th paragraph on http://www.mysql.com/doc/en/Replication_HOWTO.html. It would seem logical that it's covered by either REPLICATION CLIENT or REPLICATION SLAVE, but not so. Anyway, my experimentation leads me to believe that it's controlled by the SUPER privilege. The online docs don't mention these in the description, only this: Allows one connect (once) even if max_connections is reached and execute commands CHANGE MASTER, KILL thread, mysqladmin debug, PURGE MASTER LOGS and SET GLOBAL. Paul DuBois' 2nd ed. of MySQL 4 (the purple book) doesn't mention them directly in the description of SUPER, either (pages 682 and 869). Hmm...you're right. I just looked in the source, and it's the SUPER privilege that's needed for START SLAVE and STOP SLAVE. I've updated the manual to indicate this: http://www.mysql.com/doc/en/START_SLAVE.html http://www.mysql.com/doc/en/STOP_SLAVE.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: grants for START/STOP SLAVE
From: Paul DuBois [mailto:[EMAIL PROTECTED] At 11:31 -0500 1/15/04, Mike Johnson wrote: This is in reference to 4.0.16, FWIW... I can't find any direct references to the actual grant permission for controlling a slave thread in the online docs. The closest I saw was the 5th paragraph on http://www.mysql.com/doc/en/Replication_HOWTO.html. It would seem logical that it's covered by either REPLICATION CLIENT or REPLICATION SLAVE, but not so. Anyway, my experimentation leads me to believe that it's controlled by the SUPER privilege. The online docs don't mention these in the description, only this: Allows one connect (once) even if max_connections is reached and execute commands CHANGE MASTER, KILL thread, mysqladmin debug, PURGE MASTER LOGS and SET GLOBAL. Paul DuBois' 2nd ed. of MySQL 4 (the purple book) doesn't mention them directly in the description of SUPER, either (pages 682 and 869). Hmm...you're right. I just looked in the source, and it's the SUPER privilege that's needed for START SLAVE and STOP SLAVE. I've updated the manual to indicate this: http://www.mysql.com/doc/en/START_SLAVE.html http://www.mysql.com/doc/en/STOP_SLAVE.html Hey, thanks. Now that's service. :) -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GRANTs Options
At 18:47 -0500 6/19/03, Miguel Perez wrote: Hi everyone: I wonder if the command GRANT could be alered. I mean if I have the following privileges over certain database: +--+ | Grants for [EMAIL PROTECTED] | +--+ | GRANT USAGE ON test.* TO 'olap'@'%' IDENTIFIED BY PASSWORD 'helloworld' | | GRANT SELECT, INSERT, DELETE ON products.* TO 'olap'@'%' | +--+ And I would like to change the privileges over the database test and then give privileges for the database products. Is there a way to do that?. I just want to take off the privileges from the database test and give new permission to the database products. In what way do you want the GRANT statement to be altered? Use REVOKE to remove the USAGE privilege on test: REVOKE USAGE ON test.* FROM 'olap'@'%'; Then use GRANT to add the privileges that you want to add to the products database. Thnx. Greetings everyone -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GRANTs Options
Thnx for the hint Paul, I will do that. Regards.. From: Paul DuBois [EMAIL PROTECTED] To: Miguel Perez [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: GRANTs Options Date: Thu, 19 Jun 2003 18:57:15 -0500 At 18:47 -0500 6/19/03, Miguel Perez wrote: Hi everyone: I wonder if the command GRANT could be alered. I mean if I have the following privileges over certain database: +--+ | Grants for [EMAIL PROTECTED] | +--+ | GRANT USAGE ON test.* TO 'olap'@'%' IDENTIFIED BY PASSWORD 'helloworld' | | GRANT SELECT, INSERT, DELETE ON products.* TO 'olap'@'%' | +--+ And I would like to change the privileges over the database test and then give privileges for the database products. Is there a way to do that?. I just want to take off the privileges from the database test and give new permission to the database products. In what way do you want the GRANT statement to be altered? Use REVOKE to remove the USAGE privilege on test: REVOKE USAGE ON test.* FROM 'olap'@'%'; Then use GRANT to add the privileges that you want to add to the products database. Thnx. Greetings everyone -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ _ Únete al mayor servicio mundial de correo electrónico: http://www.hotmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Grants UGGH! Not working for some reason ...
if you don't restart MySQL after changing the grants priviliges, you have to issue the flush privileges command from the MySQL Monitor. If you don't do either one your privilege changes will not take affect. not sure if when you said reloaded you meant flush privileges but if not try it and see how it goes. hope that helps, Don -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 09, 2002 12:02 PM To: [EMAIL PROTECTED] Subject: Grants UGGH! Not working for some reason ... Hi all! I have a user that I am specifying for one database within the system. I have granted, reloaded, deleted, re-done, and screamed at the server. None the less, it still will not allow this user to do mysqlimport, as follows; mysqlimport --user=rcr_user --password=thepassword -f --fields-terminated-by =, rcr tblCDR.tmp mysqlimport: Error: Access denied for user: 'rcr_user@localhost' (Using password: YES), when using table: tblCDR I have 3 rcr_users now, one at 'localhost' one at '127.0.0.1' and one at 'theservername'. All have been granted select, insert, update, delete on the database in question. I also have this user in a PHP script that is able to do an INSERT INTO from the script that works fine. So, have I lost it completely? (I knew that this day would come :^) ) Any hints? I have read everything at http://www.mysql.com, I have looked all through my trusty MySQL book, I have made offerings to the gawds of database(s). Thanks in advance... Jay sql, mysql, query - 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 - 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
RE: Grants UGGH! Not working for some reason ...
The user might need the FILE permission, I'm not sure. Also might need the LOCK TABLES permission. http://www.mysql.com/doc/G/R/GRANT.html - Johnny Withers [EMAIL PROTECTED] p. 601.853.0211 c. 601.209.4985 -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 09, 2002 11:23 AM To: [EMAIL PROTECTED] Subject: FW: Grants UGGH! Not working for some reason ... [snip] if you don't restart MySQL after changing the grants priviliges, you have to issue the flush privileges command from the MySQL Monitor. If you don't do either one your privilege changes will not take affect. not sure if when you said reloaded you meant flush privileges but if not try it and see how it goes. [/snip] That's what I meant. I have reloaded MySQL, flushed, stopped and started the server. The killer is that this user is able to access the database just hunky-dorie, do selects, inserts, deletes, updates. Thanks! Jay sql, mysql, query - 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 - 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
Re: Grants UGGH! Not working for some reason ...
Hi, - Original Message - From: Jay Blanchard [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, July 09, 2002 7:01 PM Subject: Grants UGGH! Not working for some reason ... Hi all! I have a user that I am specifying for one database within the system. I have granted, reloaded, deleted, re-done, and screamed at the server. None the less, it still will not allow this user to do mysqlimport, as follows; mysqlimport --user=rcr_user --password=thepassword -f --fields-terminated-by =, rcr tblCDR.tmp mysqlimport: Error: Access denied for user: 'rcr_user@localhost' (Using password: YES), when using table: tblCDR I have 3 rcr_users now, one at 'localhost' one at '127.0.0.1' and one at 'theservername'. All have been granted select, insert, update, delete on the ...this can be a problem.I think it should be work if you add and -h 'theservername' database in question. I also have this user in a PHP script that is able to do an INSERT INTO from the script that works fine. So, have I lost it completely? (I knew that this day would come :^) ) Any hints? I have read everything at http://www.mysql.com, I have looked all through my trusty MySQL book, I have made offerings to the gawds of database(s). Thanks in advance... Jay sql, mysql, query - 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 - 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
RE: Grants UGGH! Not working for some reason ...
[snip] if you don't restart MySQL after changing the grants priviliges, you have to issue the flush privileges command from the MySQL Monitor. If you don't do either one your privilege changes will not take affect. not sure if when you said reloaded you meant flush privileges but if not try it and see how it goes. [/snip] That's what I meant. I have reloaded MySQL, flushed, stopped and started the server. The killer is that this user is able to access the database just hunky-dorie, do selects, inserts, deletes, updates. Thanks! Jay sql, mysql, query If you use the GRANT command, you don't have to flush privileges. If you UPDATE,INSERT your own grant records, then you have to flush. Try granting FILE privilege. mysqlimport is LOAD DATA INFILE like, which needs the FILE privilege - 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
RE: Grants UGGH! Not working for some reason ...
[snip] I have a user that I am specifying for one database within the system. I have granted, reloaded, deleted, re-done, and screamed at the server. None the less, it still will not allow this user to do mysqlimport, ... [/snip] Figured it out. The user must have FILE privileges in order to execute items like mysqlimport from the command line or a script that generates command line like commands. Thanks! Jay sql, mysql, query - 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
RE: GRANTs and %
GRANT ALL ON newstesting.* TO 'clients'@'%' IDENTIFIED BY '123'; TO Simon -Original Message- From: Kaan Oglakci [mailto:[EMAIL PROTECTED]] Sent: 11 June 2002 09:05 To: '[EMAIL PROTECTED]' Subject: GRANTs and % Thanks Dan And Okan for replying to my email but I have tried what you have said but I still get the same problem. GRANT ALL ON newstesting.* 'clients'@'%' IDENTIFIED BY '123'; ERROR 1064: You have an error in your SQL syntax near ''clients'@'%' IDENTIFIED BY '123'' at line 1 GRANT ALL ON newstesting.* clients@'%' IDENTIFIED BY '123'; ERROR 1064: You have an error in your SQL syntax near 'clients@'%' IDENTIFIED BY '123'' at line 1 any ideas what is going on? MySQL is server version: 3.23.36 Thanks, Kaan - 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 - 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
Re: GRANTs and %
On Tue, 11 Jun 2002 09:04:36 +0100 Kaan Oglakci [EMAIL PROTECTED] wrote: Thanks Dan And Okan for replying to my email but I have tried what you have said but I still get the same problem. GRANT ALL ON newstesting.* 'clients'@'%' IDENTIFIED BY '123'; ERROR 1064: You have an error in your SQL syntax near ''clients'@'%' IDENTIFIED BY '123'' at line 1 try this : GRANT ALL ON newstesting.* to clients identified by '123'; -- Linux: Where Don't We Want To Go Today? -- Submitted by Pancrazio De Mauro, paraphrasing some well-known sales talk - 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
Re: GRANTs and %
On Tuesday 11 Jun 2002 11:04 am, Kaan Oglakci wrote: Thanks Dan And Okan for replying to my email but I have tried what you have said but I still get the same problem. GRANT ALL ON newstesting.* 'clients'@'%' IDENTIFIED BY '123'; ERROR 1064: You have an error in your SQL syntax near ''clients'@'%' IDENTIFIED BY '123'' at line 1 GRANT ALL PRIVILEGES ON newstesting.* TO 'clients'@'%' IDENTIFIED BY '123'; You're missing the 'TO'. Cheers, Markus -- Markus Lervik Linux-administrator Vaasa City Library - Regional Library, Finland [EMAIL PROTECTED] +358-6-325 3589/+358-40-832 6709 - 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
Re: GRANTs and %
Kaan, Tuesday, June 11, 2002, 11:04:36 AM, you wrote: KO Thanks Dan And Okan for replying to my email but I have tried what you have KO said but I still get the same problem. KO GRANT ALL ON newstesting.* 'clients'@'%' IDENTIFIED BY '123'; KO ERROR 1064: You have an error in your SQL syntax near ''clients'@'%' KO IDENTIFIED KO BY '123'' at line 1 KO GRANT ALL ON newstesting.* clients@'%' IDENTIFIED BY '123'; KO ERROR 1064: You have an error in your SQL syntax near 'clients@'%' KO IDENTIFIED BY KO '123'' at line 1 KO any ideas what is going on? You used a wrong GRANT syntax. You are missing 'TO' before user name. GRANT ALL ON newstesting.* TO 'clients'@'%' IDENTIFIED BY '123'; ^^ KO MySQL is server version: 3.23.36 KO Thanks, KO Kaan -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - 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
Re: GRANTs and %
In the last episode (Jun 10), Kaan Oglakci said: Hi, For some reason when I try to set up grants for a database by typing this GRANT ALL ON newstesting.* to clients@% IDENTIFIED BY 'pass'; I get this error ERROR 1064: You have an error in your SQL syntax near '% IDENTIFIED BY 'pass'' a t line 1 Try quoting the %: '%' In fact, to be safe, I always quote the username and the hostname: 'clients'@'%' -- Dan Nelson [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
Re: Grants, rights, permissions
Thomas, Wednesday, May 01, 2002, 9:39:24 PM, you wrote: TS I am having some trubble understanding, how permissions work with MySQL: TS I've set up user, database, and host permissions: TS User: Hosts:PW: Permissions: TS tps Any pwAll TS Anonymous 192.168.107.204 ''None TS Database: User: Hosts: Permissions: TS duwtAnonymous Any All TS Databases: Hosts: Permissions: TS Any 192.168.107.0/24 All TS Now, if I understand correctly I might connect to database duwt as user TS tps from any host. But trying this I can't connect to database duwt. TS Why? Yes, it should work, I tested. Check if there are any other conflicting entries in privilege tables. Seems, you gave incomplete info. If you set up privileges directly editing privileges tables don't forget to execute FLUSH PRIVILEGES. -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - 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
RE: Grants, rights, permissions
[snip] Now, if I understand correctly I might connect to database duwt as user tps from any host. But trying this I can't connect to database duwt. Why? [/snip] Did you flush your privileges either using MySQL monitor or mysqladmin? Jay Blanchard - 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
RE: Grants to a database table that doesn't exist yet
Hi Luc, I don't know if this will help you but you can do: GRANT DELETE,SELECT,UPDATE on qbslive.* TO . But this will give permission to ALL tables in qbslive database... Gurhan -Original Message- From: Luc Foisy [mailto:[EMAIL PROTECTED]] Sent: Friday, March 15, 2002 10:45 AM To: MYSQL-List (E-mail) Subject: Grants to a database table that doesn't exist yet Is there someway I can force a Grant to take if the database table doesn't exist yet? I have the database created, awaiting the data that is being dropped into it, but I want to set up privileges to it before it exists so its ready to go live in an instant mysql GRANT DELETE,SELECT,UPDATE on qbslive.PRINTJOB to PrintHandler@'192.168.0.250' identified by 'passwd'; ERROR 1146: Table 'qbslive.PRINTJOB' doesn't exist there would be several grant statements ( about 20, trying to keep the database controlled as much as possible) I suppose I could do this by editing the tables directly, there is just a bit more work involved ( user and tables_priv inserts ). I would really like the server to do the work though through the grant though I could create the table first, then make the grants, then delete the tables, BUT I have a slave replicating this database ( so when I load the data, it will be replicated right from the get go) and I would rather not have that unneccissary stuff in the bin logs. ( now I see I should have started the replication after I did all the messing around ) I don't want to stop anything and reconfigure it, cause I know its working now and I don't want to make something not work :) Anyways, can I force a GRANT on a table that doesnt exist? - 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 - 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
Re: Grants to a database table that doesn't exist yet
Luc, I think you have to do the manual inserts to make this work before the tables are there. I wrestled with this problem briefly, before writing a quick script. Scott Helms Director of Technology, ZCorum - Original Message - From: Luc Foisy [EMAIL PROTECTED] To: MYSQL-List (E-mail) [EMAIL PROTECTED] Sent: Friday, March 15, 2002 10:44 AM Subject: Grants to a database table that doesn't exist yet Is there someway I can force a Grant to take if the database table doesn't exist yet? I have the database created, awaiting the data that is being dropped into it, but I want to set up privileges to it before it exists so its ready to go live in an instant mysql GRANT DELETE,SELECT,UPDATE on qbslive.PRINTJOB to PrintHandler@'192.168.0.250' identified by 'passwd'; ERROR 1146: Table 'qbslive.PRINTJOB' doesn't exist there would be several grant statements ( about 20, trying to keep the database controlled as much as possible) I suppose I could do this by editing the tables directly, there is just a bit more work involved ( user and tables_priv inserts ). I would really like the server to do the work though through the grant though I could create the table first, then make the grants, then delete the tables, BUT I have a slave replicating this database ( so when I load the data, it will be replicated right from the get go) and I would rather not have that unneccissary stuff in the bin logs. ( now I see I should have started the replication after I did all the messing around ) I don't want to stop anything and reconfigure it, cause I know its working now and I don't want to make something not work :) Anyways, can I force a GRANT on a table that doesnt exist? - 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 - 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
Re: Grants question..
Everything works great, except that I can't access a database created by root as another user. When I grant privileges, from the command line or phpmyadmin, things seem to work fine (no error message), but access is still denied. I did restart MySQL after granting rights. You can also check the following. Open the mysql database (the 'real' mysql db, the one that's part of the distribution). Issue a 'select * from user' command and check if the 'another user' is really there with the correct settings for the privileges. Issue also a 'select * from db'. Peter van der Kamp - 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
Re: Grants question..
At 4:10 PM -0400 9/4/01, Gil G. wrote: Hello, I recently started using MySQL on FreeBSD. Everything works great, except that I can't access a database created by root as another user. When I grant privileges, from the command line or phpmyadmin, things seem to work fine (no error message), but access is still denied. I did restart MySQL after granting rights. The manual and book by Dubois did not provide me with an answer. Obviousely I am overlooking something...? Thanks a lot, sincerely, Gil. It'd help if we could see the GRANT statement that you're using and the error message that you get. You might also have a look a pp. 464-465 of the book. It may be that you're running into the problem described there. -- Paul DuBois, [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
