0

I want to provide grant-all (All the schema objects) of one user to another user. I don't want to the table grant-all. What are the user1 having i.e. table, procedure, triggers, synonyms, functions .. etc that should be accessible by user2 in the same database (Oracle). Actually I created trigger that will provide the grant all privilege to the user2 if anything newly deploying in the user1. But before creating the trigger rest of the schema objects are not privileged. That's my question Thanks 

CREATE OR REPLACE PROCEDURE myddl
(p_ddl IN VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE p_ddl;
END;
/

CREATE OR REPLACE TRIGGER new_obj_grant_prv
AFTER CREATE ON schema
DECLARE
l_jobno NUMBER;
BEGIN
  IF ora_dict_obj_type IN ('TABLE','VIEW') THEN
     dbms_job.submit(l_jobno,'myddl(''GRANT ALL ON '||ora_dict_obj_owner||'.'||ora_dict_obj_name||' TO user2'');');
  END IF;

  IF ora_dict_obj_type IN ('SEQUENCE','PACKAGE') THEN
     dbms_job.submit(l_jobno,'myddl(''GRANT ALL ON '||ora_dict_obj_owner||'.'||ora_dict_obj_name||' TO user2'');');
  END IF;

  IF ora_dict_obj_type IN ('TRIGGER','SYNONYMS') THEN
     dbms_job.submit(l_jobno,'myddl(''GRANT ALL ON '||ora_dict_obj_owner||'.'||ora_dict_obj_name||' TO user2'');');
  END IF;

  IF ora_dict_obj_type IN ('PROCEDURE','FUNCTION') THEN
     dbms_job.submit(l_jobno,'myddl(''GRANT ALL ON '||ora_dict_obj_owner||'.'||ora_dict_obj_name||' TO user2'');');
  END IF;

END;
sathish srd
  • 25
  • 1
  • 10

1 Answers1

1

You can create db-link with user1 credentials, then if user2 will work with your schema over this db-link he will have the same rights as user1.

Update:

In user2 schema - 

CREATE DATABASE LINK MY_DB_LINK
 CONNECT TO USER1
 IDENTIFIED BY **USER1_PASSWORD**
 USING '(DESCRIPTION =
    (ADDRESS =
                (PROTOCOL = TCP)
                (HOST = **HOST**)
                (PORT = 1521))
    (CONNECT_DATA = (SID = **SERVICE_NAME**))
  )'
/

Where HOST and SERVICE_NAME can be founded in tnsnames.ora (it can be founded in \oracle\product\<version>\client_1\network\admin\tnsnames.ora)

Then you can use user1 database objects in user2 schema over the db-link - 

select * from dual@MY_DB_LINK
Stawros
  • 918
  • 1
  • 10
  • 20
  • Can you please explain the db-llink, how to create it? – sathish srd Aug 27 '15 at 14:10
  • Thanks for your comments stawros, I am trying to create the db_link but it throws the Insufficient Privilege Error. Still I am confused with the db_link, db_link is use to connect the different database right ? In my question I want to do the operations in the same db. – sathish srd Sep 07 '15 at 13:06
  • Yep, usually db-link is typically used to connect to another database. Insufficient Privilege Error it means that you should give grant to create db-link before `GRANT CREATE DATABASE LINK to my_schema_name;`. Well, I still dont fully understand the business logic, but maybe better way for you is creating role with all grants of user1 and then give this role to another schemas. – Stawros Sep 07 '15 at 13:33
  • Do you have any other ideas? – sathish srd Sep 18 '15 at 07:41