This article details how to make a “god user” in the database. Creating a god user isn’t something you want to do with your production databases. Generally, you want to follow the principle of least privilege. In other words, only grant the permissions that a user needs. This prevents someone from doing accidental damage (e.g. accidentally dropping a column). Also, if someone compromises your account, it limits the damage an attacker can do.

The following script will create a user if the user doesn’t already exist. If the user does exist, this script will convert them into a “god user.” The script also gives you the opportunity to specify things like a default tablespace or a password.

A couple of warnings:

  1. This script will perform a number of grants, which, on a busy system can cause contention.
  2. This script doesn’t give someone “true” god privileges on the database, but it’s close. For example, in order to give someone the ability to reference any table in a foreign key, you would have to loop through all the tables in the database and issue a GRANT REFERENCE ON … TO .. WITH GRANT OPTION. I chose to stay away from object privileges here, but you can make that modification to the script if it’s something you’d like.
  3. If you’re running this script on a database that is NOT a “sandbox” database, you really need to stop and consider.

Running the “God User” Script

Please read through this script before you execute it.

You’ll want to execute this script as SYS.

First, you’ll need to change GOD_USERNAME to the user name you’d like to make a god user.

Second, the user doesn’t yet exist and you want to create a password for them, change the CONST_USERPASS variable.

Third, if you’d like to specify a default tablespace for the user, you can also do that with the CONST_DEFAULT_TBSPACE variable. Otherwise, leave it blank.

If nothing else, this script can serve as a starting point that you can add to or take away from.

  CONST_USERNAME constant varchar2(30) := 'GOD_USERNAME';
  CONST_USERPASS constant varchar2(50) := 'this_password_sucks_change_it';
  CONST_DEFAULT_TBSPACE varchar2(30) := '';

  -- If the password is specified, go ahead and create the user.
  -- If no password is specified, then just assign CONNECT and RESOURCE to the user
  if CONST_USERPASS is not null then
    execute immediate 'grant connect, resource to ' || CONST_USERNAME || ' identified by "' || CONST_USERPASS || '"';
    execute immediate 'grant connect, resource to ' || CONST_USERNAME;
  end if;

  execute immediate 'grant dba, sysdba, sysoper to ' || CONST_USERNAME || ' with admin option';

  execute immediate 'grant all privileges to ' || CONST_USERNAME || ' with admin option';

  if CONST_DEFAULT_TBSPACE is not null then
    execute immediate 'alter user ' || CONST_USERNAME || ' default tablespace ' || CONST_DEFAULT_TBSPACE;
  end if;

  for v_row in (select tablespace_name 
                from dba_tablespaces 
                where contents = 'PERMANENT') loop

    execute immediate 'alter user ' || CONST_USERNAME || ' quota unlimited on "' || v_row.tablespace_name || '"';

  end loop unlimited_tablespace_loop;

  for v_row in (select privilege
                from dba_sys_privs
                where grantee <> CONST_USERNAME
                select privilege
                from dba_sys_privs
                where grantee = CONST_USERNAME) loop

    execute immediate 'grant ' || v_row.privilege || ' to ' || CONST_USERNAME || ' with admin option';

  end loop leftover_sys_privs;

  for v_row in (select role
                from dba_roles
                where authentication_type = 'NONE'
                select granted_role
                from dba_role_privs
                where grantee = CONST_USERNAME) loop

    execute immediate 'grant ' || v_row.role || ' to ' || CONST_USERNAME || ' with admin option';

  end loop role_privs;


Free Oracle SQL Tuning Guide

Check out 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:

7 SQL Tuning Secrets You can Use Immediately

Leave a Reply

Your email address will not be published. Required fields are marked *