Be seen. Boost your question’s priority for more expert views and faster solutions
SQL> connect / as sysdba
Connected.
SQL> grant create view to USR1 identified by USR1;
Grant succeeded.
SQL> grant select on SYSMAN.MGMT$HA_BACKUP to USR1 with grant option;
Grant succeeded.
SQL> create view USR1.USR1_BACKUPS as select * from MGMT$HA_BACKUP;
View created.
SQL> grant create session to USR2 identified by USR2;
Grant succeeded.
SQL> grant select on USR1.USR1_BACKUPS to USR2;
Grant succeeded.
SQL> connect USR2/USR2
Connected.
SQL> select * from USR1.USR1_BACKUPS;
no rows selected
SQL> connect / as sysdba
Connected.
SQL> drop user USR1 cascade;
User dropped.
SQL> drop user USR2 cascade;
User dropped.
SQL> Conn system/system
Connected.
SQL>
SQL> Drop user master cascade;
User dropped.
SQL> Drop user usr1 cascade;
User dropped.
SQL> Drop user usr2 cascade;
User dropped.
SQL>
SQL> Create user master identified by master;
User created.
SQL> Grant create session, resource to master;
Grant succeeded.
SQL>
SQL> Create user usr1 identified by usr1;
User created.
SQL> Grant create session, create view to usr1;
Grant succeeded.
SQL>
SQL> Create user usr2 identified by usr2;
User created.
SQL> Grant create session to usr2;
Grant succeeded.
SQL>
SQL> --create the base table
SQL> Conn master/master
Connected.
SQL> Drop table tab1 purge;
Drop table tab1 purge
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> Create table tab1(col1 char(1));
Table created.
SQL> Insert into tab1 values('a');
1 row created.
SQL> Commit;
Commit complete.
SQL> Grant select on tab1 to usr1;
Grant succeeded.
SQL>
SQL> --create the view from usr1 and grant permission
SQL> Conn usr1/usr1
Connected.
SQL> Create view usr1_vw as select * from master.tab1;
View created.
SQL> Grant select on usr1_vw to usr2;
Grant select on usr1_vw to usr2
*
ERROR at line 1:
ORA-01720: grant option does not exist for 'MASTER.TAB1'
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Think about this:
I grant you select on a table. Should any user you then decide be able to access it? No. I granted you access and only you.
Creating a view then granting access to someone else access to that view is doing just that.