Oracle Database views and permissions

Oracle Database views and permissions

Three users, test1, test2, and test3, have DBA privileges.

Use the TEST1 user to create a table T1 and grant its query permission to TEST2:

SQL> create table t1 as select * from all_objects;

The table has been created.

SQL> grant select on t1 to test2;

Authorization successful.

SQL> create table t1 as select * from all_objects;

The table has been created.

SQL> grant select on t1 to test2;

Authorization successful.

Create a view with the TEST2 user. The base table of the view is TEST1.T1, and grant the query permission to TEST3:

SQL> create view v_t1 as select * from test1.t1;

The view has been created.

SQL> grant select on v_t1 to test3;

Authorization successful.

SQL> create view v_t1 as select * from test1.t1;

The view has been created.

SQL> grant select on v_t1 to test3;

Authorization successful.

TEST3 user query view TEST2.V _ T1:

SQL> select * from test2.v _ t1 where rownum <1;

Select * from test2.v _ t1 where rownum <1

*

ERROR is located in row 1st:

ORA-01031: insufficient Permissions

SQL> select * from test2.v _ t1 where rownum <1;

Select * from test2.v _ t1 where rownum <1

*

ERROR is located in row 1st:

ORA-01031: insufficient Permissions

We can see that an error with insufficient permissions is reported, even if the TEST3 user has the DBA permission.

What the hell is going on?

In fact, two points of attention need to be paid to view permissions:

1. In the view, a stored procedure similar to a definer permission shields the role permission. For example, if TEST1 does not explicitly grant the SELECT permission for table T1 to TEST2, TEST2 reports a ORA-01031 error when creating the view V_T1, even if the TEST2 user has DBA role permissions.

2. if the table of user B is referenced in user A's view, user A gives the view access permission to user C, therefore, user B's table access permission is granted to user C in disguise. Therefore, user A must have the permission to transfer the table access permission of user B to user C, that is, user B must use the with grant option when granting A permission.

Obviously, because of, TEST3 cannot access the view. User TEST1 executes the following operations to solve this problem:

SQL> grant select on t1 to test2 with grant option;

Authorization successful.

SQL> grant select on t1 to test2 with grant option;

Authorization successful.

The same applies to the UPDATE and DELETE permissions of views.

During the test, there is a symptom, which is a bit interesting. If TEST2 does not explicitly grant the SELECT permission of V_T1 to TEST3, and TEST3 has the select any table or DBA permission, no error of insufficient permissions will be reported when querying this view. Because the select any table permission exists, all user tables can be accessed. However, when explicitly granting table permissions, it seems that the table permissions have a higher priority and are not combined with system permissions and role permissions. In my test, Oracle 9.2.0.8 for Windows is used.