Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Paul.

Asked: June 24, 2005 - 8:20 am UTC

Answered by: Tom Kyte - Last updated: February 25, 2013 - 11:36 am UTC

Category: Database - Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Tom -

I have encountered an issue with granting select permission on a view. User 'A' creates a view selecting data from a table owned by user 'B' . User 'A' then tries to to grant select access to user 'C', but receives the following error: ORA-01720: grant option does not exist for 'OPF.CPOS'. I have granted select permission on the table owned by user 'B' directly to user 'C', but I still receive the same error.

For the example listed below:
User 'A' = BOAPPUSER
User 'B' = OPF
User 'C' = BOUSER

CREATE USER OPF IDENTIFIED BY OPF ACCOUNT UNLOCK;
GRANT CONNECT TO OPF;
GRANT RESOURCE TO OPF;
ALTER USER OPF DEFAULT ROLE ALL;


CREATE USER BOUSER IDENTIFIED BY BOUSER ACCOUNT UNLOCK;
GRANT CONNECT TO BOUSER;
ALTER USER BOUSER DEFAULT ROLE ALL;


CREATE USER BOAPPUSER IDENTIFIED BY BOAPPUSER ACCOUNT UNLOCK;
GRANT CONNECT TO BOAPPUSER;
GRANT RESOURCE TO BOAPPUSER;
ALTER USER BOAPPUSER DEFAULT ROLE ALL;


connect OPF

CREATE TABLE OPF.CPOS
(
CODE VARCHAR2(2) NOT NULL,
TEXT VARCHAR2(40) NOT NULL
);



GRANT SELECT ON OPF.CPOS TO BOUSER;

GRANT SELECT ON OPF.CPOS TO BOAPPUSER;


INSERT INTO OPF.CPOS ( CODE, TEXT ) VALUES ( 'EW', 'HAWAII');
INSERT INTO OPF.CPOS ( CODE, TEXT ) VALUES ( 'FC', 'JAPAN');
INSERT INTO OPF.CPOS ( CODE, TEXT ) VALUES ( 'XX', 'UNAVAILABLE');
INSERT INTO OPF.CPOS ( CODE, TEXT ) VALUES ( 'EV', 'ALASKA');
commit;


connect boappuser

CREATE OR REPLACE VIEW BOAPPUSER.V_CPOS(CODE, TEXT) AS select "CODE","TEXT" from opf.cpos;

grant select on v_CPOS to bouser;

I get the following error when I try to grant the select on the view.

SQL> grant select on v_CPOS to bouser;
grant select on v_CPOS to bouser
*
ERROR at line 1:
ORA-01720: grant option does not exist for 'OPF.CPOS'


The 2 options that I know exist to accomplish this are granting 'Select any Table to User 'C' or using the 'Grant with Admin' Option when granting select on the table. Both of these choices are against the Security Requirements set for our operating environment. Is there another option that I am not thinking about? If I have to choose one of the options I listed above, which would you suggest? I can request a 'waiver' as long as I provide an explanation.

Also, is this version specific? We are going to be upgrading to 9.2.0.4 in the near future.

Thanks,

Paul



and we said...

the 2 options are not necessary, the necessary privilege is "grant select on t WITH GRANT OPTION". The owner of the table must convey the ability to the creator of the view to grant select on their data.

If this violates your systems security policy -- well, I don't know what to say. If you have a valid, well defined reason for something...

Here is the minimum set of privs you need (else, almost anyone would be able to give access to almost anyone elses data)

Quick question for you: why would you upgrade from the un-supported to the un-supported?



tkyte@ORA8IW> create user a identified by a quota unlimited on users default tablespace users;

User created.

tkyte@ORA8IW> create user b identified by b;

User created.

tkyte@ORA8IW> create user c identified by c;

User created.

tkyte@ORA8IW>
tkyte@ORA8IW> grant create session, create table to a;

Grant succeeded.

tkyte@ORA8IW> grant create session, create view to b;

Grant succeeded.

tkyte@ORA8IW> grant create session to c;

Grant succeeded.

tkyte@ORA8IW>
tkyte@ORA8IW> @connect a/a
tkyte@ORA8IW> set termout off
a@ORA8IW> set termout on
a@ORA8IW> create table t ( x int );

Table created.

a@ORA8IW> grant select on t to b with grant option;

Grant succeeded.

a@ORA8IW>
a@ORA8IW> @connect b/b
a@ORA8IW> set termout off
b@ORA8IW> set termout on
b@ORA8IW> create view v as select * from a.t;

View created.

b@ORA8IW> grant select on v to c;

Grant succeeded.

b@ORA8IW>
b@ORA8IW> @connect c/c
b@ORA8IW> set termout off
c@ORA8IW> set termout on
c@ORA8IW> select * from b.v;

no rows selected


and you rated our response

  (10 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Hit the nail on the head.

July 04, 2005 - 10:59 pm UTC

Reviewer: Colin Laird from NZ

Confirms to me that this is the best site for Oracle on the web!!

August 16, 2005 - 8:29 am UTC

Reviewer: A reader


v$mystat and @@SPID

September 20, 2006 - 2:58 pm UTC

Reviewer: sz from NYC

The q&a are helpful, thx.
But why Oracle need even to grant v$mystat as sys.v_$mystat from a procedure?
select v$mystat (the seesion info itself) should be a basic thing without additional grant.
And there is a distinghished session id can be reccognized by the session itself, if the user login on mopre than one time with the same name at the same time. SQL Server has a simple @@SPID.

Thanks.


Tom Kyte

Followup  

September 20, 2006 - 3:44 pm UTC

but it isn't, none of the v$ views are.

"sorry". sql server doesn't have (well, this would be long list too - starting with a "real procedural language" and going from there)

November 16, 2006 - 12:46 am UTC

Reviewer: Jim from MA

As always, your solutions are timeless.

surprising solution

September 29, 2009 - 11:00 am UTC

Reviewer: Piotr from Poland

Tom,

I run into a similar problem with ORA-01720 while creating a view, however the workaround that I found surprised me. 
Is it really how it was intended to be, or have I just found an inconsistency with how Oracle manages object privileges?

Here is the full scenario:

SQL> create user a identified by a;
User created.

SQL> create user b identified by b;
User created.

SQL> grant connect, resource to a, b;
Grant succeeded.

SQL> grant select any dictionary to a, b;
Grant succeeded.

SQL> grant create any view to a, b;
Grant succeeded.

SQL> connect a/a
Connected.

SQL> create table t (x number);
Table created.

SQL> create view v as select t.x, o.object_name 
from t 
inner join dba_objects o on t.x=o.object_name;
View created.

SQL> select * from v;
no rows selected

SQL> grant select on v to b;
grant select on v to b
                *
ERROR at line 1:
ORA-01720: grant option does not exist for 'SYS.DBA_OBJECTS'


And this is absolutely fine, exactly what I expected.

Now the workaround:
First, we create a "dummy" view, then we grant select on it to the other user, and then we redefine the view so that it presents what we wanted. No error message appears, and the view works correctly from the other user.

SQL> create or replace view v as select x from t;
View created.

SQL> grant select on v to b;
Grant succeeded.

SQL> create or replace view v as 
select t.x, o.object_name 
from t 
inner join dba_objects o on t.x=o.object_name;
View created.

SQL> connect b/b
Connected.

SQL> select * from a.v;
no rows selected


My question is: why Oracle is restricting us from granting select privileges on a view that is based on dba_xxx views (absolutely understandable), and it still allows us to redefine existing views in a way that they contain joins with dba_xxx views, without taking into consideration existing grants?
(I'm on 11.1.0.7).

Tom Kyte

Followup  

October 07, 2009 - 7:14 am UTC

it is the addition of the GRANT SELECT ANY DICTIONARY - without this, it works as 'expected'

I'll report that - but, you should avoid ANY privileges in any case, in all cases possible.

ORA-01720: "WORKAROUND"

June 16, 2010 - 12:14 pm UTC

Reviewer: Kevin McCabe from Bulverde, TX USA

Hey, look at this cool "feature" I found to get around the ORA-01720 error...

1. CREATE OR REPLACE VIEW X AS SELECT DUMMY FROM DUAL;
2. GRANT SELECT ON X TO OTHERUSER;
3. CREATE OR REPLACE VIEW X AS "insert real view def here"

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

Still one Issue

February 25, 2013 - 5:15 am UTC

Reviewer: Rajan Sehgal from India

I tried with "With Grant Option" and grant was indeed successful.

However the problem is that user c can't see any data for 'View' created by b user.?

Why user C can't see data even when permissions have been granted?
Tom Kyte

Followup  

February 25, 2013 - 11:36 am UTC

give entire example, I don't know what you mean.

Please don't ask me to re-read the entire page and try to figure out what your issue is exactly from it, phrase concisely and precisely what your issue is.

Stop monkeying with privileges.

February 27, 2013 - 1:51 am UTC

Reviewer: Lange Fran├žois

Dear all,
Why grant "select with admin option", this implicate the fact the user who received this grant (the grantee) can grant access to the object(view, table) to any body else!
Forget about the privilege with any!


Let's take back the example:
connect / as sysdba
drop user a cascade ;
drop user b cascade ;
drop user c cascade ;
create user a identified by a quota unlimited on users default tablespace users;
create user b identified by b;
create user c identified by c;

grant create session, create table to a;
grant create session, create view to b;
grant create session to c;
connect a/a
create table t ( x int );
prompt populate table t with 10 rows...
begin for i in 1..10 loop insert into t ( x ) values ( i ) ; end loop ; commit ; end ;
/
Doc
GRANT WITHOUT grant option! User B could not grant select to hacker_user!
#

grant select on t to b ;

Doc
Now the trick!
The first view "NOT_WORK" will not wor the view V will work!
When Oracle recompile view it ONLY checks:
THE FIRST SUB LEVEL OF OBJECTS ARE:
- From the view owner
OR - The owner has received access with grant option!
In sql
select 'ORA-01031: insufficient privileges' ora from dual
where exists ( select null from dba_dependencies where owner = 'B' and name = 'NOT_WORK' and referenced_owner != owner
and not exists ( select null from dba_tab_privs where owner = referenced_owner and table_name = referenced_name and privilege = 'SELECT' and GRANTABLE = 'YES') )
/
#

connect b/b
create view not_work as select * from a.t;
create view "v" as select * from a.t;
create view v as select * from "v";

Doc
THIS ONLY WORK WITH SYS! I TRYED WITH USER HAVING ONLY GRANT ANY OBJECT PRIVILEGE , SELECT ANY TABLE THAT DOES NOT WORK!
#
connect / as sysdba
grant select on b.not_work to c ;
grant select on b.V to c ;

Doc
Now let's try.
The not_work query will return:
select * from b.not_work where x = 5
*
ERROR at line 1:
ORA-01031: insufficient privileges
the second query will return
select * from b.v_work where x = 5 ;
5
#
connect c/c
select * from b.not_work where x = 5 ;
select * from b.v where x = 5 ;


connect / as sysdba
drop user a cascade ;
drop user b cascade ;
drop user c cascade ;


Now you can grant create view to user "C". Create the view c."v" and c.V and with sys grant select on c.v to d.

You can audit who got the access to your data
audit select on a.t by access ;
Better should have been create a view on "t"
Create view table_t_for_user_b as select * from t ;
grant select on table_t_for_user_b to b;
audit select on table_t_for_user_b by access ;


set colsep ";"
select USERID,USERHOST,TERMINAL,ACTION#,RETURNCODE,OBJ$CREATOR,OBJ$NAME,SQLTEXT from aud$ where ACTION# = 3 and returncode=0 ;
D;computer;pts/1;3;0;A;T;select * from c.v

Now follow dependencies and privileges from "D" to "A" and you will find the access path.

The first exemple:
grant select on t to b with grant option;
B can grant acces to anybody without telling it!!

BECAREFULL!BECAREFULL!BECAREFULL!BECAREFULL!BECAREFULL!BECAREFULL!

WITH THIS METHOD ONLY SYS CAN RECOMPILE VIEW "v","V" IN SOMECASE NOT USER "C"!


Fran├žois

Perfect

May 04, 2016 - 5:53 pm UTC

Reviewer: Raul from Brazil

It works.

Works for me

July 22, 2016 - 4:47 am UTC

Reviewer: Gaurav Komawar from Pune, India

It worked for me, the grant option.