Sharing Oracle views between users with preexisting table permissions

Question

Is it possible to grant permission to use a view when the grantee already has select permissions on the underlying tables but the grantor does not have "with grant" permissions on those tables? That is, can the grantee use the structure of the view but with their own underlying table permissions instead of the view owner's permissions?

For example, I have users A, B, and C. User A has some tables. Users B and C currently have identical select permissions on those tables. User B builds a view on those tables. Can user C use that view?

Note: I'm aware that if B is given "select with grant" permissions on those tables that this can run on B's permissions, but I don't want B to be able to grant their permissions forward; I only want users who already have permissions of their own to be able to use the structure of the view.

Context: I'd like to do some ad hoc work using some data in the A schema as queried through views already present in the B schema; this involves creating some tables to hold data sets I want to work with, but I don't want to clutter up the B schema with these tables while I'm working. Can I do this as user C by just copying user B's permissions on schema A without also having to recreate all of user B's views in the C schema? Is there an easier way to do this?

Answer 1

The short answer is "No". The documentation is quite clear:

To grant SELECT on a view to another user, either you must own all of the objects underlying the view or you must have been granted the SELECT object privilege WITH GRANT OPTION on all of those underlying objects. This is true even if the grantee already has SELECT privileges on those underlying objects.

Here is a worked example. First the grants...

SQL> grant select on product to b;

Grant succeeded.

SQL> grant select on sales to b;

Grant succeeded.

SQL> grant select on product to c;

Grant succeeded.

SQL> grant select on sales to c;

Grant succeeded.

SQL> conn b/b
Connected.
SQL> select count(*) from a.sales;

  COUNT(*)
----------
        40

SQL> conn c/c
Connected.
SQL> select count(*) from a.sales;

  COUNT(*)
----------
        40

SQL> 

Now let's create a view in B's schema.

SQL> conn b/b
Connected.
SQL> create or replace view tot_product_sales as
  2    select p.name as product_name
  3           , sum(s.pieces) as units_sold
  4           , sum(s.pieces * p.price) as turnover
  5    from a.sales s
  6         join a.product p on p.product_id = s.product_id
  7    group by p.name
  8  /

View created.

SQL> select * from tot_product_sales
  2  /

PRODUCT_NAME                   UNITS_SOLD   TURNOVER
------------------------------ ---------- ----------
Mobile                               1161     928800
Laptop                                970    1552485

SQL> grant select on tot_product_sales to c;
grant select on tot_product_sales to c
                *
ERROR at line 1:
ORA-01720: grant option does not exist for 'A.PRODUCT'


SQL> 

---

Why does the Oracle security model do this? Think of it this way: GRANT SELECT allows user B to do two things with A's data

• select data from A's tables
• with grant option, permit other users to select data from A's tables

There is no granted ability for B to know which other users A has granted privileges to. So B cannot know what privileges C has on A's schema.

Solutions?

1. A grants select ... with grant option to B (and C)?
2. B and C create duplicated views
3. A creates the view and grants select on it to B and C

Which solution suits best will depend on the specific scenario. Generally the third option is the one, because if there are several use cases for a specific presentation of some data it makes sense for the data owner to own that presentation. Other times we have a dedicated reporting schema which collates, aggregates and enriches data from several schemas, in which case the first option is the one.

Your situation appears to fit neither of those, and so it seems like duplicating the views in schema C is your best net.