Pete Finnigan's Oracle Security Weblog

I want to wish a very happy New year for 2026 to all of our customers, web site visitors and all of our social media connections and likers and commenters. Thanks for all of your support over the years!!

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

PL/SQL packages can have a single BEGIN/EXCEPTION/END block as part of the package such as:

create or replace package body block 
as
	procedure test
	is
	begin
		dbms_output.put_line('Hello from test()');
	end;
begin
	dbms_output.put_line('Hello from begin; end');
exception
	when others then
		dbms_output.put_line('BEGIN:['||sqlerrm||']');
end;
/

This begin block is executed when the package is instantiated in a session:

C:\ >sqlplus orablog/orablog@//192.168.56.33:1539/xepdb1

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Nov 20 16:14:03 2025
Version 19.26.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.

Last Successful login time: Tue Nov 18 2025 12:45:55 +00:00

Connected to:
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL>
Set serveroutput on and compile the code:
SQL> set serveroutput on
SQL> @pack

Package created.


Package body created.

SQL>

Now we can execute the code:

SQL> exec block.test;
Hello from begin; end
Hello from test()

PL/SQL procedure successfully completed.

SQL>

As we can see the output from the begin/end block is printed first then the print line from the procedure test().

If we execute again, what happens:

SQL> exec block.test;
Hello from test()

PL/SQL procedure successfully completed.

SQL>

The begin/end block is not executed again. This is a feature of PL/SQL packages and the begin/end block at the package level. The begin/end block will execute again if the package became invalid and was re-compiled dynamically before its run or if we log into a new session then its run again:

SQL> connect orablog/orablog@//192.168.56.33:1539/xepdb1
Connected.
SQL> set serveroutput on
SQL> exec block.test;
Hello from begin; end
Hello from test()

PL/SQL procedure successfully completed.

SQL>

Now; imagine that this package contains some sensitive procedures and functions and we want to implement a check/test to ensure that the user is allowed to execute the procedure(s) before he/she is allowed to do so.

Maybe we want to check the IP address or time of the day or date or role enabled or?? We can write a procedure that is executed that tests the security fields we choose and if we pass the test allow the execution to continue.

If we add this test/check to the begin/end block what happens. We will simulate this as a simple raise command in the begin/end block to simulate that the security check failed. We change the code as follows:

create or replace package body block 
as
	security_fail exception;
	pragma exception_init(security_fail,-20001);
	procedure test
	is
	begin
		dbms_output.put_line('Hello from test()');
	end;
begin
	dbms_output.put_line('Hello from begin; end');
	raise security_fail;
exception
	when others then
		dbms_output.put_line('BEGIN:['||sqlerrm||']');
end;
/

Now reinstall this:

SQL> connect orablog/orablog@//192.168.56.33:1539/xepdb1
Connected.
SQL> set serveroutput on
SQL> @pack.sql

Package created.


Package body created.

SQL>

Now we can execute it and simulate that the security check failed and therefore we are stopped from running the test() procedure or indeed any other procedure:

SQL> exec block.test;
Hello from begin; end
BEGIN:[ORA-20001: ]
Hello from test()

PL/SQL procedure successfully completed.

SQL>

Hmmm, we caught the exception that the user failed the simulated security check and this sent us to the main package exception block and printed its message BUT we also then ran the test() procedure. So that did not work.

What if we do a raise_application_error() in the exception block. Lets change the code to do that:

create or replace package body block 
as
	security_fail exception;
	pragma exception_init(security_fail,-20001);
	procedure test
	is
	begin
		dbms_output.put_line('Hello from test()');
	end;
begin
	dbms_output.put_line('Hello from begin; end');
	raise security_fail;
exception
	when others then
		dbms_output.put_line('BEGIN:['||sqlerrm||']');
		raise_application_error(-20001,'Error in Security check');
end;
/

And install it:

SQL> connect orablog/orablog@//192.168.56.33:1539/xepdb1
Connected.
SQL> set serveroutput on
SQL> @pack

Package created.


Package body created.

SQL>

So now we can run the test() procedure:

SQL> exec block.test;
Hello from begin; end
BEGIN:[ORA-20001: ]
BEGIN block.test; END;

*
ERROR at line 1:
ORA-20001: Error in Security check
ORA-06512: at "ORABLOG.BLOCK", line 16
ORA-06512: at line 1


SQL>

That is better as the print message from the package begin/end block was printed and then the raise security_error took us to the package exception block which in turn printed out the sqlerrm error message before raise_application_error() took us out of the PL/SQL code before the procedure test() would have been executed.

So, we failed the security check and the code did not run. If run it again we get:

SQL> exec block.test;
Hello from begin; end
BEGIN:[ORA-20001: ]
BEGIN block.test; END;

*
ERROR at line 1:
ORA-20001: Error in Security check
ORA-06512: at "ORABLOG.BLOCK", line 16
ORA-06512: at line 1


SQL>

So, we can add a security check in the package begin/end block BUT we need to ensure that raise_application_error() throws us out of the package begin/end block back to the caller.

If we did not use raise_application_error() then we would have raised an error but initialised the package correctly and the next time we ran it the begin/end block would not be executed and the code would run BUT with this method even though we would catch the exception the package procedure still executes.
In summary if you wish to check security at the package level rather than in a single (or multiple) procedures then the package initialise MUST FAIL so that next time its executed then the security is guaranteed to run and fail again.

BUT, BUT, we missed a step; This is because the example is too simple. Yes, the raise_application_error() will kill the package initialise and even if its run again the begin/end block will execute again and fail again â€" IF the simulated security check failed. This at first glance seems to work as a solution BUT if the security check in the begin/end block succeeded first time then the raise_application_error() is not fired so the initialisation does not fail and therefore this design does not work as next time no matter if the security check would pass or fail the begin/end block is not processed.

So, if on first execution the security check passed then on a second execution it would potentially fail then the security check DOES NOT WORK.
Let us create a simple example by modifying my code. I will add an integer at the start as a counter and set to zero. Then the test function will increment the integer which will happen after the begin/end block as the begin/end block is called. I will also change the simulated security check to test the integer. If the integer is greater than zero the security check fails and then raise_application_error() is called.

In summary on first execution the security check passes but subsequent checks would fail.

Here is the modified code:

create or replace package body block 
as
	--
	i number:=0;
	security_fail exception;
	pragma exception_init(security_fail,-20001);
	--
	procedure test
	is
	begin
		dbms_output.put_line('Hello from test() - value is ['||i||']');
		i:=i+1;
	end;
	--
begin
	dbms_output.put_line('Hello from begin; end, the check value is=['||i||']');
	if(i>0) then
		raise security_fail;
	end if;
exception
	when others then
		dbms_output.put_line('BEGIN:['||sqlerrm||']');
		raise_application_error(-20001,'Error in Security check');
end;
/

Now install in the database:

SQL> connect orablog/orablog@//192.168.56.33:1539/xepdb1
Connected.
SQL> set serveroutput on
SQL> @pack

Package created.


Package body created.

SQL>

And run the first time where the security check should succeed:

SQL> exec block.test;
Hello from begin; end, the check value is=[0]
Hello from test() - value is [0]

PL/SQL procedure successfully completed.

SQL>

This worked correctly. Now run a second time where the security check should fail:

SQL> exec block.test;
Hello from test() - value is [1]

PL/SQL procedure successfully completed.

SQL>

As we can see the integer is now 1 and the execution of the protected code succeeded even though it should have failed the security check because the package BEGIN/END block did not fire again.

In summary do not put security checks in the begin/end block of a package. It may work once or twice if the begin/end block is not completed and the package not initialised but will fail to check security if the package is initialised. This would give false security

#oracleace #sym_42 #oracle #database #security #checks #plsql #package

Are your organisation’s Oracle databases fully secured or are you leaving the door ajar to unwanted access, data leaks or worse? If you’re responsible for managing, designing or auditing Oracle databases, then the upcoming three-day course by Pete Finnigan is one you won’t want to miss.

What’s on offer

A three-day intensive training event, running from Wednesday 3rd to Friday 5th December 2025, in the historic city of York (Tower Court, Oakdale Rd, YO30 4XL).

Live demo coverage of both theory and practice: you’ll start by exploring how an Oracle database can be attacked from end-user, developer and DBA vantage points and then you’ll dig into how to defend, design and audit your Oracle database environment fully.

Topics include authentication, authorisation, roles & profiles, the underlying OS and network layers, Oracle specific security options (Database Vault, VPD, TSDP, Encryption), audit trails and forensic response processes.

Suitable whether your databases are on-premises or in the cloud this training covers the architecture, threats and mitigation strategies relevant to both.

Why this course stands out

Pete Finnigan is a recognised expert an Oracle ACE Pro, member of the OakTable and SYM42 groups, with decades of experience in auditing and securing Oracle environments.

The training provides over 650 pages of slides and notes, 150+ free tools and scripts, and a 30-day engagement licence for PFCLScan and PFCLForensics (tools created by Pete’s team).

With a small-group format, you’ll benefit from direct interaction, real-life anecdotes and hands-on learning rather than generic lectures.

Who should attend

Whether you’re a DBA, system architect, developer (especially PL/SQL/Apex), security auditor, or a manager overseeing Oracle teams if you engage with Oracle databases in any way and care about protecting data, this class is highly relevant.

Investment & registration

The fee is £1,095 GBP + VAT for the full three days.

Tea/coffee and lunch are included each day.

Places are limited to keep the class intimate so if you’re serious, book early.

Final word

In a world where data breaches and insider threats loom large, this is your chance to sharpen your Oracle-security toolkit, learn from one of the field’s best, and return to your organisation with actionable insights and tools you can deploy immediately. Plus you’ll be based in beautiful York, a city steeped in Roman and Viking history, with the chance to enjoy the surroundings while you learn.

Ready to take your Oracle security game to the next level? Let me know by emailingpete@petefinnigan.com, and I can send you the registration and key details!

#OracleSecurity #DatabaseSecurity #OracleDBA #CyberSecurityTraining #OracleTraining #YorkEvents #InfoSec #DataProtection #DBASkills #OracleExpert #PeteFinnigan #CyberAwareness #ITTraining #SecureOracle #York2025 #oracleace #sym_42

I spoke about AI and Oracle security back in February in this blog - AI and Oracle Security. I asked the question can we use AI (LLM / Generative AI) in Oracle Security and if so how?

The two most obvious things would be to analyse audit data to find things we do not spot naturally and to use AI to check configurations. I also talked about how the rise of AI might have come to be in the last few years - hardware is available now to build large neural nets (LLMs) in memory and process with matrix calculations in video cards and also the large availability of training data through search websites indexes, books and many other sources happened around the same time.

My view also is that these potential learning data sources are being corrupted by the web based get rich crowd who use AI to generate huge amounts of content for websites and social media. We can all spot fake posts but wrong or inaccurate facts in posts are harder to spot. If the AI model learning data gets corrupted and the AI models learn from it and people who do not know better then use that AI data to generate more bad data where are we left in the end?

The AI could be trained on inaccurate data in the first place and the data used to train AI can be corrupted by spam/inaccurate data generated by AI so devaluing the content generated by prompts. It is like a self-fulfilling prophecy in reverse.

Another aspect is copyright. I see everywhere emails and adverts and articles (again driven often by get rich quick schemes) about give a generative AI an idea and ask it to make an app including hosting, deploying the full stack and payments, generate a web site and more. In this case its less of an issue for me as the code would never become public - maybe, maybe not.

But, if you use code assist in you development environment and then deploy that code as part of a commercial application / Oracle database or ? then what is the source - the real source of that code generated by AI and who owns it. Did the AI learning phases check the license of every code snippet that is in the future used to generate new code? I think this is something managers need to consider before their commercial code base is updated with generative code. Yes, it makes simple things easy and quick and saves research time and document discovery tasks BUT is it legally the companies code as it was not written keyword for keyword by a developer.

If you generate code for something it often does not compile; as time goes on and the code gets better then developers could be replaced by generative AI (let me be very clear, I do not agree with this) BUT the skills needed to work out why something does not compile or why there is a logical bug in compiled code will be lost and suddenly your commercial application and database are developed and supported by AI but the skill base is lost when something critical happens you cannot support it or fix it.

I think that AI that uses specific reliable input data via RAG maybe will be the best to assist finding and searching that data and compiling answers BUT it depends on the quality of the input data. This can be FAQ, manuals, previous tickets and bugs and more. The creation of this data cannot be by Gen AI if it does not exist. For instance imagine we create a database, queries, tables, PL/SQL code and more and build a finance or CRM or ERP on top of Oracle. Gen AI cannot document it and teach itself; it needs input from designers/developers and more. Yes, i know AI could mine/learn from the source, designs etc but its unlikely to test the system and use it and create data that can answer any question. Well maybe we could do exactly that and the code generation if its exclusively from our code (we own the copyright) can solve the previous issue

In terms of using AI to mine data; this is clearer. We can point audit data at AI and ask it general questions or specific ones to find anomalies and edge cases and potential violations in our audit. This can work as a viable assist to security

I watched and read quite a lot on AI recently and there are some interesting discussions going on. For instance Steven Bartlett interviewed Dr. Roman Yampolskiy and he made a number of statements that i did not agree with. He said by 2027/2030/2045 99% of people will be redundant and only 5 jobs will exist because jobs will be replaced by AI LLMs. This does not make sense. Yes, in principal this could be a worst case Armageddon and companies will want to replace people with generative AI but going to 99% of people replaced is not including the practicalities. If everything went to AI where are all the servers/learning/bots and so on hosted, how do all companies transition to this quickly and how does AI availability increase to cope with the demand of 99% of jobs. Also stated was that cars and lorries will be replaced by self driving lorries and cars. Who is going to make all these cars and lorries very quickly and replace people driving them. The scale of manufacturing is immense.

Imagine a world in a few years where millions of lorries are cars are self driving with no one in them. How is that going to work, imagine 99% of people are redundant; they will no longer need shopping delivered from super markets - plenty of time to walk and buy, no more online retailers as they do not have money to buy, no more take away deliveries... would the take aways need to cook burgers automatically and send them down a Shute to a self driving car waiting outside and the poor redundant people have to go outside their homes to collect the burger from the same self driving cars or do these drive up to the house and an accurate AI slingshot send the food to the letter box, a bit like the reverse of the trains in the past picking up mail bags from hangers as they passed without stopping.

Others are talking that the AI bubble might burst like Web 1.0 and the dot com crash; Don't know, even if it did there will still be AI at some level.

What is needed is AI models built / learned for hundreds of dollars on small devices and not massive data center devices for billions or hundreds of billions. Maybe we ought to have specialised AI models and even create AI people; like tens/hundreds/thousands of models all taught slightly differently so they are like people!! We can have an AI Dinesh, AI Gilfoyle, AI Pete doing Oracle Security, AI Oracle Tuning, AI... the first two are from the excellent Silicon Valley comedy

Yes, AI can be good, can speed things up and reduce costs but there is a big risk that the use of it is being imagined too far ahead and far too end of days.

For Oracle security; Yes, if we have good data that can be learned from and good clean sources to use in other tasks then AI can work

#oracleace #sym_42 #ukoug #ai #UKOUGDiscover25 #OracleCommunity #JoelKallmanDay #oracle #database #AI

If i want to create layered security in a database around something such as specific data or to protect access to a resource or perhaps control access to a privilege then we can create a security layer using standard features BUT the next thing we should think about if we create a solution is someone (a hacker) then turning off parts of that solution.

Therefore we need to then create a protection layer that protects the security we have just implemented. We can even then create another layer that protects the protection layer.

Finally we must also audit all of the layers to test if anyone is trying to access the thing we are protecting or trying to change the security layer to beat the protection.

So, as part of this goal we created DDL triggers as part of the security protection so I want to see if there is a way to detect if someone disables a DDL trigger.

Let us create a simple DDL trigger that can capture all DDL to see if we can catch the disabling of a trigger. Actually not all DDL is caught by a DDL trigger as we already know that ALTER SYSTEM whilst is described as DDL is not caught.

Lets create a DDL trigger that will debug out details of actions:

SQL> get ddl
  1  create or replace trigger atk_ddl
  2  after ddl on database
  3  declare
  4     lv_sql ora_name_list_t;
  5     lv_stmt varchar2(32767);
  6     lv_n pls_integer:=0;
  7  begin
  8     --
  9     lv_n:=ora_sql_txt(lv_sql);
 10     for i in 1 .. lv_n loop
 11             lv_stmt:=lv_stmt||lv_sql(i);
 12     end loop;
 13     --
 14     dbms_output.put_line('DDL>'||lv_stmt);
 15* end;
SQL> @ddl

Trigger created.

SQL>

Now run a DISABLE command on an other trigger and see if its caught:

SQL> sho user
USER is "SYS"
SQL> set serveroutput on
SQL> alter trigger atk_sec_logon disable;
DDL>alter trigger atk_sec_logon disable

Trigger altered.

SQL> alter trigger atk_sec_logon enable;
DDL>alter trigger atk_sec_logon enable

Trigger altered.

SQL>

Yes, fantastic, we can detect disabling of a system trigger, what about disabling our DML trigger. First find out the owner of our trigger:

SQL> col owner for a30
SQL> col trigger_name for a30
SQL> l
  1* select owner,trigger_name from dba_triggers where trigger_name like 'ATK%'
SQL> /

OWNER                          TRIGGER_NAME
------------------------------ ------------------------------
ATK_SEC                        ATK_DML
SYS                            ATK_DDL
SYS                            ATK_SEC_ALTER
SYS                            ATK_SEC_CREATE
SYS                            ATK_SEC_DROP
SYS                            ATK_SEC_GRANT
SYS                            ATK_SEC_LOGON
SYS                            ATK_SEC_TRUN

8 rows selected.

SQL>

Now we can try and disable and re-enable this trigger:

SQL> alter trigger atk_sec.atk_dml disable;
DDL>alter trigger atk_sec.atk_dml disable

Trigger altered.

SQL> alter trigger atk_sec.atk_dml enable;
DDL>alter trigger atk_sec.atk_dml enable

Trigger altered.

SQL>

Yes, we can detect system triggers and DML triggers being disabled. Now we can write a new DDL trigger that can block disable of a trigger such as the DML trigger used in out AUDSYS.AUD$UNIFIED experiment. Now create a new trigger that can detect the disabling of a trigger and block it. First create a version that will find a trigger and report it. We will extend it soon:

SQL> get disable
  1  create or replace trigger atk_disable
  2  after ddl on database
  3  declare
  4     lv_sql ora_name_list_t;
  5     lv_stmt varchar2(32767);
  6     lv_n pls_integer:=0;
  7     atk_disable exception;
  8     pragma exception_init(atk_disable,-20012);
  9  begin
 10     --
 11     lv_n:=ora_sql_txt(lv_sql);
 12     for i in 1 .. lv_n loop
 13             lv_stmt:=lv_stmt||lv_sql(i);
 14     end loop;
 15     if(ora_dict_obj_type = 'TRIGGER') then
 16             if(ora_dict_obj_name like 'ATK%') then
 17                     dbms_output.put_line('Found '||ora_dict_obj_type||'.'||ora_dict_obj_name);
 18             end if;
 19     end if;
 20     --
 21     dbms_output.put_line('DDL>'||lv_stmt);
 22* end;
SQL> @disable

Trigger created.

SQL>

Now test it to make sure we can find a trigger and also its our trigger:

SQL> set serveroutput on
SQL>  alter trigger atk_sec.atk_dml disable;
Found TRIGGER.ATK_DML
DDL>alter trigger atk_sec.atk_dml disable

Trigger altered.

SQL>

Fantastic, we captured the fact that the DML trigger was being disabled. Now lets change the code in the disable trigger to prevent this for our triggers:

SQL> get disable
  1  create or replace trigger atk_disable
  2  after ddl on database
  3  declare
  4     lv_sql ora_name_list_t;
  5     lv_stmt varchar2(32767):='*';
  6     lv_n pls_integer:=0;
  7     atk_disable exception;
  8     pragma exception_init(atk_disable,-20012);
  9  begin
 10     --
 11     lv_n:=ora_sql_txt(lv_sql);
 12     for i in 1 .. lv_n loop
 13             lv_stmt:=lv_stmt||lv_sql(i);
 14     end loop;
 15     if(ora_dict_obj_type = 'TRIGGER') then
 16             if(ora_dict_obj_name like 'ATK%') then
 17                     dbms_output.put_line('Found '||ora_dict_obj_type||'.'||ora_dict_obj_name);
 18                     if(instr(upper(lv_stmt),'DISABLE')<>0) then
 19                             raise atk_disable;
 20                     end if;
 21             end if;
 22     end if;
 23     --
 24     dbms_output.put_line('DDL>'||lv_stmt);
 25* end;
SQL> @disable

Trigger created.

SQL>

Note that we initialise the SQL string to * to prevent a NULL search. It does not matter if the SQL string now starts with *. Now run a DISABLE on our trigger:

SQL> set serveroutput on
SQL> alter trigger atk_sec.atk_dml disable;
Found TRIGGER.ATK_DML
alter trigger atk_sec.atk_dml disable
              *
ERROR at line 1:
ORA-04088: error during execution of trigger 'SYS.ATK_DISABLE'
ORA-00604: error occurred at recursive SQL level 1
ORA-20012:
ORA-06512: at line 17


SQL>

Fantastic, this works, we can now block the disabling of our DML and system triggers. This is one further layer of protection that we can add to protect our security interface on our table.

#oracleace #sym_42 #oracle #database #security #grants #ddl #trigger #protect #hacking #databreach

I have just been notified that I have had two sessions accepted for the UKOUG conference from 30th November to 2nd December 2025 at the East Side Rooms in Birmingham.

My first session is called Fuzzing PL/SQL and is overviewed here from my submission:

traditionally developers may review PL/SQL code by hand or by using free or commercial tools to parse the code looking for potential exploits.

In this talk we take a different approach. We attack the PL/SQL packages and procedures just as an attacker would.

I will explain what fuzzing is and how it can be used on PL/SQL and also do some live demos to show how errors may be generated and what that means for security of the PL/SQL.

Of course we will take some of the fuzzing to the limit and exploit the code. What does that mean? - i.e. if we exploit the code successfully then maybe an error is not generated.

I will discuss the limits of this type of code testing and what they mean

Fuzzing is an other string to your bow of securing PL/SQL

The second talk is called "A design Pattern to Secure Data in Your Database" and is overviewed here from my submission:

I have been advising and teaching people to use a simple design pattern to secure something critical in the database such as a system privilege like ALTER USER or an external resource such as a file system or to secure your most important data.

We can use standard features of the database, some security, some not strictly security.

We can use a simple design pattern that isolates the thing we want to secure and then build layers of security around that.

Sounds intriguing?

I will walk through a complete example to show how a particular set of data can be secured from tampering and change and allows only the access you wish.

The method can be tweaked and changed to your desires and can be used on any type of database ADB, cloud, premise, EE, SE, etc

Hope to see you all there this year!

#oracleace #sym_42 #UKOUGDiscover25 #UKOUG #conference #community #oracle #database security

The 21st anniversary of this blog is coming up on the 20th September 2025. I started this blog on the 20th of September 2004 and it has been doing well ever since. I write almost exclusively on the subject of Oracle Security; occasionally I might go off topic and discuss anniversaries or similar and I have talked about coding in C or Lua or PL/SQL. I recent times I discussed creating a compiler or interpreter in PL/SQL for a simple language based initially on BASIC but quickly changed to a different language. You can find the posts on that series via the blog archives page. Even those posts on creating a compiler for me actually have a security angle as we are adding a version of that in our PFCLObfuscate product that can be used to protect PL/SQL. The idea being that it adds a layer of complexity to PL/SQL code and makes it harder for someone to steal or understand especially when it is also obfuscated.

My blog has 1611 posts at this point of writing, actually 1612 including this post.

The blogs have had an array of subjects BUT almost 99% in the area of Oracle security; please have a look at some of the historic posts to see what I mean. We cover all aspects of Oracle database security, secure coding in PL/SQL, protecting PL/SQL, audit trails and also forensics but also delved into GDPR in the database and lost of subjects in between but all on Oracle security.

I think I am the longest running blog on Oracle Security and also probably quite unique. In the past there have been others who focused on Oracle Security and there are some who still occasionally do post on Oracle security BUT their blogs are not dedicated to the sole subject of Oracle Security.

Blogging started in 1997 and there were few people doing it then before blogging became much more popular. Wordpress was launched in 2003, just a short while before I started blogging. I used the Greymatter software that was released in 2000. My blog was there not at the start of Oracle blogging but soon after. At that time Brian Duff ran a site called OraBlogs that is now defunct that listed Oracle related blogs in one place for people. The site was down by the start of 2006 as Brian moved to Oracle and started to encourage Oracle blogs to collate on an Oracle domain. During the time from me starting to blog on Oracle and security I rose up the rankings on his site and ended up at number 1 position of Oracle blogs for a short period before the site was gone


My journey in blogging did not start with this blog. For a time before the first actual blog post I posted articles on my Ramblings section.

In the first year of this blog my site visits (not hits) went from around 10,000 a month to about 64,000 a month and approximately 0.5 Million visits in a year. Now as i write this we get around 225,000 visits a month and around 7,500 on average every day with peaks some times above 10,000 a day and some rarer times about 21,000 a day. We get around 2.3 million visits a month. So for me it is worth continuing this blog journey as a lot of people still visit and read what I say. Of course we are also now in a social media world and I have around 45,000 connections/followers on all of the main channels and we post there many times per week also.

In a similar fashion to Orablogs all those years ago there are lists on feedspot of blogs that are of interest. The top 100 Oracle blogs has me at position 6 and the top 70 database blogs on feedspot has me at position 13

My first blog post was A new Oracle security based weblog was basically a welcome to my blog and what I plan to write about and when.

Please keep visiting and reading what I have to say about Oracle and Security. I have written an off-line tool called PFCLSocial that i use to write drafts of blogs and I have quite a few stacked up. I might do a post about PFCLSocial at some point in the future.

Thanks for reading for 21 years

#oracleace #sym_42 #oracle #database #security #tools #software #training #products #blogging #birthday