MySql Create temporary table with read-only permission

Question

MySql Create temporary table with read-only permission

Navigation

#1 by (0 votes)

0

I need to bring different results from the same table. For this I used the following code:

CREATE TEMPORARY TABLE IF NOT EXISTS nombre_de_tabla_temporal AS 
(SELECT ...)

My surprise is that the user I have is read-only and does not allow the creation of temporary tables.

Is there a way to create a temporary table with a read-only user? If not, I would need some alternative way of storing the information (in a variable for example?) To perform the subsequent queries.

I know that a Select ... from (Select ...) would work, but it is less clean and making changes is more tedious

Thanks

P.D.

That user can not have permissions beyond reading (reasons are not relevant) In the end I have managed to save the fields as text variables and then:

SET @campo1 = 'tabla_original.id AS "Primer_Campo",';
SET @campo2 = ...;
...
SET @tabla_temporal = CONCAT ('Select ', @campo1, @campo2, ...);
PREPARE stmt1 FROM @tabla_temporal; 
EXECUTE stmt1

At the moment it helps me even if it's a little bit cumbersome I got what I wanted

Thanks for the answers, although in my case they have not been sure that other users do;)

mysql

asked by Dudas 06.09.2017 в 07:29

source

1 answer

Why is not an object properly ordered like other objects in a recursive function? problem with setResult (1, intent) returns another value

score 0 · Answer 1

The solution I would do is create a view in mysql with the SELECT you want (A whole table, some data in particular ...) is cleaner than creating a temporary table and you do not have to change the original data.

In your case a view would look like this:

CREATE [OR REPLACE] VIEW nombre_vista 
AS consulta_SELECT