Creating read write user for non FCUBS

Creating read write user for non FCUBS

In Production database if application user want trouble shoot the issue in back end,Then he wants to do DML on the FCUBS scheme.
Then due to policy restrictions you cannot be able to give application schema credentials,in that case So you can role with read and write privileges as below.
and grant to the user,so that they can login with their credential and perform the actions by this you can filter or audit who did what on the FCUBS application schema

create role RWFCUBS_TABLE_ROLE not identified;

set head off
spool table_gnt.sql
select 'grant all on ' || owner || '.' ||table_name || ' to RWFCUBS_TABLE_ROLE;' from dba_tables where owner='<FCUBS_SCHEMA>';
spool off
@table_gnt.sql

set head off
spool view_gnt.sql
select 'grant select on ' || owner || '.' || view_name || ' to RWFCUBS_TABLE_ROLE;' from dba_views where owner='<FCUBS_SCHEMA>';
spool off
@view_gnt.sql

set head off
spool procedure_gnt.sql
select 'grant debug on ' || owner || '.' || object_name || ' to RWFCUBS_TABLE_ROLE;'
from dba_objects where owner='<FCUBS_SCHEMA>' and object_type in ('PROCEDURE','FUNCTION','PACKAGE','BODY');
spool off
@procedure_gnt.sql

connect to <FCUBS_SCHEMA>:

create or replace trigger <FCUBS_SCHEMA>.RWFCUBS_TABLE_ROLE_trigger after CREATE on schema
declare
l_stat varchar2(255);
l_job number;
begin
if ( ora_dict_obj_type = 'TABLE' )
then
l_stat := 'execute immediate "grant all on ' || ora_dict_obj_name || ' to RWFCUBS_TABLE_ROLE";';
dbms_job.submit( l_job, replace(l_stat,'"','''') );
end if;
if ( ora_dict_obj_type = 'VIEW' )
then
l_stat := 'execute immediate "grant select on ' || ora_dict_obj_name || ' to RWFCUBS_TABLE_ROLE";';
dbms_job.submit( l_job, replace(l_stat,'"','''') );
end if;

if ( ora_dict_obj_type in ('PROCEDURE','FUNCTION','PACKAGE','BODY'))
then
l_stat := 'execute immediate "grant debug on ' || ora_dict_obj_name || ' to RWFCUBS_TABLE_ROLE";';
dbms_job.submit( l_job, replace(l_stat,'"','''') );
end if;
end;
/

grant  EXECUTE ANY PROCEDURE to RWFCUBS_TABLE_ROLE ;
No you can grant this read only role of FCUBS objects to other users.
grant RWFCUBS_TABLE_ROLE to <USER Name>; -- Assuming the user already exist if not create the user and grant the role