VIEWS for Oracle object privileges

The most important VIEWS for Oracle object privileges are:

USER_TAB_PRIVS
ALL_TAB_PRIVS
ROLE_TAB_PRIVS
DBA_TAB_PRIVS

USER_ROLE_PRIVS
ALL_ROLE_PRIVS
ROLE_ROLE_PRIVS
DBA_ROLE_PRIVS

 

Additionally there are Oracle object privileges that pertain to the whole system called system privileges.

USER_SYS_PRIVS
ALL_SYS_PRIVS
ROLE_SYS_PRIVS
DBA_SYS_PRIVS

Then the VIEWS that contain information about the users in the database are:

USER_USERS
ALL_USERS
DBA_USERS 

There are many others but these are the starting points. Remember to use the “desc” <name> command to see what the view has in it.

We will now create a user with low Oracle object privileges to test the vulnerabilities later on in this book. Please note this is not an example of a securely created user as connect and resource are not recommended default roles so do not do this on your production database. This is in order to get you up and running.

Create_user.sql

create user userexample identified by userexample
default tablespace users
temporary tablespace temp;
grant create session to userexample;
grant connect to userexample;
grant resource to userexample;
alter user userexample quota unlimited on users;
/

Please note the secure method for you to set your personal password in Oracle is by using the password command after the user has been created as follows.

SQL>password <username>

Alter user is used in the scripts in this book with the proviso that the account will have its password changed using the password command. The reason for this is that the  alter user identified by command will show in the redo logs and there will also be clear text on the network in early versions of Oracle. The password command is encrypted and not in the redo.

If we connect as userexample the low privileged user, we can test the VIEWS above.

SQL> conn userexample/userexample@dbinstancename;

Connected.

N.B. Default dbinstancename is “orcl”

You can see the role privileges assigned to your account by entering:

SQL> select * from user_role_privs; USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
USEREXAMPLE                    CONNECT                        NO  YES NO
USEREXAMPLE                    RESOURCE                       NO  YES NO

The aim of an attacker is often to elevate this low account to access higher level Oracle Object Privileges or to include the DBA Role, as we shall see later.

Formating SQL*PLUS can be awkward but as a rule using the set command as follows will help.

Set wrap off
Set linesize 600 (or preference)
Set serveroutput on (for plsql display)

For the purposes of the rest of the book you may find it easier to use SQL*PLUS for the administrative commands and for reports of large datasets use a separate formatted interface such as that provided by SQL Developer or SQLTools which are both free of charge.

Oracle documentation is free, though in-depth support information is via MOSC, which requires a valid license in order to access.

That is the end of the Oracle primer and the next section moves onto Oracle Security.

This is an excerpt from the book "Oracle Forensics: Oracle Security Best Practices", by Paul M. Wright, the father of Oracle Forensics.