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.