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\robc:\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\robc:\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\robC:\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\robc:\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]
