DBConnect Solutions, Inc.

To Truncate or Delete, Is it Really a Question?

2011-02-25T16:25:00.003-05:00

One of the primary objectives of an oracle DBA is to protect the data that is stored in the database. The potential for data loss can come from many different threats and an often overlooked one is from a user accidentally deleting data. By implementing proper security mechanisms, the DBA can define the proper access to prevent unauthorized users from having the ability to delete data from tables that shouldn't be accessing. However, there will occasions when a developer will need to delete data for testing purposes or when working with custom tables. Defining this access is fairly straight-forward as well, but there can also be situations when providing additional access can actually be beneficial. If a developer needs to delete all the data in a table, they have two options: they can issue a regular DELETE statement or they can use an Oracle command called the TRUNCATE command. Now most DBAs will get very nervous at the thought of allowing a user to truncate tables because there is more risk involved when this privilege is in the wrong hands. However, consider this scenario we recently encountered:

User A needs to delete data from a custom table and issues "DELETE FROM T;"
The table T in question is 50 GB in size, so behind the scenes what does Oracle have to do for this "simple" statement? The database must keep track of all the rollback information for the records being deleted in the UNDO area in case the user decides to rollback his changes or the statement fails. The modifications to the table generates change records in the database redo logs that Oracle uses to keep track of the sequence of changes made to the database and also copies these files to archive redo logs as they fill up. All of the indexes on table T must be maintained to keep track of the record pointers, which also generates redo log activity.

After running for 2 hours, the delete statement finally failed when it consumed all of the available UNDO space because of the amount of changes being made. The change generated so much activity that it also almost completely used all the available disk space on the server where the archive logs are stored. Once the transaction failed, then Oracle had to reverse the changes to put the data back the way it was. During this rollback process that took an additional 2 hours, Oracle generated additional redo activity from reverting the changes. All of this to delete data that isn't needed anymore and meanwhile the system resources are being consumed, potentially impacting activity that end users are trying to complete.

Now compare this to the effect of issuing a TRUNCATE on the same table. Truncating table T completed in a matter of seconds, consumes no UNDO space, and generates no redo activity. The truncate command also reclaims the space used by the table by resetting the high water mark (a marker Oracle uses to track the used blocks in a table). If the delete command had completed successfully, table T would still be 50 GB in size unless additional maintenance was performed to reclaim the unused free space. Truncate doesn't come without risks, the biggest being that it cannot be undone. When issuing a delete statement, the user has a chance to verify they deleted the intended data before issuing a commit (save) or rollback (undo) statement. With a truncate, there is no opportunity for Oracle to ask the user "Are you sure?" so this must be used with caution.

Careful consideration should be used when allowing access to the TRUNCATE command. The elevated privileges required by the TRUNCATE command will allow the user to truncate any table in the database; however the potential for reduced impact on the system can often outweigh the security risk in non-production databases. Hopefully this example shows how using the features that are available in Oracle can help save your system from unnecessary problems.

Finding PUBLIC Privileges

2011-02-21T15:15:00.010-05:00

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.

The Ubiquitous To-Do List

2011-02-14T01:13:00.003-05:00

Most Oracle DBAs are, by nature, ornery little task masters. We're called detail-oriented when someone wants to be generous. We're called anal-retentive micro-managers the other 6 days of the week.

It's all true, and it's also what makes a good DBA an excellent DBA.

I figured out something very important soon after I became an IT Manager back in the mid 90s. None of the technical people who worked for me could tell me, at ANY point in time, exactly what they were working on. Oh sure, they could tell me about some pending big project or talk about a nagging issue that never seemed to go away (often as a direct result of that same individual dropping the ball day after day), but they couldn't tell me what they needed to do in the next day, let alone the next hour.

I was pretty incredulous. How could technical people not know what they needed to get done?

People who knew me at the time joked about my to-do list. I took it to meetings and added tasks and "to-dos" during the meeting. I'd then work on my tasks and cross them off as I completed them. If I told someone I would do something, I did it. If I made a commitment, I followed through. The buzzword in my company at the time was accountability, and every member of the IT leadership team was evaluated accordingly. It never came as a surprise when I blew the lid off the accountability chart.

It also came as no surprise to my direct reports when I made it a requirement that everyone create a to-do list and begin working from a list of prioritized tasks. It became part of everyone's quarterly review process. It became part of every one-on-one meeting and part of every status meeting.

It drove a lot of people crazy. Some people couldn't deal with structure. A few couldn't deal with accountability (i.e. having to work a full day). A couple people were, unfortunately, terminated. The to-list, and their inability and/or refusal to have their work ethic exposed, was more than some could bear.

Today, IT is replete with people who believe in the art of black magic. They don't believe they need structure, processes, or documentation. They scoff at the idea of having rules or boundaries of any sort. They're the day-to-day IT heroes who make computers work, never mind the fact that they're also the ones who break them in the first place. They seem to be in a constant state of panic, seemingly unable to extricate themselves from their situation.

Some people, especially the IT hero, would argue that my ubiquitous to-do list concept is an unnecessary burden when most IT organizations have project plans, help desk ticketing systems, and the like to identify and track an organization's priorities and schedules. They'd claim that they are already overworked. B.S. Nothing could be further from the truth.

An accountable IT professional is like a lion on the open Savannah. He or she works smart, conserving energy and identifying targets with the greatest return on their investment in time and energy. He or she is uniquely positioned to identify, track, and leverage technology for the betterment of the company. This type of person has light bulbs go off in the head when they see an opportunity to improve processes, implement new technologies, standardize methods, reduce costs, automate manual processes, or increase efficiencies across cross-functional boundaries.

This is the person who has a hallway conversation, an IM chat, or an email discussion, and identifies possible opportunities to improve the way business is done. These ideas go on a to-do list, a living, breathing, ever-changing list of "stuff" that needs to be done. These are the intangible things that never appear on a project plan or a Help Desk ticket. These are the things that separate an average worker from a superior employee. These are the things that lead to great things in the workplace.

Not working smart is one of IT's dirty little secrets. Corporations are demanding ever-increasing productivity from their employees. One way or another they'll get it, with or without the IT hero.

Don't be a hero. If you don't have a to-do list, give it a try for a month and let me know how it goes for you.

What are my batch UBEs doing in the Oracle database?

2011-02-05T23:59:00.000-05:00

The Oracle DBA is often asked to find out why a UBE is running so long, or if it's running at all inside the Oracle database. A good place to start is to locate all UBEs that have database connections, and check what they are doing. The following simple query can be used to locate the database connections:

column process  heading "Process|Id"            format a10
column sid      heading "Oracle|SID"            format 999999
column event    heading "Wait Event"            format a10 wrap
column machine  heading "Client|Machine"        format a10 wrap
column program  heading "Program"               format a10 wrap
column time     heading "Logon|Time"            format a20

set pagesize 9999

select  process,
        sid, 
        substr(event,1,80) event,
        substr(machine,1,20) machine,
        substr(program,1,30) program,
        to_char(logon_time,'mm/dd/yyyy hh24:mi:ss') time
from    gv$session
where   program like 'runbatch%'
order   by 1,logon_time

Of particular importance is the Process Id, which indicates the operating system process number of the UBE application, and the Wait Event, which indicates what the database session is doing. These bits of information will help the DBA determine if the UBE is active, and what it might be waiting on inside the database.

The above information can also be connected to the JDE job table to show the name of the UBE that is running, and to reveal exactly when the job was submitted.