When modifying a form (adding or deleting a field to anything but a Vendor or Display Only form) the system has always (I say always, but I can only prove since v4.x) done a 2 step process to maintain a set of views in the database. DROP VIEW xxxxxxxxx; CREATE VIEW xxxxxxxxxx
I believe the reason that it has been done this way in the system is so they can use the exact same SQL no matter what DB. Not all databases support the CREATE OR REPLACE VIEW statement. We run a nightly cron script to grant DB access to views. This way we can also drop access to views and tables we don't want any outside databases access to (Think of the situation of someone creating a ticket to HR for a payroll issue. You don't want someone getting access to that thru the database, or worse a ticket for medical stuff). For some forms we use LJ's suggestion to create custom database views. Fred -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of LJ LongWing Sent: Wednesday, January 22, 2014 1:55 PM To: [email protected] Subject: Re: Namesake Views ** Mark, The way we combated this particular issue was to actually create a new db view, one that is defined specifically for your purposes, and is not re-created after every form change. -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Brittain, Mark Sent: Wednesday, January 22, 2014 1:38 PM To: [email protected] Subject: Namesake Views ** HI All, The move from 6.3 (custom) to 7.6 & ITSM always seems to have more surprises than I care to sustain. Anyway, I have a monitoring tool that queries the hpd_help_desk view. The monitoring tool is granted permission to the view using the command "grant select on aradmin.hpd_help_desk to monitoringtool; Before today I did not know about these namesake views. Last night I added a field to the HPD:HelpD Desk and this query broke. Apparently there is a script that runs, deletes rather than updates the view and builds a new view. The result is any grants assigned to the view are wiped out. After that monitoring tool could not query the view and the DatabaseError: ORA-00942: table or view does not exist was being returned. One solution would be to grant the permission the monitoring tool anytime the view is rebuilt. Not my favorite approach. Has anyone run into this and come up with a better solution? ARS 7.6.04 ITSM 7.6.04 Oracle 11 Thanks Mark Mark Brittain Remedy Developer ITILv3 Foundation, Continual Service Improvement NaviSite, Inc. - A Time Warner Cable Company [email protected] Office: 315.634.9337 Mobile: 315.882.5360 _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org "Where the Answers Are, and have been for 20 years"
