🐢 Install RAC 19c

1SELECT *
2  FROM USER_OBJECTS
3 WHERE object_type = 'PROCEDURE'
4   AND object_name = 'grant_RW'

 1CREATE OR REPLACE PROCEDURE grant_RO_to_schema(
 2    username VARCHAR2,
 3    grantee VARCHAR2)
 4AS
 5BEGIN
 6    FOR r IN (
 7        SELECT owner, table_name
 8        FROM all_tables
 9        WHERE owner = username
10    )
11    LOOP
12        EXECUTE IMMEDIATE
13            'GRANT SELECT ON '||r.owner||'.'||r.table_name||' to ' || grantee;
14    END LOOP;
15END;
16/
17
18-- See if procedure is ok -- 
19SHOW ERRORS 
20
21CREATE ROLE '${ROLE_NAME}' NOT IDENTIFIED;
22GRANT CONNECT TO '${ROLE_NAME}';
23GRANT SELECT ANY SEQUENCE TO '${ROLE_NAME}';
24GRANT CREATE ANY TABLE TO '${ROLE_NAME}';
25
26-- Play the Procedure -- 
27EXEC grant_RO_to_schema('${SCHEMA}','${ROLE_NAME}')

 1su - oracle -c '
 2export SQLPLUS="sqlplus -S / as sysdba"
 3export ORAENV_ASK=NO;
 4export ORACLE_SID='${SID}';
 5. oraenv | grep -v "remains";
 6
 7${SQLPLUS} <<EOF2
 8set lines 200 pages 2000;
 9CREATE OR REPLACE PROCEDURE grant_RW_to_schema(
10    username VARCHAR2,
11    grantee VARCHAR2)
12AS
13BEGIN
14    FOR r IN (
15        SELECT owner, table_name
16        FROM all_tables
17        WHERE owner = username
18    )
19    LOOP
20        EXECUTE IMMEDIATE
21            '\''GRANT SELECT,DELETE,UPDATE,INSERT,ALTER ON '\''||r.owner||'\''.'\''||r.table_name||'\'' to '\'' || grantee;
22    END LOOP;
23END;
24/
25CREATE ROLE '${ROLE_NAME}' NOT IDENTIFIED;
26GRANT CONNECT TO '${ROLE_NAME}';
27GRANT SELECT ANY SEQUENCE TO '${ROLE_NAME}';
28GRANT CREATE ANY TABLE TO '${ROLE_NAME}';
29GRANT CREATE ANY INDEX TO '${ROLE_NAME}';
30EXEC grant_RW_to_schema('\'''${SCHEMA}''\'','\'''${ROLE_NAME}''\'')
31exit;
32EOF2
33unset ORAENV_ASK;
34'

 1-- This one is working better : 
 2CREATE OR REPLACE PROCEDURE grant_RW_to_schema(
 3myschema VARCHAR2,
 4myrole VARCHAR2)
 5AS
 6BEGIN
 7for t in (select owner,object_name,object_type from all_objects where owner=myschema and object_type in ('TABLE','VIEW','PROCEDURE','FUNCTION','PACKAGE')) loop
 8if t.object_type in ('TABLE','VIEW') then
 9EXECUTE immediate 'GRANT SELECT, UPDATE, INSERT, DELETE ON '||t.owner||'.'||t.object_name||' TO '|| myrole;
10elsif t.object_type in ('PROCEDURE','FUNCTION','PACKAGE') then
11EXECUTE immediate 'GRANT EXECUTE ON '||t.owner||'.'||t.object_name||' TO '|| myrole;
12end if;
13end loop;
14end;
15/