Home » SQL & PL/SQL » SQL & PL/SQL » SELECT grant on view to a role. (Oracle 11G)
SELECT grant on view to a role. [message #617441] Mon, 30 June 2014 04:19 Go to next message
sinpeak
Messages: 59
Registered: January 2011
Location: india
Member
Hi,

I am trying to create a view and grant SELECT on it :

Create or replace view JBP_CUSTOM.SE_VATCODEDETAILS (VAT_CODE, VAT_DESCRIPTION, VAT_PERCENTAGE)
as
select TC.TAXCODE_NAME, TT.TAX_TYPE_DESC, TAD.TAX_RATE_PCT
from JBP_ADMIN.TAXCODE TC, JBP_ADMIN.TAXTYPE TT,JBP_ADMIN.TAXCODEDETAILS TAD
where TC.TAX_TYPE_ID = TT.TAX_TYPE_ID
and TC.TAXCODE_ID = TAD.TAXCODE_ID
and TC.LAST_VERSION_DAT <=trunc(JBP_ADMIN.GNVGEN.SYSTEMDATE)
and TAD.VALID_FROM_DAT <=trunc(JBP_ADMIN.GNVGEN.SYSTEMDATE)
and (TAD.VALID_TO_DAT is null or TAD.VALID_TO_DAT >= trunc(JBP_ADMIN.GNVGEN.SYSTEMDATE));

CREATE OR REPLACE PUBLIC SYNONYM SE_VATCODEDETAILS FOR JBP_CUSTOM.SE_VATCODEDETAILS;
GRANT SELECT ON JBP_CUSTOM.SE_VATCODEDETAILS TO JBPROLE;


The "GNVGEN.SYSTEMDATE" expression in the view is a package-function that returns a date type variable and the package is owned by a schema user ( JBP_ADMIN ) different from the one that will own the view ( JBP_CUSTOM )
The view is to be owned by JBP_CUSTOM schema user and the SELECT grant is to assigned to a role.
The view gets created but the GRANT fails with the error :

ORA-01720: grant option does not exist for 'JBP_ADMIN.GNVGEN'

If I remove the GNVGEN.SYSTEMDATE part from the view query and replace it with SYSDATE, then the GRANT works fine.

Please advise what would be the appropriate GRANT to assign to the role in such a case.

Thanks.

Re: SELECT grant on view to a role. [message #617444 is a reply to message #617441] Mon, 30 June 2014 04:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As the error message tells you: you must have the privilege with grant option.

Re: SELECT grant on view to a role. [message #617449 is a reply to message #617444] Mon, 30 June 2014 05:04 Go to previous messageGo to next message
sinpeak
Messages: 59
Registered: January 2011
Location: india
Member
Hi,

I used "WITH GRANT OPTION" as well but I am getting the same error.

Please advise.

Thanks
Re: SELECT grant on view to a role. [message #617451 is a reply to message #617449] Mon, 30 June 2014 05:05 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
how did you use with grant option?
You need to be granted execute on GNVGEN with grant option.
Re: SELECT grant on view to a role. [message #617461 is a reply to message #617449] Mon, 30 June 2014 06:38 Go to previous message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
sinpeak wrote on Mon, 30 June 2014 06:04
Please advise.


My advise is RTFM:

The owner of the schema containing the view must have the privileges necessary to either select, insert, update, or delete rows from all the tables or views on which the view is based. The owner must be granted these privileges directly, rather than through a role.

SY.
Previous Topic: Sort data output to excel (owa_util) from 2 cursor
Next Topic: >= date field in Oracle not retrieving expected results (merged 2 by MC)
Goto Forum:
  


Current Time: Fri Mar 29 07:31:16 CDT 2024