Adding Custom Tables to a Noetix View

Suppose one is adding a custom table’s column(s) to a view.  Again, one has checked to make sure that this does not change the views granularity.  Are there any additional steps needed to insure that this modification will work successfully?

1.        Of course run the script, get_data_tmpl.sql, which gives you a good perspective of the query blocks applicable for your version of Oracle Applications.

2.       In addition to this, it is important to be cognizant that a select grant with grant option is given from the table’s owner to the Noetix_Sys database account.

Why is step two necessary?

Part of the view security setup is to grant select to the Noetix Roles (these are actually database roles). 

If one peruses the results of this query, this granting that occurs through the NVA can be seen:

select

 *

 from

 dba_tab_privs

 where

 owner = 'NOETIX_SYS'

 and grantee = &favorite_Noetix_Role;

If a column from a custom table is added to a Noetix View, it is important to grant select (with grant option) to this custom table to the Noetix Sys database account. 

A query like this can reveal that this privilege has been granted  (here the custom table is 'XXMES_WIP_ONHAND' and its owner is 'XXMESXFER') :

select

 *

 from

 DBA_TAB_PRIVS

 where

 owner = 'XXMESXFER'

 and table_name = 'XXMES_WIP_ONHAND'

and grantee = 'NOETIX_SYS'

and grantable = ‘YES’

This will enable the Noetix Sys database account to grant select to various Noetix roles.