CODEWITHSUNDEEP
oracle-databasesql-grantprivilege
sathish srd
sathish srd

Reputation: 25

Oracle one user's Full(All schema objects) privilege to another user

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;

Upvotes: 0

Views: 862

Answers (1)

Stawros
Stawros

Reputation: 935

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

Upvotes: 1

Related Questions