Call: +44 (0)7759 277220 Call
PeteFinnigan.com Limited Products, Services, Training and Information
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:

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:

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
OraBlogs


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:
  • 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:

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