How do I figure out why MySQL isn't letting me query a view?

Tags:

3 Answers

The full error message is:

... or definer/invoker of view lack rights to use them

Check the view definer, and if there is one, check that the definer account can execute the select.

To see the definer, use:

SHOW CREATE VIEW

SELECT DEFINER FROM INFORMATION_SCHEMA.VIEWS WHERE ...

answered Sep 24 '22 20:09

Marc Alff

DEFINER=myuser@localhost SQL SECURITY DEFINER means that the view will be executed with the rights of myuser@localhost (not as your current root login), see the documentation:

A stored program or view that executes in definer security context executes with the privileges of the account named by its DEFINER attribute. These privileges may be entirely different from those of the invoking user. The invoker must have appropriate privileges to reference the object (for example, EXECUTE to call a stored procedure or SELECT to select from a view), but when the object executes, the invoker's privileges are ignored and only the DEFINER account privileges matter. If this account has few privileges, the object is correspondingly limited in the operations it can perform. If the DEFINER account is highly privileged (such as a root account), the object can perform powerful operations no matter who invokes it.

A stored routine or view that executes in invoker security context can perform only operations for which the invoker has privileges. The DEFINER attribute can be specified but has no effect for objects that execute in invoker context.

So depending on what you want to achieve, either grant the required rights to myuser@localhost, change the definer to a user that has these rights or change the sql security to invoker. Usually, the second option will be used, unless you have reasons not to.

To change the definer or the sql security, you will have to alter (or drop and recreate) the view, including the whole as select ... part. You can use the output of show create view report_toc_item for that and just change it at the beginning. The default values for definer and sql security will be the current user and definer.

answered Sep 21 '22 20:09

Solarflare

If a view references an invalid column, then show create view won't work.

So check the following query that define the view work properly. Because if you delete any column from toc_item table then the view will not work.

select `ti`.`ID` AS `ID`,`ti`.`PARENT_ID` AS `PARENT_ID`,`ti`.`TOC_ID` AS TOC_ID\,`ti`.`TITLE` AS `TITLE`,`ti`.`DESCRIPTION` AS `DESCRIPTION`,`ti`.`TYPE_ID` AS `TYPE_ID`,`ti`.`ORDER_NUM` AS `ORDER_NUM`,`ti`.`MY_OBJECT_SEGMENT_ID` AS `MY_OBJECT_SEGMENT_ID`,`ti`.`MY_OBJECT_SEGMENT_ORDER_NUM` AS `MY_OBJECT_SEGMENT_ORDER_NUM`, `ti`.`ELEMENT_ID` AS `ELEMENT_ID`, `ti`.`UNIT_TOC_ITEM_ID` AS `UNIT_TOC_ITEM_ID`,`ti`.`SHORT_NAME` AS `SHORT_NAME`,`ti`.`THIRD_PARTY_PROMPT_ID` AS `THIRD_PARTY_PROMPT_ID`, `pti`.`TYPE_ID` AS `PARENT_TYPE_ID` from (`my_db`.`toc_item` `ti` join `my_db`.`toc_item` `pti` on((`pti`.`ID` = `ti`.`PARENT_ID`))) where ((`ti`.`TYPE_ID` = 'sub_segment') and ((`pti`.`TYPE_ID` = 'lesson') or (`pti`.`TYPE_ID` = 'activity') or (`pti`.`TYPE_ID` = 'activity_practice') or (`pti`.`TYPE_ID` = 'unit_opener')));

See the following example:

 create table test
 (
    x integer not null,
    y integer not null
 );

 create or replace view test_view as select * from test;

 alter table test drop column y;

As expected, this won't work.

 select * from test_view;

But this won't work either.

 show create view test_view;

 drop table test;

As expected, this still won't work.

 select * from test_view;

But bizarrely, this now does work!

 show create view test_view;

Source here

answered Sep 24 '22 20:09

Rafiqul Islam

Related questions
                            
                                PyMySQL variables in queries
                            
                                Using Delete, Update, Insert in MySQL
                            
                                Laravel DB first() "Trying to get property of non-object"
                            
                                php- get last insert id
                            
                                Sequelize Transactions : ER_LOCK_WAIT_TIMEOUT
                            
                                No connection configured
                            
                                Ignore cascade on foreign key update?
                            
                                MySQL and JSON - transform array to rows
                            
                                how group by having limit works
                            
                                SQL: Combining sum and max aggregate function
                            
                                MySQL with Sequelize: ER_BAD_DB_ERROR: Unknown database
                            
                                MySQL set secure-file-priv to multiple directories
                            
                                Laravel selectRaw with joined table data
                            
                                Laravel how to update value in mysql only if value is changed
                            
                                Integrity constraint violation: 1048 Column 'user_id' cannot be null error occurs when assigning roles (Laravel 5.3)
                            
                                How to structure database for Tinder functionality [closed]
                            
                                How to use aws athena using nodejs?
                            
                                Laravel Get Record ID from Update Query
                            
                                MySQL server slow to respond with Wordpress [closed]
                            
                                Edit duplicate if exists mysqli php

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!

Donate Us With