This
practice uses common UNIX and Windows NT Oracle utilities to review database
user objects’ key roles and
permissions. You will:
1.
See
default user objects and their default passwords.
2.
Understand
V$PWFILE_USER data dictionary view, and how to use this view to inspect SYSDBA
or SYSOPER privileges assigned to user objects.
3.
Examine
two key data dictionary views that reveal information about an important
privilege for Oracle Audit.
4.
Investigate
how to find user objects that are assigned unlimited tablespace in your active
Oracle instances.
5.
Review
the secure method for invoking SQLPlus, utilizing the /nolog command line
switch.
1.
This
practice references SQL commands that function equivalently on the UNIX and NT
operating systems.
2.
Results
may vary slightly according to your Oracle environment.
3.
To
further illustrate and guide this activity, view this topic’s Demo in
conjunction with this Practice.
4.
Login to
your sqlplus session using the SYSTEM user object and the current password.
1.
UNIX: Open a shell, login, and type the following command:
NT: Open a DOS prompt, or invoke from the
START/RUN pull down box.
|
sqlplus
system/<password> SQL> connect
system/manager Connected. SQL>
connect sys/change_on_install Connected. SQL>
connect scott/tiger Connected. SQL> |
Each connect command shows if the default password is in use with
that particular default database user object.
Those objects using the default passwords should have their passwords
changed.
2.
UNIX:
Open a shell, login, and type the
following command
NT: Open a DOS prompt, or invoke from the START/RUN pull down box.
|
sqlplus
system/<password> SQL> select
* from v$pwfile_users; USERNAME SYSDB SYSOP ------------------------------ ----- ----- INTERNAL TRUE TRUE SYS TRUE
TRUE ROD TRUE FALSE SQL> |
It is normal for INTERNAL or SYS to have the
SYSOPER and/or SYSDBA privilege. Any
other user objects are suspect and should be scrutinized.
3.
UNIX: Open a shell, login, and type the following command:
NT: Open a DOS prompt, or invoke from the START/RUN pull down box.
|
sqlplus
system/<current password> SQL>
select role,privilege from role_sys_privs 2
where role='DELETE_ANY_CATALOG'; no rows
selected SQL> select
role,privilege from role_sys_privs 2
where role='DELETE_ANY_CATALOG'; no rows
selected |
Any user objects that have the
DELETE_ANY_CATALOG privilege should be scrutinized closely. Verify the need for this object to have this
privilege. If in doubt, revoke the
privilege.
4.
UNIX: Open a shell, login, and type this command
NT: Open a DOS prompt, or invoke from the START/RUN pull down box.
|
sqlplus
system/<current password> SQL>
select username,max_bytes,max_blocks from dba_ts_quotas; USERNAME MAX_BYTES MAX_BLOCKS ------------------------------
---------- ---------- RODOEMREPOS -1 -1 RODOEMREPOS -1 -1 SQL> |
The numeric “-1” is the indication of unlimited
tablespace assignments, whether in a MAX_BYTES or MAY_BLOCKS column. Examine those usernames that display the
“-1” value. A username with unlimited
tablespace privilege could accidentally or intentionally use 100% of that
tablespace.
5.
UNIX: open a shell, login, and type the following command:
NT: no practice for this concept on NT.
|
sqlplus
system/<current password> SQL>
host ps -ef|grep sqlplus |
The results of this UNIX command within sqlplus
will display the sqlplus session with the SYSTEM user object’s password in
plain text. This is a security breach,
and can be avoided with utilizing sqlplus in the NOLOG mode displayed next.
6.
UNIX: Open a shell, login, and type the following command:
NT: No practice for this concept on NT.
|
sqlplus
system /nolog SQL>
connect system/manager Connected. SQL>
host ps -ef|grep sqlplus |
The results of this UNIX command within sqlplus will
display the sqlplus session with the SYSTEM user object’s password hidden. The session now displays only the “/nolog”
in the UNIX process table. This keeps
unauthorized users from gaining passwords by watching active sessions using the
UNIX command “ps”.