We are organising a 3 day detailed Oracle security class here in our offices in York, UK. We are running this between Monday 16th February to Wednesday the 18th February 2026. This is a detailed overview taught by Pete Finnigan of how to secure data in an Oracle database. We cover all aspects of why your data can be insecure and what to do to make that data secure. We cover every area of reviewing and securing the data as well as a look at suitable audit trail designs and how to react to a possible incident. This is a one off event and places are limited; more details to register are here - Oracle 3 day security training in York February 2026.
We are also organising a live one day class also in our offices in York, UK on February 23rd 2026. This class is all about secure coding in PL/SQL. There are details on the class and how to register here - One day secure coding in PL/SQL class in York February 2026
It is also coming close to PeteFinnigan.com Limited 23rd anniversary. We were founded on February 12th 2003 with a goal of helping customers secure data in their Oracle databases via our software products that we have developed to help with security audits, securing PL/SQL, reviewing PL/SQL for security issues, implementing audit trails as a toolkit, forensic and live analysis and also a user rights tool (coming in 2026). Plus we also have a cookie analyser to find cookies in websites. Our second stream is training in all details of securing Oracle from audits, hardening and securing, audit trail design, forensics, secure coding and more. Our third stream is to provide consulting in any area related to Oracle security. This can be as fixed term pieces of work or ad-hoc or short term or whatever you need related to Oracle security. We act as the on-call Oracle Security experts for a very small number of companies as well so that we can be the Oracle security experts and part of their teams; we do this for 5 customers normally and we have one space available at the moment.
Finally in this short post some MySQL. I normally focus only on Oracle Security but I do access and use other databases such as MySQL, PostgreSQL, SQL Server and more.
We used a web based product called OS Ticket to manage support and tickets for our software products. This software is based on Linux, MySQL for the database and PHP for the website but the version we use is old now and it is time to move on. We stopped customers directly adding tickets some time ago and we add tickets on what is now a private instance after being made aware via our support email address. We want to move away from this system and move to a new ticket system BUT we don't know exactly what we will use yet.
The first step was to migrate the data we wanted to keep from the existing MySQL database so we can decide what to do next, retire the existing system / application but also keep access to the tickets to allow working on them and to add new ones but to keep the data in a format that will be easy to move to a new system.
The database was easy to understand simply by reviewing the schema via phpMyAdmin. We decided we do not need all of the data stored currently ; basically just the ticket numbers, the title, the created date, status, the original issue and any responses and notes. We also decided to only move OPEN tickets and forget all other ticket status. The tickets are stored in a table called OST_TICKET and the messages in OST_TICKET_MESSAGE, the responses in OSP_TICKET_RESPONSE and the notes in OST_TICKET_NOTE.
The ticket table has one entry per ticket but each of the other three have one or more entries per ticket and the messages and responses in my database for OPEN tickets have at least one message per ticket BUT the notes also are mostly NULL per ticket BUT some that are not null still have more than one note. This may be different in other instances of OS Ticket in that maybe each ticket does not have at least one response for instance.
So we needed a query against the MySQL database that is driven on tickets and for responses, messages and notes return a single row for each join to tickets and for notes where there are no joined rows for most return null - i.e. we needed normal joins for the messages and responses and an outer join for the notes.
Here is the SQL we created to extract all the open tickets from MySQL:
select t.ticketID,
t.ticket_id,
t.subject,
case when mess.message='Ticket created by staff' then '' else mess.message end as message,
resp.response,
note.title,
note.note,
t.status,
t.created
from ost_ticket t
inner join
(
select m.ticket_id,
group_concat(m.message,' ') message
from ost_ticket_message m,
ost_ticket t
where t.ticket_id=m.ticket_id
and t.status='open'
group by m.ticket_id
) mess on t.ticket_id=mess.ticket_id
inner join
(
select r.ticket_id r_id,
group_concat(r.response,' ') response
from ost_ticket_response r,
ost_ticket t
where t.ticket_id=r.ticket_id
and t.status='open'
group by r.ticket_id
) resp on t.ticket_id=resp.r_id
left outer join
(
select n.ticket_id not_id,
group_concat(n.title,' ') title,
group_concat(n.note,' ') note
from ost_ticket_note n,
ost_ticket t
where t.ticket_id=n.ticket_id
and t.status='open'
and n.note not like 'Ticket assigned'
group by n.ticket_id
) note on t.ticket_id=note.not_id
where t.status='open'
I decided to post it here in case its useful to anyone else. Review it before use as it may not be 100% correct. We tested various tickets in the web interface against what it returns and its good enough for our next step - i.e. the data looks fine and the right amount of records exist. It can be extended to add more fields and to include more status records but for us this is fine.
I ran this in phpMyAdmin and then extracted to csv for Excel. So we can still view tickets or add tickets in Excel before we decide what to do next. Excel is good as an intermediate step as SQL scripts can easily be created from Excel to allow the data to be added to a new database or similarly, CSV or XML.
What will we use next? not sure yet, we are still evaluating. We may use another free system we can host or a commercial system or ideally create our own. PFCLScan is very flexible and open so we can easily host our tickets as an app in this product. For instance we currently use PFCLScan to manage licenses and build PFCLScan. This is done via plugins
Let me know your thoughts on ticket systems, commercial, free, home grown via our social media posts
#oracleace #sym42 #oracle #security #training #mysql
