Audit Unsuccessful Oracle Login Attempts

Recently, the corporate parent of a client began dictating regular password changes for various Oracle service accounts, ie. accounts that the developers with the client as well as myself had been using to run automated tasks, automated reports, etc. After the password was changed, most of those jobs were documented and thus easy to find and update, but almost immediately we noticed that one of the service accounts began locking up every few minutes, so we obviously missed at least one, and it was too early in the morning for us to have consumed enough cups of coffee to remember what we may have missed.

The built-in DBA_AUDIT_TRAIL view came to our rescue.

The following command enables the logging of failed login attempts.

audit session whenever not successful;

Once that was enabled, we simply waited a few minutes, and then ran this SQL below.

select os_username, username, terminal, returncode, to_char(timestamp,'mm/dd/yyyy hh24:mi:ss') as fail_time, comment_text
from dba_audit_trail
where username='ACCOUNT_USERNAME' 
and returncode in(1017,28000)
order by timestamp desc;

VoilĂ ! We found our culprit. The field “terminal” showed us the machine’s hostname, and its IP address was found in the “comment_text” field. Return code 1017 signifies a login failure due to bad password, and 28000 is a login failure due to locked account.

Connecting PHP to Oracle with OCI8

Below is the set of steps I took to connect a XAMPP setup to an Oracle database server. I am doing this on a server running Windows Server 2016.

  1. Download and install XAMPP. I chose the one bundled with the 32-bit version of PHP 7.3.2 with thread safety. Sourceforge: xampp-win32-7.3.2-0-VC15-installer.exe
  2. Download and install Oracle Instant Client. Because the PHP architecture is 32-bit, Oracle Instant Client must also be 32-bit. Take note of your Oracle server’s version, and install the appropriate client. Oracle: Oracle Instant Client Downloads
  3. Set up your sqlnet.ora and tnsnames.ora files at %ORACLE_HOME%\network\admin\. These two files together will tell your Oracle Instant Client how to get to your Oracle server. Sample sqlnet.ora:

    Sample tnsnames.ora:

    			(ADDRESS = (PROTOCOL = TCP)(HOST = = 1521))
    			(SERVICE_NAME = db)
  4. In Windows, add the folder of Oracle Instant Client to the PATH system variable. Create a new system variable called ORACLE_HOME and put the same path in there as well.
  5. Download the OCI8 package. I chose the one labeled “7.3 Thread Safe (TS) x86” in order to match my PHP version, thread safe configuration, and the 32-bit architecture. Once downloaded, extract either php_oci8.dll, php_oci8_11g.dll, or php_oci8_12c.dll (depending on your Oracle server version) and place the DLL file in the PHP Extensions directory. By default, that folder for my installation of XAMPP is C:\xampp\php\ext\. PHP PECL: OCI8 for Windows
  6. Launch XAMPP Control Panel and start the Apache server. Add phpinfo(); to one of your pages to see if OCI8 has its own section; if so, you are ready to make your connection to your Oracle server. Here’s a quick and dirty PHP snippet you can use to do a quick connection test:
    $conn = oci_connect($user, $password, $server);
    $sql = "select user from dual";
    $stid = oci_parse($conn, $sql);
    $row = oci_fetch_array($stid, OCI_BOTH);

PowerShell script for exporting Print Server info to Oracle

The example below covers how a PowerShell script can connect to a Windows Print Server, how it can query printers information, and how it can connect to an Oracle database to perform a query.

# Start configuring parameters
Param (
	[string]$Printservers = "printserver1",
	[string]$OracleServer = "orcl",
	[string]$OracleUser = "scott",
	[string]$OraclePassword = "tiger"
	[string]$sql = "insert into printer_list (print_server, printer_name, printer_location, printer_comment, printer_ip, printer_driver_name, printer_driver_version, printer_driver, entry_dt) values(:print_server, :printer_name, :printer_location, :printer_comment, :printer_ip, :printer_driver_name, :printer_driver_version, :printer_driver, sysdate) "
# End configuring parameters

ForEach ($Printserver in $Printservers) { # Start looping through each print server
	$Printers = Get-WmiObject Win32_Printer -ComputerName $Printserver

	ForEach ($Printer in $Printers) { # Start looping through each printer
		$connectionString = "User Id=$OracleUser;Password=$OraclePassword;Data Source=$OracleServer;"
		$connection = $null
		$command = $null

		Try {
			$connection = New-Object System.Data.OracleClient.OracleConnection($connectionString)
			$command = New-Object System.Data.OracleClient.OracleCommand -ArgumentList $sql, $connection
			$NoOutput = $command.Parameters.Add("print_server", $Printserver)
			$NoOutput = $command.Parameters.Add("printer_name", $Printer.Name)
			$Location = $Printer.Location
			if (!$Location) {
				$Location = " "
			$NoOutput = $command.Parameters.Add("printer_location", $Location)
			$Comment = $Printer.Comment
			if (!$Comment) {
				$Comment = " "
			$NoOutput = $command.Parameters.Add("printer_comment", $Comment)
			$NoOutput = $command.Parameters.Add("printer_ip", $Printer.Portname)
			$Drivers = Get-WmiObject Win32_PrinterDriver -Filter "__path like '%$($Printer.DriverName)%'" -ComputerName $Printserver
			$DriverVersion = " "
			$Driver = " "
			ForEach ($Driver in $Drivers) {
				$Drive = $Driver.DriverPath.Substring(0,1)
				$DriverVersion = (Get-ItemProperty ($Driver.DriverPath.Replace("$Drive`:","\\$PrintServer\$Drive`$"))).VersionInfo.ProductVersion
				$Driver = Split-Path $Driver.DriverPath -Leaf
			$Drivername = $Printer.Drivername
			if (!$Drivername) {
				$Drivername = " "
			$NoOutput = $command.Parameters.Add("printer_driver_name", $Drivername)
			$NoOutput = $command.Parameters.Add("printer_driver_version", $DriverVersion)
			$NoOutput = $command.Parameters.Add("printer_driver", $Driver)
		Finally {
			if ($connection -ne $null) {

			if ($command -ne $null) {
	} # End looping through each printer
} # End looping through each print server

Large listener.log file size causing Oracle Listener to fail

Recently, I ran into a case in which the user was attempting to log on to a database, but the log on process was simply churning and churning without coming to an end. When I tried to perform a TNSPING against it, that process also failed to return any results, or at least not in a reasonable amount of time.

Thinking that it might be caused by a dead listener, I went on to the server and attempted to use the LSNRCTL command to restart the listener. Surprisingly, I was encountered with a number of errors, including “TNS-12541: TNS:no listener” and “TNS-12560: TNS:protocol adapter error”.

I was able to find a much more knowledgeable DBA to locate root problem — On this Windows-hosted Oracle database server, the listener.log file had grown too large. Our solution:

1. Archive the old listener.log file
2. Create an empty file by the same name

After those two action, the listener was able to restart successfully, allowing user connections once again.

Scheduling Jobs with Oracle 9i DBMS_JOB Package

Creating a new job that runs every day at 4:00am:

	l_job number; 
		l_job, -- OUT; the job ID number that will be generated
		'schema_name.procedure_name;', -- IN; the name of the job you wish to run, aka. "what"
		trunc(sysdate)+1+4/24,  -- IN; the first time the job will be run
		'trunc(sysdate)+1+4/24' -- IN; the interval the job will be repeated

Regarding the interval, here are some examples:

-- Every 15 minutes starting from the minute/second of the previous execution

-- Every hour, same minute/second as the previous execution

-- Every hour, at the 15-minute mark
'trunc(sysdate, 'hh')+1/24+15/24/60'

-- Every hour, limited to between 9:00am and 5:00pm
'case when to_char(sysdate, ''hh24mi'') between ''0900'' and ''1700'' then sysdate+1/24 else null end'

-- Every 3 days, same hour/minute/second as the previous execution

-- Every day at 5:00am

-- Every Monday at 5:00am
'next_day(trunc(sysdate), ''monday'')+5/24'

To see a list of existing jobs:

select * from dba_jobs;

Altering all properties of an existing job:

		123, -- IN; job ID number
		'schema_name.procedure_name;', -- IN; the name of the job, aka. "what"
		trunc(sysdate)+1+4/24,  -- IN; the first time the job will be run after this change
		'trunc(sysdate)+1+4/24' -- IN; the interval the job will be repeated

Altering just the “what”:

		123, -- IN; job ID number
		'schema_name.procedure_name;' -- IN; the name of the job, aka. "what"

These procedures allows you to make changes in a manner very similar to dbms_job.what illustrated above:


Force a job to run:

	-- ... where the "123" is the job's ID number

Removing an existing job:

	-- ... where the "123" is the job's ID number

Combining Oracle trigger and DBMS_UTILITY.FORMAT_CALL_STACK to track transactions

I recently encountered a situation where a small number of records in a large Oracle table contain wrong values, and naturally I need to find out exactly which program is causing this problem. I decided to use Oracle triggers to do this job, making use of the built-in DBMS_UTILITY.FORMAT_CALL_STACK function as the main ingredient.

create or replace trigger trg_stack_trace_logger
before insert or update on inventory_table
for each row
	if (:old.expiration_date <> :new.expiration_date) then
		insert into stack_trace_log
		'User=' || user || '; ' ||
		'Date=' || to_char(sysdate,'mm/dd/yyyy hh24:mi:ss') || '; ' ||
		'Old Value=' || :old.expiration_date || '; ' ||
		'New Value=' || :new.expiration_date || '; ' ||
	end if;

As you can see, the output contains both old/new values of the transaction as well as some metadata (ie. the stack trace) of the transaction itself. The output is inserted into a table called “stack_trace_log”, which, for simplicity sake, is just a table consisted of a single varchar2 field; if you will use this type of tracking over a longer period, it is probably best to track username, date, etc. in their own fields for better reporting capabilities.

select * from stack_trace_log;

Date=11/24/2015 08:33:39; 
Old Value=2015-11-15-00.00.00; 
New Value=2030-11-15-08.33.39; 
----- PL/SQL Call Stack -----    
object handle line number object name
0x91626018    1           anonymous block
0x8dcb7b30    3           ERP.TRG_STACK_TRACE_LOGGER
0x9657ec50    354         package body ERP.INVENTORY_API
0x9657ec50    1483        package body ERP.INVENTORY_API
0x8c7d2758    4254        package body ERP.INVENTORY_API
0x969315a0    650         package body ERP.RECEIVING_API
0x969315a0    3524        package body ERP.RECEIVING_API
0x969315a0    2861        package body ERP.RECEIVING_API
0x91411208    342         package body ERP.BARCODE_ARRIVAL_API
0x8bd5cca8    1           anonymous block
0x82871f48    1120        package body SYS.DBMS_SYS_SQL
0x82886f48    323         package body SYS.DBMS_SQL
0x99f8e6c0    138         package body ERP.BARCODE_INTERFACE_API
0x93980f88    1           anonymous block

1 rows selected

Insert Microsoft Word content into Oracle database

The following sample is over-simplified, but it shows how we can iterate through tables (and their columns and rows) to extract text, and in turn inserting them into an Oracle table.

Option Explicit

Public Sub InsertIntoOracle()
    Dim cn As ADODB.Connection
    Dim source, user, password, str As String
    Dim aTable As Table
    Dim tbl, row, col As Long

    source = "database"
    user = "scott"
    password = "tiger"
    tbl = 0

    Set cn = New ADODB.Connection
    cn.Open "Provider = OraOLEDB.Oracle; Data Source=" & source & "; User Id=" & user & "; Password=" & password & ""
    For Each aTable In ActiveDocument.Tables
        tbl = tbl + 1
        For row = 1 To aTable.Rows.Count
            For col = 1 To aTable.Columns.Count
                str = Trim(aTable.Cell(row, col).Range.Text)
                If (Len(str) > 2) Then
                    cn.Execute "insert into document_content values('" & tbl & "-" & row & "-" & col & ": " & str & "')"
                End If

    If cn.Errors.Count = 0 Then
    End If

End Sub

Obfuscate sensitive data in Oracle

If business needs requires you to store sensitive data such as social security numbers, bank routing/account numbers, and so on, you should ensure the data is stored in a safe way. Below are a set of two simple functions to encrypt/obfuscate such data to get your started.

To encrypt a varchar2 string with a specific encryption phrase (or “key”):

create or replace function your_schema.encrypt(clear_varchar_ varchar2, key_ varchar2) return varchar2 
	v_clear_varchar varchar2(2000);
	v_enc_raw		raw(2000);
	v_enc_varchar	varchar2(2000);
	if (mod(length(clear_varchar_), 8) != 0) then
		v_clear_varchar := rpad(clear_varchar_, length(clear_varchar_) + 8 - mod(length(clear_varchar_), 8), chr(0));
		v_clear_varchar := clear_varchar_;
	end if;
	dbms_obfuscation_toolkit.desencrypt(input => utl_raw.cast_to_raw(rpad(v_clear_varchar, 64, ' ')),
		key => utl_raw.cast_to_raw(key_), 
		encrypted_data => v_enc_raw);
		v_enc_varchar := utl_raw.cast_to_varchar2(v_enc_raw);
	return v_enc_varchar;

The following function decrypts; you must use the same key that was used to encrypt it.

create or replace function your_schema.decrypt(enc_varchar_ varchar2, key_ varchar2) return varchar2 
	v_tmp_raw    	 raw(2048);
	v_clear_varchar	varchar2(4000);
	dbms_obfuscation_toolkit.desdecrypt(input => utl_raw.cast_to_raw(enc_varchar_),
		key =>  utl_raw.cast_to_raw(key_), 
		decrypted_data => v_tmp_raw);
	v_clear_varchar := replace(trim(utl_raw.cast_to_varchar2(v_tmp_raw)),chr(0),'');
	return v_clear_varchar;

Here is an example usage: The following SQL statement inserts an obfuscated password into a table that stores user data.

insert into your_schema.user_accounts (username, password)
	your_schema.encrypt('tiger', '_seCret!keY:3')

And below is how you would retrieve and decrypt the password.

select your_schema.decrypt(password, '_seCret!keY:3') from your_schema.user_accounts where username='scott';

Security is a serious matter and it warrants extensive research. This article merely offers the awareness that sensitive data should not be stored in clear text, and hopefully provides a good starting point.

Oracle role hierarchy report

The SQL below can be used to provide a list of roles that inherits the CONNECT role, and with the use of START WITH clause, it will also iterate through all the roles beneath those roles, thus providing a hierarchy report.

select level, drp.granted_role, rpad('-',6*level,'-')||drp.grantee as grantee,
	when u.username is not null and account_status='OPEN' then 'Ua'
	when u.username is not null and account_status<>'OPEN' then 'Ux'
	when r.role is not null then 'R'
end as grantee_type
from dba_role_privs drp, dba_users u, dba_roles r
where drp.grantee=u.username(+) and drp.grantee=r.role(+)
start with drp.granted_role='CONNECT'
connect by prior drp.grantee=drp.granted_role;

Below is a sample of what may be returned.


Using DBA_SOURCE to query package source code

Recently I had the need to find out exactly what existing PL/SQL logic was touching a certain field that seems to update by itself, wiping out important data. Below was the quick query I put together, using the DBA_SOURCE view, to hunt down the culprit.

select name as package_name, line, text
from dba_source
where owner='MY_SCHEMA'
and type='PACKAGE BODY'
and (
  upper(text) like '%MY_TABLE_NAME%FIELD_NAME%'
  upper(text) like '%FIELD_NAME%MY_TABLE_NAME%'
order by name, line;

Note that I could have searched for “and upper(text) like ‘%UPDATE%MY_TABLE_NAME%FIELD_NAME%'” rather than having two conditions, but I wanted to err on the side of caution, thus my wish to pull more data out to be safe.