Remedy - Server - Error:"ORA-00942: table or view does not exist" for a non Remedy Database user after modifying a form in DevStudio

Version 1
    Share This:

    This document contains official content from the BMC Software Knowledge Base. It is automatically updated when the knowledge article is modified.


    PRODUCT:

    Remedy AR System Server


    COMPONENT:

    AR System


    APPLIES TO:

    Remedy AR System Database



    PROBLEM:

    A non-Remedy Database user is given access to a Remedy Table/View, for example CTM:People form, T1426 in the Database.
    The SQL used for example is:
    GRANT SELECT on "ARADMIN"."CTM_PEOPLE" TO "SYS";

    The user can query it to retrieve data, for example:
    SELECT PERSON_ID,SUBMITTER,REMEDY_LOGIN_ID from CTM_PEOPLE;

    Result:
    PPL000000000005    Demo    appadmin
    PPL000000000012    Demo    Orchestration
    PPL000000000013    Demo    Allen
    PPL000000000014    Demo    Bob
    PPL000000000015    Demo    Michel
    PPL000000000016    Demo    Mary
    PPL000000000017    Demo    Hans
    PPL000000000018    Demo    Ian
    PPL000000000019    Demo    Silvio
    PPL000000000020    Demo    Manuel
    PPL000000000021    Demo    Francie
    PPL000000000022    Demo    Joe


    The CTM:People form is modified in DevStudio and saved.

    When the non-Remedy Database user tries to query the table again receives the error:
    ...................
    ORA-00942: table or view does not exist
    00942. 00000 -  "table or view does not exist"

    *Cause:    
    *Action:
    Error at Line: 1 Column: 49
    ...................

     


    CAUSE:

    External permissions no native to ARSystem are not taken into account when a form/table/view is recreated by ARS code. This is expected as it is not related to Remedy.


    SOLUTION:

    The observed behavior is expected and it does not relate to Remedy.
    Remedy creates its own database user (ARADMIN) and any other users set by the DBA to access any of the ARSystem database tables is not controlled by Remedy but the DBA Team.

    If the form/table changes by recreating the views, etc... external access will no longer work.
    This applies to any form modified in DevStudio.

    When a form is modified using DevStudio by adding a field, overlay, delete fields, etc.... Remedy will drop the table and views and recreate them so the object will contain the new elements.
    This means that any access to the original table will be lost and this is not controlled as mentioned above by Remedy but by the DBA Team.

    For example in DevStudio we perform an activity adding more fields following the example described above, in the logs we observe:
    ------
    */ +SMSF    ARSetMultipleFields -- schema CTM:People # of fields 26 from Developer Studio (protocol 24) at IP address {IP_Address} using RPC // :q:0.0s 
    ...etc...
    */ DROP VIEW T1426
    */ OK
    */ DROP VIEW H1426
    */ OK
    */ DROP VIEW B1426
    */ OK
    */ CREATE OR REPLACE VIEW T1426 (C1000000346, C1, C2, C4, C7, C1000000018, C1000000082, C1000000019, ....etc....
    */ OK
    */ CREATE OR REPLACE VIEW B1426 (C1, E0, E1, E2, E3)  AS SELECT t.C1, t.E0, t.E1, t.E2, t.E3 FROM T1426 t
    */ OK
    */ CREATE OR REPLACE VIEW H1426 AS SELECT * FROM H1408
    */ OK
    ...etc...
    ------


    For this scenario the DBA needs to grant access again to the table for the external database user.
    Note, as mentioned above, this is not an issue with Remedy but how any SQL Database logic works.



     


    Article Number:

    000169761


    Article Type:

    Solutions to a Product Problem



      Looking for additional information?    Search BMC Support  or  Browse Knowledge Articles