Re: GRANT CREATE VIEW proble

From: <krichine_at_juno.com>
Date: 29 Mar 2006 09:42:28 -0800
Message-ID: <1143654148.651022.88780@e56g2000cwe.googlegroups.com>

Lukasz,

Oracle does not have owner-level privileges, only object-level privileges.

http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_8004.htm#i2065510 The owner of the schema containing the view must have the privileges necessary to either select, insert, update, or delete rows from all the tables or views on which the view is based. The owner must be granted these privileges directly, rather than through a role.

However, owner-level privileges can be effectively emulated with DDL triggers. For example,

create or replace trigger table_owner.autogrant after create on schema
declare
  j pls_integer;
begin
  if ora_dict_obj_type = 'TABLE'
  then
    dbms_job.submit (job=>j, what=>'begin execute immediate ''grant select on ' || ora_dict_obj_name || ' to view_owner''; end;');   end if;
end;
/

Now when table_owner creates table, select on it will automatically be granted to view_owner.

See "Responding to System Events through Triggers " in http://download-east.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm Received on Wed Mar 29 2006 - 11:42:28 CST