OraFAQ Forum: SQL & PL/SQL » ORA-01720 grant option does not exist

Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01720 grant option does not exist

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-01720 grant option does not exist [message #258671]

Mon, 13 August 2007 05:47

rajesh_bhadu
Messages: 48
Registered: June 2007

Member

Hi ALL,

I m using oracle 9i database.

I have created below schema.

SET SCAN OFF;
DROP USER TESTDB cascade;
CREATE USER TESTDB IDENTIFIED BY TESTDB DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;
GRANT CREATE SESSION, RESOURCE, CREATE VIEW TO TESTDB;


-- SYSTEM PRIVILEGES
GRANT DROP ANY PROCEDURE TO TESTDB ;
GRANT ALTER DATABASE TO TESTDB ;
GRANT UPDATE ANY TABLE TO TESTDB ;
GRANT SELECT ANY SEQUENCE TO TESTDB ;
GRANT DROP ANY VIEW TO TESTDB ;
GRANT CREATE ANY INDEX TO TESTDB ;
GRANT CREATE ANY PROCEDURE TO TESTDB ;
GRANT ALTER ANY INDEX TO TESTDB ;
GRANT CREATE ANY SEQUENCE TO TESTDB ;
GRANT EXECUTE ANY LIBRARY TO TESTDB ;
GRANT EXECUTE ANY PROCEDURE TO TESTDB ;
GRANT INSERT ANY TABLE TO TESTDB ;
GRANT DROP ANY LIBRARY TO TESTDB ;
GRANT ALTER ANY TABLE TO TESTDB ;
GRANT DROP ANY SEQUENCE TO TESTDB ;
GRANT CREATE ANY LIBRARY TO TESTDB ;
GRANT CREATE LIBRARY TO TESTDB ;
GRANT ALTER ANY PROCEDURE TO TESTDB ;
GRANT SELECT ANY TABLE TO TESTDB ;
GRANT CREATE ANY TRIGGER TO TESTDB ;
GRANT GRANT ANY OBJECT PRIVILEGE TO TESTDB ;
GRANT DROP ANY TABLE TO TESTDB ;
GRANT CREATE ANY SYNONYM TO TESTDB ;
GRANT GRANT ANY PRIVILEGE TO TESTDB ;
GRANT CREATE ANY VIEW TO TESTDB ;
GRANT ALTER ANY TRIGGER TO TESTDB ;
GRANT DROP ANY TRIGGER TO TESTDB ;
GRANT CREATE ANY TABLE TO TESTDB ;
GRANT ALTER ANY LIBRARY TO TESTDB ;
GRANT ALTER ANY SEQUENCE TO TESTDB ;

In above schema i have created a function decrypt_data.

I have provided public access on this function as

GRANT EXECUTE ON decrypt_data TO public;

Now have created another schema as:

SET SCAN OFF;
DROP USER NEW_TESTDB CASCADE;
CREATE USER NEW_TESTDB IDENTIFIED BY NEW_TESTDB DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;
GRANT CREATE SESSION, RESOURCE, CREATE VIEW TO NEW_TESTDB;
connect NEW_TESTDB/NEW_TESTDB;

In NEW_TESTDB schema I have created a view as below

CREATE OR REPLACE VIEW myview AS
SELECT 
      NAME,
   		CAST(testdb.decrypt_data() AS char(20)) AS ccchar
FROM mytable_enc;

Now I m providing grant on view to testdb as:

grant select on myview to testdb;

but it's showing below error:
ORA-01720:grant option does not exist for 'testdb.decrypt_data'

Why this error is coming while decrypt_data is function of testdb so definately testdb have access on it.Further i have granted public access on decrypt_data.

Plesae tell what am i doing wrong.

Thanx in advance.

Rajesh Embarassed

Report message to a moderator

Re: ORA-01720 grant option does not exist [message #258678 is a reply to message #258671]

Mon, 13 August 2007 06:19

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

new_testdb is allowed to execute testdb procedure but when it creates the view and grant select on this view it allows other to execute the procedure, so it must have the privilege with grant option to be allowed to transmit the privilege.
You must grant the execute privilege with grant option.

Regards
Michel

Report message to a moderator

Re: ORA-01720 grant option does not exist [message #258684 is a reply to message #258678]

Mon, 13 August 2007 06:27

rajesh_bhadu
Messages: 48
Registered: June 2007

Member

Hi Michel,

I have tried this one.

grant select on myview to testdb with grant option;

But same problem still exist.

Report message to a moderator

Re: ORA-01720 grant option does not exist [message #258688 is a reply to message #258684]

Mon, 13 August 2007 06:29

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I said

Quote:

You must grant the execute privilege [on the procedure] with grant option.

I didn't talk about the view.

Regards
Michel

Report message to a moderator

Re: ORA-01720 grant option does not exist [message #258690 is a reply to message #258684]

Mon, 13 August 2007 06:39

rajesh_bhadu
Messages: 48
Registered: June 2007

Member

But

Quote:

GRANT EXECUTE ON decrypt_data TO public;

I have executed above sql.

So execute on decrypt_data is public.

So please tell me how to transmit execute privleges using grant option.

Report message to a moderator

Re: ORA-01720 grant option does not exist [message #258691 is a reply to message #258690]

Mon, 13 August 2007 06:42

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

GRANT EXECUTE ON decrypt_data TO public WITH GRANT OPTION;

Regards
Michel

Report message to a moderator

Re: ORA-01720 grant option does not exist [message #258704 is a reply to message #258691]

Mon, 13 August 2007 07:22

rajesh_bhadu
Messages: 48
Registered: June 2007

Member

Thanx for your help.

i have done whatever you told me.

But problem still exist with same error.

One more thing its working in oracle 10g without grant option.

Report message to a moderator

Re: ORA-01720 grant option does not exist [message #258729 is a reply to message #258704]

Mon, 13 August 2007 08:33

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Post exactly what you did: copy and paste the execution.

Regards
Michel

Report message to a moderator

Re: ORA-01720 grant option does not exist [message #258994 is a reply to message #258729]

Tue, 14 August 2007 01:27

rajesh_bhadu
Messages: 48
Registered: June 2007

Member

Hi @Michel,

Thanx a lot for your help. It's working fine with grant option as below. i have done this work after droping all schema and again start work from refresh.

GRANT EXECUTE ON decrypt_data TO public WITH GRANT OPTION;

Again thanx For help. Razz

Report message to a moderator

Previous Topic:	bind variable
Next Topic:	to_date format question

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 03 11:14:57 CST 2024