TIP 87# : Create/Remove jobs for other users

I am sure that every DBA faces a situation to remove or submit a job for other user, If DBA knows the password of other user, life will be easy and it is as simple as login with the user and submit a job with dbms_job.submit or remove it with dbms_job.remove.

What if DBA has not the password, If DBA tries to remove other user jobs, the following error will be shown :


ORA-23421: job number nnnn is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 536
ORA-06512: at "SYS.DBMS_JOB", line 261
ORA-06512: at line 1


Also DBA can not submit job with dbms_job for other users with dbms_job if DBA does not know the password.

Fortunately, Oracle has undocumented dbms_ijob package which let you as DBA to submit jobs as other users and also remove jobs of other users.

Here is a sample on how to submit a job for user TEST.


declare
job_num number;
nlsvar varchar2(4000);
envvar raw(32);
begin
select nls_env,misc_env into nlsvar,envvar from dba_jobs where rownum<2 and nls_env is not null and misc_env is not null ;
select max(job)+1 into job_num from dba_jobs;
sys.dbms_ijob.submit(job=>job_num,luser=>'TEST',puser=>'TEST',cuser=>'TEST',what=>'insert into tst values(1);' ,next_date=>sysdate+1/(24*60), interval=>'trunc(sysdate,''MI'')+1/48', broken=>FALSE,nlsenv=>nlsvar,env=>envvar);
dbms_output.put_line(job_num);
end;
/
commit;

nlsenv and env should not be passed NULL,In above sample to make it simple, I copied it from the existing jobs. puser (privileged user), luser (login user), cuser(schema user) are the same as what we have in dba_jobs. Job number needs to be passed to dbms_ijob, to make sure its uniqueness, a number higher than max job number is passed.

In order to remove any job , the following simple code can be run.


dbms_ijob.remove(job_number??);


7 comments:

Anonymous said...

Shervin, it's a useful trick, thank you.
I wanted to add two other ways to get the job done which the first one performs as your trick while the other one just changes the schema_user (not the log_user and priv_user):
suppose you are connected as sys or any other user having the "become user" privilege, then you can simply use:
exec sys.kupp$proc.change_user('CMS');
to become the CMS user, without knowing or changing the user's password and then you can use dbms_job as usual to submit the job.
second way to achieve something similar but not exactly the same as yours is to use:
alter session set current_schema=cms;
in this case just the schema_user will be changed (you can see that in the dba_jobs) and if you use qualified object names, you can accieve the same result.
here are samples;

------------------------
exec sys.kupp$proc.change_user('CMS');

declare
job_num number;
begin
select max(job)+1 into job_num from dba_jobs;

dbms_job.submit(job=>job_num,
what=>'insert into tst values(1);',
next_date=>sysdate+1/(24*60),
interval=>'trunc(sysdate,''MI'')+1/48');

dbms_output.put_line(job_num);
end;
/
commit;




col job for 999
col log_user for a14
col priv_user for a14
col schema_user for a14
col what for a49
set lines 120
select job, log_user, priv_user, schema_user, what, broken, failures from dba_jobs;

------------------------
regards,

Shervin said...

Hi,

Thanks for your input.
The problem with current_schema is that the job is not created at CMS - Still the log_user and priv_user would be SYS although schema_user would be CMS. (Other words it is SYS jobs not CMS job)
Also with current_schema you can not remove a job which is owned by CMS (log_user is CMS), please test this and let me know the result. I have already tested it.
dbms_ijob is useful when you have a job which is owned by user X and you want to drop it, or you want to submit a job in User X (owns by user X). Job and DB links ownership are not changed with change_user or cirrent_schema.

Hope this helps.

Regards,

Shervin.

Anonymous said...

Hi Shervin,

I suggested two alternatives to your trick. As I mentioned in my comment the first and exact alternative is using sys.kupp$proc.change_user('CMS');
if you are connected as sys or have "become user" privilege, this way you get exactly the same results as your trick. here is the sample spool:

SQL> connect / as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> col job for 999
SQL> col log_user for a14
SQL> col priv_user for a14
SQL> col schema_user for a14
SQL> col what for a49
SQL> set lines 120
SQL> select job, log_user, priv_user, schema_user, what, broken, failures from dba_jobs;

JOB LOG_USER PRIV_USER SCHEMA_USER WHAT B FAILURES
---- -------------- -------------- -------------- ------------------------------------------------- - ----------
1 SYSMAN SYSMAN SYSMAN EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); N 0
21 SYSMAN DBSNMP DBSNMP BEGIN MGMT_BSLN.COMPUTE_ALL_STATISTICS;/*DB*/END; N 0
22 SYSMAN DBSNMP DBSNMP BEGIN MGMT_BSLN.SET_ALL_THRESHOLDS;/*DB*/END; N 0

SQL> exec sys.kupp$proc.change_user('CMS');

PL/SQL procedure successfully completed.

SQL> show user
USER is "SYS"
SQL> declare
2 job_num number;
3 begin
4 select max(job)+1 into job_num from dba_jobs;
5
6 execute immediate 'alter session set current_schema=cms';
7
8 dbms_job.submit(job=>job_num,
9 what=>'insert into tst values(1);',
10 next_date=>sysdate+1/(24*60),
11 interval=>'trunc(sysdate,''MI'')+1/48');
12
13 dbms_output.put_line(job_num);
14 end;
15 /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select job, log_user, priv_user, schema_user, what, broken, failures from dba_jobs;

JOB LOG_USER PRIV_USER SCHEMA_USER WHAT B FAILURES
---- -------------- -------------- -------------- ------------------------------------------------- - ----------
1 SYSMAN SYSMAN SYSMAN EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); N 0
21 SYSMAN DBSNMP DBSNMP BEGIN MGMT_BSLN.COMPUTE_ALL_STATISTICS;/*DB*/END; N 0
22 SYSMAN DBSNMP DBSNMP BEGIN MGMT_BSLN.SET_ALL_THRESHOLDS;/*DB*/END; N 0
46 CMS CMS CMS insert into tst values(1); N

SQL> exec dbms_job.remove(46);

PL/SQL procedure successfully completed.

SQL> select job, log_user, priv_user, schema_user, what, broken, failures from dba_jobs;

JOB LOG_USER PRIV_USER SCHEMA_USER WHAT B FAILURES
---- -------------- -------------- -------------- ------------------------------------------------- - ----------
1 SYSMAN SYSMAN SYSMAN EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); N 0
21 SYSMAN DBSNMP DBSNMP BEGIN MGMT_BSLN.COMPUTE_ALL_STATISTICS;/*DB*/END; N 0
22 SYSMAN DBSNMP DBSNMP BEGIN MGMT_BSLN.SET_ALL_THRESHOLDS;/*DB*/END; N 0

SQL> spool off

Anonymous said...

Hi Again (just because comment size should be less than 4096),

The second alternative as I mentioned in my comment doesn't change the log_user and priv_user but still you can remove the job, the only problem is that any objects referenced in the job definition should be fully qualified (cms.tst).
A sample spool is:

SQL> connect / as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> col job for 999
SQL> col log_user for a14
SQL> col priv_user for a14
SQL> col schema_user for a14
SQL> col what for a49
SQL> set lines 120
SQL> select job, log_user, priv_user, schema_user, what, broken, failures from dba_jobs;

JOB LOG_USER PRIV_USER SCHEMA_USER WHAT B FAILURES
---- -------------- -------------- -------------- ------------------------------------------------- - ----------
1 SYSMAN SYSMAN SYSMAN EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); N 0
21 SYSMAN DBSNMP DBSNMP BEGIN MGMT_BSLN.COMPUTE_ALL_STATISTICS;/*DB*/END; N 0
22 SYSMAN DBSNMP DBSNMP BEGIN MGMT_BSLN.SET_ALL_THRESHOLDS;/*DB*/END; N 0

SQL> alter session set current_schema=cms;

Session altered.

SQL> show user
USER is "SYS"
SQL> declare
2 job_num number;
3 begin
4 select max(job)+1 into job_num from dba_jobs;
5
6 execute immediate 'alter session set current_schema=cms';
7
8 dbms_job.submit(job=>job_num,
9 what=>'insert into cms.tst values(1);',
10 next_date=>sysdate+1/(24*60),
11 interval=>'trunc(sysdate,''MI'')+1/48');
12
13 dbms_output.put_line(job_num);
14 end;
15 /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select job, log_user, priv_user, schema_user, what, broken, failures from dba_jobs;

JOB LOG_USER PRIV_USER SCHEMA_USER WHAT B FAILURES
---- -------------- -------------- -------------- ------------------------------------------------- - ----------
1 SYSMAN SYSMAN SYSMAN EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); N 0
21 SYSMAN DBSNMP DBSNMP BEGIN MGMT_BSLN.COMPUTE_ALL_STATISTICS;/*DB*/END; N 0
22 SYSMAN DBSNMP DBSNMP BEGIN MGMT_BSLN.SET_ALL_THRESHOLDS;/*DB*/END; N 0
47 SYS SYS CMS insert into cms.tst values(1); N

SQL> exec dbms_job.remove(47);

PL/SQL procedure successfully completed.

SQL> select job, log_user, priv_user, schema_user, what, broken, failures from dba_jobs;

JOB LOG_USER PRIV_USER SCHEMA_USER WHAT B FAILURES
---- -------------- -------------- -------------- ------------------------------------------------- - ----------
1 SYSMAN SYSMAN SYSMAN EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); N 0
21 SYSMAN DBSNMP DBSNMP BEGIN MGMT_BSLN.COMPUTE_ALL_STATISTICS;/*DB*/END; N 0
22 SYSMAN DBSNMP DBSNMP BEGIN MGMT_BSLN.SET_ALL_THRESHOLDS;/*DB*/END; N 0

SQL> spool off

Anonymous said...

Hi Shervin, I repeated the test for db link, current_schema dowsn't work but change_user works:

SQL> connect / as sysdba
Connected.
SQL> col db_link for a49
SQL> set lines 120
SQL> ----------------------- Test 1 ------------------------
SQL> select owner, db_link from dba_db_links;

no rows selected

SQL> CREATE DATABASE LINK cms."TEST" USING 'MYDB';

Database link created.

SQL> select owner, db_link from dba_db_links;

OWNER DB_LINK
------------------------------ -------------------------------------------------
SYS CMS.TEST

SQL> drop DATABASE LINK cms."TEST";

Database link dropped.

SQL> select owner, db_link from dba_db_links;

no rows selected

SQL> ----------------------- Test 2 ------------------------
SQL> show user
USER is "SYS"
SQL> exec sys.kupp$proc.change_user('CMS');

PL/SQL procedure successfully completed.

SQL> grant create database link to cms;

Grant succeeded.

SQL> select owner, db_link from dba_db_links;

no rows selected

SQL> CREATE DATABASE LINK cms."TEST" USING 'MYDB';

Database link created.

SQL> select owner, db_link from dba_db_links;

OWNER DB_LINK
------------------------------ -------------------------------------------------
CMS CMS.TEST

SQL> drop DATABASE LINK cms."TEST";

Database link dropped.

SQL> select owner, db_link from dba_db_links;

no rows selected

SQL> connect / as sysdba
Connected.
SQL> revoke create database link from cms;

Revoke succeeded.

SQL> ----------------------------- Test 3 ------------------------
SQL> grant create database link to cms;

Grant succeeded.

SQL> alter session set current_schema=cms;

Session altered.

SQL> select owner, db_link from dba_db_links;

no rows selected

SQL> CREATE DATABASE LINK cms."TEST" USING 'MYDB';
CREATE DATABASE LINK cms."TEST" USING 'MYDB'
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> select owner, db_link from dba_db_links;

no rows selected

SQL> drop DATABASE LINK cms."TEST";
drop DATABASE LINK cms."TEST"
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> select owner, db_link from dba_db_links;

no rows selected

SQL> connect / as sysdba
Connected.
SQL> revoke create database link from cms;

Revoke succeeded.

SQL> spool off

madireddi vasu said...

Thanks for the tip.
Its worked.
exec dbms_ijob.remove(job);

Dmitriy Kucheriyvy said...

Unfortunately, in 12c version these techniques don't work.