Skip to main content

Search

Welcome to Tibero GTS!

Issue of Other Users' Tables Not Being Queried When Running PSM with DBA Privilege Account

Document Type | Troubleshooting

Category | Administration

Applicable Product Version | 6FS01

Document Number | TADTS005

 

Issue

This is an issue where TBR-8033: Specified schema object was not found occurs when creating PROCEDURE or FUNCTION that queries other users' objects with a user having DBA privileges.
Note
Generally, a user with DBA privileges can query other users' objects, but in execution environments such as PROCEDURE or FUNCTION, access is restricted without specific privileges.

 

Reproducing the Issue

Creating Procedure

vi proc.sql

CREATE OR REPLACE PROCEDURE SELECT_PROC_01
IS
    CURSOR cSor IS
        SELECT a, b FROM TEST.tbl_01;


AAA number ;
BBB VARCHAR2(30);


BEGIN
    OPEN cSor;
    LOOP
        FETCH cSor INTO AAA, BBB;
            EXIT WHEN cSor%NOTFOUND;
            DBMS_OUTPUT.PUT_LINE(AAA || chr(9) || BBB);
    END LOOP;


CLOSE cSor;

END
;
/

 

Granting DBA Privileges

SQL> select * from user_role_privs;

GRANTEE    GRANTED_RO ADMIN_OPTION DEFAULT_ROLE
-------    ---------- ------------ ------------

TIBERO     DBA        NO           YES
TIBERO     CONNECT    YES          YES
TIBERO     RESOURCE   YES          YES

 

Attempt to Create Procedure - Error Occurs

SQL> @proc

Warning: PROCEDURE created with compilation errors.


SQL> show errors;
Errors for Procedure TIBERO.SELECT_PROC_01:


ERROR DESCRIPTION
ใ…กใ…กใ…กใ…กใ…กใ…กใ…กใ…กใ…กใ…กใ…กใ…กใ…กใ…กใ…กใ…กใ…กใ…กใ…กใ…กใ…กใ…กใ…ก
TBR-8033: Specified schema object was not found.
at line 4, column 27 of null:
        SELECT a, b FROM TEST.tbl_01;

 

 

Cause

Until Tibero 5 SP1, execution was possible with DBA Role alone, but from Tibero 6, some operations are restricted with DBA Role alone.
This is because role-based privileges are not applied at the time of PSM (Procedural Stored Module) compilation or execution. 
Privileges granted by the DBA Role such as SELECT ANY TABLE work in direct SQL sessions, but inside PSM, direct privileges (e.g., GRANT SELECT ON USER_B.TABLE_X TO USER_A) are required instead of role-based privileges.
 
 

Solutions

This can be resolved by granting SELECT privileges on the relevant table.
CONN sys/tibero

GRANT SELECT ON TEST.TBL_01 TO TIBERO;

CONN tibero/tmax

@proc

Procedure 'SELECT_PROC_01' created.
 
 
 
 
 
 

Related to