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

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.

As always, your solutions are timeless.

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).

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

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?

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

It works.

It worked for me, the grant option.