2

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.

2 Answers 2

0

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> 
1
  • Thanks for testing, the problem was that I expected the CREATE VIEW privilege to be granted through the CONNECT role, as it has been in old versions of Oracle. Current versions have only the CREATE SESSION privilege granted through CONNECT.
    – Erik Hart
    Nov 5, 2013 at 10:39
1

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.

1
  • I found out that the CONNECT role contained the CREATE VIEW privilege in older versions of Oracle, but does no longer for some time now. The script user could create tables, sequences and more through the RESOURCE role, but not views.
    – Erik Hart
    Nov 5, 2013 at 10:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.