Enter your custom HTML codes in this section …
Taking a break out of the APEX Exploits series for this as it’s something I keep coming across at different client sites.
select p.full_name, su.supervisor from per_people_x p, per_assignments_x a, per_people_x s where a.person_id = p.person_id and a.person_id (+) = a.supervisor_id;
With me (or the reader) being told: This query gives you all employees with their supervisors. Ah – but does it? Well, yes it does… sometimes.
See the problem is, many a time a developer will use a view such as per_people_x without knowing exactly what it is doing, and what impact future configuration changes might have. They know the view only returns current records, It runs fine in the development tool (Toad, SQL Developer etc), and when put in an Oracle form it works fine too. All good. However, at some point in the future, after HR have been changing some of their security settings, we suddenly find that people’s supervisors aren’t being reported as expected. Why? Because the per_person_x view is secured. We have the ability to see the people via our HR security profile, however we might not have access to see the records of their supervisor – that includes something as simple as the name. In reality, our query should probably be:
select p.full_name, su.supervisor from per_people_x p, per_assignments_x a, per_all_people_f s where a.person_id = p.person_id and a.person_id (+) = a.supervisor_id and trunc(sysdate) between a.effective_start_date(+) and a.effective_end_date(+);
But then again – should it? Are we interested in who is a person’s supervisor now, or who was the supervisor at a particular point in time (i.e. maybe the run date of the Payroll) – hint – the above query shows the former. What about if the person is no longer an employee – they may not get included in the above.
What I am leading up to here is that it is important to use the appropriate/correct view for the purpose, with the business requirements defining that.
The principles I’m going to explain here apply to a number of different objects in the same manner – people, assignments, positions etc. However I’m going to use people for the purposes of this demonstration.
Let’s look at the objects available for querying people (from an R12.2. environment).
select owner, object_name, object_type from all_objects where object_name like 'PER%PEOPLE%' and object_type in ('VIEW','TABLE') order by owner, object_name, object_type OWNER OBJECT_NAME OBJECT_TYPE ----- ------------------------------ ------------------- APPS PER_ALL_PEOPLE VIEW APPS PER_ALL_PEOPLE_D VIEW APPS PER_PEOPLE VIEW APPS PER_PEOPLE_F VIEW APPS PER_PEOPLE_V VIEW APPS PER_PEOPLE_V2 VIEW APPS PER_PEOPLE_V3 VIEW APPS PER_PEOPLE_V4 VIEW APPS PER_PEOPLE_V7 VIEW APPS PER_PEOPLE_X VIEW HR PER_ALL_PEOPLE_F TABLE We’re not interested in some – so I’ve trimmed them from the ouput, but there are a few variations we can see in terms of usages of the word _ALL_ and the suffix (_X, _D, No Suffix etc). So here is a brief summary of what does what. In general the suffix denotes the Date Tracking mode (although it can be an indication that a view contains translated values) and the usage of _ALL_ (or absence of) denotes security.
- View/Table contains _ALL_ : The data is not secured. I.e. Per_all_people_f.
- View doesn’t contain _ALL_ : The data is secured according to the HR Security Profile of the users responsibility.
- View/Table has a _F suffix : The data is not date-tracked. It will return all visible records. I.e. per_all_people_f.
- View/Table has a _D suffix : The data is not date-tracked. The view generally contains translated values, which are descriptive names based on the users language such as the Nationality description. I.e. per_all_people_d.
- View has a _X suffix : The data is date-tracked to sysdate. I.e. per_people_x.
- View has no suffix : The data is date-tracked to the session date. That is – the effective_date held against the record in the table fnd_sessions for the current session_id. i.e. per_people.
- View has a _V{Number} suffix : The implementation of these varies significantly. Some are session date tracked, some are date-tracked to the latest record, others not at all. They usually contain lookups to language translated values and are generally used in Oracle Forms. I.e. per_people_v7
As you can see there are not objects for all possible combinations (i.e. per_all_people_x doesn’t exist), however given the above set of rules it is usually easy to see which view to use. I.e. if you want a secured session date effective query, one would use the per_people view. However if you wanted an unsecured session date tracked query, we would use per_all_people instead. Note in our example above where we wanted a Sysdate tracked non-secured view. There is no per_all_people_x view – and it would be wrong of us to use per_people_x as this would potentially be implicated by security changes in the future – so we must use an unsecured object (either the per_all_people_f base table or the per_all_people_d unsecured view) and date-track it manually.
This kind of thing is especially important in LOV queries. I recently had to use a form which used per_people_x to select the list of employees for an Email To field for a system alert. As I wasn’t the supervisor of the System Administrator, I wasn’t able to select him in the form. The LOV should have used an unsecured object instead, however when it was written it was done so for a non-HR responsibility – and unfortunately the default functionality in HR is that the views return all data unless a security profile is applied – so at time of creation it seemed to work correctly.
Any developer who is using HR data needs to be making these considerations – not just looking at the current configuration, but at what would be the effect if that was to change. In reality, that should be in the business specification anyway. A final word of warning – please don’t just date-track everything to sysdate as I see a lot of people doing 🙂 . Understand the data – how it changes over time – and where you can and cannot expect to find a record under different circumstances.
Back to the APEX Security Exploits next time – keep an eye out.
Also, as a footnote, I’m pleased to announce that I’ve been invited to become an Oracle ACE ♠ – which means I will have to keep up the contributions and think of interesting things to write 🙂 . You can find my profile here.
Enter your text here …