Using sys user, executed the below.
create table XX_GRANTS_FAIL_RO (
object_name VARCHAR2(100)
,sqlerrm varchar2(2000)
,creation_date DATE
);
========================
DECLARE
v_error VARCHAR2(2000);
BEGIN
FOR p_rec IN (SELECT *
FROM all_objects
WHERE owner = 'x'
AND object_type IN ('VIEW','TABLE','SEQUENCE')
AND object_name NOT LIKE '%\_S' escape '\')
LOOP
BEGIN
EXECUTE IMMEDIATE 'grant select on ' || p_rec.object_name ||
' to y';
EXECUTE IMMEDIATE 'grant update on ' || p_rec.object_name ||
' to y';
EXECUTE IMMEDIATE 'grant delete on ' || p_rec.object_name ||
' to y';
EXCEPTION
WHEN OTHERS THEN
v_error := substr(SQLERRM, 1, 2000);
INSERT INTO SYS.XX_GRANTS_FAIL_RO
(object_name
,SQLERRM
,creation_date
)
VALUES
(p_rec.object_name
,v_error
,sysdate
);
END;
END LOOP;
COMMIT;
END;
/
select * from dba_tab_privs where grantee='y' shows that y has select privilege assigned to.
However, when I connect to y schema, I am getting error ORA-00942: table or view does not exist when I query table even when referencing to x as select * from x.<table_name>;
Any idea what could be the reason of this error?