Monday, February 21, 2011

Finding PUBLIC Privileges

One of the most common security issues that a DBA will encounter in a JDE database is PUBLIC access to database objects. By default, JDE will grant full access to the Oracle internal PUBLIC role whenever a table is created. What this means is that any Oracle account that is created in the database will inherent full read/write access to all JDE data. This security hole becomes very apparent as soon as there is a requirement for an additional database account (for example an application support analyst or a reporting tool). A DBA would typically setup a role with read-only access to the tables which the user needs to query, however it is easy to overlook the default PUBLIC access.

Here is a query that can be used to list the JDE objects which have PUBLIC access granted on them (this assumes E1 version 9.0, for version 8.11 substitute 900 with 811, etc.):


set pagesize 9999
set linesize 120
set colsep ' '

column owner format a30 heading 'Owner'
column table_name format a30 heading 'Object'
column privilege format a40 heading 'Privilege'

select owner, table_name, privilege
from dba_tab_privs a
where grantee = 'PUBLIC'
and (owner like '%900' or
owner like '%SAVE' or
owner like '%CTL' or
owner like '%DTA')
and not exists (select null from dba_recyclebin b
where b.owner = a.owner
and b.object_name = a.table_name)
order by owner, table_name privilege;

Identifying the objects with PUBLIC access is the first step in addressing the issue, but how can this security gap be closed to protect your data and satisfy security audit requirements? Simply revoking the PUBLIC grants only provides a temporary fix and will cause permissions errors in the JDE application. A proper solution is to implement a robust, maintainable security framework along with automated tools to maintain the proper levels of security in your database. Contact DBConnect Solutions, Inc. for more information on how we can help secure your JDE database.

No comments:

Post a Comment