CODEWITHSUNDEEP
oracle-databasevieworacle11gprivilegessynonym
Kenny
Kenny

Reputation: 1982

Privileges on views/synonyms vs on underlying tables

Referring to here and here

Would a user need both SELECT /INSERT/DELETE/UPDATE, etc. privileges on the view AND on the underlying table to be able to perform these actions ? Or privileges on EITHER table/view is enough ?

In other words, consider a user A owning the table T and the view V (constructed from T). Can a user B with SELECT right on V execute a SELECT if he does not SELECT right on T, and vice-versa ? If he could, wouldn't that mean the View privilege "overrides" the table privilege, as A does not give him right over T ?

Update

In a related question, how about synonym ? From what I understand in a book, users need both SELECT privileges on the synonym and the underlying table. This will be different from views.

On the other hand, Oracle seems to indicate that synonyms behave similar to views.

A user can be granted the SELECT privilege on a synonym or a view without being explicitly granted the SELECT privilege on the originating table

Update 2

Following everyone's answer that we only need the privileges on view to select the table (at least what the view sees from the table) and no privilege on table is needed, let's consider this scenario :

  • Table T belongs to A
  • A GRANT SELECT ON T to B (without GRANT OPTION)
  • B CREATE VIEW V AS SELECT * FROM A.T
  • B GRANT SELECT ON V TO C
  • C performing SELECT * FROM B.V

According to what you have said, C will be able to select from V, therefore equivalent to selecting from T. Is it that cheating ? B is effectively letting C seeing A.T although C does not have the right on T and B does not have GRANT OPTION. Is there a security hole somewhere ?

Upvotes: 1

Views: 2000

Answers (2)

Chris Travers
Chris Travers

Reputation: 26464

Yes. Usually the view runs as the view owner, and the user runs with permission on the view. So user b only needs access to the view.

However when looking at this sort of question, you may want to look into row level security as well. This works by granting access to a portion of the table to a given user or group (i.e. effectively enforcing where clauses at the end of queries). Depending on your use case, it may be simpler or more complex to manage.

Upvotes: 2

user5683823
user5683823

Reputation:

One of the fundamental uses of views is to protect privacy. A base table may have confidential information that some users don't need to see (for example, in an employee table, you may have salary). Some users need access to query (select), or to update, only certain fields from the base table, without having access to the full information. For example: select phone number, or update address (but no access to see salary or bonus). Then one would create a view and give those users "select" and "update" privileges on the view only, and not on the base table. (The select still goes against the base table, but the COLUMNS will be limited to those included in the view... updates can/will be made to the base table, but again, only for values in the columns included in the view.) The view can limit not only the columns, but also the rows - for example, with a WHERE clause in the view, you may exclude the CEO from the view completely.

So, one of the main uses of views is based exactly on that: some users may have privileges on the view, but not on the base table.

Upvotes: 3

Related Questions