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
