Create a user command as follows:
create user READONLY_USER identified by password;
Script will provide all read permission for all objects:
DECLARE
v_sql VARCHAR2(500);
BEGIN
-- Grant SELECT on all tables and views in the schema
FOR r IN (SELECT object_name
FROM all_objects
WHERE owner = 'SCHEMA_NAME'
AND object_type IN ('TABLE', 'VIEW'))
LOOP
v_sql := 'GRANT SELECT ON SCHEMA_NAME.' || r.object_name || ' TO READ_ONLY_USER';
EXECUTE IMMEDIATE v_sql;
END LOOP;
-- Grant access to view source code of procedures, functions, and packages
EXECUTE IMMEDIATE 'GRANT SELECT ON ALL_SOURCE TO READ_ONLY_USER';
-- Grant access to metadata of procedures, functions, and packages
EXECUTE IMMEDIATE 'GRANT SELECT ON ALL_OBJECTS TO READ_ONLY_USER';
EXECUTE IMMEDIATE 'GRANT SELECT ON ALL_PROCEDURES TO READ_ONLY_USER';
EXECUTE IMMEDIATE 'GRANT SELECT ON ALL_ARGUMENTS TO READ_ONLY_USER';
END;
/
You can grant permission one by one according to your need:
For tables and views only:
BEGIN
FOR r IN (SELECT object_name FROM dba_objects WHERE owner = 'SCHEMA_NAME' AND object_type IN ('TABLE', 'VIEW'))
LOOP
EXECUTE IMMEDIATE 'GRANT SELECT ON SCHEMA_NAME.' || r.object_name || ' TO READ_ONLY_USER';
END LOOP;
END;
/
For procedure and see their source code:
GRANT SELECT ON ALL_SOURCE TO READ_ONLY_USER;
GRANT SELECT ON ALL_OBJECTS TO READ_ONLY_USER;
GRANT SELECT ON ALL_PROCEDURES TO READ_ONLY_USER;
OR
GRANT SELECT ON DBA_SOURCE TO READ_ONLY_USER;
GRANT SELECT ON DBA_OBJECTS TO READ_ONLY_USER;
GRANT SELECT ON DBA_PROCEDURES TO READ_ONLY_USER;