ORA-01720 on GRANT SELECT on view to user
Somehwere inmidst 11g versions (and keeps on to stop working in 12c) a thing we were doing in some GRANT script stopped working and throws an ORA-01720 suddenly.
A user creates a VIEW after the following SQL:
create VIEW SYSTABLES (CREATOR, NAME, COLCOUNT, TYPE)
AS
SELECT
OWNER,TABLE_NAME,
( select
count(*)
from
ALL_TAB_COLUMNS R2
where
R2.TABLE_NAME = R1.TABLE_NAME
and
R2.OWNER = 'USER'),
'T'
from
ALL_TABLES R1
where
OWNER = 'USER';
When the user who has created the above view, wants to issue the follwoing GRANT, it happens:
SQL> grant SELECT on user.systables TO user1;