granting select to role in procedure
Hello !
In my procedure I grant select to role:
CREATE OR REPLACE PROCEDURE prod.grant_select(
table_name IN VARCHAR ) AS
v_table VARCHAR2(30);
BEGIN
SELECT TABLE_NAME INTO v_table FROM ALL_TABLES WHERE TABLE_NAME = UPPER(table_name) AND OWNER = 'PROD';
EXECUTE IMMEDIATE 'grant select on PROD.'|| v_table || ' to ANALYTICS' ;
EXCEPTION
WHEN too_many_rows then dbms_output.put_line('too_many_rows ' || table_name);
WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('Invalid table: ' || table_name);
END;
/
Exec idb_prod.grant_select('TEST_TABLE');
procedure successfully completed.
please explain the reason the query to dba_tab_privs (WHERE grantee = 'ANALYTICS') return no rows
0