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.
NoteGenerally, 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.