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:
- This script will perform a number of grants, which, on a busy system can cause contention.
- 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.
- 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.
declare
CONST_USERNAME constant varchar2(30) := 'GOD_USERNAME';
CONST_USERPASS constant varchar2(50) := 'this_password_sucks_change_it';
CONST_DEFAULT_TBSPACE varchar2(30) := '';
begin
-- 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 || '"';
else
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;
<<unlimited_tablespace_loop>>
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;
<<leftover_sys_privs>>
for v_row in (select privilege
from dba_sys_privs
where grantee <> CONST_USERNAME
----
minus
----
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;
<<role_privs>>
for v_row in (select role
from dba_roles
where authentication_type = 'NONE'
----
minus
----
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;
end;
/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: tuningsql.com/secrets
