1

I'm using mysql 5.5, trying to write a script to create users, views, and grant select privileges on those views. Here's what I have so far.

set @type_id := 1;
set @username := 'somecompany';
set @password := 'company1234';
set @prefix := 'somecompany';

CREATE OR REPLACE VIEW CONCAT(@prefix, '_report') AS
SELECT * FROM my_table
WHERE type_id = @type_id;

Which won't work because it isn't looking for a string for the view name. I got around this for creating users with the statement:

INSERT INTO mysql.user (Host, User, Password) VALUES ('%', @username, PASSWORD(@password));

Is there a similar trick I can use to create views and grant select on those views to the user I created?

Devart
  • 119,203
  • 23
  • 166
  • 186
Luke L
  • 83
  • 1
  • 7

1 Answers1

1

You can use Prepared Statements to execute this queries. Just construct the query as a string and run it with prepared statements.

Edit

MySQL 5.5.12-log

SET @s = 'CREATE  VIEW view_actor AS SELECT * FROM sakila.actor;';
PREPARE stmt2 FROM @s;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;


-- Check CREATE VIEW
SHOW CREATE VIEW view_actor;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `view_actor` AS select `sakila`.`actor`.`actor_id` AS `actor_id`,`sakila`.`actor`.`first_name` AS `first_name`,`sakila`.`actor`.`last_name` AS `last_name`,`sakila`.`actor`.`last_update` AS `last_update` from `sakila`.`actor`
Devart
  • 119,203
  • 23
  • 166
  • 186
  • http://dev.mysql.com/doc/refman/5.5/en/sql-syntax-prepared-statements.html In the 5.5 user manual, it has a list of queries prepared statements can be used for. Create view can't be used with a prepared statement in anything I can find, and it didn't work for me in workbench. – Luke L Dec 16 '11 at 15:54
  • Yes, really, CREATE VIEW is still unsupported;-( You only can use INSERT INTO to add new user or use CREATE USER statement. Another way is to do it in the application. – Devart Dec 16 '11 at 16:55
  • Stop!!! I have found very intresting thing. The CREATE VIEW can be used with prepared statements. I have managed to do it on MySQL 5.5, and everething is OK. – Devart Dec 16 '11 at 17:00
  • Did you mean to edit my post with the example you posted? I want to give you credit for answering, but it will be more confusing for people trying to find this answer in the future. Can you move the example code to your answer before I accept please? – Luke L Dec 19 '11 at 15:28
  • Oh, sorry. I accidentally added it to your question;-) – Devart Dec 20 '11 at 07:44