CIS recommendations

The Center for Internet Security publishes the “CIS Oracle database 19c Benchmark” with recommendations to enhance the security of Oracle databases.

One type of recommendations is to remove grant execute to public (chapter 5.1.1.1-5.1.1.7 Public Privileges). There is a list of powerful SYS packages. And for security reasons, only users that really need this functionality should have access to it. But per default, it is granted to public and all users can use it.

In theory, to fix that is easy, e.g.

REVOKE EXECUTE ON DBMS_LDAP FROM PUBLIC;

But is that really a good idea?

Who is using an object from another schema?

If the object is used in a program unit, a named PL/SQL block (package, function, procedure, trigger), you can see the dependency in the view dba_dependencies.

select distinct owner from dba_dependencies 
where referenced_name='DBMS_LDAP' and owner<>'SYS'
order by 2,1;

And for these objects, the users already have a direct grant for it. So, remove of the public grant does not affect these user-objects.
But wait! Rarely used, but there are named blocks with invokers right’s (create procedure procname AUTHID CURRENT_USER is…) . See How Roles Work in PL/SQL Blocks

select owner, object_name from dba_procedures where authid='CURRENT_USER';

In this case the user can also access objects used in program units he has granted via a role. You have to check which users have access to these program units. These users are potentially affected by the change!

For objects used outside of above program units: If a user has a direct grant, or an indirect grant via a role to the object, removing the grant to public does not affect the work of this user with these objects.

So, what about the other users without direct/indirect grants to the object (except “public”)? How can we see if above mentioned objects are used (e.g. from external code in a Perl script or an application server connecting to the database)?

To see the usage of an object, we can use unified auditing and create an audit policy for the object.

create audit policy CIS_CHECK_USAGE
actions
execute on sys.dbms_ldap
when 'SYS_CONTEXT(''USERENV'', ''CURRENT_USER'') != ''SYS''' EVALUATE PER STATEMENT;

audit policy CIS_CHECK_USAGE;
alter audit policy cis_check_usage add actions EXECUTE on SYS.DBMS_LOB;
alter audit policy cis_check_usage add actions ...

Hint: Unified auditing can also be used if the Oracle binary is not relinked for unified audit (the relink only deactivates traditional auditing, unified auditing is always active)

To automate above steps, you can do it dynamically with the Perl script below (run it with $ORACLE_HOME/perl/bin/perl, so the required Oracle modules are present):

  use DBI;
  my $dbh = DBI->connect('dbi:Oracle:', '', '',{ PrintError => 1, ora_session_mode=>2 });
  my @pdblist;
  my $sth=$dbh->prepare(q{select PDB_NAME from cdb_pdbs where pdb_name<>'PDB$SEED' union select 'CDB$ROOT' from dual});
  $sth->execute();
  while (my @row = $sth->fetchrow_array) {
    push(@pdblist, $row[0]);
  }

  foreach my $pdb (@pdblist){
    # switch PDB
    print "PDB=$pdb\n";
    $dbh->do("alter session set container=$pdb");

    # create cis_check_usage
    print q{ create audit policy cis_check_usage actions all on sys.AUD$ when 'SYS_CONTEXT(''USERENV'', ''CURRENT_USER'') != ''SYS''' EVALUATE PER STATEMENT}."\n";
    $dbh->do(q{ create audit policy cis_check_usage actions all on sys.AUD$ when 'SYS_CONTEXT(''USERENV'', ''CURRENT_USER'') != ''SYS''' EVALUATE PER STATEMENT});
    $dbh->do(q{ audit policy cis_check_usage});

    # add execute to public
    my $sql=q{
     SELECT  PRIVILEGE||' on '||owner||'.'||table_name FROM DBA_TAB_PRIVS WHERE GRANTEE='PUBLIC' AND PRIVILEGE='EXECUTE' AND TABLE_NAME IN (
     'DBMS_LDAP','UTL_INADDR','UTL_TCP','UTL_MAIL','UTL_SMTP','UTL_DBWS','UTL_ORAMTS','UTL_HTTP','HTTPURITYPE',
     'DBMS_ADVISOR','DBMS_LOB','UTL_FILE',
     'DBMS_CRYPTO','DBMS_OBFUSCATION_TOOLKIT', 'DBMS_RANDOM',
     'DBMS_JAVA','DBMS_JAVA_TEST',
     'DBMS_SCHEDULER','DBMS_JOB',
     'DBMS_SQL', 'DBMS_XMLGEN', 'DBMS_XMLQUERY','DBMS_XMLSTORE','DBMS_XMLSAVE','DBMS_AW','OWA_UTIL','DBMS_REDACT',
     'DBMS_CREDENTIAL'
      )};
    $sth=$dbh->prepare("$sql");
    $sth->execute();
    while (my @result = $sth->fetchrow_array) {
      print  "alter audit policy cis_check_usage add actions $result[0]\n";
      $dbh->do("alter audit policy cis_check_usage add actions $result[0]");
    }
  }

Revoke the grants

After some days/weeks, you can evaluate the usage of dbms_ldap or other objects audited by the cis_check_usage policy

select dbusername, current_user, object_schema||'.'||object_name as object, 
      sql_text, system_privilege_used,
       system_privilege, unified_audit_policies, con_id , event_timestamp 
from cdb_unified_audit_trail 
where unified_audit_policies like '%CIS_CHECK_USAGE%';

With this query, we see the usage of the objects we audited with the CIS_CHECK_USAGE policy. If there are no rows, check if you really enabled the policy (select * from audit_unified_enabled_policies where policy_name='CIS_CHECK_USAGE';)

With the next query, we exclude the objects per user that can be accessed by a direct grant or a grant via a role, so, a revoke from public will not affect this user.

select distinct current_user, action_name, object_schema, object_name, con_id 
from cdb_unified_audit_trail a
where unified_audit_policies like '%CIS_CHECK_USAGE%'
and current_user not in ( 
  select grantee from cdb_tab_privs -- direct grant
  where owner=a.object_schema and table_name=a.object_name and con_id=a.con_id
union all
  select r.grantee from cdb_role_privs r, cdb_tab_privs t -- grant via role
  where r.granted_role=t.grantee and r.con_id=t.con_id 
  and r.grantee=a.current_user   and t.owner=a.object_schema 
  and t.table_name=a.object_name and r.con_id=a.con_id
);

And what is left, needs attention.

Sometimes the objects are used by a background process, e.g. if you see the object_name DBMS_SQL, but in sql_text it is not used, then the user probably does not need it. But if it is present in sql_text, then the user definitely needs a grant. I recommend to grant the object via a role, so it behaves as before, the user can use it directly, but not in procedures/functions/packages.

create  role cis_dbms_sql ;
grant execute on sys.dbms_sql to cis_dbms_sql;
grant cis_dbms_sql to user1;

Then pragmatically, remove the execute rights from public on a test system and check if the application still works as expected. Generate the revoke commands dynamically, and do not forget to also dynamically generate an undo script in case of problems:

SELECT  'revoke '||PRIVILEGE||' on '||owner||'.'||table_name||' from PUBLIC;' 
FROM DBA_TAB_PRIVS 
WHERE GRANTEE='PUBLIC' AND PRIVILEGE='EXECUTE' AND TABLE_NAME IN (
   'DBMS_LDAP',' UTL_INADDR' ,'UTL_TCP', 'UTL_MAIL', 'UTL_SMTP', 'UTL_DBWS',
 'UTL_ORAMTS','UTL_HTTP','HTTPURITYPE',
'DBMS_ADVISOR','DBMS_LOB','UTL_FILE',
'DBMS_CRYPTO','DBMS_OBFUSCATION_TOOLKIT', 'DBMS_RANDOM',
'DBMS_JAVA','DBMS_JAVA_TEST',
'DBMS_SCHEDULER','DBMS_JOB',
'DBMS_SQL', 'DBMS_XMLGEN', 'DBMS_XMLQUERY','DBMS_XMLSTORE','DBMS_XMLSAVE','DBMS_AW','OWA_UTIL','DBMS_REDACT',
'DBMS_CREDENTIAL'
);

SELECT  'grant '||PRIVILEGE||' on '||owner||'.'||table_name||' to PUBLIC;'
FROM DBA_TAB_PRIVS 
WHERE GRANTEE='PUBLIC' AND PRIVILEGE='EXECUTE' AND TABLE_NAME IN (
   'DBMS_LDAP',' UTL_INADDR' ,'UTL_TCP', 'UTL_MAIL', 'UTL_SMTP', 'UTL_DBWS',
 'UTL_ORAMTS','UTL_HTTP','HTTPURITYPE',
'DBMS_ADVISOR','DBMS_LOB','UTL_FILE',
'DBMS_CRYPTO','DBMS_OBFUSCATION_TOOLKIT', 'DBMS_RANDOM',
'DBMS_JAVA','DBMS_JAVA_TEST',
'DBMS_SCHEDULER','DBMS_JOB',
'DBMS_SQL', 'DBMS_XMLGEN', 'DBMS_XMLQUERY','DBMS_XMLSTORE','DBMS_XMLSAVE','DBMS_AW','OWA_UTIL','DBMS_REDACT',
'DBMS_CREDENTIAL'
);

It has to be run in each PDB and CDB$ROOT.

If all works as expected, then it is fine.

Installation of patches and new components

But keep that in mind if you want to install something later. It may fail. For example, install an rman catalog:

RMAN> create catalog;
create catalog;
error creating dbms_rcvcat package body
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06433: error installing recovery catalog
RMAN Client Diagnostic Trace file : /u01/app/oracle/diag/clients/user_oracle/RMAN_1732619876_110/trace/ora_rman_635844_0.trc

To create a valid rman catalog, you need to grant the execute right for UTL_HTTP, DBMS_LOB, DBMS_XMLGEN and DBMS_SQL directly to the rman user. Strange for me: it does not work if you grant it to a role (e.g. recovery_catalog_owner), but it works with a grant to public.

My recommendation to install new softare or patches is:

  • Run the undo-script mentioned above (grant execute to public)
  • Apply the Oracle or application patch or new application installation
  • Check for invalid objects
  • Run the hardening-script (revoke execute from public)
  • Check for additional invalid objects and determine the missing grants
  • Extend your hardening script with the required grants and re-run it.

Conclusion

Generally, the CIS hardening about revoking execute from public is possible. But it is very dangerous that the functionality of the application could be compromised. Especially with components that are used very rarely, this could only be noticed very late at best, e.g. in the case of end-of-year processing.

Post Views: 13
Thumbnail [60x60]
by
Martin Bracher