What Privilege Is Needed for a User to Create Views

Q

What Privilege Is Needed for a User to Create Views? - Oracle DBA FAQ - Managing Oracle User Accounts, Schema and Privileges

✍: FYIcenter.com

A

To be able to create views in a user's own schema, the user needs to have the CREATE VIEW privilege, or the CREATE ANY VIEW privilege, which is more powerful, and allows the user to create views in other user's schema. The following tutorial exercise gives you a good example on CREATE VIEW privilege:

>.\bin\sqlplus /nolog
SQL> CONNECT DEV/developer

SQL> CREATE VIEW fyi_view AS SELECT * FROM fyi;
ORA-01031: insufficient privileges

SQL> disconnect
SQL> connect SYSTEM/fyicenter

SQL> GRANT CREATE VIEW TO dev;
Grant succeeded.

SQL> disconnect
SQL> CONNECT DEV/developer

SQL> CREATE VIEW fyi_view AS SELECT * FROM fyi;
View created.

SQL> DROP VIEW fyi_view;
View dropped.

SQL> CREATE VIEW fyi_view AS SELECT * FROM fyi;
View created.

As you can see, "dev" can create and drop views now.

2007-05-01, 5267👍, 0💬