August 2, 2012

PROXY: How to modify private JOBs or DB_LINKs

【PROXY is not a new knowledge discovered by me. On internet there are serveral articles introduce it. Still I want to share it here with one of my case, since seems many DBA are not aware of it.】
Every DBA should be familiar with below command:
alter session set current_schema=GPDC;

With this command, we can still have SYS privilege while our enviroment switch to GPDC.
We create a table, it will build under schema GPDC and its default tablespace:

SYS @ xxxtmp > show user
USER is "SYS"

SYS @ xxxtmp > select username,default_tablespace from dba_users where username='SYS' or username='GPDC';
USERNAME   DEFAULT_TABLESPACE
---------- --------------------
SYS        SYSTEM
GPDC       TOPZZZZZZT01

SYS @ xxxtmp > alter session set current_schema=GPDC;
Session altered.

SYS @ xxxtmp >  create table TABTEST as select * from dba_objects where rownum<10;
Table created.

GAPC @ apcitmp >  select owner,segment_name,tablespace_name from dba_segments where segment_name='TABTEST';
OWNER      SEGMENT_NAME         TABLESPACE_NAME
---------- -------------------- --------------------
GAPC       TABTEST              TOPZZZZZZT01

But sometimes this command still can't meet our requirement, since you are still SYS:
SYS @ xxxtmp > alter session set current_schema=GPDC;
Session altered.

SYS @ xxxtmp > show user
USER is "SYS"

When we want to modify some real private objects such as DB link or Jobs, it will fail with below:
SYS @ xxxtmp > conn / as sysdba
Connected.

SYS @ xxxtmp > select JOB,SCHEMA_USER,BROKEN from dba_jobs;
JOB SCHEMA_USER BRO
---------- ------------------------------ ---
3587 GPDC Y
4187 GPDC Y

SYS @ xxxtmp > exec dbms_job.remove(3587);
BEGIN dbms_job.remove(3587); END;
*
ERROR at line 1:
ORA-23421: job number 3587 is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 770
ORA-06512: at "SYS.DBMS_JOB", line 180
ORA-06512: at line 1

SYS @ xxxtmp > alter session set current_schema=GPDC;
Session altered.

SYS @ xxxtmp > exec dbms_job.remove(3587);
BEGIN dbms_job.remove(3587); END;
*
ERROR at line 1:
ORA-23421: job number 3587 is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 770
ORA-06512: at "SYS.DBMS_JOB", line 180
ORA-06512: at line 1

Require the password from application team for these priviate account is one method. But what can we do now if we can't get the password?
Answer is :PROXY.
Via PROXY, we create a different credentail and then attach it to target schema:
SYS @ xxxtmp > create user kevin identified by kev12_vin;
User created.

SYS @ xxxtmp > grant dba to kevin;
Grant succeeded.

SYS @ xxxtmp > alter user GPDC grant connect through kevin;
User altered.

SYS @ xxxtmp > conn kevin[GPDC]/kev12_vin;
Connected.

GPDC @ xxxtmp > show user;
USER is "GPDC"

GPDC @ xxxtmp > exec dbms_job.remove(3587);
PL/SQL procedure successfully completed.

SEE? With this method we input a different credential, but logins as true GPDC.

1 Comments:

Anonymous said...

InstantProxies.com offers private HTTP proxies with the most advanced proxy service on the web.
private proxies

Post a Comment