Skip navigation

Note:  I have simulated the following behaviors in both 11.2.0.3 and 12.1.0.2.


The OE user needs to access objects in the SH schema. So I create synonyms for the SALES2 table and for the PROFITS view. 

grant create any synonym to sh ; -- run with SYS

create synonym oe.profits FOR sh.profits ;

create synonym oe.sales2 FOR sh.sales2 ;

 

User OE also happens to have the following 2 system privileges:

grant exp_full_database,imp_full_database to oe ; -- run with SYS

Then I use the followings commands to verify that OE can access those SH objects

conn oe/oracle

sho user

delete profits ;

rollback ;

select count(*)  countProfits from profits ;

select count(*) countSAles2  from sales2 ;

select count(*) "# of Privileges" from SESSION_PRIVS ;


TEST 1:

Connected

 

USER is "OE"

 

Error starting at line : 10 in command -

delete profits

Error at Command Line : 10 Column : 8

Error report -

SQL Error: ORA-01752: cannot delete from view without exactly one key-preserved table

01752. 00000 -  "cannot delete from view without exactly one key-preserved table"

*Cause:    The deleted table had

           - no key-preserved tables,

           - more than one key-preserved table, or

           - the key-preserved table was an unmerged view.

*Action:   Redefine the view or delete it from the underlying base tables.

 

Rollback complete.

 

                           COUNTPROFITS

---------------------------------------

                                 916039

 

                            COUNTSALES2

---------------------------------------

                               10107273

 

                        # of Privileges

---------------------------------------

                                     99

 

That was my original situation.   Everything back then was OK: I could SELECT from my SALES2 table, I could SELECT from my PROFITS view.  The DELETE went awry but not because of a rights problem.


TEST 2:  later, it was decided that user OE should no longer do datapump jobs, so

revoke imp_full_database ,exp_full_database from oe ;  -- with SYS


Connected

 

USER is "OE"

 

Error starting at line : 10 in command -

delete profits

Error at Command Line : 10 Column : 8

Error report -

SQL Error: ORA-00942: table or view does not exist

00942. 00000 -  "table or view does not exist"

*Cause:

*Action:

 

Rollback complete.

 

 

Error starting at line : 12 in command -

select count(*)  countProfits from profits

Error at Command Line : 12 Column : 36

Error report -

SQL Error: ORA-00942: table or view does not exist

00942. 00000 -  "table or view does not exist"

*Cause:

*Action:

 

Error starting at line : 13 in command -

select count(*) countSAles2  from sales2

Error at Command Line : 13 Column : 35

Error report -

SQL Error: ORA-00942: table or view does not exist

00942. 00000 -  "table or view does not exist"

*Cause:

*Action:

                        # of Privileges

---------------------------------------

                                     19

 

All of a sudden, I could not SELECT from my table nor from my view, much less DELETE from my view.  I got these ORA-00942 errors instead.


TEST 3: with exp_full_database only

grant exp_full_database  to oe ;  -- with SYS


Connected

 

USER is "OE"

 

Error starting at line : 6 in command -

delete profits

Error at Command Line : 6 Column : 8

Error report -

SQL Error: ORA-01031: insufficient privileges

01031. 00000 -  "insufficient privileges"

*Cause:    An attempt was made to perform a database operation without

           the necessary privileges.

*Action:   Ask your database administrator or designated security

           administrator to grant you the necessary privileges

 

Rollback complete.

 

                           COUNTPROFITS

---------------------------------------

                                 916039

 

                            COUNTSALES2

---------------------------------------

                               10107273

 

 

                        # of Privileges

---------------------------------------

                                     29


With exp_full_database only, the situation is close to the original one, except that I do not have the right to modify (DELETE) my view data, but I can SELECT from it.



TEST 4: with imp_full_database only

Connected

 

USER is "OE"

Error starting at line : 5 in command -

delete profits

Error at Command Line : 5 Column : 8

 

Error report -

SQL Error: ORA-01752: cannot delete from view without exactly one key-preserved table

01752. 00000 -  "cannot delete from view without exactly one key-preserved table"

*Cause:    The deleted table had

           - no key-preserved tables,

           - more than one key-preserved table, or

           - the key-preserved table was an unmerged view.

 

*Action:   Redefine the view or delete it from the underlying base tables.

 

Rollback complete.

 

                           COUNTPROFITS

---------------------------------------

                                 916039

 

                            COUNTSALES2

---------------------------------------

                               10107273

 

 

                        # of Privileges

---------------------------------------

                                     95

 

With imp_full_database only, the situation is  as the original one.  So, having gone full circle, we can safely say that what allowed us, in the original situation, to view and modify data from a view that did not belong to us, was imp_full_database (hence the title of this post).

You will have noticed that at the end of each test, there's the number of session privileges.  And the bigger that number, the more rights I had.  In fact, exp_full_database and imp_full_database are roles, which each contain a set of privileges.  As you can see in the enclosed pictures (taken from the DBA part of SQLDeveloper), imp_full_database has many more privileges  than exp_full_database.   And what I did not know was that one of those many privileges is the "SELECT ANY TABLE" privilege, which allows you to SELECT from a view!