Grant readonly permission to a user in Oracle

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;
This entry was posted in Oracle on by .
Unknown's avatar

About SandeepSingh

Hi, I am working in IT industry with having more than 15 year of experience, worked as an Oracle DBA with a Company and handling different databases like Oracle, SQL Server , DB2 etc Worked as a Development and Database Administrator.

Leave a Reply