Configure Oracle user in Enterprise Manager to CREATE VIEW on any of his tables

Question

Is it possible to configure an Oracle user in Enterprise Manager, so that he can later create views on any future, yet unknown table in his schema?

I am currently running into "ORA-01031 insufficient privileges" error, because no individual SELECT or data modification rights have been granted (only through role, plus the CONNECT role including CREATE VIEW - EDIT: Wrong! Does no longer!).

However, I run a DB creation script, executed from SQL Developer, which creates tables and is now supposed to create a view based on the previously created tables (just like another table). The script does not "know" the user account who is executing it, and is basically doing no rights/privileges settings at all.

It's basically like:

CREATE TABLE Members (
    ID        number(10),
    Name      varchar2(100),
    Street_No varchar2(100),
    ZIP       number(5),
    City      varchar2(100)
);

CREATE VIEW MembersNamesOnly AS
    SELECT ID, Name FROM Members;

Detecting the current user (executing the script) and grant himself the rights does not work, and it would result in annoying code overhead, granting rights for many, individual tables. And problems again, when other users shall use the view, but get their rights only through roles, without tedious user-table-right assignments for each user and table.

Is such a setting possible? Create tables and then views based on them, in a single script? I'm currently afraid I can't use any views at all, without heavy re-writing of all the existing database creation code.

Answer 1

I'm not sure if I'm understanding your question properly. I created a user with the ability to create views for his own schema.

SQL> create user view_user identified by password default tablespace users quota unlimited on users;

User created.

SQL> grant create session, create table, create view to view_user;

Grant succeeded.

SQL> connect view_user
Enter password: 
Connected.
SQL> CREATE TABLE Members (ID number(10), Name varchar2(100), Street_No varchar2(100), ZIP number(5), City varchar2(100));

Table created.

SQL> CREATE VIEW MembersNamesOnly AS SELECT ID, Name FROM Members;

View created.

SQL> 

Answer 2

To query data from the tables owned by an user, the user does not need any additinoal privileges. Your script fails because the user does not have the privilege to create a view.

GRANT CREATE VIEW TO user;

Note: privileges granted through a role have no effect in a PL/SQL script.