Password File Analysis (pw.sql)

What This Script Does

Queries a centralized password repository table (DBPWD.PWDS) to retrieve database connection credentials based on hostname, SID, and username criteria. This appears to be a custom database password management solution.

The Script

rem  pw.sql
rem
ttitle 'Database Password Repository'
rem
set lines 180
rem
col HOSTNAME format a20
col SID format a15
col USR format a20 heading 'USERNAME'
col PWD format a25 heading 'PASSWORD'
col INACTIVE format a5
col DOMAIN format a20
col NOTES format a50
rem
select
    HOSTNAME,
    SID,
    USR,
    PWD,
    INACTIVE,
    DOMAIN,
    NOTES
from
    DBPWD.PWDS
where
    upper(HOSTNAME) like nvl(upper('&host'), '%')
    AND upper(SID) like nvl(upper('&sid'), '%')
    AND upper(USR) like nvl(upper('&usr'), '%')
order by
    HOSTNAME,SID,USR
/

Usage

-- Query all password entries
@pw.sql
-- Enter: [press enter for all hosts]
-- Enter: [press enter for all SIDs]
-- Enter: [press enter for all users]

-- Filter by specific hostname
@pw.sql
-- Enter: prodserver01 (for specific host)
-- Enter: [press enter for all SIDs]
-- Enter: [press enter for all users]

-- Filter by database SID
@pw.sql
-- Enter: [press enter for all hosts]
-- Enter: PROD (for specific database)
-- Enter: [press enter for all users]

-- Filter by username
@pw.sql
-- Enter: [press enter for all hosts]
-- Enter: [press enter for all SIDs]
-- Enter: scott (for specific user)

Parameters

The script prompts for:

host: Hostname pattern (% for all hosts)
sid: Database SID pattern (% for all SIDs)
usr: Username pattern (% for all users)

Required Privileges

SELECT on DBPWD.PWDS
-- (Requires access to the custom password repository)

Sample Output

                                Database Password Repository

HOSTNAME             SID             USERNAME             PASSWORD                  INACT DOMAIN               NOTES
-------------------- --------------- -------------------- ------------------------- ----- -------------------- --------------------------------------------------
prodserver01         PROD            app_user             my_secure_password        N     PRODUCTION           Production application account
prodserver01         PROD            dba_user             admin_password            N     PRODUCTION           Production DBA account
testserver02         TEST            app_user             test_password             N     TESTING              Testing environment application
devserver03          DEV             app_user             dev_password              Y     DEVELOPMENT          Development - currently inactive

Key Output Columns

HOSTNAME: Server hostname where database resides
SID: Database SID/service identifier
USERNAME: Database username
PASSWORD: Associated password (sensitive information)
INACTIVE: Flag indicating if account is inactive (Y/N)
DOMAIN: Environment or domain classification
NOTES: Additional notes or comments about the account

Security Considerations

Access Control

This script queries highly sensitive information
Should only be accessible to authorized personnel
Consider implementing additional security layers

Audit Trail

Log access to password repository
Monitor who queries password information
Maintain change history for password updates

Environment Separation

Separate repositories for different environments
Restrict production password access
Implement role-based access controls

Common Use Cases

Database Connection Management

-- Find connection details for specific database
@pw.sql
-- Enter: [hostname]
-- Enter: [database SID]

Account Inventory

-- List all accounts for a specific server
@pw.sql
-- Enter: servername
-- Review all database accounts on server

Inactive Account Review

-- Find inactive accounts for cleanup
@pw.sql
-- Look for INACTIVE = 'Y' entries

Environment Validation

-- Verify account setup across environments
@pw.sql
-- Compare DEV, TEST, PROD configurations

Repository Management

Table Structure (Assumed)

CREATE TABLE DBPWD.PWDS (
    HOSTNAME VARCHAR2(50),
    SID VARCHAR2(30),
    USR VARCHAR2(50),
    PWD VARCHAR2(100),
    INACTIVE CHAR(1),
    DOMAIN VARCHAR2(50),
    NOTES VARCHAR2(200)
);

Maintenance Operations

-- Update password
UPDATE DBPWD.PWDS
SET PWD = 'new_password'
WHERE HOSTNAME = 'server' AND SID = 'db' AND USR = 'user';

-- Mark account inactive
UPDATE DBPWD.PWDS
SET INACTIVE = 'Y'
WHERE USR = 'old_account';

Best Practices

Password Security

Encrypt passwords in the repository
Use secure connection methods
Implement password rotation policies
Regular access reviews

Repository Management

Regular backup of password repository
Version control for password changes
Documentation of account purposes
Cleanup of obsolete entries

Access Monitoring

Log all queries to password repository
Alert on unusual access patterns
Regular review of access logs
Implement time-based access controls

Alternatives

Modern Password Management

Consider Oracle Wallet for secure credential storage
Implement enterprise password management solutions
Use database authentication methods
External authentication integration

Security Enhancements

Encrypt sensitive columns
Implement row-level security
Use database vault for additional protection
Regular security assessments

Performance Impact

Low Impact: Simple table query
Depends on Table Size: Performance varies with repository size
Consider Indexing: Index on commonly queried columns

Database connection and authentication scripts
Account management and provisioning tools
Security audit and compliance reports

Important Notes

This script reveals sensitive password information
Ensure proper authorization before use
Consider security implications in your environment
May require customization for different password repository schemas