{"id":8010,"date":"2017-12-17T00:46:08","date_gmt":"2017-12-16T23:46:08","guid":{"rendered":"https:\/\/www.salvis.com\/blog\/?p=8010"},"modified":"2023-11-08T20:38:06","modified_gmt":"2023-11-08T19:38:06","slug":"how-to-prove-that-your-smartdb-app-is-secured-against-sql-injection-attacks","status":"publish","type":"post","link":"https:\/\/www.salvis.com\/blog\/2017\/12\/17\/how-to-prove-that-your-smartdb-app-is-secured-against-sql-injection-attacks\/","title":{"rendered":"How to Prove That Your SmartDB App Is Secure"},"content":{"rendered":"\n

If you are guarding your data behind a hard shell PL\/SQL API<\/a> as Bryn Llewellyn, Toon Koppelaars and others recommend, then it should be quite easy to prove, that your PL\/SQL application is secured against SQL injection<\/a> attacks. The basic idea is 1) that you do not expose data via tables nor views to Oracle users used in the middle-tier, by end-users and in the GUI; and 2) that you use only static SQL within PL\/SQL packages. By following these two rules, you ensure that only SQL statements with bind variables are used in your application, making the injection of unwanted SQL fragments impossible. In this blog post, I show how to check if an application is complying with these two rules.<\/p>\n\n\n\n

Demo Applications<\/h2>\n\n\n\n

I’ve prepared three tiny demo applications to visualise what guarding data behind a hard shell PL\/SQL API means and how static and dynamic SQL can be used within Oracle Database 12c Release 2 (12.2). You may install these applications using this script<\/a>.
<\/p>\n\n\n\n

\n
\n

The Good<\/h3>\n\n\n
\n
\"\"<\/a><\/figure>\n<\/div>\n\n\n
<\/div>\n\n\n
\n
\"\"<\/a><\/figure>\n<\/div>\n\n\n

The table T is stored in the schema THE_GOOD_DATA and grants SELECT, INSERT, UPDATE and DELETE privileges to the schema  THE_GOOD_API. This schema owns the PL\/SQL package PKG, which implements the data access to table T via static SQL to eliminate the risk of SQL injection. The EXECUTE right on PKG is granted to THE_GOOD_USER. This user has the CONNECT role only and can be safely configured in the connection pool of the middle-tier application.<\/p>\n\n\n\n

The Good Data Access<\/span><\/path><\/path><\/svg><\/span>
CREATE OR REPLACE<\/span> <\/span>PACKAGE<\/span> <\/span>BODY<\/span> <\/span><\/span>\n   the_good_api.pkg <\/span><\/span>\nAS<\/span><\/span>\n   <\/span>FUNCTION<\/span> <\/span>f2<\/span> (<\/span><\/span>\n      p_c2 <\/span>IN<\/span> <\/span>VARCHAR2<\/span><\/span>\n   ) <\/span>RETURN<\/span> <\/span>CLOB<\/span> <\/span>IS<\/span><\/span>\n      <\/span>l_result<\/span> <\/span>CLOB<\/span>;<\/span><\/span>\n      <\/span>l_c2<\/span> the_good_data.t.c2%<\/span>TYPE<\/span>;<\/span><\/span>\n   <\/span>BEGIN<\/span><\/span>\n      <\/span>l_c2<\/span> := p_c2;<\/span><\/span>\n      <\/span>SELECT<\/span> JSON_ARRAYAGG (<\/span><\/span>\n                JSON_OBJECT (<\/span><\/span>\n                   <\/span>'c1'<\/span> value c1,<\/span><\/span>\n                   <\/span>'c2'<\/span> value c2<\/span><\/span>\n                ) <\/span><\/span>\n                <\/span>RETURNING<\/span> <\/span>CLOB<\/span><\/span>\n             )<\/span><\/span>\n        <\/span>INTO<\/span> <\/span>l_result<\/span><\/span>\n        <\/span>FROM<\/span> the_good_data.t<\/span><\/span>\n       <\/span>WHERE<\/span> <\/span>lower<\/span>(c2) <\/span>LIKE<\/span> <\/span>'%'<\/span> <\/span><\/span>\n             || <\/span>lower<\/span>(<\/span>l_c2<\/span>) || <\/span>'%'<\/span>;<\/span><\/span>\n      <\/span>RETURN<\/span> <\/span>l_result<\/span>;<\/span><\/span>\n   <\/span>END<\/span> f2;<\/span><\/span>\nEND<\/span> pkg;<\/span><\/span><\/code><\/pre><\/div>\n<\/div>\n\n\n\n
\n
The Bad<\/h3>\n\n\n
\n
\"\"<\/a><\/figure>\n<\/div>\n\n\n
<\/div>\n\n\n
\n
\"\"<\/a><\/figure>\n<\/div>\n\n\n
This application looks very similar to “The Good”. One difference is, that the access to table T is implemented through dynamic SQL. There is also a private PL\/SQL package named PKG2 which does a series of bad things with dynamic SQL. The implementation of all dynamic SQL is safe, there is no SQL injection possible. However, it is difficult to come to this conclusion by static code analysis and since the use of dynamic SQL is not necessary, this application is considered bad.<\/p>\n\n\n\n
The Bad Data Access<\/span><\/path><\/path><\/svg><\/span>
CREATE OR REPLACE<\/span> <\/span>PACKAGE<\/span> <\/span>BODY<\/span> <\/span><\/span>\n   the_bad_api.pkg <\/span><\/span>\nAS<\/span><\/span>\n   <\/span>FUNCTION<\/span> <\/span>f2<\/span> (<\/span><\/span>\n      p_c2 <\/span>IN<\/span> <\/span>VARCHAR2<\/span><\/span>\n   ) <\/span>RETURN<\/span> <\/span>CLOB<\/span> <\/span>IS<\/span><\/span>\n      co_sql_template <\/span>CONSTANT<\/span> <\/span>CLOB<\/span><\/span>\n         := q<\/span>'[<\/span><\/span>\n   SELECT JSON_ARRAYAGG (<\/span><\/span>\n             JSON_OBJECT (<\/span><\/span>\n                '<\/span>c1<\/span>' value c1, <\/span><\/span>\n                '<\/span>c2<\/span>' value c2<\/span><\/span>\n             ) <\/span><\/span>\n             RETURNING CLOB<\/span><\/span>\n          )<\/span><\/span>\n     FROM the_bad_data.t<\/span><\/span>\n    WHERE lower(c2) LIKE '<\/span>%<\/span>' <\/span><\/span>\n          || lower(:c2_bind) || '<\/span>%<\/span>'<\/span><\/span>\n         ]'<\/span>;     <\/span><\/span>\n      <\/span>l_result<\/span> <\/span>CLOB<\/span>;<\/span><\/span>\n      <\/span>l_c2<\/span> the_bad_data.t.c2%<\/span>TYPE<\/span>;<\/span><\/span>\n   <\/span>BEGIN<\/span><\/span>\n      <\/span>l_c2<\/span> := p_c2;<\/span><\/span>\n      <\/span>EXECUTE IMMEDIATE<\/span> <\/span><\/span>\n              co_sql_template <\/span><\/span>\n         <\/span>INTO<\/span> <\/span>l_result<\/span> <\/span>USING<\/span> <\/span>l_c2<\/span>;<\/span><\/span>\n      <\/span>RETURN<\/span> <\/span>l_result<\/span>;<\/span><\/span>\n   <\/span>END<\/span> f2;<\/span><\/span>\nEND<\/span> pkg;<\/span><\/span><\/code><\/pre><\/div>\n<\/div>\n\n\n\n
\n
The Ugly<\/h3>\n\n\n
\n
\"\"<\/a><\/figure>\n<\/div>\n\n\n
<\/div>\n\n\n
\n
\"\"<\/a><\/figure>\n<\/div>\n\n\n
The table T is stored in the schema THE_UGLY_DATA and grants SELECT, INSERT, UPDATE and DELETE privileges to THE_UGLY_USER. This user has the CONNECT role only. This is good and prohibits schema extensions. But without a PL\/SQL API layer, there is no way to prevent SQL injection in the database. This is now becoming a responsibility of the middle-tier application along with other duties such as data consistency and efficient data processing.<\/p>\n\n\n\n
The Ugly Data Access<\/span><\/path><\/path><\/svg><\/span>
\/* not available<\/span><\/span>\n *<\/span><\/span>\n * Query is crafted in <\/span><\/span>\n * the middle tier application<\/span><\/span>\n *\/<\/span><\/span><\/code><\/pre><\/div>\n<\/div>\n<\/div>\n\n\n\n
<\/div>\n\n\n\n
Example result of a PKG.F2(‘SQL’) call<\/span><\/path><\/path><\/svg><\/span>
[<\/span><\/span>\n   {<\/span><\/span>\n      <\/span>"c1"<\/span>: <\/span>2<\/span>,<\/span><\/span>\n      <\/span>"c2"<\/span>: <\/span>"I like SQL."<\/span><\/span>\n   },<\/span><\/span>\n   {<\/span><\/span>\n      <\/span>"c1"<\/span>: <\/span>3<\/span>,<\/span><\/span>\n      <\/span>"c2"<\/span>: <\/span>"And JSON is part of SQL and PL\/SQL."<\/span><\/span>\n   }<\/span><\/span>\n]<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Rule 1) Do not expose data via tables nor views<\/h2>\n\n\n\n
The idea behind a hard shell PL\/SQL is to expose data through PL\/SQL units only. Direct access to tables or views is unwanted. The Oracle users configured in the connection pool of the middle tier, need the CONNECT role and EXECUTE rights on the PL\/SQL API only. These rights may be granted directly or indirectly via various levels of Oracle roles.<\/p>\n\n\n\n
The following query shows if an Oracle database user is ready to be used in the middle-tier application. Oracle users maintained by Oracle itself, such as SYS, SYSTEM, SYSAUX, etc. are excluded along with some other users which grant objects to PUBLIC (see lines 35 to 38). To execute this query you need the SELECT_CATALOG_ROLE.<\/p>\n\n\n\n
Users ready to be used in the middle tier<\/span><\/path><\/path><\/svg><\/span>
WITH<\/span><\/span>\n   <\/span>-- roles as recursive structure<\/span><\/span>\n   role_base <\/span>AS<\/span> (<\/span><\/span>\n      <\/span>-- roles without parent (=roots)<\/span><\/span>\n      <\/span>SELECT<\/span> r.role, <\/span>NULL<\/span> <\/span>AS<\/span> parent_role<\/span><\/span>\n        <\/span>FROM<\/span> dba_roles r<\/span><\/span>\n       <\/span>WHERE<\/span> r.role <\/span>NOT<\/span> <\/span>IN<\/span> (<\/span><\/span>\n                <\/span>SELECT<\/span> p.granted_role<\/span><\/span>\n                  <\/span>FROM<\/span> role_role_privs p<\/span><\/span>\n             )<\/span><\/span>\n      <\/span>UNION ALL<\/span><\/span>\n      <\/span>-- roles with parent (=children)<\/span><\/span>\n      <\/span>SELECT<\/span> granted_role <\/span>AS<\/span> <\/span>role<\/span>, <\/span>role<\/span> <\/span>AS<\/span> parent_role<\/span><\/span>\n        <\/span>FROM<\/span> role_role_privs<\/span><\/span>\n   ),<\/span><\/span>\n   <\/span>-- roles tree, calculate role_path for every hierarchy level<\/span><\/span>\n   role_tree <\/span>AS<\/span> (<\/span><\/span>\n      <\/span>SELECT<\/span> <\/span>role<\/span>,<\/span><\/span>\n             parent_role,<\/span><\/span>\n             sys_connect_by_path(<\/span>ROLE<\/span>, <\/span>'\/'<\/span>) <\/span>AS<\/span> role_path<\/span><\/span>\n        <\/span>FROM<\/span> role_base<\/span><\/span>\n      <\/span>CONNECT<\/span> <\/span>BY<\/span> <\/span>PRIOR<\/span> <\/span>role<\/span> = parent_role<\/span><\/span>\n   ),<\/span><\/span>\n   <\/span>-- roles graph, child added to all ancestors including self<\/span><\/span>\n   <\/span>-- allows simple join to parent_role to find all descendants<\/span><\/span>\n   role_graph <\/span>AS<\/span> (<\/span><\/span>\n      <\/span>SELECT DISTINCT<\/span><\/span>\n             <\/span>role<\/span>,<\/span><\/span>\n             regexp_substr(role_path, <\/span>'(\/)(\\w+)'<\/span>, <\/span>1<\/span>, <\/span>1<\/span>, <\/span>'i'<\/span>, <\/span>2<\/span>) <\/span>AS<\/span> parent_role<\/span><\/span>\n        <\/span>FROM<\/span> role_tree<\/span><\/span>\n   ),<\/span><\/span>\n   <\/span>-- application users in scope of the analysis<\/span><\/span>\n   <\/span>-- other users are treated as if they were not istalled<\/span><\/span>\n   app_user <\/span>AS<\/span> (<\/span><\/span>\n      <\/span>SELECT<\/span> username<\/span><\/span>\n        <\/span>FROM<\/span> dba_users<\/span><\/span>\n       <\/span>WHERE<\/span> oracle_maintained = <\/span>'N'<\/span> <\/span>-- SYS, SYSTEM, SYSAUX, ...<\/span><\/span>\n         <\/span>AND<\/span> username <\/span>NOT<\/span> <\/span>IN<\/span> (<\/span>'FTLDB'<\/span>, <\/span>'PLSCOPE'<\/span>)<\/span><\/span>\n   ),<\/span><\/span>\n   <\/span>-- user system privileges<\/span><\/span>\n   sys_priv <\/span>AS<\/span> (<\/span><\/span>\n      <\/span>-- system privileges granted directly to users<\/span><\/span>\n      <\/span>SELECT<\/span> u.username, p.privilege<\/span><\/span>\n        <\/span>FROM<\/span> dba_sys_privs p<\/span><\/span>\n        <\/span>JOIN<\/span> app_user u <\/span>ON<\/span> u.username = p.grantee<\/span><\/span>\n      <\/span>UNION<\/span><\/span>\n      <\/span>-- system privileges granted directly to PUBLIC<\/span><\/span>\n      <\/span>SELECT<\/span> u.username, p.privilege<\/span><\/span>\n        <\/span>FROM<\/span> dba_sys_privs p<\/span><\/span>\n       <\/span>CROSS JOIN<\/span> app_user u<\/span><\/span>\n       <\/span>WHERE<\/span> p.grantee = <\/span>'PUBLIC'<\/span><\/span>\n         <\/span>AND<\/span> p.privilege <\/span>NOT<\/span> <\/span>IN<\/span> (<\/span><\/span>\n                <\/span>SELECT<\/span> r.role<\/span><\/span>\n                  <\/span>FROM<\/span> dba_roles r<\/span><\/span>\n             )<\/span><\/span>\n      <\/span>UNION<\/span><\/span>\n      <\/span>-- system privileges granted to users via roles<\/span><\/span>\n      <\/span>SELECT<\/span> u.username, p.privilege<\/span><\/span>\n        <\/span>FROM<\/span> dba_role_privs r<\/span><\/span>\n        <\/span>JOIN<\/span> app_user u <\/span>ON<\/span> u.username = r.grantee<\/span><\/span>\n        <\/span>JOIN<\/span> role_graph g <\/span>ON<\/span> g.parent_role = r.granted_role<\/span><\/span>\n        <\/span>JOIN<\/span> dba_sys_privs p <\/span>ON<\/span> p.grantee = g.role<\/span><\/span>\n      <\/span>UNION<\/span><\/span>\n      <\/span>-- system privileges granted to PUBLIC via roles<\/span><\/span>\n      <\/span>SELECT<\/span> u.username, p.privilege<\/span><\/span>\n        <\/span>FROM<\/span> dba_role_privs r<\/span><\/span>\n        <\/span>JOIN<\/span> role_graph g <\/span>ON<\/span> g.parent_role = r.granted_role<\/span><\/span>\n        <\/span>JOIN<\/span> dba_sys_privs p <\/span>ON<\/span> p.grantee = g.role<\/span><\/span>\n        <\/span>CROSS JOIN<\/span> app_user u<\/span><\/span>\n       <\/span>WHERE<\/span> r.grantee = <\/span>'PUBLIC'<\/span><\/span>\n   ),<\/span><\/span>\n   <\/span>-- user object privileges<\/span><\/span>\n   obj_priv <\/span>AS<\/span> (<\/span><\/span>\n      <\/span>-- objects granted directly to users<\/span><\/span>\n      <\/span>SELECT<\/span> u.username, p.owner, p.type <\/span>AS<\/span> object_type, p.table_name <\/span>AS<\/span> object_name<\/span><\/span>\n        <\/span>FROM<\/span> dba_tab_privs p<\/span><\/span>\n        <\/span>JOIN<\/span> app_user u <\/span>ON<\/span> u.username = p.grantee<\/span><\/span>\n       <\/span>WHERE<\/span> p.owner <\/span>IN<\/span> (<\/span><\/span>\n                <\/span>SELECT<\/span> u2.username<\/span><\/span>\n                  <\/span>FROM<\/span> app_user u2<\/span><\/span>\n             )<\/span><\/span>\n      <\/span>UNION<\/span><\/span>\n      <\/span>-- objects granted to users via roles<\/span><\/span>\n      <\/span>SELECT<\/span> u.username, p.owner, p.type <\/span>AS<\/span> object_type, p.table_name <\/span>AS<\/span> object_name<\/span><\/span>\n        <\/span>FROM<\/span> dba_role_privs r<\/span><\/span>\n        <\/span>JOIN<\/span> app_user u <\/span>ON<\/span> u.username = r.grantee<\/span><\/span>\n        <\/span>JOIN<\/span> role_graph g <\/span>ON<\/span> g.parent_role = r.granted_role<\/span><\/span>\n        <\/span>JOIN<\/span> dba_tab_privs p <\/span>ON<\/span> p.grantee = g.role<\/span><\/span>\n       <\/span>WHERE<\/span> p.owner <\/span>IN<\/span> (<\/span><\/span>\n                <\/span>SELECT<\/span> u2.username<\/span><\/span>\n                  <\/span>FROM<\/span> app_user u2<\/span><\/span>\n             )<\/span><\/span>\n      <\/span>-- objects granted to PUBLIC<\/span><\/span>\n      <\/span>UNION<\/span><\/span>\n      <\/span>SELECT<\/span> u.username, p.owner, p.type <\/span>AS<\/span> object_type, p.table_name <\/span>AS<\/span> object_name<\/span><\/span>\n        <\/span>FROM<\/span> dba_tab_privs p<\/span><\/span>\n       <\/span>CROSS JOIN<\/span> app_user u<\/span><\/span>\n       <\/span>WHERE<\/span> p.owner <\/span>IN<\/span> (<\/span><\/span>\n                <\/span>SELECT<\/span> u2.username<\/span><\/span>\n                  <\/span>FROM<\/span> app_user u2<\/span><\/span>\n             )<\/span><\/span>\n         <\/span>AND<\/span> p.grantee = <\/span>'PUBLIC'<\/span><\/span>\n   ),<\/span><\/span>\n   <\/span>-- issues if user is configured in the connection pool of a middle tier<\/span><\/span>\n   issues <\/span>AS<\/span> (<\/span><\/span>\n      <\/span>-- privileges not part of CONNECT role<\/span><\/span>\n      <\/span>SELECT<\/span> username,<\/span><\/span>\n             <\/span>'SYS'<\/span> <\/span>AS<\/span> <\/span>owner<\/span>,<\/span><\/span>\n             <\/span>'PRIVILEGE'<\/span> <\/span>AS<\/span> object_type,<\/span><\/span>\n             privilege <\/span>AS<\/span> object_name,<\/span><\/span>\n             <\/span>'Privilege is not part of the CONNECT role'<\/span> <\/span>AS<\/span> issue<\/span><\/span>\n        <\/span>FROM<\/span> sys_priv<\/span><\/span>\n       <\/span>WHERE<\/span> privilege <\/span>NOT<\/span> <\/span>IN<\/span> (<\/span>'CREATE SESSION'<\/span>, <\/span>'SET CONTAINER'<\/span>)<\/span><\/span>\n      <\/span>UNION ALL<\/span><\/span>\n      <\/span>-- access to non PL\/SQL units<\/span><\/span>\n      <\/span>SELECT<\/span> username,<\/span><\/span>\n             <\/span>owner<\/span>,<\/span><\/span>\n             object_type,<\/span><\/span>\n             object_name,<\/span><\/span>\n             <\/span>'Access to non-PL\/SQL unit'<\/span><\/span>\n        <\/span>FROM<\/span> obj_priv<\/span><\/span>\n       <\/span>WHERE<\/span> object_type <\/span>NOT<\/span> <\/span>IN<\/span> (<\/span>'PACKAGE'<\/span>, <\/span>'TYPE'<\/span>, <\/span>'FUNCTION'<\/span>, <\/span>'PROCEDURE'<\/span>)<\/span><\/span>\n      <\/span>-- own objects<\/span><\/span>\n      <\/span>UNION ALL<\/span><\/span>\n      <\/span>SELECT<\/span> u.username,<\/span><\/span>\n             o.owner,<\/span><\/span>\n             o.object_type,<\/span><\/span>\n             o.object_name,<\/span><\/span>\n             <\/span>'Connect user must not own any object'<\/span><\/span>\n        <\/span>FROM<\/span> app_user u<\/span><\/span>\n        <\/span>JOIN<\/span> dba_objects o <\/span>ON<\/span> o.owner = u.username<\/span><\/span>\n      <\/span>-- missing CREATE SESSION privilege<\/span><\/span>\n      <\/span>UNION ALL<\/span><\/span>\n      <\/span>SELECT<\/span> u.username,<\/span><\/span>\n             <\/span>'SYS'<\/span>,<\/span><\/span>\n             <\/span>'PRIVILEGE'<\/span>,<\/span><\/span>\n             <\/span>'CREATE SESSION'<\/span>,<\/span><\/span>\n             <\/span>'Privilege is missing, but required'<\/span><\/span>\n        <\/span>FROM<\/span> app_user u<\/span><\/span>\n       <\/span>WHERE<\/span> u.username <\/span>NOT<\/span> <\/span>IN<\/span> (<\/span><\/span>\n                <\/span>SELECT<\/span> username<\/span><\/span>\n                  <\/span>FROM<\/span> sys_priv<\/span><\/span>\n                 <\/span>WHERE<\/span> privilege = <\/span>'CREATE SESSION'<\/span> <\/span><\/span>\n             )<\/span><\/span>\n   ),<\/span><\/span>\n   <\/span>-- aggregate issues per user<\/span><\/span>\n   issue_aggr <\/span>AS<\/span> (<\/span><\/span>\n      <\/span>SELECT<\/span> u.username, <\/span>COUNT<\/span>(i.username) issue_count<\/span><\/span>\n        <\/span>FROM<\/span> app_user u<\/span><\/span>\n        <\/span>LEFT JOIN<\/span> issues i <\/span>ON<\/span> i.username = u.username<\/span><\/span>\n       <\/span>GROUP BY<\/span> u.username<\/span><\/span>\n   ),<\/span><\/span>\n   <\/span>-- user summary (calculate is_connect_user_ready)<\/span><\/span>\n   summary <\/span>AS<\/span> (<\/span><\/span>\n      <\/span>SELECT<\/span> username,<\/span><\/span>\n             <\/span>CASE<\/span><\/span>\n                <\/span>WHEN<\/span> issue_count = <\/span>0<\/span> <\/span>THEN<\/span><\/span>\n                   <\/span>'YES'<\/span><\/span>\n                <\/span>ELSE<\/span><\/span>\n                   <\/span>'NO'<\/span><\/span>\n             <\/span>END<\/span> <\/span>AS<\/span> is_connect_user_ready,<\/span><\/span>\n             issue_count<\/span><\/span>\n        <\/span>FROM<\/span> issue_aggr<\/span><\/span>\n       <\/span>ORDER BY<\/span> is_connect_user_ready <\/span>DESC<\/span>, username<\/span><\/span>\n   )<\/span><\/span>\n-- main<\/span><\/span>\nSELECT<\/span> * <\/span><\/span>\n  <\/span>FROM<\/span> summary<\/span><\/span>\n <\/span>WHERE<\/span> username <\/span>LIKE<\/span> <\/span>'THE%'<\/span>;<\/span><\/span>\n <\/span><\/span>\nUSERNAME      IS_CONNECT_USER_READY ISSUE_COUNT<\/span><\/span>\n------------- --------------------- -----------<\/span><\/span>\nTHE_BAD_USER  YES                             <\/span>0<\/span><\/span>\nTHE_GOOD_USER YES                             <\/span>0<\/span><\/span>\nTHE_BAD_API   <\/span>NO<\/span>                              <\/span>9<\/span><\/span>\nTHE_BAD_DATA  <\/span>NO<\/span>                              <\/span>3<\/span><\/span>\nTHE_GOOD_API  <\/span>NO<\/span>                              <\/span>4<\/span><\/span>\nTHE_GOOD_DATA <\/span>NO<\/span>                              <\/span>3<\/span><\/span>\nTHE_UGLY_DATA <\/span>NO<\/span>                             <\/span>10<\/span><\/span>\nTHE_UGLY_USER <\/span>NO<\/span>                              <\/span>1<\/span><\/span>\n <\/span><\/span>\n8<\/span> <\/span>rows<\/span> selected.<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Just THE_GOOD_USER and THE_BAD_USER are ready to be used in the middle tier. To see the issues of all other users you may simply change the main part of the query as follows:<\/p>\n\n\n\n
All rule 1 issues<\/span><\/path><\/path><\/svg><\/span>
-- main<\/span><\/span>\nSELECT<\/span> * <\/span><\/span>\n  <\/span>FROM<\/span> issues<\/span><\/span>\n <\/span>WHERE<\/span> username <\/span>LIKE<\/span> <\/span>'THE%'<\/span><\/span>\n <\/span>ORDER BY<\/span> username, <\/span>owner<\/span>, object_type, object_name;<\/span><\/span>\n<\/span>\nUSERNAME      <\/span>OWNER<\/span>         OBJECT_TYPE  OBJECT_NAME          ISSUE                                    <\/span><\/span>\n------------- ------------- ------------ -------------------- -----------------------------------------<\/span><\/span>\nTHE_BAD_API   SYS           PRIVILEGE    <\/span>CREATE<\/span> <\/span>DATABASE<\/span> LINK Privilege <\/span>is<\/span> <\/span>not<\/span> part of the <\/span>CONNECT<\/span> <\/span>role<\/span><\/span>\nTHE_BAD_API   SYS           PRIVILEGE    <\/span>CREATE<\/span> <\/span>PROCEDURE<\/span>     Privilege <\/span>is<\/span> <\/span>not<\/span> part of the <\/span>CONNECT<\/span> <\/span>role<\/span><\/span>\nTHE_BAD_API   THE_BAD_API   JAVA CLASS   C                    <\/span>Connect<\/span> user must <\/span>not<\/span> own any <\/span>object<\/span>     <\/span><\/span>\nTHE_BAD_API   THE_BAD_API   JAVA SOURCE  C                    <\/span>Connect<\/span> user must <\/span>not<\/span> own any <\/span>object<\/span>     <\/span><\/span>\nTHE_BAD_API   THE_BAD_API   PACKAGE      PKG                  <\/span>Connect<\/span> user must <\/span>not<\/span> own any <\/span>object<\/span>     <\/span><\/span>\nTHE_BAD_API   THE_BAD_API   PACKAGE      PKG2                 <\/span>Connect<\/span> user must <\/span>not<\/span> own any <\/span>object<\/span>     <\/span><\/span>\nTHE_BAD_API   THE_BAD_API   PACKAGE BODY PKG                  <\/span>Connect<\/span> user must <\/span>not<\/span> own any <\/span>object<\/span>     <\/span><\/span>\nTHE_BAD_API   THE_BAD_API   PACKAGE BODY PKG2                 <\/span>Connect<\/span> user must <\/span>not<\/span> own any <\/span>object<\/span>     <\/span><\/span>\nTHE_BAD_API   THE_BAD_DATA  <\/span>TABLE<\/span>        T                    Access <\/span>to<\/span> non-PL\/<\/span>SQL<\/span> unit                <\/span><\/span>\nTHE_BAD_DATA  SYS           PRIVILEGE    <\/span>CREATE<\/span> <\/span>TABLE<\/span>         Privilege <\/span>is<\/span> <\/span>not<\/span> part of the <\/span>CONNECT<\/span> <\/span>role<\/span><\/span>\nTHE_BAD_DATA  THE_BAD_DATA  <\/span>INDEX<\/span>        SYS_C0012875         <\/span>Connect<\/span> user must <\/span>not<\/span> own any <\/span>object<\/span>     <\/span><\/span>\nTHE_BAD_DATA  THE_BAD_DATA  <\/span>TABLE<\/span>        T                    <\/span>Connect<\/span> user must <\/span>not<\/span> own any <\/span>object<\/span>     <\/span><\/span>\nTHE_GOOD_API  SYS           PRIVILEGE    <\/span>CREATE<\/span> <\/span>PROCEDURE<\/span>     Privilege <\/span>is<\/span> <\/span>not<\/span> part of the <\/span>CONNECT<\/span> <\/span>role<\/span><\/span>\nTHE_GOOD_API  THE_GOOD_API  PACKAGE      PKG                  <\/span>Connect<\/span> user must <\/span>not<\/span> own any <\/span>object<\/span>     <\/span><\/span>\nTHE_GOOD_API  THE_GOOD_API  PACKAGE BODY PKG                  <\/span>Connect<\/span> user must <\/span>not<\/span> own any <\/span>object<\/span>     <\/span><\/span>\nTHE_GOOD_API  THE_GOOD_DATA <\/span>TABLE<\/span>        T                    Access <\/span>to<\/span> non-PL\/<\/span>SQL<\/span> unit                <\/span><\/span>\nTHE_GOOD_DATA SYS           PRIVILEGE    <\/span>CREATE<\/span> <\/span>TABLE<\/span>         Privilege <\/span>is<\/span> <\/span>not<\/span> part of the <\/span>CONNECT<\/span> <\/span>role<\/span><\/span>\nTHE_GOOD_DATA THE_GOOD_DATA <\/span>INDEX<\/span>        SYS_C0012873         <\/span>Connect<\/span> user must <\/span>not<\/span> own any <\/span>object<\/span>     <\/span><\/span>\nTHE_GOOD_DATA THE_GOOD_DATA <\/span>TABLE<\/span>        T                    <\/span>Connect<\/span> user must <\/span>not<\/span> own any <\/span>object<\/span>     <\/span><\/span>\nTHE_UGLY_DATA SYS           PRIVILEGE    <\/span>CREATE<\/span> CLUSTER       Privilege <\/span>is<\/span> <\/span>not<\/span> part of the <\/span>CONNECT<\/span> <\/span>role<\/span><\/span>\nTHE_UGLY_DATA SYS           PRIVILEGE    <\/span>CREATE<\/span> INDEXTYPE     Privilege <\/span>is<\/span> <\/span>not<\/span> part of the <\/span>CONNECT<\/span> <\/span>role<\/span><\/span>\nTHE_UGLY_DATA SYS           PRIVILEGE    <\/span>CREATE<\/span> OPERATOR      Privilege <\/span>is<\/span> <\/span>not<\/span> part of the <\/span>CONNECT<\/span> <\/span>role<\/span><\/span>\nTHE_UGLY_DATA SYS           PRIVILEGE    <\/span>CREATE<\/span> <\/span>PROCEDURE<\/span>     Privilege <\/span>is<\/span> <\/span>not<\/span> part of the <\/span>CONNECT<\/span> <\/span>role<\/span><\/span>\nTHE_UGLY_DATA SYS           PRIVILEGE    <\/span>CREATE<\/span> <\/span>SEQUENCE<\/span>      Privilege <\/span>is<\/span> <\/span>not<\/span> part of the <\/span>CONNECT<\/span> <\/span>role<\/span><\/span>\nTHE_UGLY_DATA SYS           PRIVILEGE    <\/span>CREATE<\/span> <\/span>TABLE<\/span>         Privilege <\/span>is<\/span> <\/span>not<\/span> part of the <\/span>CONNECT<\/span> <\/span>role<\/span><\/span>\nTHE_UGLY_DATA SYS           PRIVILEGE    <\/span>CREATE<\/span> TRIGGER       Privilege <\/span>is<\/span> <\/span>not<\/span> part of the <\/span>CONNECT<\/span> <\/span>role<\/span><\/span>\nTHE_UGLY_DATA SYS           PRIVILEGE    <\/span>CREATE<\/span> <\/span>TYPE<\/span>          Privilege <\/span>is<\/span> <\/span>not<\/span> part of the <\/span>CONNECT<\/span> <\/span>role<\/span><\/span>\nTHE_UGLY_DATA THE_UGLY_DATA <\/span>INDEX<\/span>        SYS_C0012877         <\/span>Connect<\/span> user must <\/span>not<\/span> own any <\/span>object<\/span>     <\/span><\/span>\nTHE_UGLY_DATA THE_UGLY_DATA <\/span>TABLE<\/span>        T                    <\/span>Connect<\/span> user must <\/span>not<\/span> own any <\/span>object<\/span>     <\/span><\/span>\nTHE_UGLY_USER THE_UGLY_DATA <\/span>TABLE<\/span>        T                    Access <\/span>to<\/span> non-PL\/<\/span>SQL<\/span> unit                <\/span><\/span>\n<\/span>\n30<\/span> <\/span>rows<\/span> selected.<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
THE_UGLY_USER has access to table T owned by THE_UGLY_DATA. This violates rule 1.<\/p>\n\n\n\n
However, it is important to note, that we have excluded all Oracle maintained users from the analysis. So let’s have a look at all the tables and views granted to PUBLIC by the Oracle-maintained users.<\/p>\n\n\n\n
Privileges on tables and views granted to PUBLIC<\/span><\/path><\/path><\/svg><\/span>
WITH<\/span><\/span>\n   public_privs <\/span>AS<\/span> (<\/span><\/span>\n      <\/span>SELECT<\/span> p.owner, <\/span><\/span>\n             p.type       <\/span>AS<\/span> object_type, <\/span><\/span>\n             p.privilege, <\/span><\/span>\n             <\/span>count<\/span>(*)     <\/span>AS<\/span> priv_count<\/span><\/span>\n        <\/span>FROM<\/span> dba_tab_privs p<\/span><\/span>\n       <\/span>WHERE<\/span> p.grantee = <\/span>'PUBLIC'<\/span><\/span>\n         <\/span>AND<\/span> p.type <\/span>IN<\/span> (<\/span>'VIEW'<\/span>, <\/span>'TABLE'<\/span>)<\/span><\/span>\n         <\/span>AND<\/span> p.owner <\/span>IN<\/span> (<\/span><\/span>\n                <\/span>SELECT<\/span> u2.username <\/span><\/span>\n                  <\/span>FROM<\/span> dba_users u2 <\/span><\/span>\n                 <\/span>WHERE<\/span> u2.oracle_maintained = <\/span>'Y'<\/span><\/span>\n             )<\/span><\/span>\n       <\/span>GROUP BY<\/span> p.owner, p.type, p.privilege  <\/span><\/span>\n   ),<\/span><\/span>\n   public_privs_pivot <\/span>AS<\/span> (<\/span><\/span>\n      <\/span>SELECT<\/span> <\/span>owner<\/span>,<\/span><\/span>\n             object_type,<\/span><\/span>\n             insert_priv,<\/span><\/span>\n             update_priv,<\/span><\/span>\n             delete_priv,<\/span><\/span>\n             select_priv, <\/span>-- allows SELECT ... FOR UPDATE ...<\/span><\/span>\n             read_priv,   <\/span>-- does not allow SELECT ... FOR UPDATE ...<\/span><\/span>\n             flashback_priv,<\/span><\/span>\n             nvl(insert_priv,<\/span>0<\/span>) + nvl(update_priv,<\/span>0<\/span>) + nvl(delete_priv,<\/span>0<\/span>) <\/span><\/span>\n             + nvl(select_priv,<\/span>0<\/span>) + nvl(read_priv,<\/span>0<\/span>) <\/span><\/span>\n             + nvl(flashback_priv,<\/span>0<\/span>) <\/span>AS<\/span> total_priv<\/span><\/span>\n        <\/span>FROM<\/span> public_privs<\/span><\/span>\n       PIVOT (<\/span><\/span>\n                <\/span>sum<\/span>(priv_count) <\/span>FOR<\/span> privilege <\/span>IN<\/span> (<\/span><\/span>\n                   <\/span>'INSERT'<\/span>    <\/span>AS<\/span> insert_priv, <\/span><\/span>\n                   <\/span>'UPDATE'<\/span>    <\/span>AS<\/span> update_priv, <\/span><\/span>\n                   <\/span>'DELETE'<\/span>    <\/span>AS<\/span> delete_priv, <\/span><\/span>\n                   <\/span>'SELECT'<\/span>    <\/span>AS<\/span> select_priv, <\/span><\/span>\n                   <\/span>'READ'<\/span>      <\/span>AS<\/span> read_priv, <\/span><\/span>\n                   <\/span>'FLASHBACK'<\/span> <\/span>AS<\/span> flashback_priv<\/span><\/span>\n                )<\/span><\/span>\n             )<\/span><\/span>\n       <\/span>ORDER BY<\/span> <\/span>owner<\/span><\/span>\n   ),<\/span><\/span>\n   public_privs_report <\/span>AS<\/span> (<\/span><\/span>\n      <\/span>SELECT<\/span> <\/span>owner<\/span>,<\/span><\/span>\n             object_type,<\/span><\/span>\n             <\/span>sum<\/span>(insert_priv)    <\/span>AS<\/span> <\/span>"INSERT"<\/span>,<\/span><\/span>\n             <\/span>sum<\/span>(update_priv)    <\/span>AS<\/span> <\/span>"UPDATE"<\/span>,<\/span><\/span>\n             <\/span>sum<\/span>(delete_priv)    <\/span>AS<\/span> <\/span>"DELETE"<\/span>,<\/span><\/span>\n             <\/span>sum<\/span>(select_priv)    <\/span>AS<\/span> <\/span>"SELECT"<\/span>,<\/span><\/span>\n             <\/span>sum<\/span>(read_priv)      <\/span>AS<\/span> <\/span>"READ"<\/span>,<\/span><\/span>\n             <\/span>sum<\/span>(flashback_priv) <\/span>AS<\/span> <\/span>"FLASHBACK"<\/span>,<\/span><\/span>\n             <\/span>sum<\/span>(total_priv)     <\/span>AS<\/span> <\/span>"TOTAL"<\/span><\/span>\n        <\/span>FROM<\/span> public_privs_pivot<\/span><\/span>\n       <\/span>GROUP BY<\/span> <\/span>ROLLUP<\/span>(<\/span>owner<\/span>, object_type)<\/span><\/span>\n      <\/span>HAVING<\/span> (<\/span>GROUPING<\/span>(<\/span>owner<\/span>), <\/span>GROUPING<\/span>(object_type)) <\/span>IN<\/span> ((<\/span>0<\/span>,<\/span>0<\/span>), (<\/span>1<\/span>,<\/span>1<\/span>))<\/span><\/span>\n       <\/span>ORDER BY<\/span> <\/span>owner<\/span>, object_type<\/span><\/span>\n   )<\/span><\/span>\n-- main<\/span><\/span>\nSELECT<\/span> * <\/span>FROM<\/span> public_privs_report;<\/span><\/span>\n<\/span>\nOWNER<\/span>             OBJECT_TYPE     <\/span>INSERT<\/span>     <\/span>UPDATE<\/span>     <\/span>DELETE<\/span>     <\/span>SELECT<\/span>       <\/span>READ<\/span>  FLASHBACK      TOTAL<\/span><\/span>\n----------------- ----------- ---------- ---------- ---------- ---------- ---------- ---------- ----------<\/span><\/span>\nAPEX_050100       VIEW                 <\/span>1<\/span>          <\/span>1<\/span>          <\/span>4<\/span>          <\/span>5<\/span>        <\/span>192<\/span>                   <\/span>203<\/span><\/span>\nCTXSYS            <\/span>TABLE<\/span>                                                 <\/span>1<\/span>          <\/span>4<\/span>                     <\/span>5<\/span><\/span>\nCTXSYS            VIEW                <\/span>11<\/span>          <\/span>5<\/span>          <\/span>8<\/span>         <\/span>12<\/span>         <\/span>51<\/span>                    <\/span>87<\/span><\/span>\nGSMADMIN_INTERNAL VIEW                                                             <\/span>3<\/span>                     <\/span>3<\/span><\/span>\nLBACSYS           VIEW                                                            <\/span>18<\/span>                    <\/span>18<\/span><\/span>\nMDSYS             <\/span>TABLE<\/span>               <\/span>21<\/span>         <\/span>14<\/span>         <\/span>19<\/span>         <\/span>21<\/span>         <\/span>36<\/span>                   <\/span>111<\/span><\/span>\nMDSYS             VIEW                <\/span>27<\/span>         <\/span>26<\/span>         <\/span>26<\/span>         <\/span>26<\/span>         <\/span>68<\/span>                   <\/span>173<\/span><\/span>\nOLAPSYS           VIEW                                                            <\/span>18<\/span>                    <\/span>18<\/span><\/span>\nORDDATA           VIEW                                       <\/span>1<\/span>                     <\/span>5<\/span>                     <\/span>6<\/span><\/span>\nORDSYS            VIEW                                                             <\/span>5<\/span>                     <\/span>5<\/span><\/span>\nORDS_METADATA     VIEW                                                 <\/span>20<\/span>                               <\/span>20<\/span><\/span>\nSYS               <\/span>TABLE<\/span>               <\/span>21<\/span>         <\/span>10<\/span>         <\/span>16<\/span>         <\/span>30<\/span>         <\/span>12<\/span>                    <\/span>89<\/span><\/span>\nSYS               VIEW                                                  <\/span>1<\/span>       <\/span>1717<\/span>          <\/span>2<\/span>       <\/span>1720<\/span><\/span>\nSYSTEM<\/span>            <\/span>TABLE<\/span>                <\/span>3<\/span>          <\/span>3<\/span>          <\/span>3<\/span>          <\/span>4<\/span>                               <\/span>13<\/span><\/span>\nSYSTEM<\/span>            VIEW                                                  <\/span>1<\/span>                                <\/span>1<\/span><\/span>\nWMSYS             VIEW                                                            <\/span>40<\/span>                    <\/span>40<\/span><\/span>\nXDB               <\/span>TABLE<\/span>                <\/span>8<\/span>          <\/span>6<\/span>          <\/span>8<\/span>          <\/span>8<\/span>         <\/span>14<\/span>                    <\/span>44<\/span><\/span>\nXDB               VIEW                 <\/span>2<\/span>          <\/span>2<\/span>          <\/span>2<\/span>          <\/span>2<\/span>          <\/span>3<\/span>                    <\/span>11<\/span><\/span>\n                                      <\/span>94<\/span>         <\/span>67<\/span>         <\/span>87<\/span>        <\/span>131<\/span>       <\/span>2186<\/span>          <\/span>2<\/span>       <\/span>2567<\/span><\/span>\n<\/span>\n19<\/span> <\/span>rows<\/span> selected.<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Even THE_GOOD_USER has access to 2317 views and tables. To reduce this number we have to uninstall some components, but that’s just a drop in the ocean. There is currently no way to create an Oracle user without access to views and tables. Hence we just have to focus on our application and our data.<\/p>\n\n\n\n
Rule 2) Use only static SQL within PL\/SQL<\/h2>\n\n\n\n
If you use just static SQL in your PL\/SQL units, then no SQL injection is possible. The absence of dynamic SQL proves that your application is secured against SQL injection attacks. Of course, there are good reasons for dynamic SQL. However, proving that a dynamic SQL is not injectable is difficult. Checking for the absence of dynamic SQL is the simpler approach, even if it is not as easy as I initially thought.<\/p>\n\n\n\n
In How to write SQL injection proof PL\/SQL<\/a> the following ways are mentioned to implement dynamic SQL:<\/p>\n\n\n\n