Authority error when attempting to INSERT into a VIEW

Matthew Roessner
Matthew Roessner used Ask the Experts™
on
One of our developers is attempting to do an "Insert" into a view.  They are connecting in via ODBC.

I gave the user profile *ALL authority to the View but I am getting the error:

Message: [SQL0551] Not authorized to object VSTOWERINT in DCTEST type *FILE. Cause . . . . . :   An operation was attempted on object VSTOWERINT in DCTEST type *FILE.  This operation cannot be performed without the required authority. Recovery  . . . :   Obtain the required authority from either the security officer, the object owner, or a user that is authorized to the QIBM_DB_SECADM function. If you are not authorized to a logical file, obtain the authority to the based-on files of the logical file. Try the operation again.   Processing ended because the highlighted statement did not complete successfully

I figured I needed to give the user access to the file that the view was based on...but I gave the user *ALL access to this file too and we are still getting the same error.

Does anyone have any insight into why we would be getting this error - even though we gave the user *ALL access?

Thanks in advance
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Russell FoxDatabase Developer
Top Expert 2014

Commented:
Are you sure the view is updateable? You generally want to INSERT into a table, not a view.

https://docs.oracle.com/cd/E17952_01/mysql-5.1-en/view-updatability.html
VP Technology / Senior Consultant
Commented:
You also need proper data authorities to physical in addition to object and data authorities to view.
Matthew RoessnerSenior Systems Programmer

Author

Commented:
It turns out that we had granted authorities to a SQL trigger associated with the view - but not the physical file itself. After granting authority to the right physical file - we are able to do our INSERT.  Definitely helps to double-check what the developers are telling us!

Thanks for your help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial