Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01720 grant option does not exist
ORA-01720 grant option does not exist [message #258671] Mon, 13 August 2007 05:47 Go to next message
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 Embarassed
Re: ORA-01720 grant option does not exist [message #258678 is a reply to message #258671] Mon, 13 August 2007 06:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: ORA-01720 grant option does not exist [message #258684 is a reply to message #258678] Mon, 13 August 2007 06:27 Go to previous messageGo to next message
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.
Re: ORA-01720 grant option does not exist [message #258688 is a reply to message #258684] Mon, 13 August 2007 06:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: ORA-01720 grant option does not exist [message #258690 is a reply to message #258684] Mon, 13 August 2007 06:39 Go to previous messageGo to next message
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.
Re: ORA-01720 grant option does not exist [message #258691 is a reply to message #258690] Mon, 13 August 2007 06:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
GRANT EXECUTE ON decrypt_data TO public WITH GRANT OPTION;

Regards
Michel
Re: ORA-01720 grant option does not exist [message #258704 is a reply to message #258691] Mon, 13 August 2007 07:22 Go to previous messageGo to next message
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.



Re: ORA-01720 grant option does not exist [message #258729 is a reply to message #258704] Mon, 13 August 2007 08:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post exactly what you did: copy and paste the execution.

Regards
Michel
Re: ORA-01720 grant option does not exist [message #258994 is a reply to message #258729] Tue, 14 August 2007 01:27 Go to previous message
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 Razz
Previous Topic: bind variable
Next Topic: to_date format question
Goto Forum:
  


Current Time: Mon Dec 05 19:16:36 CST 2016

Total time taken to generate the page: 0.09165 seconds