Oracle sqlplus: Restrict TRUNCATE, CONNECT, INSERT, UPDATE and DELETE capabilities

Oracle sqlplus: Restrict TRUNCATE, CONNECT, INSERT, UPDATE and DELETE capabilities

****************************** Code Begins **************************************************

REM
rem Script Description: This script is designed to restrict the TRUNCATE, CONNECT, INSERT, UPDATE and DELETE capabilities
rem of end-users from within SQL*Plus. It was implemented to perform weekly maintenance of
rem the product user profile table.
rem
rem Output file: prodprof.sql
rem
rem Prepared By: Oracle Resource Stop
rem
rem Usage Information: SQLPLUS SYS/pswd
rem @prodprofmaint.sql
rem

set pages 10000
set heading off
set lines 200
set feedback off

spool prodprof.sql

rem
rem The following adds any new users to the PRODUCT_USER_PROFILE table using the standard access restrictions.
rem

select 'insert into product_user_profile(product,userid,attribute,char_value) values ('||
'''SQL*Plus'','''||username||''',''INSERT'',''DISABLED'''||');',
'insert into product_user_profile(product,userid,attribute,char_value) values ('||
'''SQL*Plus'','''||username||''',''UPDATE'',''DISABLED'''||');',
'insert into product_user_profile(product,userid,attribute,char_value) values ('||
'''SQL*Plus'','''||username||''',''DELETE'',''DISABLED'''||');',
'insert into product_user_profile(product,userid,attribute,char_value) values ('||
'''SQL*Plus'','''||username||''',''CONNECT'',''DISABLED'''||');',
'insert into product_user_profile(product,userid,attribute,char_value) values ('||
'''SQL'','''||username||''',''TRUNCATE'',''DISABLED'''||');'
from dba_users a
where not exists
(select * from product_user_profile
where userid = a.username);
spool off

@prodprof

rem
rem The following removes and deleted users from the PRODUCT_USER_PROFILE table.
rem

delete from product_user_profile a
where not exists
(select * from dba_users
where username = a.userid);

commit;

set pages 1000
set heading on
set lines 80
set feedback on

****************************** Code Ends **************************************************