Most DBAs are familiar with the quirk of Oracle’s security model that does not allow a user to utilize privileges granted via roles when executing PL/SQL stored procedures. However, I recently ran into this same quirk when trying to create a simple view. Essentially, I discovered that the user could not create a view when access to the table underlying the view was granted via a role. I could only create the view when access to the table underlying the view was granted directly to the user.
I started by creating a new user, TESTUSR and granted the user the privileges needed to create the view:
SQL> create user testusr identified by secret 2 default tablespace users 3 temporary tablespace temp; User created. SQL> grant create session to testusr; Grant succeeded. SQL> grant create view to testusr; Grant succeeded.
Next I created a role, R_TESTUSR, and granted SELECT privileges on the table underlying the view (SCOTT.DEPT) to that role:
SQL> create role r_testusr; Role created. SQL> grant select on scott.dept to r_testusr; Grant succeeded. SQL> grant r_testusr to testusr; Grant succeeded.
With everything in place, I signed in as the new user, confirmed that I could access the table underlying my view, and then tried to make the view:
SQL> connect testusr/secret Connected. SQL> select * from scott.dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 4 rows selected. SQL> create or replace view dept_v as select * from scott.dept; create or replace view dept_v as select * from scott.dept * ERROR at line 1: ORA-01031: insufficient privileges
As shown above, even though the user TESTUSR can query the SCOTT.DEPT table using the privileges granted via the R_TESTUSR role, the user cannot create a view on that same table. Granting privileges on the SCOTT.DEPT table directly to TESTUSR (i.e. outside of the role) resolved the issue:
SQL> connect system/manager Connected. SQL> grant select on scott.dept to testusr; Grant succeeded. SQL> connect testusr/secret Connected. SQL> create or replace view dept_v as select * from scott.dept; View created
After some searching, I found that this behavior is documented pretty clearly, but this is the first time I’ve bumped into it.
