Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2004
    Posts
    50

    Unanswered: viewing another schema's object status

    i have a user (USER_Z) who needs to see the status of the objects in another user's schema (USER_X).

    for some reason USER_Z can see that USER_X has invalid FUNCTIONs, but cannot see the invalid PACKAGE BODYs or VIEWS.

    what the heck permission setting is causing this? i find it strange that you can see function statuses but not package statuses. i would think they would be covered under the same permissions.

    ================================================== =======================

    AS OWNER (USER_X):

    Code:
    select OBJECT_TYPE, OBJECT_NAME, STATUS 
    from   ALL_OBJECTS
    where  OWNER='USER_X'
    and    STATUS='INVALID'
    order by OBJECT_TYPE, OBJECT_NAME, STATUS;
    
    
    OBJECT_TYPE         OBJECT_NAME                    STATUS 
    ------------------- ------------------------------ -------
    FUNCTION            MATCH_FUNCTION                 INVALID
    PACKAGE BODY        UTILITY_PKG                    INVALID
    VIEW                V_EMPLOYEE                     INVALID
    VIEW                V_WORKQUEUE_A                  INVALID
    VIEW                V_WORKQUEUE_B                  INVALID
    
    5 rows selected.
    ================================================== =======================

    AS USER_Z:

    Code:
    select OBJECT_TYPE, OBJECT_NAME, STATUS 
    from   ALL_OBJECTS
    where  OWNER='USER_X'
    and    STATUS='INVALID'
    order by OBJECT_TYPE, OBJECT_NAME, STATUS;
    
    
    OBJECT_TYPE         OBJECT_NAME                    STATUS 
    ------------------- ------------------------------ -------
    FUNCTION            MATCH_FUNCTION                 INVALID
    
    1 row selected.
    ================================================== =======================

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    as user_z run

    Code:
    SELECT NAME, TYPE, COUNT(LINE)
    FROM ALL_SOURCE
    WHERE OWNER = 'USER_X'
    GROUP BY NAME, TYPE
    ORDER BY 1, 2;
    post results from SQL above
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2004
    Posts
    50
    Code:
    SELECT NAME, TYPE, COUNT(LINE)
    FROM ALL_SOURCE
    WHERE OWNER = 'USER_X'
    GROUP BY NAME, TYPE
    ORDER BY 1, 2;
    
    
    NAME                           TYPE         COUNT(LINE)
    ------------------------------ ------------ -----------
    ALTERNATE_1                   PROCEDURE            336
    ALTERNATE_2                   PROCEDURE             35
    ALTERNATE_3                   PROCEDURE             90
    ALTERNATE_4                   PROCEDURE            169
    APPLICATION_P1                PACKAGE              116
    APPLICATION_U1                PACKAGE              176
    CORR_UTILITY                  PACKAGE               29
    CORRECTIONS                   PROCEDURE             47
    EXTRACTS                      PROCEDURE            214
    GET_MESSAGE                   FUNCTION             134
    GET_STATUS                    FUNCTION             280
    LOAD_TOOLS                    PACKAGE              120
    MATCHFXNA                     FUNCTION             248
    MATCHFXNB                     FUNCTION             245
    MATCHFXN1                     FUNCTION             248
    MATCH_FUNCTION                FUNCTION             513
    OPT_B                         PACKAGE               15
    OPT_CONSTANTS                 PACKAGE               76
    OPT_JOB                       PACKAGE               33
    OPT_CITY                      PACKAGE               35
    OPT_PRINT                     PACKAGE                8
    OS_UTIL                       PACKAGE              145
    OTI_UTIL                      PACKAGE              258
    PAYMENTS                      PACKAGE              236
    PERSONS                       PACKAGE              125
    REQUIREMENTS                  PACKAGE               97
    REQS_LVL3                     PROCEDURE             26
    REQS_LVLP                     PROCEDURE             26
    TERMINATIONS                  PROCEDURE             35
    UPDATE_DATE                   PROCEDURE             16
    UPDATE_HISTORY                PROCEDURE            173
    UPDATE_RECORDS                FUNCTION              11
    UPDATE_RETIRE                 PROCEDURE             22
    UPDATE_REQUIREMENTS           PROCEDURE             16
    UTILITY_PACKAGE               PACKAGE                9
    UTILITY_PACKAGE               PACKAGE BODY         144
    
    37 rows selected.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    ALL_OBJECTS shows only those objects to which you have access, which in the case of functions and packages I believe means those that you can execute. Check that user_z is granted EXECUTE privileges to all those objects. Alternatively you can grant user_z SELECT privileges to DBA_OBJECTS
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •