302: No GRANT option or illegal option
A user on IDS 11.70 (Solaris) created a view that casts an lvarchar(1500) column to char(1500) to work around an application limitation, but GRANT INSERT on the view failed with error 302 ("No GRANT option or illegal option on multi-table view"), while select/delete/update grants worked. Early replies wrongly claimed views never allow inserts; testing by others confirmed the cast itself marks the view as non-insertable (same in 12.10), possibly a bug/RFE. The working fix: define an INSTEAD OF INSERT trigger on the view that inserts into the base table, after which GRANT INSERT succeeds.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi,
We are running Informix 11.70.FC7W2 on Solaris. We create the table and
view below and are able to grant select, delete and update permission to
public. However we get the following error when trying to grant insert
permissions. The challenge is that the application has an issue using the
lvarchar(1500) datatype so we created a view and tried casting that to
char(1500) to "trick" the application. We are aware of another workaround
of using a header/detail table to allow messages of unlimited length as was
previously presented in this forum. Any suggestions for this particular
issue?
create table mytab
(
rec_key serial
,message lvarchar(1500)
) lock mode row;
create view v_mytab (rec_key, message) as
select rec_key,
message::char(1500)
from mytab;
grant select, delete, update on v_mytab to public;
Permission granted.
grant insert on v_mytab to public;
302: No GRANT option or illegal option on multi-table view.
Error in line 1Near character position 32
Thank You,
--Dave
You cannot GRANT INSERT on a view.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Informix DBA
Sent: Tuesday, June 20, 2017 10:25 AM
To: ids@iiug.org
Subject: 302: No GRANT option or illegal option [39397]
Hi,
We are running Informix 11.70.FC7W2 on Solaris. We create the table and
view below and are able to grant select, delete and update permission to
public. However we get the following error when trying to grant insert
permissions. The challenge is that the application has an issue using the
lvarchar(1500) datatype so we created a view and tried casting that to
char(1500) to "trick" the application. We are aware of another workaround
of using a header/detail table to allow messages of unlimited length as
was previously presented in this forum. Any suggestions for this
particular issue?
create table mytab
(
rec_key serial
,message lvarchar(1500)
) lock mode row;
create view v_mytab (rec_key, message) as select rec_key,
message::char(1500)
from mytab;
grant select, delete, update on v_mytab to public;
Permission granted.
grant insert on v_mytab to public;
302: No GRANT option or illegal option on multi-table view.
Error in line 1Near character position 32
Thank You,
--Dave
**************************************************************************
*****
Forum Note: Use "Reply" to post a response in the discussion forum.
Hi
You can't insert on a view, it's Just for reading data.
Regards
On Jun 20, 2017 17:24, "Martin Graney" <mgraney@qed.com> wrote:
> You cannot GRANT INSERT on a view.
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Informix DBA
> Sent: Tuesday, June 20, 2017 10:25 AM
> To: ids@iiug.org
> Subject: 302: No GRANT option or illegal option [39397]
>
> Hi,
>
> We are running Informix 11.70.FC7W2 on Solaris. We create the table and
> view below and are able to grant select, delete and update permission to
> public. However we get the following error when trying to grant insert
> permissions. The challenge is that the application has an issue using the
> lvarchar(1500) datatype so we created a view and tried casting that to
> char(1500) to "trick" the application. We are aware of another workaround
> of using a header/detail table to allow messages of unlimited length as
> was previously presented in this forum. Any suggestions for this
> particular issue?
>
> create table mytab
> (>
> rec_key serial
> ,message lvarchar(1500)
> ) lock mode row;
>
> create view v_mytab (rec_key, message) as select rec_key,>
> message::char(1500)
>
> from mytab;
>
> grant select, delete, update on v_mytab to public;>
> Permission granted.
>
> grant insert on v_mytab to public;>
> 302: No GRANT option or illegal option on multi-table view.
> Error in line 1> Near character position 32
>
> Thank You,
>
> --Dave
>
> **************************************************************************
> *****
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
> ************************************************************
> *******************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
Hi Martin and Juan,
On Informix 11.70 You can grant insert on a view. If I remove the cast in
the view definition, you can grant insert on the view.
grant insert on v_mytab to public;
Permission granted.
Inserting Rows Through a View:
https://www.ibm.com/support/knowledgecenter/en/SSGU8G_11.70.0/com.ibm.sqls.doc/i
ds_sqs_0864.htm
--Dave
On Tue, Jun 20, 2017 at 11:27 AM, Juan Francisco González Navarro <
jfrancisco.navarro@gmail.com> wrote:
> Hi
>
> You can't insert on a view, it's Just for reading data.
>
> Regards
>
> On Jun 20, 2017 17:24, "Martin Graney" <mgraney@qed.com> wrote:
>
> > You cannot GRANT INSERT on a view.
> >
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> > Informix DBA
> > Sent: Tuesday, June 20, 2017 10:25 AM
> > To: ids@iiug.org
> > Subject: 302: No GRANT option or illegal option [39397]
> >
> > Hi,
> >
> > We are running Informix 11.70.FC7W2 on Solaris. We create the table and
> > view below and are able to grant select, delete and update permission to
> > public. However we get the following error when trying to grant insert
> > permissions. The challenge is that the application has an issue using the
> > lvarchar(1500) datatype so we created a view and tried casting that to
> > char(1500) to "trick" the application. We are aware of another workaround
> > of using a header/detail table to allow messages of unlimited length as
> > was previously presented in this forum. Any suggestions for this
> > particular issue?
> >
> > create table mytab
> > (> >
> > rec_key serial
> > ,message lvarchar(1500)
> > ) lock mode row;
> >
> > create view v_mytab (rec_key, message) as select rec_key,> >
> > message::char(1500)
> >
> > from mytab;
> >
> > grant select, delete, update on v_mytab to public;> >
> > Permission granted.
> >
> > grant insert on v_mytab to public;> >
> > 302: No GRANT option or illegal option on multi-table view.
> > Error in line 1> > Near character position 32
> >
> > Thank You,
> >
> > --Dave
> >
> > ************************************************************
> **************
> > *****
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
> > ************************************************************
> > *******************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
>
> ************************************************************
> *******************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
Maybe look at using an "INSTEAD OF" trigger on a view to so that when the
insert occurs into the view, it executes a stored procedure instead to
perform the insert with he cast to a char?
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Juan
Francisco González Navarro
Sent: Tuesday, June 20, 2017 9:27 AM
To: ids@iiug.org
Subject: RE: 302: No GRANT option or illegal option [39399]
Hi
You can't insert on a view, it's Just for reading data.
Regards
On Jun 20, 2017 17:24, "Martin Graney" <mgraney@qed.com> wrote:
> You cannot GRANT INSERT on a view.
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Informix DBA
> Sent: Tuesday, June 20, 2017 10:25 AM
> To: ids@iiug.org
> Subject: 302: No GRANT option or illegal option [39397]
>
> Hi,
>
> We are running Informix 11.70.FC7W2 on Solaris. We create the table
> and view below and are able to grant select, delete and update
> permission to public. However we get the following error when trying
> to grant insert permissions. The challenge is that the application has
> an issue using the
> lvarchar(1500) datatype so we created a view and tried casting that to
> char(1500) to "trick" the application. We are aware of another
> workaround of using a header/detail table to allow messages of
> unlimited length as was previously presented in this forum. Any
> suggestions for this particular issue?
>
> create table mytab
> (>
> rec_key serial
> ,message lvarchar(1500)
> ) lock mode row;
>
> create view v_mytab (rec_key, message) as select rec_key,>
> message::char(1500)
>
> from mytab;
>
> grant select, delete, update on v_mytab to public;>
> Permission granted.
>
> grant insert on v_mytab to public;>
> 302: No GRANT option or illegal option on multi-table view.
> Error in line 1> Near character position 32
>
> Thank You,
>
> --Dave
>
> **********************************************************************
> ****
> *****
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
> ************************************************************
> *******************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
Probably try granting it as the owner of the underlying table. See what you
get.
grant insert on v_mytab to public as " owner".
Josue Pierrot
Database Administrator, Information Technology
82 Hopmeadow St, Simsbury, US
O 8604082129 M 8609214920 F 8604082139
E jpierrot@chubb.com
ACE and Chubb are now one.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Juan
Francisco González Navarro
Sent: Tuesday, June 20, 2017 11:27 AM
To: ids@iiug.org
Subject: RE: 302: No GRANT option or illegal option [39399]
Hi
You can't insert on a view, it's Just for reading data.
Regards
On Jun 20, 2017 17:24, "Martin Graney" <mgraney@qed.com> wrote:
> You cannot GRANT INSERT on a view.
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Informix DBA
> Sent: Tuesday, June 20, 2017 10:25 AM
> To: ids@iiug.org
> Subject: 302: No GRANT option or illegal option [39397]
>
> Hi,
>
> We are running Informix 11.70.FC7W2 on Solaris. We create the table
> and view below and are able to grant select, delete and update
> permission to public. However we get the following error when trying
> to grant insert permissions. The challenge is that the application has
> an issue using the
> lvarchar(1500) datatype so we created a view and tried casting that to
> char(1500) to "trick" the application. We are aware of another
> workaround of using a header/detail table to allow messages of
> unlimited length as was previously presented in this forum. Any
> suggestions for this particular issue?
>
> create table mytab
> (>
> rec_key serial
> ,message lvarchar(1500)
> ) lock mode row;
>
> create view v_mytab (rec_key, message) as select rec_key,>
> message::char(1500)
>
> from mytab;
>
> grant select, delete, update on v_mytab to public;>
> Permission granted.
>
> grant insert on v_mytab to public;>
> 302: No GRANT option or illegal option on multi-table view.
> Error in line 1> Near character position 32
>
> Thank You,
>
> --Dave
>
> **********************************************************************
> ****
> *****
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
> ************************************************************
> *******************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
____________________________________________________________________
This email (including any attachments) is intended for the designated
recipient(s) only, and may be confidential, non-public, proprietary, and/or
protected by the attorney-client or other privilege. Unauthorized reading,
distribution, copying or other use of this communication is prohibited and may
be unlawful. Receipt by anyone other than the intended recipient(s) should not
be deemed a waiver of any privilege or protection. If you are not the intended
recipient or if you believe that you have received this email in error, please
notify the sender immediately and delete all copies from your computer system
without reading, saving, printing, forwarding or using it in any manner.
Although it has been checked for viruses and other malicious software
("malware"), we do not warrant, represent or guarantee in any way that this
communication is free of malware or potentially damaging defects. All
liability for any actual or alleged loss, damage, or injury arising out of or
resulting in any way from the receipt, opening or use of this email is
expressly disclaimed.
_____________________________________________________________________
Dave:
Quite right. Thank you. I should have elaborated more. Too busy trying
to do multiple things rather than getting one thing done right.
Cheers!
Best regards,
Martin M. Graney
Queues Enforth Development, Inc.
'
This electronic message contains information which may be privileged,
confidential, or otherwise protected from disclosure. The information
contained herein is intended for the addressee or recipient only. If you
are not the addressee, or not the intended recipient, any disclosure,
copying, distribution, or use of the contents of this message (including
any attachments) is prohibited. If you have received this electronic
message in error, please notify the sender immediately and destroy the
original message and all copies. (Queues Enforth Development, Inc.)
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Informix DBA
Sent: Tuesday, June 20, 2017 11:37 AM
To: ids@iiug.org
Subject: Re: 302: No GRANT option or illegal option [39400]
Hi Martin and Juan,
On Informix 11.70 You can grant insert on a view. If I remove the cast in
the view definition, you can grant insert on the view.
grant insert on v_mytab to public;
Permission granted.
Inserting Rows Through a View:
https://www.ibm.com/support/knowledgecenter/en/SSGU8G_11.70.0/com.ibm.sqls
.doc/ids_sqs_0864.htm
--Dave
On Tue, Jun 20, 2017 at 11:27 AM, Juan Francisco González Navarro <
jfrancisco.navarro@gmail.com> wrote:
> Hi
>
> You can't insert on a view, it's Just for reading data.
>
> Regards
>
> On Jun 20, 2017 17:24, "Martin Graney" <mgraney@qed.com> wrote:
>
> > You cannot GRANT INSERT on a view.
> >
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf
> > Of Informix DBA
> > Sent: Tuesday, June 20, 2017 10:25 AM
> > To: ids@iiug.org
> > Subject: 302: No GRANT option or illegal option [39397]
> >
> > Hi,
> >
> > We are running Informix 11.70.FC7W2 on Solaris. We create the table
> > and view below and are able to grant select, delete and update
> > permission to public. However we get the following error when trying
> > to grant insert permissions. The challenge is that the application
> > has an issue using the
> > lvarchar(1500) datatype so we created a view and tried casting that
> > to
> > char(1500) to "trick" the application. We are aware of another
> > workaround of using a header/detail table to allow messages of
> > unlimited length as was previously presented in this forum. Any
> > suggestions for this particular issue?
> >
> > create table mytab
> > (> >
> > rec_key serial
> > ,message lvarchar(1500)
> > ) lock mode row;
> >
> > create view v_mytab (rec_key, message) as select rec_key,> >
> > message::char(1500)
> >
> > from mytab;
> >
> > grant select, delete, update on v_mytab to public;> >
> > Permission granted.
> >
> > grant insert on v_mytab to public;> >
> > 302: No GRANT option or illegal option on multi-table view.
> > Error in line 1> > Near character position 32
> >
> > Thank You,
> >
> > --Dave
> >
> > ************************************************************
> **************
> > *****
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
> > ************************************************************
> > *******************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
>
> ************************************************************
> *******************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
**************************************************************************
*****
Forum Note: Use "Reply" to post a response in the discussion forum.
You are right.... I believe IT is possible on a single table view.... on a
multi tables view I think THAT FEATURE is available on IDS12. Please validate!
Josue Pierrot
Database Administrator, Information Technology
82 Hopmeadow St, Simsbury, US
O 8604082129 M 8609214920 F 8604082139
E jpierrot@chubb.com
ACE and Chubb are now one.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Martin
Graney
Sent: Tuesday, June 20, 2017 11:24 AM
To: ids@iiug.org
Subject: RE: 302: No GRANT option or illegal option [39398]
You cannot GRANT INSERT on a view.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Informix
DBA
Sent: Tuesday, June 20, 2017 10:25 AM
To: ids@iiug.org
Subject: 302: No GRANT option or illegal option [39397]
Hi,
We are running Informix 11.70.FC7W2 on Solaris. We create the table and
view below and are able to grant select, delete and update permission to
public. However we get the following error when trying to grant insert
permissions. The challenge is that the application has an issue using the
lvarchar(1500) datatype so we created a view and tried casting that to
char(1500) to "trick" the application. We are aware of another workaround
of using a header/detail table to allow messages of unlimited length as
was previously presented in this forum. Any suggestions for this
particular issue?
create table mytab
(
rec_key serial
,message lvarchar(1500)
) lock mode row;
create view v_mytab (rec_key, message) as select rec_key,
message::char(1500)
from mytab;
grant select, delete, update on v_mytab to public;
Permission granted.
grant insert on v_mytab to public;
302: No GRANT option or illegal option on multi-table view.
Error in line 1Near character position 32
Thank You,
--Dave
**************************************************************************
*****
Forum Note: Use "Reply" to post a response in the discussion forum.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
____________________________________________________________________
This email (including any attachments) is intended for the designated
recipient(s) only, and may be confidential, non-public, proprietary, and/or
protected by the attorney-client or other privilege. Unauthorized reading,
distribution, copying or other use of this communication is prohibited and may
be unlawful. Receipt by anyone other than the intended recipient(s) should not
be deemed a waiver of any privilege or protection. If you are not the intended
recipient or if you believe that you have received this email in error, please
notify the sender immediately and delete all copies from your computer system
without reading, saving, printing, forwarding or using it in any manner.
Although it has been checked for viruses and other malicious software
("malware"), we do not warrant, represent or guarantee in any way that this
communication is free of malware or potentially damaging defects. All
liability for any actual or alleged loss, damage, or injury arising out of or
resulting in any way from the receipt, opening or use of this email is
expressly disclaimed.
_____________________________________________________________________
I tested it in both 11.70.FC7W1 (didn't have W2 to hand) and 12.10.FC8W2 and
both behave in the same way. The problem is the cast in the view:
create view v_mytab (rec_key, message) as
select rec_key,
message::char(1500)
from mytab;
It will work if this becomes:
create view v_mytab (rec_key, message) as
select rec_key,
message
from mytab;
Views with aggregation functions don't support inserts and I suspect the cast
marks this as a view that does not support inserts. It is either a bug or an
RFE :-)
Ben.
Also did some tests on a 12.10FC8 and the cast does mark the view as not
supporting inserts.
You can work around it with an INSTEAD trigger:
CREATE TRIGGER ti_v_mytab
INSTEAD OF INSERT ON v_mytab
REFERENCING NEW AS n
FOR EACH ROW
(INSERT INTO mytab (rec_key, message) VALUES (n.rec_key, n.message));
After creating the trigger on the view, you can grant the INSERT privilege:
GRANT INSERT ON v_mytab TO PUBLIC;