?
Solved

Let Oracle Trigger do the work

Posted on 2014-10-08
2
Medium Priority
?
436 Views
Last Modified: 2014-10-27
I need to create a trigger to create the synonyms when any new table is created.

Currently; this is a manual task as below:-
Create created (say Test). Create table test a(number);

After that the manual tasks as below:-
create public synonym test for test;
grant select,insert,delete on test to emp_role;

I need help in automate the process using trigger. ( The trigger should create the synonym for the new objects and also grant privileges to roles).
0
Comment
Question by:Oranew
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 16

Accepted Solution

by:
Wasim Akram Shaik earned 2000 total points
ID: 40368173
You directly cannot use grants in triggers, you may have to use Pragma and call it via a procedure..

first create the procedure and afterwards create the trigger

Try something like this, this is an extract from the oracle forums website, https://community.oracle.com/thread/402826

create or replace TRIGGER TRIG_CREATE_TABLE
AFTER CREATE ON SCHEMA
begin
  if ora_dict_obj_type='TABLE' then
    grant_privs (ora_dict_obj_owner, ora_dict_obj_name);
  end if;
end;

create or replace procedure grant_privs (p_owner in varchar2, p_tablename in varchar2) authid current_user
is
   pragma autonomous_transaction;
begin
   execute immediate 'grant select, insert, delete, update on ' || p_owner ||'.'|| p_tablename ||' to role_crud_mtd';
execute immediate 'create public synonym '||p_tablename||' for '||p_table_name;

   commit;
end grant_privs;
0
 

Author Closing Comment

by:Oranew
ID: 40407707
Thank you so much.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question