Four things a developer can do now to improve their applications #infosec posture.

Lets face it, we have deadlines to meet and millions of lines of code in production. I get it, I’ve been a working PL/SQL developer off and on for over 20 years. If we get into the habit of using some of the security features in the language along with some practices, we can improve the security of you code. So, lets get into it.

1) Use packages. Steve Feuerstein http://www.oracle.com/technetwork/issue-archive/index-087690.html has been saying for years to move those functions and procedures to packages and there is good security reasons to do that. If you have a SQL Injection bug in your application, I can get to ALL_SOURCE and read your code and if I can get to your code, I can find other exploits.

So we can read the code in Functions and Procedures.

 1 SQL> select text from all_source where owner = user and name = 'PARSE_STRING';
 2 procedure parse_string(p_string varchar2) AS
 3   CURSOR col_cur IS
 4   select distinct (instr(p_string||',',',',1,level)) loc
 5   FROM dual
 6   CONNECT BY LEVEL <= LENGTH(REPLACE(p_string,','))+1
 7   ORDER BY 1;
 8   l_col varchar2(65);
 9 BEGIN
10   for col_rec in col_cur
11   loop
12     l_col := substr(p_string, col_rec.loc, instr(p_string, col_rec.loc+1));
13     dbms_output.put_line(l_col || 'col pos ' || col_rec.loc);
14   end loop;
15 end;
16 
17 14 rows selected.
18 
19 SQL> create user u2 identified by MY##56SecurePassword;
20 
21 User created.
22 
23 SQL> grant create session to u2;
24 
25 Grant succeeded.
26 
27 SQL> grant execute on parse_string to u2;
28 
29 Grant succeeded.
30 
31 SQL> conn u2@orcl
32 Enter password: 
33 Connected.
34 SQL> select text from all_source where owner = 'RLOCKARD'          
35   2  and name = 'PARSE_STRING';
36 procedure parse_string(p_string varchar2) AS
37   CURSOR col_cur IS
38   select distinct (instr(p_string||',',',',1,level)) loc
39   FROM dual
40   CONNECT BY LEVEL <= LENGTH(REPLACE(p_string,','))+1
41   ORDER BY 1;
42   l_col varchar2(65);
43 BEGIN
44   for col_rec in col_cur
45   loop
46     l_col := substr(p_string, col_rec.loc, instr(p_string, col_rec.loc+1));
47     dbms_output.put_line(l_col || 'col pos ' || col_rec.loc);
48   end loop;
49 end;
50 
51 14 rows selected.
52 
53 SQL> 
54

Now when we put this into a package, the only thing I can extract from it is the package specification.

First lets put it into a package.

 1 SQL> sho user
 2 USER is "RLOCKARD"
 3 SQL> create or replace package rlockard.utility AS
 4   procedure parse_string(p_string varchar2);
 5 end;
 6 /
 7 
 8 Package created.
 9 
10 SQL> create or replace package body rlockard.utility AS
11 
12     procedure parse_string(p_string varchar2) IS
13     CURSOR col_cur IS
14         select distinct (instr(p_string||',',',',1,level)) loc
15         FROM dual
16         CONNECT BY LEVEL <= LENGTH(REPLACE(p_string,','))+1
17         ORDER BY 1;
18         l_col varchar2(65);
19     BEGIN
20         for col_rec in col_cur
21         loop
22             l_col := substr(p_string, col_rec.loc, instr(p_string, col_rec.loc+1));
23             sys.dbms_output.put_line(l_col || 'col pos ' || col_rec.loc);
24         end loop;
25     end;
26 end;
27 /
28 
29 Package body created.
30 
31 SQL>

Now, lets test to see what we can see.

 1 SQL> grant execute on utility to u2;
 2 
 3 Grant succeeded.
 4 
 5 SQL> conn u2@orcl
 6 Enter password: 
 7 Connected.
 8 SQL> select text from all_source where owner = 'RLOCKARD'
 9 and name = 'UTILITY';
10   2  
11 TEXT
12 --------------------------------------------------------------------------------
13 package   utility AS
14   procedure parse_string(p_string varchar2);
15 end;
16

As we can see, now you only get the package specification. This is really more that I would like to get out, but it’s much better than getting all your source code.

2) Split up your packages into smaller packages based on function. I normally split them up by UTILITY, SENSITIVE and NON SENSITIVE. If there are functions / procedures against sensitive tables those will go into the sensitive packages. You can further break down you sensitive packages. ie: CUSTOMER_API_PKG that would be your interface into your customers table.

Here is a good post by Steve Feuerstein on breaking packages down and keeping them compatible with existing code. http://www.oracle.com/technetwork/issue-archive/2015/15-jan/o15plsql-2398996.html

3) Limit the execution rights to a package and what a user can do with a package.

3a) We have been granting execute to packages for decades now. Then Oracle 11g gave us Invoker and Definer rights. When you create a package and don’t specify invoker or definer rights, the package is created with definer rights as the default. That’s all well and good, but let’s think this through. If I execute a package with definer rights and that package updates the customers table, even thought I don’t have update on the customers table, the package will work.

 1 SQL> create or replace package rlockard.cust_api AS
 2   function update_customers_credit_limit(pID in number, pCredit in number) return number;
 3 end;
 4 /
 5 Package created.
 6 
 7 SQL> create or replace package body rlockard.cust_api AS
 8 
 9     function update_customers_credit_limit(pID in number, pCredit in number) return number is
10     retVal number;
11     begin
12         update customers set credit = pCredit where id = pId;
13         return 1;
14     exception when no_data_found
15     then
16       retVal := helpdesk.utility.log_error(pPkg => $$PLSQL_UNIT, pLine => $$PLSQL_LINE,
17                                            pParm => 'pID = ' || to_char(iID) ||
18                                                    ' pAmount= ' || to_char(pCredit),
19                                            pErr => sqlcode);
20       return retVal * -1;  -- we are flipping the sign to indacate it's an error to caller.
21     end;
22 end;
23 /
24 Package body created.
25 SQL>
26

I am going to grant execute to the user U2 that we create earlier and test this.

 1 SQL> grant execute on rlockard.cust_api to u2;
 2 
 3 Grant succeeded.
 4 
 5 SQL> conn u2@orcl
 6 Enter password: 
 7 Connected.
 8 SQL> declare
 9   2  x number;
10   3  begin
11   4    x:=rlockard.cust_api.update_customers_credit_limit(pId => 1770, pCredit => 1000000);
12   5  end;
13   6  /
14 
15 PL/SQL procedure successfully completed.
16 
17 SQL> select credit from rlockard.customers where id = 1770;
18 select credit from rlockard.customers where id = 1770
19                             *
20 ERROR at line 1:
21 ORA-00942: table or view does not exist
22 
23 
24 SQL> conn rlockard@orcl
25 Enter password: 
26 Connected.
27 SQL> select credit from rlockard.customers where id = 1770;
28 
29     CREDIT
30 ----------
31    1000000
32 
33 SQL> 
34

Did you expect that to happen? How are we going to tighten this down. We are going to set the package to use invokers rights. By adding AUTHID CURRENT_USER to the package specification, the package executes with U2’s rights. U2 does not have any rights on the customers table, the package fails with ORA-00942: table or view does not exists.

 1 SQL> create or replace package rlockard.cust_api 
 2 AUTHID CURRENT_USER
 3 AS
 4   function update_customers_credit_limit(pID in number, pCredit in number) return number;
 5 end;
 6 /
 7   2    3    4    5    6  
 8 Package created.
 9 
10 
11 SQL> conn u2@orcl
12 Enter password:
13 Connected.
14 SQL> declare
15  x number;
16 begin
17  x := rlockard.cust_api.update_customers_credit_limit(pId => 1770, pCredit => 2500000);
18 exception when others then
19   sys.dbms_output.put_line(sqlerrm);
20 end;
21 /
22 ORA-00942: table or view does not exist
23 
24 SQL> conn rlockard@orcl
25 Enter password:
26 Connected.
27 SQL> select credit from customers where id = 1770;
28 
29     CREDIT
30 ----------
31    1000000
32 SQL>
33

3b) In Oracle 12c we were given the ability to grant roles to packages. (procedures and functions too, but you should be using packages) Now, when we have sensitive tables in another schema, we can create a role that a package needs and grant that role to a package.

1 CREATE ROLE update_customers; 2 3 grant update_customers to rlockard; 4 5 GRANT SELECT 6 ON customers 7 TO update_customers; 8 9 GRANT update_customers TO PACKAGE cust_api; 10 11 declare 12 x number; 13 begin 14 x := rlockard.cust_api.update_customers_credit_limit(pId => 1770, pCredit => 2500000); 15 exception when others then 16 sys.dbms_output.put_line(sqlerrm); 17 end; 18 /

3c) Oracle 12c also gave us the accessible by clause. This creates a white list of the packages that can call a package. This way you are narrowing down the ways a package can get called, creating a trusted path to your secure data. So here the public package can call the private package, but if anything else tries to call it a PLS-00904 error will be raised.

 1 SQL> create or replace package public_package AS
 2   procedure update_customers(pId     in number,
 3                              pColumn in varchar2,
 4                              pValue  in varchar2);
 5 end;
 6 /
 7 
 8 Package created.
 9 
10 SQL> create or replace package body public_package as
11   procedure update_customers(pId     in number,
12                              pColumn in varchar2,
13                              pValue  in varchar2) IS
14   x number;  -- we know it's a function that returs a number.
15   begin    -- this is simplistic to demo accessable_by
16     if pColumn = 'CREDIT' then
17       x := rlockard.cust_api.update_customers_credit_limit(pId => pId, pCredit => pValue);
18     end if;
19   end;
20 end;
21 /
22 
23 Package body created.
24 
25 SQL> create or replace package rlockard.cust_api
26 accessible by (public_package) AS
27   function update_customers_credit_limit(pID in number, pCredit in number) return number;
28 end;
29 /
30 
31 Package created.
32 
33 SQL>
34 
35 SQL> declare
36   x number;
37 begin
38   x := rlockard.cust_api.update_customers_credit_limit(pId => 1770, pCredit => 2500000);
39 exception when others then
40   sys.dbms_output.put_line(sqlerrm);
41 end;
42 /
43 x := rlockard.cust_api.update_customers_credit_limit(pId => 1770, pCredit => 2500000);
44        *
45 ERROR at line 4:
46 ORA-06550: line 4, column 8:
47 PLS-00904: insufficient privilege to access object CUST_API
48 ORA-06550: line 4, column 3:
49 PL/SQL: Statement ignored
50 
51 
52 SQL> begin
53   public_package.update_customers(pId => 1771, pColumn => 'CREDIT', pValue => '200');
54 end;
55 /
56 
57 PL/SQL procedure successfully completed.
58 
59 SQL>
60

But when we call it from the package in the accessible by clause, then it works fine. Again, we are limiting the paths to get to the sensitive information.

4a) We are getting down to the meat of what every shop should be doing. Reviewing code. You should be looking for dynamic code that is concatenating variables together. This is a painfully bad piece of code with a major SQL Injection bug.

 1 create or replace PROCEDURE UserLogin 
 2           (p_email    logins.email%type    DEFAULT NULL,
 3            p_password  logins.password%type  DEFAULT NULL)
 4 AS
 5 
 6   STMT CONSTANT VARCHAR2(4000) :=
 7     'SELECT  email
 8      FROM    logins
 9      WHERE   email = ''' || p_email ||
10      ''' AND    password = ''' || p_password || '''';
11 
12   l_result logins.email%type;
13 BEGIN
14 
15   dbms_output.put_line ('SQL STMT: ' || STMT);
16 
17   EXECUTE IMMEDIATE STMT INTO l_result;
18 
19   dbms_output.put_line ('Logon succeeded.');
20 
21 EXCEPTION WHEN OTHERS THEN
22   null; -- OH NO HE DID NOT 
23 END UserLogin;

How would I fix this. Well, lets’ change the dynamic SQL and put in some bind variables. We can still do a lot more with this code, but this fixes the SQL Injection bug and a couple other issues.

 1 create or replace function UserLogin2
 2           (p_email    logins.email%TYPE    DEFAULT NULL,
 3            p_password  logins.PASSWORD%TYPE  DEFAULT NULL) 
 4 RETURN NUMBER AS
 5   kount number; -- a dumb variable to hold a count
 6 BEGIN
 7 
 8   SELECT count(*)
 9   INTO kount
10   FROM logins
11   WHERE email = p_email
12     and password = p_password;
13 
14   IF kount = 1 THEN
15     sys.dbms_output.put_line ('Logon succeeded.');
16     RETURN kount;
17   ELSE
18     return -1;
19   end if;
20 
21 END UserLogin2;

Now you will find I love code reviews. Frequently we learn a way to do something and because it works, we continue doing it. Heck, I loved cursor for loops until I learned better in a code review. Code reviews should be approached as learning opportunities. You are going to learn a lot more tricks reading other peoples code and you may catch something that will improve the security of your system.

So in review the steps you can do now to improve the security posture of your applications are: Control the rights to executing code. Put everything in packages. Split up your packages. Do code reviews.

Four things a developer can do now to improve their applications #infosec posture.

Leave a Reply