如何创建名称中包含用户变量的 mysql 视图

发布于 2024-12-21 14:30:43 字数 519 浏览 1 评论 0原文

我正在使用 mysql 5.5，尝试编写一个脚本来创建用户、视图并授予对这些视图的选择权限。这是我到目前为止所拥有的。

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;

这是行不通的，因为它不寻找视图名称的字符串。我通过以下语句解决了创建用户的问题：

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

是否有类似的技巧可以用来创建视图并将这些视图的选择权授予我创建的用户？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

如果没结果 2024-12-28 14:30:43

您可以使用准备语句来执行这个查询。只需将查询构造为字符串并使用准备好的语句运行即可。

编辑

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`

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`

回复收藏 0 原文

~没有更多了~