Database Users Analysis (duser.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script provides comprehensive analysis of database user accounts, displaying essential security and configuration information including account status, tablespace assignments, security profiles, and creation dates. It includes visual indicators for potentially problematic configurations such as accounts using the SYSTEM tablespace and locked accounts, making it essential for user management, security auditing, and compliance monitoring.

The Script

rem  duser.sql
rem
rem  linesize = 80
rem
ttitle 'Database Users'
rem
col user_id format 9990 heading 'ID'
col account_status format a1 heading 'S'
col username format a12 heading 'USERNAME'
col dt format a1 heading ''
col default_tablespace format a15 heading 'DEFAULT'
col tt format a1 heading ''
col temporary_tablespace format a12 heading 'TEMPORARY'
col profile format a16 heading 'PROFILE'
col created format a9 heading 'CREATED'
rem
select user_id,
       decode( account_status, 'OPEN', null, 'LOCKED', 'L', '?' ) account_status,
       username,
       decode( default_tablespace, 'SYSTEM', '*', null ) dt,
       default_tablespace,
       decode( temporary_tablespace, 'SYSTEM', '*', null ) tt,
       temporary_tablespace,
       profile,
       created
  from sys.dba_users
 where username like nvl(upper('&user'), '%')
 order by username;

Key Features

• Comprehensive User Information: Shows user ID, status, tablespaces, and profiles
• Security Status Indicators: Visual markers for locked accounts and SYSTEM tablespace usage
• Flexible Filtering: Filter by username pattern or show all users
• Compliance Support: Identifies potential security configuration issues
• Creation Date Tracking: Shows when accounts were created for audit trails
• Clean Formatting: Organized output for easy analysis and reporting

Usage

@duser.sql

Input Parameters:

• user: Username or pattern to filter (press Enter for all users)

Required Privileges

SELECT on SYS.DBA_USERS

Sample Output

                                              Database Users

   ID S USERNAME      DEFAULT         TEMPORARY    PROFILE          CREATED
----- - ------------ --------------- ------------ ---------------- ---------
    0   SYS          * SYSTEM        * SYSTEM     DEFAULT          01-JAN-20
    5   SYSTEM       * SYSTEM        * SYSTEM     DEFAULT          01-JAN-20
   45   SCOTT          USERS           TEMP       DEFAULT          15-MAR-20
   46 L DEMO_USER      USERS           TEMP       DEMO_PROFILE     20-MAR-20
   47   HR             USERS           TEMP       DEFAULT          22-MAR-20
   48   SALES_APP      SALES_DATA      TEMP       APP_PROFILE      25-MAR-20
   49 L OLD_ACCOUNT   USERS           TEMP       DEFAULT          10-JAN-19
   50   FINANCE        FINANCE_DATA    TEMP       FINANCE_PROFILE  01-APR-20

Key Output Columns

• ID: Unique user ID number assigned by Oracle
• S: Status indicator (blank=OPEN, L=LOCKED, ?=other status)
• USERNAME: Database username
• DEFAULT: Default tablespace (* indicates SYSTEM tablespace usage)
• TEMPORARY: Temporary tablespace (* indicates SYSTEM tablespace usage)
• PROFILE: Security profile governing password and resource limits
• CREATED: Date when the user account was created

Understanding User Security Configuration

Account Status Analysis

Account Status Indicators

-- Status meanings:
-- Blank (OPEN): Account is active and can connect
-- L (LOCKED): Account is locked and cannot connect
-- ? (Other): EXPIRED, EXPIRED_LOCKED, or other status

Security Implications

-- Locked accounts indicate:
-- Administrative security action
-- Failed login attempts (if profile configured)
-- Expired passwords (if profile configured)
-- Disabled service accounts

Tablespace Configuration

SYSTEM Tablespace Usage

-- Asterisk (*) indicators show:
-- Users with SYSTEM as default tablespace (security risk)
-- Users with SYSTEM as temporary tablespace (performance risk)
-- Potential configuration issues needing attention

Best Practice Violations

-- SYSTEM tablespace usage problems:
-- Default tablespace = SYSTEM: User objects in SYSTEM
-- Temporary tablespace = SYSTEM: Performance degradation
-- Security risk: Critical system tablespace exposure

Common Use Cases

1. Security Auditing

   • Review user account configurations
   • Identify security policy violations
   • Monitor account status changes
   • Support compliance requirements

2. User Management

   • Plan user account maintenance
   • Identify unused or old accounts
   • Review tablespace assignments
   • Validate profile assignments

3. Configuration Assessment

   • Find users with SYSTEM tablespace assignments
   • Review profile distribution
   • Identify configuration inconsistencies
   • Plan configuration standardization

4. Compliance Monitoring

   • Support regulatory compliance
   • Audit user account governance
   • Track account creation patterns
   • Monitor security policy adherence

Security Analysis

High-Risk Configurations

SYSTEM Tablespace Usage

-- Identify high-risk accounts:
-- Default tablespace = SYSTEM
-- Temporary tablespace = SYSTEM
-- Both indicate immediate attention needed
-- Plan migration to appropriate tablespaces

Account Security Review

-- Security assessment criteria:
-- Locked accounts needing cleanup
-- Accounts with weak profiles
-- Old accounts requiring review
-- Service accounts needing validation

Administrative Actions

Account Maintenance

Locked Account Review

-- For locked accounts (L status):
-- Determine reason for lock
-- Verify if account still needed
-- Unlock if appropriate:
-- ALTER USER username ACCOUNT UNLOCK;

SYSTEM Tablespace Migration

-- Fix SYSTEM tablespace usage:
-- Create appropriate tablespaces
-- Migrate default tablespace:
-- ALTER USER username DEFAULT TABLESPACE new_tablespace;
-- Migrate temporary tablespace:
-- ALTER USER username TEMPORARY TABLESPACE temp_tablespace;

Related Scripts

• drole.sql - Database roles analysis
• dprivall.sql - User privileges analysis
• dtabprivs.sql - Table privileges analysis
• vuser.sql - Active user sessions

Summary

This script is essential for:

1. Security Auditing - Comprehensive review of user account configurations
2. User Management - Effective administration of database accounts
3. Compliance Monitoring - Supporting regulatory and policy compliance
4. Configuration Assessment - Identifying and resolving security issues
5. Risk Management - Maintaining proper database security posture