اجرای یک اسکریپت خاص در چند اسکیمای مختلف ( DBMS_SYS_SQL )

اگر بخواهیم یک اسکریپت خاص را در چند اسکیما اجرا کنیم ، راه حل های زیر وجود دارد ( البته به جز این موارد ممکن است راه حل های دیگری هم وجود داشته باشد ) :

1- به تک تک آن اسکیماها وصل شده و اسکریپت مورد نظر را اجرا کنیم.

2- به یک اسکیمای خاص وصل شده و با دستور alter session set current_schema=X ،  اسکیمای خود را تغییر دهیم و اسکریپت مورد نظر را اجرا کنیم.

3- راه حل سوم استفاده از پکیج DBMS_SYS_SQL است. کاربرد این پکیج شبیه DBMS_SQL است با این تفاوت که یک کارکرد اضافه تر با نام "parse as user"  دارد که به شما اجازه میدهد یک SQL خاص را برای دیگر اسکیماها در دیتابیس اجرا کنید.

فقط قبل از اجرا باید به دو نکته خیلی خیلی مهم توجه کنید :

- این پکیج در اوراکل جزو undocumented packageها به حساب می آید و اوراکل به صورت internally برای سرویس های خود از آن استفاده میکند. و برای استفاده در خارج از اوراکل در نظر گرفته نشده است.

- این پکیج بسیار پکیج قدرتمندی است ، زیرا هر دستوری را با آن میتوان اجرا کرد ، به همین دلیل باید شدیدا این موضوع تحت کنترل قرار گیرد که این پکیج توسط چه user قرار است اجرا شود.

با در نظر گرفتن نکات بالا من یک مثال از نحوه استفاده از این پکیج را در زیر شرح داده ام :

1-Create User

create user TEST identified by TEST quota unlimited on USERS;

grant connect,create procedure,select on dba_users to TEST;

grant execute on sys.dbms_sys_sql to TEST;

 

create user C identified by C quota unlimited on USERS;

grant CONNECT,CREATE TABLE to C;

 

create user D identified by D quota unlimited on USERS;

grant CONNECT,CREATE TABLE to D;

 

2-Create Procedure In TEST User

create or replace procedure execute_script_prc(v_script in varchar2) as

  l_cursors dbms_sql.number_table;

  l_result  number;

begin

 for cur in (select user_id 

                from dba_users 

               where username in ('C','D')) loop

  --parse the cursor only if we haven't already

  if (not l_cursors.exists(cur.user_id)) then

    l_cursors(cur.user_id) := sys.dbms_sys_sql.open_cursor;

    --parsing anonymous pl/sql block

    sys.dbms_sys_sql.parse_as_user(

    c => l_cursors(cur.user_id), 

    statement => v_script, 

    language_flag => dbms_sql.native,

    userid => cur.user_id);

  end if;

 --execute script

 l_result := sys.dbms_sys_sql.execute(l_cursors(cur.user_id));

 end loop;

end execute_script_prc;

 

3-Execute script on C,D schemas

--Create table T1 in C,D schemas

exec test.execute_script_prc('create table T1 ( id number )');

--Insert to table T1 in C,D schemas

exec test.execute_script_prc('begin insert into T1 values(1); commit; end;');

• منبع

https://blog.pythian.com/using-dbms_sys_sql-to-execute-statements-as-another-user/

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1325202421535

تغییر پسورد user یک دیتابیس دیگر با استفاده از db link

اگر بخواهیم پسورد یک user را که در دیتابیس دیگری قرار دارد با استفاده از database link تغییر دهیم ، میتوانیم از پروسیجر زیر برای انجام این کار استفاده کنیم.

create or replace procedure change_pass_prc authid current_user as

begin

  cursor usr_cur is

    select username from dba_users@pass_link;

  begin

    for usr_rec in usr_cur loop

      if usr_rec.username = 'USR1' then

        declare

          job binary_integer;

        begin

          dbms_job.submit@pass_link(job,'begin execute immediate ''alter user ' || usr_rec.username ||

                                                             ' identified by "USR1" ''; end;');

          commit;

        end;

        dbms_output.put_line(usr_rec.username || ' password changed.');

      end if;

    end loop; 

  when

  exception when others then 

     raise_application_error(-20000,sqlerrm);

end change_pass_prc;

•  منبع 

https://community.oracle.com/thread/709410