Granting too many privileges on a view could be disastrous. A view is often used as a security element; you grant access to only a subset of columns and rows to one user. Mostly only SELECT. If you want to grant update to only some rows, the security could be enhanced with the WITH CHECK OPTION.
But let’s talk about granting too much privs.
disclaimer: it may damaged your database forever
SQL> create or replace view v as select trunc(sysdate) today from dual;
View created.
SQL> create public synonym v for v;
Synonym created.
SQL> grant all on v to public;
Grant succeeded.
SQL> conn u/***@db01
Connected.
SQL> select * from v;
TODAY
----------
2018-10-15
SQL> select * from v;
TODAY
----------
2018-10-15
SQL> delete from v;
1 row deleted.
SQL> select * from v;
TODAY
----------
2018-10-15
SQL> select count(*) from v;
COUNT(*)
----------
1
SQL> select count(dummy) from dual;
COUNT(DUMMY)
------------
0
SQL> rollback;
Rollback complete.
Wait… what happened ???
SQL> delete from v;
1 row deleted.
SQL> select count(*) from v;
COUNT(*)
----------
1
This is a biaised test, because nobody creates view in SYS schema and nobody shall ever do GRANT ALL TO PUBLIC. But sometimes, people do. Because of the grant, you have emptied dual. 😮
The COUNT(*) is a magic thing. select count(*) from dual returns 1. Unless your instance collapses.
SQL> delete dual;
1 row deleted.
SQL> alter session set "_fast_dual_enabled"=false;
Session altered.
SQL> select count(*) from dual;
COUNT(*)
----------
1
SQL> rollback;
One reader once asked for assistance because he tried it and its db was broken. I won’t help you. Just do it for fun on a database that you can recreate afterwards.
Okay, enough fun for today, let’s see another side effect of excessive rights.
SQL> create user u identified by ***;
User created.
SQL> create role r;
Role created.
SQL> grant create view, create session to u;
Grant succeeded.
SQL> conn u/***@db01
Connected.
SQL> create view v as select trunc(trunc(sysdate)-.5) yesterday from dual;
View created.
SQL> create role r;
Role created.
SQL> select * from v;
YESTERDAY
----------
2018-10-14
SQL> delete from v;
delete from v
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> grant delete on v to r;
grant delete on v to r
*
ERROR at line 1:
ORA-01720: grant option does not exist for 'SYS.DUAL'
SQL> grant select on v to r;
Grant succeeded.
So far so good, I have created a view and granted select only on that view. I cannot delete DUAL. I cannot grant delete.
Now learn about this less-known annoyance
SQL> conn / as sysdba
Connected.
SQL> grant select, update, insert, delete on u.v to r;
Grant succeeded.
What? SYS can give access to my view to a role, even if I have no DELETE right on the underlying?
SQL> grant create session, r to user2;
Grant succeeded.
SQL> conn user2/***@DB01
Connected.
SQL> select * from u.v;
YESTERDAY
----------
2018-10-14
SQL> delete from v;
delete from v
*
ERROR at line 1:
ORA-01031: insufficient privileges
Sofar, it didn’t have so many side effect. It is not uncommon to see scripts that automatically generate grants ; and it is also not uncommon to see those script going doolally.
But, one side effect is preventing future CREATE OR REPLACE statements.
SQL> create or replace view v as select trunc(trunc(sysdate+6,'YYYY')+400,'YYYY')-7 xmas from dual;
create or replace view v as select trunc(trunc(sysdate+6,'YYYY')+400,'YYYY')-7 xmas from dual;
*
ERROR at line 1:
ORA-01720: grant option does not exist for 'SYS.DUAL'
CREATE OR REPLACE no longer work. You need to revoke the right. Either with a DROP VIEW or with
SQL> revoke insert, update, delete on v from r;
Revoke succeeded.
SQL> create or replace view v as select trunc(trunc(sysdate+6,'YYYY')+400,'YYYY')-7 xmas from dual;
View created.
SQL> select * from v;
XMAS
----------
2018-12-25
I’d recommend against using SYS to grant access to user tables. Use the schema owner.