首页 文章

Oracle一个用户的Full（所有架构对象）特权给另一个用户

提问于
浏览 次
0

我想将一个用户的 grant-all （所有架构对象）提供给另一个用户 . 我不是'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'我的问题谢谢

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;
oracle grant privilege

1 回答

  • 1

    您可以使用user1凭据创建db-link，然后如果user2将使用此db-link上的模式，则它将具有与user1相同的权限 .

    Update:

    在user2架构中 -

    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**))
  )'
/

    可以在tnsnames.ora中创建HOST和SERVICE_NAME（它可以在 \oracle\product\<version>\client_1\network\admin\tnsnames.ora 中创建）

    然后，您可以通过db-link在user2架构中使用user1数据库对象 -

    select * from dual@MY_DB_LINK
    回复于

相关问题