Re: Replication and Grants nightmare
P.S. the manual doesn't say that grants super, reload, replication client and replication slave can be used only on *.* and not on DB.*. Each of those privileges is listed as an administrative privilege here: http://dev.mysql.com/doc/mysql/en/Privileges_provided.html There are not listed as database or table privileges. I'm not sure what it could mean for them to be database-specific, actually. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication and Grants nightmare
Alle Friday 30 April 2004 15:22, Victoria Reznichenko ha scritto: Hmm.. Your queries worked fine for me: ANT REPLICATION SLAVE, REPLICATION CLIENT, SUPER, RELOAD ON *.* TO 'replica'@'B' IDENTIFIED BY PASSWORD '715a443962d324cc'; Query OK, 0 rows affected (0.00 sec) mysql show grants for replica@'B'; +-- + | Grants for [EMAIL PROTECTED] || +-- + | GRANT RELOAD, SUPER, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO | 'replica'@'B' IDENTIFIED BY PASSWORD '715a443962d324cc' | +-- + 1 row in set (0.01 sec) mysql revoke ALL PRIVILEGES ON *.* FROM replica@'B'; Query OK, 0 rows affected (0.00 sec) mysql flush privileges; Query OK, 0 rows affected (0.00 sec) mysql show grants for replica@'B'; +-- -+ | Grants for [EMAIL PROTECTED] | | +-- -+ | GRANT USAGE ON *.* TO 'replica'@'B' IDENTIFIED BY PASSWORD | '715a443962d324cc' | +-- -+ 1 row in set (0.00 sec) Did you have account for 'replica'@'B' before? neither other users with the same name nor other entries for the same host Are the above queries exact that you used? yes, except the host names Which OS do you use? the master is a Redhat 7.3 with Mysql-max 4.0.18 (binary rpms from www.mysql.com), the slave is a very old Cobalt 6.0 with Mysql-max 4.0.18 compiled from the .src.rpm. Thanks for your help -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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: Replication and Grants nightmare
Alle Friday 30 April 2004 15:51, hai scritto:
P.S. the manual doesn't say that grants super, reload, replication client
and replication slave can be used only on *.* and not on DB.*.
Each of those privileges is listed as an administrative privilege here:
http://dev.mysql.com/doc/mysql/en/Privileges_provided.html
There are not listed as database or table privileges.
I'm not sure what it could mean for them to be database-specific, actually.
so I should have columns Repl_client_priv and Repl_slave_priv ?
my mysql db doesn't have them:
CREATE DATABASE /*!32312 IF NOT EXISTS*/ mysql;
USE mysql;
--
-- Table structure for table `columns_priv`
--
CREATE TABLE columns_priv (
Host char(60) binary NOT NULL default '',
Db char(64) binary NOT NULL default '',
User char(16) binary NOT NULL default '',
Table_name char(64) binary NOT NULL default '',
Column_name char(64) binary NOT NULL default '',
Timestamp timestamp(14) NOT NULL,
Column_priv set('Select','Insert','Update','References') NOT NULL default
'',
PRIMARY KEY (Host,Db,User,Table_name,Column_name)
) TYPE=MyISAM COMMENT='Column privileges';
--
-- Table structure for table `db`
--
CREATE TABLE db (
Host char(60) binary NOT NULL default '',
Db char(64) binary NOT NULL default '',
User char(16) binary NOT NULL default '',
Select_priv enum('N','Y') NOT NULL default 'N',
Insert_priv enum('N','Y') NOT NULL default 'N',
Update_priv enum('N','Y') NOT NULL default 'N',
Delete_priv enum('N','Y') NOT NULL default 'N',
Create_priv enum('N','Y') NOT NULL default 'N',
Drop_priv enum('N','Y') NOT NULL default 'N',
Grant_priv enum('N','Y') NOT NULL default 'N',
References_priv enum('N','Y') NOT NULL default 'N',
Index_priv enum('N','Y') NOT NULL default 'N',
Alter_priv enum('N','Y') NOT NULL default 'N',
PRIMARY KEY (Host,Db,User),
KEY User (User)
) TYPE=MyISAM COMMENT='Database privileges';
--
-- Table structure for table `func`
--
CREATE TABLE func (
name char(64) binary NOT NULL default '',
ret tinyint(1) NOT NULL default '0',
dl char(128) NOT NULL default '',
type enum('function','aggregate') NOT NULL default 'function',
PRIMARY KEY (name)
) TYPE=MyISAM COMMENT='User defined functions';
--
-- Table structure for table `host`
--
CREATE TABLE host (
Host char(60) binary NOT NULL default '',
Db char(64) binary NOT NULL default '',
Select_priv enum('N','Y') NOT NULL default 'N',
Insert_priv enum('N','Y') NOT NULL default 'N',
Update_priv enum('N','Y') NOT NULL default 'N',
Delete_priv enum('N','Y') NOT NULL default 'N',
Create_priv enum('N','Y') NOT NULL default 'N',
Drop_priv enum('N','Y') NOT NULL default 'N',
Grant_priv enum('N','Y') NOT NULL default 'N',
References_priv enum('N','Y') NOT NULL default 'N',
Index_priv enum('N','Y') NOT NULL default 'N',
Alter_priv enum('N','Y') NOT NULL default 'N',
PRIMARY KEY (Host,Db)
) TYPE=MyISAM COMMENT='Host privileges; Merged with database privileges';
--
-- Table structure for table `tables_priv`
--
CREATE TABLE tables_priv (
Host char(60) binary NOT NULL default '',
Db char(64) binary NOT NULL default '',
User char(16) binary NOT NULL default '',
Table_name char(60) binary NOT NULL default '',
Grantor char(77) NOT NULL default '',
Timestamp timestamp(14) NOT NULL,
Table_priv
set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter')
NOT NULL default '',
Column_priv set('Select','Insert','Update','References') NOT NULL default
'',
PRIMARY KEY (Host,Db,User,Table_name),
KEY Grantor (Grantor)
) TYPE=MyISAM COMMENT='Table privileges';
--
-- Table structure for table `user`
--
CREATE TABLE user (
Host char(60) binary NOT NULL default '',
User char(16) binary NOT NULL default '',
Password char(16) binary NOT NULL default '',
Select_priv enum('N','Y') NOT NULL default 'N',
Insert_priv enum('N','Y') NOT NULL default 'N',
Update_priv enum('N','Y') NOT NULL default 'N',
Delete_priv enum('N','Y') NOT NULL default 'N',
Create_priv enum('N','Y') NOT NULL default 'N',
Drop_priv enum('N','Y') NOT NULL default 'N',
Reload_priv enum('N','Y') NOT NULL default 'N',
Shutdown_priv enum('N','Y') NOT NULL default 'N',
Process_priv enum('N','Y') NOT NULL default 'N',
File_priv enum('N','Y') NOT NULL default 'N',
Grant_priv enum('N','Y') NOT NULL default 'N',
References_priv enum('N','Y') NOT NULL default 'N',
Index_priv enum('N','Y') NOT NULL default 'N',
Alter_priv enum('N','Y') NOT NULL default 'N',
PRIMARY KEY (Host,User)
) TYPE=MyISAM COMMENT='Users and global privileges';
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication and Grants nightmare
At 16:22 +0200 4/30/04, Nico Sabbi wrote:
Alle Friday 30 April 2004 15:51, hai scritto:
P.S. the manual doesn't say that grants super, reload, replication client
and replication slave can be used only on *.* and not on DB.*.
Each of those privileges is listed as an administrative privilege here:
http://dev.mysql.com/doc/mysql/en/Privileges_provided.html
There are not listed as database or table privileges.
I'm not sure what it could mean for them to be database-specific, actually.
so I should have columns Repl_client_priv and Repl_slave_priv ?
my mysql db doesn't have them:
Indeed you should (in the user table only), and several other columns
as well.
Perhaps that is a clue to what is happening. Did you upgrade from a
release older than 4.0.2 to a release 4.0.2 or newer at some point,
without running the mysql_fix_privilege_tables script? If so, then
please read this:
http://dev.mysql.com/doc/mysql/en/Upgrading-grant-tables.html
--
-- Table structure for table `user`
--
CREATE TABLE user (
Host char(60) binary NOT NULL default '',
User char(16) binary NOT NULL default '',
Password char(16) binary NOT NULL default '',
Select_priv enum('N','Y') NOT NULL default 'N',
Insert_priv enum('N','Y') NOT NULL default 'N',
Update_priv enum('N','Y') NOT NULL default 'N',
Delete_priv enum('N','Y') NOT NULL default 'N',
Create_priv enum('N','Y') NOT NULL default 'N',
Drop_priv enum('N','Y') NOT NULL default 'N',
Reload_priv enum('N','Y') NOT NULL default 'N',
Shutdown_priv enum('N','Y') NOT NULL default 'N',
Process_priv enum('N','Y') NOT NULL default 'N',
File_priv enum('N','Y') NOT NULL default 'N',
Grant_priv enum('N','Y') NOT NULL default 'N',
References_priv enum('N','Y') NOT NULL default 'N',
Index_priv enum('N','Y') NOT NULL default 'N',
Alter_priv enum('N','Y') NOT NULL default 'N',
PRIMARY KEY (Host,User)
) TYPE=MyISAM COMMENT='Users and global privileges';
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication and Grants nightmare
Alle Friday 30 April 2004 16:44, hai scritto: At 16:22 +0200 4/30/04, Nico Sabbi wrote: Alle Friday 30 April 2004 15:51, hai scritto: P.S. the manual doesn't say that grants super, reload, replication client and replication slave can be used only on *.* and not on DB.*. Each of those privileges is listed as an administrative privilege here: http://dev.mysql.com/doc/mysql/en/Privileges_provided.html There are not listed as database or table privileges. I'm not sure what it could mean for them to be database-specific, actually. so I should have columns Repl_client_priv and Repl_slave_priv ? my mysql db doesn't have them: Indeed you should (in the user table only), and several other columns as well. Perhaps that is a clue to what is happening. Did you upgrade from a release older than 4.0.2 to a release 4.0.2 or newer at some point, without running the mysql_fix_privilege_tables script? If so, then please read this: http://dev.mysql.com/doc/mysql/en/Upgrading-grant-tables.html done, it works correctly now. Thanks very much, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
