Have you ever faced with a situation that you were asked to create a private database link or create a materialized view or submit a job as a user which you do not know its password?
As a DBA, you have the following options :
- Ask the password. (Usually it is not desired)
- Change password temporarily (This could break the application)
- Use dbms_sys_sql
With dbms_sys_sql, you are able to parse and execute SQL as other users.
Here is example on how to create private database link in scott user.
declare
uid number;
sqltext varchar2(1000) := 'create database link test_link connect to target_user?? identified by target_user_password?? using ''target_DB_TNS??''';
myint integer;
begin
select user_id into uid from all_users where username like 'SCOTT';
myint:=sys.dbms_sys_sql.open_cursor();
sys.dbms_sys_sql.parse_as_user(myint,sqltext,dbms_sql.native,UID);
sys.dbms_sys_sql.close_cursor(myint);
end ;
select owner,db_link from dba_db_links where db_link like 'TEST_LINK%';
OWNER DB_LINK
------------------------------ ----------------------------------------
SCOTT TEST_LINK.WORLD
5 comments:
create or replace procedure links_creator(inOwner varchar2, inLinkName varchar2, inLogin varchar2,
inPass varchar2, inAdresa varchar2) as
uid number;
sqltext varchar2(4000) := 'create inPublic? database link inLinkName?
connect to inLogin? identified by inPass?
using ''inAdresa?''';
myint integer;
begin
if inOwner = 'PUBLIC'
then
sqltext := replace(sqltext, 'inPublic?', 'PUBLIC');
else
sqltext := replace(sqltext, 'inPublic?', '');
end if;
sqltext := replace(sqltext, 'inLinkName?', inLinkName);
sqltext := replace(sqltext, 'inLogin?', inLogin);
sqltext := replace(sqltext, 'inPass?', inPass);
sqltext := replace(sqltext, 'inAdresa?', inAdresa);
select user_id into uid from all_users where username like decode(inOwner, 'PUBLIC', 'SYS', inOwner);
myint := sys.dbms_sys_sql.open_cursor();
sys.dbms_sys_sql.parse_as_user(myint, sqltext, dbms_sql.native, UID);
sys.dbms_sys_sql.close_cursor(myint);
end;
/
SELECT 'begin links_creator('''||u.name
||''','''|| l.NAME
||''','''|| l.userid
||''','''|| l.password
||''','''|| l.host
||''');end;'|| chr(10)||'/'
FROM sys.link$ L, sys.user$ U
WHERE L.OWNER# = U.USER#;
Thanks for the post
Good one :)
Thanks man - it's working :-)
Greetings from Bulgaria
Thanks a lot, Its working fine, really appreciated !
Post a Comment