2011-02-23

revoke through a db link

Why would someone do such a thing? Following will end up an active a user session stuck waiting "SQL*Net message from dblink".



sqlplus /nolog

connect / as sysdba


drop user a cascade;

drop user b cascade;


create user a identified by a;

grant create session to a;

grant create procedure to a;

grant create database link to a;


create user b identified by b;

grant create session to b;


connect a/a

declare
comm varchar2(200);
begin
select 'create database link b connect to b identified by b using '''||
sys_context('userenv','db_unique_name')||'''' into comm
from dual;
execute immediate comm;
end;
/


create or replace procedure p as
begin
execute immediate 'revoke execute on p from b';
end;
/


grant execute on p to b;

exec a.p@b

No comments:

Post a Comment

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. I have received Oracle ACE nomination. My main focus is on projects involving Oracle database. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.