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
