On Thu, Oct 2, 2008 at 1:29 PM, Olaf Stein <[EMAIL PROTECTED]> wrote: > Hi all, > > In my master/salve setup the only database I do not replicate is mysql. > The slave has only the users absolutely necessary to select and administer, > that is why a lot of the users I have on the master are not there. > > When I create a view on the master the definer is set to the user I am > logged in as. As this user does not exist on the slave the view is created > there but I cannot select from it. > > I tried setting the definer to current_user in the hope it would mean "user > logged in in current session", not "user that was logged in when creating > the view". > > How can I get around this, I do not want to re-create all user that > potentially could create views on the slave. > > Thanks > Olaf >
Take a look at SQL SECURITY INVOKER Example: Microsoft Windows XP [Version 5.1.2600] (C) Copyright 1985-2001 Microsoft Corp. C:\Documents and Settings\rob>"c:\Program Files\MySQL\MySQL Server 5.0\bin\mysql .exe" -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 Server version: 5.0.51a-community-nt-log MySQL Community Edition (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> grant select on test.* to bob identified by 'stuff'; Query OK, 0 rows affected (0.00 sec) mysql> grant select,create view on test.* to joe identified by 'stuff'; Query OK, 0 rows affected (0.00 sec) mysql> use test; Database changed mysql> create table t(c int); Query OK, 0 rows affected (0.31 sec) mysql> insert into t values(1),(3); Query OK, 2 rows affected (0.16 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> exit Bye C:\Documents and Settings\rob>"c:\Program Files\MySQL\MySQL Server 5.0\bin\mysql .exe" -u joe -p Enter password: ***** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 16 Server version: 5.0.51a-community-nt-log MySQL Community Edition (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> use test; Database changed mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | t | +----------------+ 1 row in set (0.00 sec) mysql> create SQL SECURITY INVOKER VIEW viewy AS select c+1 from t; Query OK, 0 rows affected (0.00 sec) mysql> exit Bye C:\Documents and Settings\rob>"C:\Program Files\MySQL\MySQL Server 5.0\bin\mysql .exe" -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 14 Server version: 5.0.51a-community-nt-log MySQL Community Edition (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> drop user joe; Query OK, 0 rows affected (0.00 sec) mysql> exit Bye C:\Documents and Settings\rob>"c:\Program Files\MySQL\MySQL Server 5.0\bin\mysql .exe" -u bob -p Enter password: ***** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 18 Server version: 5.0.51a-community-nt-log MySQL Community Edition (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> use test; Database changed mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | t | | viewy | +----------------+ 2 rows in set (0.00 sec) mysql> select * from viewy; +------+ | c+1 | +------+ | 2 | | 4 | +------+ 2 rows in set (0.00 sec) -- Rob Wultsch [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
