Hello Guys,
Can anybody help with this trigger, the point is to fire the trigger once a user creates a table on a specific tablespace X.
The trigger must give grant select rights to other users that are associated to the same tablespace X and at the same time create a synonym so the other users can see the first's user table.
What I came up with so far is this:
CREATE OR REPLACE TRIGGER grt_crt_oncrt_database
AFTER CREATE ON DATABASE
DECLARE
v_job NUMBER;
l_SQL VARCHAR2(4000);
BEGIN
IF ora_sysevent = 'CREATE' AND ora_dict_obj_type = 'TABLE' THEN
FOR cur IN (
SELECT * FROM all_tables WHERE tablespace_name = 'X')
LOOP
l_sql := 'execute immediate ''grant select, insert, update on ' || cur.owner || '.' || cur.table_name || ' to ROLE'';';
dbms_job.submit(job=>v_job, what=>l_SQL);
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/
Can anybody help.
Regards