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
Blog
Pete Finnigan's Oracle Security Weblog
This is the weblog for Pete Finnigan. Pete works in the area of Oracle security and he specialises in auditing Oracle databases for security issues. This weblog is aimed squarely at those interested in the security of their Oracle databases.
Can we Detect Disable Trigger in the Oracle Database
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:
Now run a DISABLE command on an other trigger and see if its caught:
Yes, fantastic, we can detect disabling of a system trigger, what about disabling our DML trigger. First find out the owner of our trigger:
Now we can try and disable and re-enable this trigger:
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:
Now test it to make sure we can find a trigger and also its our trigger:
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:
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:
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
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
Fuzzing PL/SQL and Secure Design Patterns for PL/SQL and Oracle
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:
The second talk is called "A design Pattern to Secure Data in Your Database" and is overviewed here from my submission:
Hope to see you all there this year!
#oracleace #sym_42 #UKOUGDiscover25 #UKOUG #conference #community #oracle #database security
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
Happy 21st Anniversary to my Oracle Security Blog
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
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
Can we Block EXECUTE ANY PROCEDURE for our API?
I did a five part series on the security of AUDSYS.AUD$UNIFIED and showed how it works at a functional level and how it might be secured by Oracle and how we might design a similar system using standard database license free components. In the last part I tested my implementation of such a system to show it is possible but with the caveat that because this is not built in to the database like Database Vault a DBA could still turn it off.
BUT, the get out clause is that its a free solution or a solution that can be done in a Standard Edition of Oracle; in other words we don't use things like Database Vault or Row Level Security so its open to everyone. Because of that a DBA or someone with access to the schema could bypass it ot turn it off
The example design was to have a table in a schema and then protect that table and allow read through a READ ONLY view. Then allow specific DELETE only via an API and INSERT only via an API and no UPDATE. In a way this is similar to AUDSYS.AUD$UNIFIED.
I know there is a gap that a user with SELECT ANY TABLE could read the base table directly and there is no easy way with standard security features to stop this as the Oracle database does not provide a SELECT TRIGGER.
One other area that could be better is that we created an API that allows INSERT and DELETE on the secured table and we can grant access to that API direct to any users that need it, OR not. BUT a user can also execute the API without a direct grant or ROLE grant if they have EXECUTE ANY PROCEDURE system privilege.
Access can be granted via a role rather than a direct grant on the PL/SQL. I appreciate that roles are disabled inside DEFINER rights code but we will come back to that later.
I use this whole example in my Secure Coding in PL/SQL Class so decided to extend this blog series to discuss how we may block EXECUTE ANY PROCEDURE.
Like SELECT ANY TABLE or indeed READ ANY TABLE there is no EXECUTE trigger we can use to detect the use of EXECUTE ANY PROCEDURE. There is at some level in the we can set auditing and react to an audit entry as fast as possible BUT the session will have succeeded with the READ before we can block it so its not an option for blocking but can be an option to audit non standard access to the data. Back to EXECUTE ANY PROCEDURE....
Whilst there is no EXECUTE trigger to react in real time and block access that is not authorised we can only ever be inside PL/SQL when EXECUTE ANY PROCEDURE is used so we can block this by modifying the PL/SQL API to detect this and allow or not the access.
To do this we need two pieces of information inside the PL/SQL:
How can we do this? first lets create a sample schema and package that be used as a test bed:
We do not strictly need CREATE PROCEDURE for this example as I will create the code as SYS in the ATK_TEST schema. Lets create a dummy package procedure to test with:
And running it gives:
So, it works. Now we need to see if we can find the caller. We can add some debug code to the procedure to see if we can find this:
And running it gives:
This solved the first part of the puzzle as the USER and Session User are SYS which is the caller. We should therefore focus on USER as session user could be spoofed BUT we could check both. Next lets see a list of roles and privileges that are granted and also enabled roles. Lets make more changes to the code:
And now running gives:
This does not help us as these privileges listed are the ones we granted to ATK_TEST not to the caller SYS. So, we have a number of paths we can follow to protect this solution:
Querying the chain of roles and roles and roles and privileges is time consuming (not massively BUT if the procedure were used a lot it could be noticeable. If we were to look for EXECUTE ANY PROCEDURE to exclude we would need to check direct grants and grants via any number of layers of roles and similarly if we checked for grants on the API direct or via roles we would have the same issue.
So a better idea is to use a ROLE as a flag. If a user has been granted the role directly then we allow the code to continue if not we don't. This means that a user would need direct grants on the API or EXECUTE ANY PROCEDURE but they would need the role as a security flag. This means we can forcefully control access to the code/API by presence of the ROLE. Roles are turned off in definer rights PL/SQL BUT in this case we are going to check if it is granted.
So next lets create a role and by default because SYS creates it then the role will be granted to SYS:
Check who has the role:
So, SYS created the role and its granted with ADMIN rights to SYS as the creator so it can be granted on. This is a security issue that causes grants of roles to users that do not need them BUT that is a different story not for now.
We also need to grant select on DBA_ROLE_PRIVS to the schema so that it can check for role membership:
Lets fix up the API to determine if the caller has the role:
And running it to see if we have the role gives:
This did not work as we did not find the role. It should work as the code run separately shows:
OK, that is a stupid mistake on my part as the user is USER: SYS as returned from the database as a debug line. Lets fix that and try again:
If we now run this lets check we can detect the caller having our role ATK_ROLE:
Great, this now works so lets now convert the ATK_TEST.EXE.TEST() procedure to block any user who does not have the ATK_ROLE and install it:
So, finally lets try as SYS and see if we can run the procedure as SYS has the ATK_ROLE:
Works, great, lets create a user with EXECUTE ANY PROCEDURE, CREATE SESSION and the ATK_ROLE:
Now try and run it:
Works!!
Now revoke the ATK_ROLE from ATK1 and test again:
This works as planned.
BUT, there are still some issues; there always are when we use standard features, we need to go down the rabbit hole.
If we wanted to block EXECUTE ANY PROCEDURE from other peoples code then we can not initially unless we have the source code. One option is replace the original procedure with a thin veneer that does the security first and then the original that has been renamed is called. There is a flaw with this also as the original can be executed direct with EXECUTE ANY PROCEDURE. In some cases we can fix even this. We can edit the original file and add an ACCESSIBLE BY clause to force access via our wrapper and security so it cannot be called direct anymore.
There is a second more obvious problem. We decided to control the security via the membership of the role ATK_ROLE; so again down the security rabbit hole we must go as we must also now think about stopping anyone from granting that role as if they did it would bypass our security mechanism. We can do this in two ways; the first to stop people having the grants to grant roles - this is not practical in any database; the second is to create a new DDL trigger that specifically blocks the granting of this role; maybe unless its an ATK security DBA.
In summary we need layers and layers of security to implement something with standard database features. We need to implement the security and then security of that security and maybe security of security of security and we must not forget to add audit events!
#oracleace #sym_42 #oracle #security #audit #trail #audittrail #databreach #hacking #grants #protect #readonly #table
BUT, the get out clause is that its a free solution or a solution that can be done in a Standard Edition of Oracle; in other words we don't use things like Database Vault or Row Level Security so its open to everyone. Because of that a DBA or someone with access to the schema could bypass it ot turn it off
The example design was to have a table in a schema and then protect that table and allow read through a READ ONLY view. Then allow specific DELETE only via an API and INSERT only via an API and no UPDATE. In a way this is similar to AUDSYS.AUD$UNIFIED.
I know there is a gap that a user with SELECT ANY TABLE could read the base table directly and there is no easy way with standard security features to stop this as the Oracle database does not provide a SELECT TRIGGER.
One other area that could be better is that we created an API that allows INSERT and DELETE on the secured table and we can grant access to that API direct to any users that need it, OR not. BUT a user can also execute the API without a direct grant or ROLE grant if they have EXECUTE ANY PROCEDURE system privilege.
Access can be granted via a role rather than a direct grant on the PL/SQL. I appreciate that roles are disabled inside DEFINER rights code but we will come back to that later.
I use this whole example in my Secure Coding in PL/SQL Class so decided to extend this blog series to discuss how we may block EXECUTE ANY PROCEDURE.
Like SELECT ANY TABLE or indeed READ ANY TABLE there is no EXECUTE trigger we can use to detect the use of EXECUTE ANY PROCEDURE. There is at some level in the we can set auditing and react to an audit entry as fast as possible BUT the session will have succeeded with the READ before we can block it so its not an option for blocking but can be an option to audit non standard access to the data. Back to EXECUTE ANY PROCEDURE....
Whilst there is no EXECUTE trigger to react in real time and block access that is not authorised we can only ever be inside PL/SQL when EXECUTE ANY PROCEDURE is used so we can block this by modifying the PL/SQL API to detect this and allow or not the access.
To do this we need two pieces of information inside the PL/SQL:
- We need to know the name of the caller
- We need to know if the caller has EXECUTE ANY PROCEDURE
How can we do this? first lets create a sample schema and package that be used as a test bed:
SQL> create user atk_test identified by atk_test default tablespace users;
User created.
SQL> grant create session, create procedure to atk_test;
Grant succeeded.
SQL>
We do not strictly need CREATE PROCEDURE for this example as I will create the code as SYS in the ATK_TEST schema. Lets create a dummy package procedure to test with:
SQL> get exe
1 create or replace package atk_test.exe as
2 procedure test;
3 end;
4 /
5 create or replace package body atk_test.exe as
6 procedure test is
7 begin
8 dbms_output.put_line('Hello from atk_test.exe.test()');
9 end;
10* end;
11 .
SQL> @exe
Package created.
Package body created.
SQL>
And running it gives:
SQL> set serveroutput on
SQL> exec atk_test.exe.test;
Hello from atk_test.exe.test()
PL/SQL procedure successfully completed.
SQL>
So, it works. Now we need to see if we can find the caller. We can add some debug code to the procedure to see if we can find this:
SQL> get exe
1 create or replace package atk_test.exe as
2 procedure test;
3 end;
4 /
5 create or replace package body atk_test.exe as
6 procedure test is
7 lv_user varchar2(100);
8 lv_username varchar2(100);
9 lv_curr varchar2(100);
10 lv_sess varchar2(100);
11 lv_scm varchar2(100);
12 begin
13 --
14 select 'USER: '||user userr,
15 'Username: '||username username,
16 'Current User: '||sys_context('userenv','current_user') curr,
17 'Session User: '||sys_context('userenv','session_user') sess,
18 'Current Schema: '||sys_context('userenv','current_schema') scm
19 into lv_user,lv_username,lv_curr,lv_sess,lv_scm
20 from user_users;
21 dbms_output.put_line('user =['||lv_user||']');
22 dbms_output.put_line('username =['||lv_username||']');
23 dbms_output.put_line('Current User =['||lv_curr||']');
24 dbms_output.put_line('Session User =['||lv_sess||']');
25 dbms_output.put_line('Current Schema=['||lv_scm||']');
26 --
27 dbms_output.put_line('Hello from atk_test.exe.test()');
28 --
29 end;
30* end;
31 .
SQL> @exe
Package created.
Package body created.
SQL>
And running it gives:
SQL> set serveroutput on
SQL> exec atk_test.exe.test;
user =[USER: SYS]
username =[Username: ATK_TEST]
Current User =[Current User: ATK_TEST]
Session User =[Session User: SYS]
Current Schema=[Current Schema: ATK_TEST]
Hello from atk_test.exe.test()
PL/SQL procedure successfully completed.
SQL>
This solved the first part of the puzzle as the USER and Session User are SYS which is the caller. We should therefore focus on USER as session user could be spoofed BUT we could check both. Next lets see a list of roles and privileges that are granted and also enabled roles. Lets make more changes to the code:
SQL> get exe
1 create or replace package atk_test.exe as
2 procedure test;
3 end;
4 /
5 create or replace package body atk_test.exe as
6 procedure test is
7 lv_user varchar2(100);
8 lv_username varchar2(100);
9 lv_curr varchar2(100);
10 lv_sess varchar2(100);
11 lv_scm varchar2(100);
12 begin
13 --
14 select 'USER: '||user userr,
15 'Username: '||username username,
16 'Current User: '||sys_context('userenv','current_user') curr,
17 'Session User: '||sys_context('userenv','session_user') sess,
18 'Current Schema: '||sys_context('userenv','current_schema') scm
19 into lv_user,lv_username,lv_curr,lv_sess,lv_scm
20 from user_users;
21 dbms_output.put_line('user =['||lv_user||']');
22 dbms_output.put_line('username =['||lv_username||']');
23 dbms_output.put_line('Current User =['||lv_curr||']');
24 dbms_output.put_line('Session User =['||lv_sess||']');
25 dbms_output.put_line('Current Schema=['||lv_scm||']');
26 --
27 for cc_priv in (
28 select 'SESSION ROLES' typ,role grants from session_roles
29 union
30 select 'GRANTED ROLE' typ,granted_role grants from user_role_privs
31 union
32 select 'GRANTED PRIVILEGE' typ,privilege grants from user_sys_privs
33 order by typ) loop
34 --
35 dbms_output.put_line(cc_priv.typ||chr(9)||cc_priv.grants);
36 --
37 end loop;
38 --
39 dbms_output.put_line('Hello from atk_test.exe.test()');
40 --
41 end;
42* end;
43 .
SQL> @exe
Package created.
Package body created.
SQL>
And now running gives:
SQL> set serveroutput on
SQL> exec atk_test.exe.test;
user =[USER: SYS]
username =[Username: ATK_TEST]
Current User =[Current User: ATK_TEST]
Session User =[Session User: SYS]
Current Schema=[Current Schema: ATK_TEST]
GRANTED PRIVILEGE CREATE PROCEDURE
GRANTED PRIVILEGE CREATE SESSION
Hello from atk_test.exe.test()
PL/SQL procedure successfully completed.
SQL>
This does not help us as these privileges listed are the ones we granted to ATK_TEST not to the caller SYS. So, we have a number of paths we can follow to protect this solution:
- We can look for any caller that has EXECUTE ANY PROCEDURE directly granted
- We can look for any caller that has EXECUTE ANY PROCEDURE granted via any ROLE
- We can check for direct grants on the API and disallow anyone else which would also disallow EXECUTE ANY PROCEDURE
- We could check for the direct granting of a role that we design and allow execution
Querying the chain of roles and roles and roles and privileges is time consuming (not massively BUT if the procedure were used a lot it could be noticeable. If we were to look for EXECUTE ANY PROCEDURE to exclude we would need to check direct grants and grants via any number of layers of roles and similarly if we checked for grants on the API direct or via roles we would have the same issue.
So a better idea is to use a ROLE as a flag. If a user has been granted the role directly then we allow the code to continue if not we don't. This means that a user would need direct grants on the API or EXECUTE ANY PROCEDURE but they would need the role as a security flag. This means we can forcefully control access to the code/API by presence of the ROLE. Roles are turned off in definer rights PL/SQL BUT in this case we are going to check if it is granted.
So next lets create a role and by default because SYS creates it then the role will be granted to SYS:
SQL> create role ATK_ROLE
2 /
Role created.
SQL>
Check who has the role:
SQL> @sc_who_has_role
Enter value for output_method: S
old 144: lv_file_or_screen:= upper('&&output_method');
new 144: lv_file_or_screen:= upper('S');
Enter value for role_to_find: ATK_ROLE
old 145: write_op('Investigating Role => '||upper('&&role_to_find')||' (PWD = '
new 145: write_op('Investigating Role => '||upper('ATK_ROLE')||' (PWD = '
old 146: ||role_pwd(upper('&&role_to_find'))||') which is granted to =>');
new 146: ||role_pwd(upper('ATK_ROLE'))||') which is granted to =>');
old 148: get_role(upper('&&role_to_find'));
new 148: get_role(upper('ATK_ROLE'));
Investigating Role => ATK_ROLE (PWD = NO) which is granted to =>
====================================================================
User => SYS (ADM = YES)
PL/SQL procedure successfully completed.
SQL>
So, SYS created the role and its granted with ADMIN rights to SYS as the creator so it can be granted on. This is a security issue that causes grants of roles to users that do not need them BUT that is a different story not for now.
We also need to grant select on DBA_ROLE_PRIVS to the schema so that it can check for role membership:
SQL> grant select on dba_role_privs to atk_test;
Grant succeeded.
SQL>
Lets fix up the API to determine if the caller has the role:
SQL> get exe
1 create or replace package atk_test.exe as
2 procedure test;
3 end;
4 /
5 create or replace package body atk_test.exe as
6 procedure test is
7 lv_user varchar2(100);
8 lv_username varchar2(100);
9 lv_curr varchar2(100);
10 lv_sess varchar2(100);
11 lv_scm varchar2(100);
12 lv_role number:=0;
13 begin
14 --
15 select 'USER: '||user userr,
16 'Username: '||username username,
17 'Current User: '||sys_context('userenv','current_user') curr,
18 'Session User: '||sys_context('userenv','session_user') sess,
19 'Current Schema: '||sys_context('userenv','current_schema') scm
20 into lv_user,lv_username,lv_curr,lv_sess,lv_scm
21 from user_users;
22 dbms_output.put_line('user =['||lv_user||']');
23 dbms_output.put_line('username =['||lv_username||']');
24 dbms_output.put_line('Current User =['||lv_curr||']');
25 dbms_output.put_line('Session User =['||lv_sess||']');
26 dbms_output.put_line('Current Schema=['||lv_scm||']');
27 --
28 for cc_priv in (
29 select 'SESSION ROLES' typ,role grants from session_roles
30 union
31 select 'GRANTED ROLE' typ,granted_role grants from user_role_privs
32 union
33 select 'GRANTED PRIVILEGE' typ,privilege grants from user_sys_privs
34 order by typ) loop
35 --
36 dbms_output.put_line(cc_priv.typ||chr(9)||cc_priv.grants);
37 --
38 end loop;
39 --
40 select count(*)
41 into lv_role
42 from dba_role_privs
43 where granted_role='ATK_ROLE'
44 and grantee=lv_user;
45 dbms_output.put_line('ATK_ROLE count=['||lv_role||']');
46 --
47 dbms_output.put_line('Hello from atk_test.exe.test()');
48 --
49 end;
50* end;
51 .
SQL> @exe
Package created.
Package body created.
SQL>
And running it to see if we have the role gives:
SQL> set serveroutput on
SQL> exec atk_test.exe.test;
user =[USER: SYS]
username =[Username: ATK_TEST]
Current User =[Current User: ATK_TEST]
Session User =[Session User: SYS]
Current Schema=[Current Schema: ATK_TEST]
GRANTED PRIVILEGE CREATE PROCEDURE
GRANTED PRIVILEGE CREATE SESSION
ATK_ROLE count=[0]
Hello from atk_test.exe.test()
PL/SQL procedure successfully completed.
SQL>
This did not work as we did not find the role. It should work as the code run separately shows:
SQL> select count(*) from dba_role_privs where granted_role='ATK_ROLE' and grantee='SYS';
COUNT(*)
----------
1
SQL>
OK, that is a stupid mistake on my part as the user is USER: SYS as returned from the database as a debug line. Lets fix that and try again:
SQL> @cs
Connected.
USER is "SYS"
SQL> get exe
1 create or replace package atk_test.exe as
2 procedure test;
3 end;
4 /
5 create or replace package body atk_test.exe as
6 procedure test is
7 lv_user varchar2(100);
8 lv_username varchar2(100);
9 lv_curr varchar2(100);
10 lv_sess varchar2(100);
11 lv_scm varchar2(100);
12 lv_role number:=0;
13 begin
14 --
15 select user userr,
16 username username,
17 sys_context('userenv','current_user') curr,
18 sys_context('userenv','session_user') sess,
19 sys_context('userenv','current_schema') scm
20 into lv_user,lv_username,lv_curr,lv_sess,lv_scm
21 from user_users;
22 dbms_output.put_line('user =['||lv_user||']');
23 dbms_output.put_line('username =['||lv_username||']');
24 dbms_output.put_line('Current User =['||lv_curr||']');
25 dbms_output.put_line('Session User =['||lv_sess||']');
26 dbms_output.put_line('Current Schema=['||lv_scm||']');
27 --
28 for cc_priv in (
29 select 'SESSION ROLES' typ,role grants from session_roles
30 union
31 select 'GRANTED ROLE' typ,granted_role grants from user_role_privs
32 union
33 select 'GRANTED PRIVILEGE' typ,privilege grants from user_sys_privs
34 order by typ) loop
35 --
36 dbms_output.put_line(cc_priv.typ||chr(9)||cc_priv.grants);
37 --
38 end loop;
39 --
40 select count(*)
41 into lv_role
42 from dba_role_privs
43 where granted_role='ATK_ROLE'
44 and grantee=lv_user;
45 dbms_output.put_line('ATK_ROLE count=['||lv_role||']');
46 --
47 dbms_output.put_line('Hello from atk_test.exe.test()');
48 --
49 end;
50* end;
51 .
SQL> @exe
Package created.
Package body created.
SQL>
If we now run this lets check we can detect the caller having our role ATK_ROLE:
SQL> set serveroutput on
SQL> exec atk_test.exe.test;
user =[SYS]
username =[ATK_TEST]
Current User =[ATK_TEST]
Session User =[SYS]
Current Schema=[ATK_TEST]
GRANTED PRIVILEGE CREATE PROCEDURE
GRANTED PRIVILEGE CREATE SESSION
ATK_ROLE count=[1]
Hello from atk_test.exe.test()
PL/SQL procedure successfully completed.
SQL>
Great, this now works so lets now convert the ATK_TEST.EXE.TEST() procedure to block any user who does not have the ATK_ROLE and install it:
SQL> get exe
1 create or replace package atk_test.exe as
2 procedure test;
3 end;
4 /
5 create or replace package body atk_test.exe as
6 procedure test is
7 lv_user varchar2(100);
8 lv_role number:=0;
9 atk_exe exception;
10 pragma exception_init(atk_exe,-20011);
11 begin
12 --
13 select user userr
14 into lv_user
15 from user_users;
16 dbms_output.put_line('user =['||lv_user||']');
17 --
18 select count(*)
19 into lv_role
20 from dba_role_privs
21 where granted_role='ATK_ROLE'
22 and grantee=lv_user;
23 dbms_output.put_line('ATK_ROLE count=['||lv_role||']');
24 --
25 if(lv_role=0) then
26 raise atk_exe;
27 end if;
28 --
29 dbms_output.put_line('Hello from atk_test.exe.test()');
30 --
31 end;
32* end;
33 .
SQL> @exe
Package created.
Package body created.
SQL>
So, finally lets try as SYS and see if we can run the procedure as SYS has the ATK_ROLE:
SQL> sho user
USER is "SYS"
SQL> set serveroutput on
SQL> exec atk_test.exe.test;
user =[SYS]
ATK_ROLE count=[1]
Hello from atk_test.exe.test()
PL/SQL procedure successfully completed.
SQL>
Works, great, lets create a user with EXECUTE ANY PROCEDURE, CREATE SESSION and the ATK_ROLE:
SQL> create user atk1 identified by atk1;
User created.
SQL> grant create session, execute any procedure to atk1;
Grant succeeded.
SQL> grant atk_role to atk1;
Grant succeeded.
SQL>
Now try and run it:
SQL> connect atk1/atk1@//192.168.56.33:1539/xepdb1
Connected.
SQL> set serveroutput on
SQL> exec atk_test.exe.test;
user =[ATK1]
ATK_ROLE count=[1]
Hello from atk_test.exe.test()
PL/SQL procedure successfully completed.
SQL>
Works!!
Now revoke the ATK_ROLE from ATK1 and test again:
SQL> @cs
Connected.
USER is "SYS"
SQL> revoke atk_role from atk1;
Revoke succeeded.
SQL> connect atk1/atk1@//192.168.56.33:1539/xepdb1
Connected.
SQL> set serveroutput on
SQL> exec atk_test.exe.test;
user =[ATK1]
ATK_ROLE count=[0]
BEGIN atk_test.exe.test; END;
*
ERROR at line 1:
ORA-20011:
ORA-06512: at "ATK_TEST.EXE", line 22
ORA-06512: at line 1
SQL>
This works as planned.
BUT, there are still some issues; there always are when we use standard features, we need to go down the rabbit hole.
If we wanted to block EXECUTE ANY PROCEDURE from other peoples code then we can not initially unless we have the source code. One option is replace the original procedure with a thin veneer that does the security first and then the original that has been renamed is called. There is a flaw with this also as the original can be executed direct with EXECUTE ANY PROCEDURE. In some cases we can fix even this. We can edit the original file and add an ACCESSIBLE BY clause to force access via our wrapper and security so it cannot be called direct anymore.
There is a second more obvious problem. We decided to control the security via the membership of the role ATK_ROLE; so again down the security rabbit hole we must go as we must also now think about stopping anyone from granting that role as if they did it would bypass our security mechanism. We can do this in two ways; the first to stop people having the grants to grant roles - this is not practical in any database; the second is to create a new DDL trigger that specifically blocks the granting of this role; maybe unless its an ATK security DBA.
In summary we need layers and layers of security to implement something with standard database features. We need to implement the security and then security of that security and maybe security of security of security and we must not forget to add audit events!
#oracleace #sym_42 #oracle #security #audit #trail #audittrail #databreach #hacking #grants #protect #readonly #table
Testing a READONLY table and sometimes WRITE and DELETE
This is the next part of the series looking at the AUDSYS schema and AUD$UNIFIED table that Oracle has created and protected. In the first part we explored what AUDSYS and AUD$UNIFIED looks like in terms of security; in part 2 we explored how Oracle might have created this security and in part 3 we designed at a high level how we might implement similar security in the database. In the last part 4 I implemented a security design to match as best we could the AUDSYS and AUD$UNIFIED set up functionally from Oracle.
In this short post I want to now see if I can abuse abuse my set up and see how it performs.
First lets do the basics and add data to the table and delete from it via the API as designed:
This works as planned but what about trying direct deletes, inserts and updates on the table as SYSDBA:
So, these all work as planned. We can insert records into the table only via the API and delete only via the API. Also I was logged on as SYSDBA and using UPDATE ANY TABLE and DELETE ANY TABLE and INSERT ANY TABLE and these were blocked so we fixed the issue of ATK_SEC not being a dictionary protected user.
Now, what if we try and truncate the table:
Hmm, that is not what we want. Lets fix that with a TRUNCATE DDL trigger:
And if try and truncate the table again:
Fixed!
What if we try and create a table or procedure in ATK_SEC:
Also covered, if an attacker is able to create objects in the schema we protected then maybe they can find a way to bypass security.
Lets try and login as ATK_SEC:
OK, lets try and add a password and unlock:
Or can we unlock the account or make it proxiable:
NO, what about trying to grant CREATE SESSION?
We did capture some audit events with the standard events of PFCLATK
This is a pretty good version of the same type of security as Oracles AUDSYS and AUD$UNIFIED.
YES, I know it can be bypassed by turning off system triggers or disabling triggers but if we have a good audit trail and can detect very quickly that this has occurred then the danger is that an attacker has update or deleted records from the table. If we know it happened quickly we can get those records back via flashback or Log Miner so yes its possible to bypass but the damage does by the attacker can be found and fixed quickly.
We can also quickly test the structure of our security every time its used and react if it has changed.
The solution is not perfect BUT we can do it with non additional cost options
#oracleace #sym_42 #oracle #security #audit #trail #audittrail #databreach #hacking #grants #protect #readonly #table
In this short post I want to now see if I can abuse abuse my set up and see how it performs.
First lets do the basics and add data to the table and delete from it via the API as designed:
SQL> exec atk_sec.atk_sec_pack.del;
PL/SQL procedure successfully completed.
SQL> exec atk_sec.atk_sec_pack.ins(3,4);
PL/SQL procedure successfully completed.
SQL> exec atk_sec.atk_sec_pack.ins(5,6);
PL/SQL procedure successfully completed.
SQL>
This works as planned but what about trying direct deletes, inserts and updates on the table as SYSDBA:
SQL> delete from atk_sec.my_tab;
delete from atk_sec.my_tab
*
ERROR at line 1:
ORA-20006:
ORA-06512: at "ATK_SEC.ATK_DML", line 20
ORA-04088: error during execution of trigger 'ATK_SEC.ATK_DML'
SQL>
SQL> insert into atk_sec.my_tab(col01,col02) values (1,2);
insert into atk_sec.my_tab(col01,col02) values (1,2)
*
ERROR at line 1:
ORA-20006:
ORA-06512: at "ATK_SEC.ATK_DML", line 20
ORA-04088: error during execution of trigger 'ATK_SEC.ATK_DML'
SQL>
SQL> update atk_sec.my_tab set col01=3;
update atk_sec.my_tab set col01=3
*
ERROR at line 1:
ORA-20006:
ORA-06512: at "ATK_SEC.ATK_DML", line 20
ORA-04088: error during execution of trigger 'ATK_SEC.ATK_DML'
SQL>
So, these all work as planned. We can insert records into the table only via the API and delete only via the API. Also I was logged on as SYSDBA and using UPDATE ANY TABLE and DELETE ANY TABLE and INSERT ANY TABLE and these were blocked so we fixed the issue of ATK_SEC not being a dictionary protected user.
Now, what if we try and truncate the table:
SQL> truncate table atk_sec.my_tab;
Table truncated.
SQL>
Hmm, that is not what we want. Lets fix that with a TRUNCATE DDL trigger:
SQL> get trunc
1 create or replace trigger atk_sec_trun
2 before truncate on database
3 declare
4 atk_trunc exception;
5 pragma exception_init(atk_trunc,-20009);
6 begin
7 if(ora_dict_obj_owner = 'ATK_SEC' and ora_sysevent='TRUNCATE') then
8 raise atk_trunc;
9 end if;
10* end;
SQL> @trunc
SQL> create or replace trigger atk_sec_trun
2 before truncate on database
3 declare
4 atk_trunc exception;
5 pragma exception_init(atk_trunc,-20009);
6 begin
7 if(ora_dict_obj_owner = 'ATK_SEC' and ora_sysevent='TRUNCATE') then
8 raise atk_trunc;
9 end if;
10 end;
11 /
Trigger created.
SQL>
And if try and truncate the table again:
SQL> truncate table atk_sec.my_tab;
truncate table atk_sec.my_tab
*
ERROR at line 1:
ORA-04088: error during execution of trigger 'SYS.ATK_SEC_TRUN'
ORA-00604: error occurred at recursive SQL level 1
ORA-20009:
ORA-06512: at line 6
SQL>
Fixed!
What if we try and create a table or procedure in ATK_SEC:
SQL> create table atk_sec.tab2(col1 number);
create table atk_sec.tab2(col1 number)
*
ERROR at line 1:
ORA-04088: error during execution of trigger 'SYS.ATK_SEC_ALTER'
ORA-00604: error occurred at recursive SQL level 1
ORA-20004:
ORA-06512: at line 6
SQL> create procedure atk_sec.my_proc is begin null; end;
2 /
create procedure atk_sec.my_proc is begin null; end;
*
ERROR at line 1:
ORA-04088: error during execution of trigger 'SYS.ATK_SEC_ALTER'
ORA-00604: error occurred at recursive SQL level 1
ORA-20004:
ORA-06512: at line 6
SQL>
Also covered, if an attacker is able to create objects in the schema we protected then maybe they can find a way to bypass security.
Lets try and login as ATK_SEC:
SQL> connect atk_sec/atk_sec@//192.168.56.33:1539/xepdb1
ERROR:
ORA-28000: The account is locked.
Warning: You are no longer connected to ORACLE.
SQL>
OK, lets try and add a password and unlock:
SQL> alter user atk_sec identified by atk_sec;
alter user atk_sec identified by atk_sec
*
ERROR at line 1:
ORA-04088: error during execution of trigger 'SYS.ATK_SEC_ALTER'
ORA-00604: error occurred at recursive SQL level 1
ORA-20002:
ORA-06512: at line 7
SQL>
Or can we unlock the account or make it proxiable:
SQL> alter user atk_sec account unlock;
alter user atk_sec account unlock
*
ERROR at line 1:
ORA-04088: error during execution of trigger 'SYS.ATK_SEC_ALTER'
ORA-00604: error occurred at recursive SQL level 1
ORA-20002:
ORA-06512: at line 7
SQL> alter user atk_sec grant connect through aud4;
alter user atk_sec grant connect through aud4
*
ERROR at line 1:
ORA-04088: error during execution of trigger 'SYS.ATK_SEC_ALTER'
ORA-00604: error occurred at recursive SQL level 1
ORA-20002:
ORA-06512: at line 7
SQL>
NO, what about trying to grant CREATE SESSION?
SQL> grant create session to atk_sec;
grant create session to atk_sec
*
ERROR at line 1:
ORA-04088: error during execution of trigger 'SYS.ATK_SEC_GRANT'
ORA-00604: error occurred at recursive SQL level 1
ORA-20003:
ORA-06512: at line 16
SQL>
We did capture some audit events with the standard events of PFCLATK
ID TIMESTAMP DBNAME PAYLOAD ERRORTEXT P
----- -------------------------- ------ -------------------------------- ------------------------------------------------------------------------------------------------------------------------ -
36 22-JUL-2025 15:16:50:21385 XE EVE_1_9:CHANGES-TO-EXTERNALS System Privilege {CREATE TRIGGER} used by {SYS} on {SYS.ATK_SEC_CREATE} using IP {192.168.56.1} with error code {0} N
36 22-JUL-2025 15:16:50:21385 XE EVE_1_9:CHANGES-TO-EXTERNALS System Privilege {CREATE TRIGGER} used by {SYS} on {SYS.ATK_SEC_DROP} using IP {192.168.56.1} with error code {0} N
36 22-JUL-2025 15:16:50:21385 XE EVE_1_9:CHANGES-TO-EXTERNALS System Privilege {CREATE TRIGGER} used by {SYS} on {SYS.ATK_SEC_GRANT} using IP {192.168.56.1} with error code {0} N
37 22-JUL-2025 15:40:43:12690 XE EVE_1_24:ERROR-LIMIT Someone on IP Adress {192.168.56.1} has has generated {9} errors for the user {SYS} in the last 30 minutes N
38 22-JUL-2025 15:40:54:44356 XE EVE_1_24:ERROR-LIMIT Someone on IP Adress {192.168.56.1} has has generated {10} errors for the user {SYS} in the last 30 minutes N
39 22-JUL-2025 15:42:02:70611 XE EVE_1_24:ERROR-LIMIT Someone on IP Adress {192.168.56.1} has has generated {11} errors for the user {SYS} in the last 30 minutes N
40 22-JUL-2025 15:46:50:11734 XE EVE_1_9:CHANGES-TO-EXTERNALS System Privilege {ALTER TABLE} used by {SYS} on {ATK_SEC.MY_TAB} using IP {192.168.56.1} with error code {4088} N
40 22-JUL-2025 15:46:50:11734 XE EVE_1_9:CHANGES-TO-EXTERNALS System Privilege {CREATE TRIGGER} used by {SYS} on {SYS.ATK_SEC_TRUN} using IP {192.168.56.1} with error code {0} N
SQL>
This is a pretty good version of the same type of security as Oracles AUDSYS and AUD$UNIFIED.
YES, I know it can be bypassed by turning off system triggers or disabling triggers but if we have a good audit trail and can detect very quickly that this has occurred then the danger is that an attacker has update or deleted records from the table. If we know it happened quickly we can get those records back via flashback or Log Miner so yes its possible to bypass but the damage does by the attacker can be found and fixed quickly.
We can also quickly test the structure of our security every time its used and react if it has changed.
The solution is not perfect BUT we can do it with non additional cost options
#oracleace #sym_42 #oracle #security #audit #trail #audittrail #databreach #hacking #grants #protect #readonly #table
Implement a Test System to Create a Readonly and Sometimes Insert / Delete Table
This is the next part (4th part) of the series exploring the AUDSYS schema and AUD$UNIFIED table that is READONLY with a lot of INSERTING and sometimes deleting.
In the first part we explored the AUDSYS schema and the AUD$UNIFIED table and what we can and cannot do. In the second part we started to explore how Oracle may have implemented this security by ruling out everything we could think of so that we came to the conclusion that Oracle must use internal policies or hard coded in C rules in the database engine. In the third part we discussed how we might design a similarly secured table using standard features of the database.
In this 4th part I am going to implement a simple example where I want a table that can be read by anyone granted access to it but no UPDATES and DELETE and where INSERT is only done via an API. I will also shadow AUDSYS and stop direct logging into my schema account.
So, lets first create the schema only account and locked; i.e. it has no password and is locked:
Now remove the INHERIT privileges:
Set up a quota on USERS:
Notice that we also did not grant CREATE SESSION to ATK_SEC to add one more hurdle to logging in as ATK_SEC.
Now, create a table that we will protect:
There will be no grants on this table of course. Now add a couple of rows just to test the view in a minute:
We created the table that is to be protected as SYS not as ATK_SEC as we do not want any logons to ATK_SEC even via proxy which I would normally recommend a way to access a schema for maintenance.
Now create the READONLY view that will have access granted to users that need to read the data:
We now have a view that will allow access to the data and not allow DML through the view. This view access can then be granted to users who need the access. We will do this here as an example via a role BUT if the access was needed via client PL/SQL then it would need to be direct or the PL/SQL would need to use a granted role.
OK, we have the basic data set up. Lets create a logon trigger to prevent login as ATK_SEC:
Lets now kill a few birds with one stone and block an attempt to allow proxy through ATK_SEC and also stop adding a password or unlocking:
This means that we did not really need a logon trigger to prevent logging in as ATK_SEC because it should not be possible to add proxy or change the password or unlock. We could add additional triggers for proxy and we could have written three separate ALTER triggers to prevent each action as that would potentially make it harder to remove the triggers separately and more interestingly it would create more noise for forensics in trying.
Next we need to create the PL/SQL API to allow insert into the table and delete on the table; these are simple for this demo and do not contain any internal security checks BUT they easily could:
And testing quickly:
Now we need to create the DML trigger on the base table and allow the package ATK_SEC.ATK_SEC_PACK to be used but direct INSERT, UPDATE and DELETE to not be used:
This will now allow the API to do inserts of records and the API to delete records but will block direct inserts and deletes and block updates all together.
Finally we can also add a CREATE trigger to prevent any additional objects being added to the ATK_SEC schema:
This will stop any objects being created in the ATK_SEC schema that could be used to try and hack the schema and change the data or similar. One final thing we can do is add a GRANT trigger:
This will detect any changes to the schema by an attacker trying to get around the security that involves adding more privileges such as GRANT CREATE SESSION needed to try and log in.
One final final thing we can do is also add a DROP system trigger to prevent dropping of any of the security we have added:
This will prevent dropping of objects in the ATK_SEC schema
One final final final thing is to consider that we cant block some things like turning off system triggers with ALTER SYSTEM but we can audit this action and also audit all no standard actions or access on this solution. I leave that as a future example. I have PFCLATK installed in this database and it already has policies for most of these events and more. Some audit generated whilst creating this set up is here:
When creating security like this we have to be careful what order we add the security protections, as adding each layer can stop the next thing from working. The same idea/restriction applies to other built in tools such as Database vault.
Remember the same ideas can be used to secure anything such as a need to allow passwords to be changed by the helpdesk; so we can create a schema that has ALTER USER and we then protect a schema that has ALTER USER and expose the privilege via an API. We could do many other things as well using this design pattern.
Remember it is not perfect as we have used just standard features and it could be disabled by a DBA BUT we augment the design with audit policies to ensure that we catch any changes to the security.
#oracleace #sym_42 #oracle #security #audit #trail #audittrail #grants #protect #readonly #table
In the first part we explored the AUDSYS schema and the AUD$UNIFIED table and what we can and cannot do. In the second part we started to explore how Oracle may have implemented this security by ruling out everything we could think of so that we came to the conclusion that Oracle must use internal policies or hard coded in C rules in the database engine. In the third part we discussed how we might design a similarly secured table using standard features of the database.
In this 4th part I am going to implement a simple example where I want a table that can be read by anyone granted access to it but no UPDATES and DELETE and where INSERT is only done via an API. I will also shadow AUDSYS and stop direct logging into my schema account.
So, lets first create the schema only account and locked; i.e. it has no password and is locked:
SQL> create user atk_sec no authentication account lock default tablespace users;
User created.
SQL>
Now remove the INHERIT privileges:
SQL> revoke inherit privileges on user atk_sec from public;
Revoke succeeded.
SQL>
Set up a quota on USERS:
SQL> alter user atk_sec quota unlimited on users;
User altered.
SQL>
Notice that we also did not grant CREATE SESSION to ATK_SEC to add one more hurdle to logging in as ATK_SEC.
Now, create a table that we will protect:
SQL> create table atk_sec.my_tab(col01 number, col02 number);
Table created.
SQL>
There will be no grants on this table of course. Now add a couple of rows just to test the view in a minute:
SQL> create table atk_sec.my_tab(col01 number, col02 number);
Table created.
SQL> insert into atk_sec.my_tab(col01,col02) values (1,2);
1 row created.
SQL> insert into atk_sec.my_tab(col01,col02) values (3,4);
1 row created.
SQL> commit;
Commit complete.
SQL>
We created the table that is to be protected as SYS not as ATK_SEC as we do not want any logons to ATK_SEC even via proxy which I would normally recommend a way to access a schema for maintenance.
Now create the READONLY view that will have access granted to users that need to read the data:
SQL> create or replace view atk_sec.my_view as select * from atk_sec.my_tab with read only;
View created.
SQL>
We now have a view that will allow access to the data and not allow DML through the view. This view access can then be granted to users who need the access. We will do this here as an example via a role BUT if the access was needed via client PL/SQL then it would need to be direct or the PL/SQL would need to use a granted role.
SQL> create role atk_sec_admin;
Role created.
SQL> grant select on atk_sec.my_view to atk_sec_admin;
Grant succeeded.
SQL>
OK, we have the basic data set up. Lets create a logon trigger to prevent login as ATK_SEC:
SQL> @cs
Connected.
USER is "SYS"
SQL> get trig
1 create or replace trigger atk_sec_logon
2 after logon on database
3 declare
4 atk_log exception;
5 pragma exception_init(atk_log,-46370);
6 begin
7 if(user='ATK_SEC') then
8 raise atk_log;
9 end if;
10* end;
SQL> @trig
Trigger created.
SQL>
Lets now kill a few birds with one stone and block an attempt to allow proxy through ATK_SEC and also stop adding a password or unlocking:
SQL> get alter
1 create or replace trigger atk_sec_alter
2 before alter on database
3 declare
4 atk_alter exception;
5 pragma exception_init(atk_alter,-20002);
6 begin
7 if(ora_dict_obj_type = 'USER') then
8 if(ora_dict_obj_name='ATK_SEC') then
9 raise atk_alter;
10 end if;
11 end if;
12* end;
SQL> @alter
Trigger created.
SQL>
This means that we did not really need a logon trigger to prevent logging in as ATK_SEC because it should not be possible to add proxy or change the password or unlock. We could add additional triggers for proxy and we could have written three separate ALTER triggers to prevent each action as that would potentially make it harder to remove the triggers separately and more interestingly it would create more noise for forensics in trying.
Next we need to create the PL/SQL API to allow insert into the table and delete on the table; these are simple for this demo and do not contain any internal security checks BUT they easily could:
SQL> get pack
1 create or replace package atk_sec.atk_sec_pack as
2 procedure ins(pv_col1 in number, pv_col2 in number);
3 procedure del;
4 end;
5 /
6 create or replace package body atk_sec.atk_sec_pack as
7 procedure ins(pv_col1 in number, pv_col2 in number)
8 is
9 begin
10 insert into atk_sec.my_tab(col01,col02) values (pv_col1, pv_col2);
11 commit;
12 end;
13 procedure del is
14 begin
15 delete from atk_sec.my_tab;
16 commit;
17 end;
18* end;
19 .
SQL> @pack
Package created.
Package body created.
SQL>
And testing quickly:
SQL> exec atk_sec.atk_sec_pack.ins(5,6);
PL/SQL procedure successfully completed.
SQL> exec atk_sec.atk_sec_pack.del;
PL/SQL procedure successfully completed.
SQL> exec atk_sec.atk_sec_pack.ins(5,6);
PL/SQL procedure successfully completed.
SQL> exec atk_sec.atk_sec_pack.ins(7,8);
PL/SQL procedure successfully completed.
SQL> exec atk_sec.atk_sec_pack.ins(1,2);
PL/SQL procedure successfully completed.
SQL>
Now we need to create the DML trigger on the base table and allow the package ATK_SEC.ATK_SEC_PACK to be used but direct INSERT, UPDATE and DELETE to not be used:
SQL> get dml
1 create or replace trigger atk_sec.atk_dml
2 before insert or update or delete
3 on atk_sec.my_tab
4 declare
5 lv_depth number;
6 lv_ins boolean:=false;
7 lv_del boolean:=false;
8 atk_dml exception;
9 pragma exception_init(atk_dml,-20006);
10 begin
11 lv_depth:=utl_call_stack.dynamic_depth;
12 for i in 1 .. lv_depth loop
13 if(utl_call_stack.concatenate_subprogram(utl_call_stack.subprogram(i))='ATK_SEC_PACK.INS') then
14 lv_ins:=true;
15 elsif(utl_call_stack.concatenate_subprogram(utl_call_stack.subprogram(i))='ATK_SEC_PACK.DEL') then
16 lv_del:=true;
17 end if;
18 end loop;
19 -- dbms_output.put_line('lv_del=['||sys.diutil.bool_to_int(lv_del)||']');
20 -- dbms_output.put_line('lv_ins=['||sys.diutil.bool_to_int(lv_ins)||']');
21 if(inserting and (lv_ins=false)) then
22 raise atk_dml;
23 elsif(deleting and (lv_del=false)) then
24 raise atk_dml;
25 elsif(updating) then
26 raise atk_dml;
27 end if;
28* end;
29 .
SQL> @dml;
Trigger created.
SQL>
This will now allow the API to do inserts of records and the API to delete records but will block direct inserts and deletes and block updates all together.
Finally we can also add a CREATE trigger to prevent any additional objects being added to the ATK_SEC schema:
SQL> create or replace trigger atk_sec_create
2 before create on database
3 declare
4 atk_create exception;
5 pragma exception_init(atk_create,-20004);
6 begin
7 if(ora_dict_obj_owner = 'ATK_SEC' and ora_sysevent='CREATE') then
8 raise atk_create;
9 end if;
10 end;
11 /
Trigger created.
SQL>
This will stop any objects being created in the ATK_SEC schema that could be used to try and hack the schema and change the data or similar. One final thing we can do is add a GRANT trigger:
SQL> create or replace trigger atk_sec_grant
2 before grant on database
3 declare
4 lv_num pls_integer;
5 lv_grantee ora_name_list_t;
6 lv_atk boolean:=false;
7 atk_grant exception;
8 pragma exception_init(atk_grant,-20003);
9 begin
10 lv_num:=ora_grantee(lv_grantee);
11 for i in 1 .. lv_num loop
12 if(lv_grantee(i)='ATK_SEC') then
13 lv_atk:=true;
14 end if;
15 end loop;
16
17 if(lv_atk=true) then
18 raise atk_grant;
19 end if;
20 end;
21 /
Trigger created.
SQL>
This will detect any changes to the schema by an attacker trying to get around the security that involves adding more privileges such as GRANT CREATE SESSION needed to try and log in.
One final final thing we can do is also add a DROP system trigger to prevent dropping of any of the security we have added:
SQL> create or replace trigger atk_sec_drop
2 before drop on database
3 declare
4 atk_drop exception;
5 pragma exception_init(atk_drop,-20008);
6 begin
7 if(ora_dict_obj_owner = 'ATK_SEC' and ora_sysevent='DROP') then
8 raise atk_drop;
9 end if;
10 end;
11 /
Trigger created.
SQL>
This will prevent dropping of objects in the ATK_SEC schema
One final final final thing is to consider that we cant block some things like turning off system triggers with ALTER SYSTEM but we can audit this action and also audit all no standard actions or access on this solution. I leave that as a future example. I have PFCLATK installed in this database and it already has policies for most of these events and more. Some audit generated whilst creating this set up is here:
27 22-JUL-2025 10:24:56:46504 XE EVE_1_5:USER-CHANGES User Privilege {ALTER USER} used by {SYS} using IP {192.168.56.1} with error code {0} N
28 22-JUL-2025 10:46:50:05447 XE EVE_1_9:CHANGES-TO-EXTERNALS System Privilege {CREATE TABLE} used by {SYS} on {ATK_SEC.MY_TAB} using IP {192.168.56.1} with error code {0} N
29 22-JUL-2025 10:46:50:06530 XE EVE_1_17:CHANGES-TO-EXTERNALS Externals Change {CREATE TABLE} used by {SYS} on {ATK_SEC.MY_TAB} using IP {192.168.56.1} with error code {0} N
30 22-JUL-2025 11:14:49:21530 XE EVE_1_19:CHANGES-TO-PARAMTERS Alter system/session command {LOGON} used by {ATK_SEC} using IP {192.168.56.1} with error code {0} N
31 22-JUL-2025 11:16:50:19369 XE EVE_1_9:CHANGES-TO-EXTERNALS System Privilege {CREATE TRIGGER} used by {SYS} on {SYS.ATK_SEC_LOGON} using IP {192.168.56.1} with error code {0} N
31 22-JUL-2025 11:16:50:19369 XE EVE_1_9:CHANGES-TO-EXTERNALS System Privilege {CREATE VIEW} used by {SYS} on {ATK_SEC.MY_VIEW} using IP {192.168.56.1} with error code {0} N
32 22-JUL-2025 12:46:50:06414 XE EVE_1_5:USER-CHANGES User Privilege {ALTER USER} used by {SYS} using IP {192.168.56.1} with error code {4088} N
33 22-JUL-2025 12:46:50:06976 XE EVE_1_9:CHANGES-TO-EXTERNALS System Privilege {CREATE TRIGGER} used by {SYS} on {SYS.ATK_SEC_ALTER} using IP {192.168.56.1} with error code {0} N
34 22-JUL-2025 14:16:50:16473 XE EVE_1_9:CHANGES-TO-EXTERNALS System Privilege {CREATE TRIGGER} used by {SYS} on {ATK_SEC.ATK_DML} using IP {192.168.56.1} with error code {0} N
35 22-JUL-2025 14:16:50:17548 XE EVE_1_17:CHANGES-TO-EXTERNALS Externals Change {EXECUTE} used by {SYS} on {.} using IP {192.168.56.1} with error code {904} N
SQL>
When creating security like this we have to be careful what order we add the security protections, as adding each layer can stop the next thing from working. The same idea/restriction applies to other built in tools such as Database vault.
Remember the same ideas can be used to secure anything such as a need to allow passwords to be changed by the helpdesk; so we can create a schema that has ALTER USER and we then protect a schema that has ALTER USER and expose the privilege via an API. We could do many other things as well using this design pattern.
Remember it is not perfect as we have used just standard features and it could be disabled by a DBA BUT we augment the design with audit policies to ensure that we catch any changes to the security.
#oracleace #sym_42 #oracle #security #audit #trail #audittrail #grants #protect #readonly #table
