Database Administration (MOSC)

MOSC Banner

ORA-01720 on GRANT SELECT on view to user

edited Nov 2, 2017 5:00AM in Database Administration (MOSC) 14 commentsAnswered

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;

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center