JL Computer Consultancy

Practical Oracle 8i - Building Efficient Databases

Chapter 21: Row-level security.


Ambiguity (20-Dec-2005)

Export (17-Apr-2001)

Tracing Predicates (6-Apr-2001)

Back to Book Index



I have made a comment (p.465, para 4) which could be interpreted exactly the wrong way round. There is a line that reads:

"If you have built an OLTP system that doesn't reuse its SQL (by using bind variables instead of building literal statements), then the overhead in enormous."

The intention of the (by using bind variables …) bit was to explain how to reuse your SQL; however the phrasing is so ambiguous that you could easily read this the wrong way round, and assume that using bind variables stops you from re-using your SQL. So just to clarify: bind variables are good for re-usability, hence scalability in OLTP systems; literal strings are (usually) bad.

Thanks to Adrian Billington for pointing out the confusion this could have caused.


There is a brief throwaway comment (p.472) about the interaction between row-level security and the exp utility where I point out a direct export bypasses row-level security. This little loophole was closed in 8.1.6, however it does have a necessary side-effect: Oracle has to convert the data in order to apply the security predicate, so the export is not done in direct mode. As each such table is reached, exp produces the 'error' messages:

EXP-00080: Data in table "XXX" is protected. Using conventional mode.
EXP-00079: Data in table "XXX" is protected. Conventional path may only be exporting partial table.
. . exporting table T1 10 rows exported

This occurs even if the predicate is a tautological one such as '1=1'. The most visible impact of this change if you upgrade from 8.1.5 to 8.1.6 is that an export of a large table may suddenly take much longer as it switches from direct path to conventional path after the upgrade.

Top of page


Tracing Predicates

I mentiond event 10730 (p.463) as a way of dumping information about generated predicates when you were using row-level security. In 8.1.5, this trace dumped the predicate, which made it quite hard to identify which statement was affected by the predicate. This feature has been improved in onwards, instead of showing just the predicate it dumps the entire generated 'inline view'. For example:

Logon user     : JPL
Table or View  : DEMO.RLS_DEMO
Policy name    : DEMO__POLICY
RLS view :
        WHERE (owner = sys_context('rls_demo','role'))

Formerly you would have seen the following instead of the RLS view.

RLS predicate : owner = sys_context('rls_demo','role')

Top of page.


Bug 1462321 'FGAC results in wrong results in 8.1.7


Top of page.

Back to Book Index