Document Type | Troubleshooting
Category | App Development
Applicable Product Versions | 6FS07, 6FS07PS, 7FS01, 7FS02, 7FS02PS
Document Number | TDETS008
Issue
Even though user A has been granted SELECT or DML privileges on user B's tables, when creating or compiling Functions, Procedures, Packages, etc. that reference those tables, a TBR-8033: Specified schema object was not found. error occurs and compilation fails.
SQL> CREATE USER USER1 identified by tibero;
User 'USER1' created.
SQL> CREATE USER USER2 identified by tibero;
User 'USER2' created.
SQL> grant connect,resource,dba to USER1;
Granted.
SQL> conn user1/tibero;
Connected to Tibero.
SQL> create table user2.dummy(a number);
Table 'USER2.DUMMY' created.
SQL> insert into user2.dummy values(1);
1 row inserted.
SQL> commit;
Commit completed.
SQL> select * from user2.dummy;
A
----------
1
1 row selected.
SQL> CREATE OR REPLACE FUNCTION GET_VAL RETURN INT
IS
ret_val number;
BEGIN
select a into ret_val from USER2.DUMMY;
RETURN ret_val;
END;
/
Warning: FUNCTION created with compilation errors.
SQL> show err
Errors for Function USER1.GET_VAL:
ERROR DESCRIPTION
---------------------------------------------------------
TBR-8033: Specified schema object was not found.
at line 5, column 30 of null:
select a into ret_val from USER2.DUMMY;
SQL> exit
Cause
When compiling an object, privileges granted through roles are not recognized; only privileges granted directly via GRANT ... TO ... are considered valid.
Therefore, if user A has not been granted privileges directly in the form of GRANT ... TO ... on user B's objects, the TBR-8033 error occurs during compilation.
Solutions
You must grant privileges directly via GRANT on the object to be compiled.
SQL> CREATE OR REPLACE FUNCTION GET_VAL RETURN INT
IS
ret_val number;
BEGIN
select a into ret_val from USER2.DUMMY;
RETURN ret_val;
END;
/
Warning: FUNCTION created with compilation errors.
SQL> show err
Errors for Function USER1.GET_VAL:
ERROR DESCRIPTION
---------------------------------------------------------
TBR-8033: Specified schema object was not found.
at line 5, column 30 of null:
select a into ret_val from USER2.DUMMY;
SQL> conn sys/tibero
Connected to Tibero.
SQL> grant select on USER2.DUMMY to user1;
Granted.
SQL> conn user1/tibero
Connected to Tibero.
SQL> CREATE OR REPLACE FUNCTION GET_VAL RETURN INT
IS
ret_val number;
BEGIN
select a into ret_val from USER2.DUMMY;
RETURN ret_val;
END;
/
Function 'GET_VAL' created.