SELECT grant on view to a role. [message #617441] |
Mon, 30 June 2014 04:19 |
|
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 #617461 is a reply to message #617449] |
Mon, 30 June 2014 06:38 |
Solomon Yakobson
Messages: 3269 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
sinpeak wrote on Mon, 30 June 2014 06:04Please 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.
|
|
|