Practice:Demonstrating Database User Objects, Roles and Permissions

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.

ASSUMPTIONS

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.

INSTRUCTIONS

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”.