/*
 * Title  : stp_sec.sql
 * Author : EXADIME LLC
 * Created: 29-Jul-2017
 * Updated: 10-Jan-2025
 * Release: 1.0.0
 * Purpose: Database Security Setup
 * History: Added sys profile.
 */

start oracle.ini

set echo off
set feedback off
set serverout on
set termout on

set linesize 100
set pagesize 0
set pause off

set heading off
set headsep on
set underline on
set colsep " "

set define on
set verify off
set escape off
set embedded on

REM 1.Lock system schemas.
spool &&tmp_dir/stp_sec.tmp

select
	'alter user '||	username ||' account lock;'
from
	dba_users
where
	username in (
		'SYS',
		'SYSTEM',
		'DBSNMP',
		'OUTLN',
		'MDSYS',
		'ORDSYS',
		'ORDPLUGINS',
		'CTXSYS',
		'DSSYS',
		'WKPROXY',
		'WKSYS',
		'WMSYS',
		'XDB',
		'ANONYMOUS',
		'ODM',
		'ODM_MTR',
		'OLAPSYS',
		'TRACESVR',
		'OPS$ORACLE',
		'ORADBA',
		'ORAMTR',
		'PERFSTAT',
		'REPADMIN',
		'RMAN',
		'EXFSYS',
		'DMSYS',
		'SYSMAN',
		'DRSYS',
		'TSMSYS',
		'WKUSER',
		'WKADMIN'		
	);

spool off

set echo on feedback on 
prompt spool &&rpt_dir/stp_sec.rpt

@@&&tmp_dir/stp_sec.tmp

REM 2.Password Verify Function
@@stp_pvf.sql

REM 3.Profiles
create profile sys_profile limit
sessions_per_user default
cpu_per_session default
cpu_per_call default
connect_time default
idle_time default
logical_reads_per_session default
logical_reads_per_call default
composite_limit default
private_sga default
failed_login_attempts 5
password_life_time unlimited
password_reuse_time 10
password_reuse_max 5
password_lock_time unlimited
password_grace_time 10
password_verify_function verify_function;

create profile app_profile limit
sessions_per_user default
cpu_per_session default
cpu_per_call default
connect_time default
idle_time default
logical_reads_per_session default
logical_reads_per_call default
composite_limit default
private_sga default
failed_login_attempts 5
password_life_time unlimited
password_reuse_time 10
password_reuse_max 5
password_lock_time unlimited
password_grace_time 10
password_verify_function verify_function;

create profile user_profile limit
sessions_per_user 3
cpu_per_session default
cpu_per_call default
connect_time default
idle_time 30
logical_reads_per_session default
logical_reads_per_call default
composite_limit default
private_sga 250
failed_login_attempts 5
password_life_time 90
password_reuse_time 10
password_reuse_max 5
password_lock_time unlimited
password_grace_time 10
password_verify_function verify_function;

REM 4.Roles
REM 4.1.System Roles
create role con_role not identified;

grant create session to con_role;
grant alter  session to con_role;
grant create synonym to con_role;

create role res_role not identified;

grant create type to res_role;
grant create table to res_role;
grant create cluster to res_role;
grant create view to res_role;
grant create sequence to res_role;
grant create indextype to res_role;
grant create operator to res_role;
grant create procedure to res_role;
grant create trigger to res_role;

create role cat_role not identified;
grant select_catalog_role to cat_role;

create role dic_role not identified;
grant select any dictionary to dic_role;

create role awr_role not identified;

grant select on sys.v_$database to awr_role;
grant select on sys.v_$instance to awr_role;
grant execute on sys.dbms_workload_repository to awr_role;
grant select on sys.dba_hist_database_instance to awr_role;
grant select on sys.dba_hist_snapshot to awr_role;
grant advisor to awr_role;

create role dba_role not identified;
grant dba to dba_role;

create role dbo_role not identified;

grant alter any cluster to dbo_role;
grant alter any dimension to dbo_role;
grant alter any index to dbo_role;
grant alter any indextype to dbo_role;
grant alter any library to dbo_role;
grant alter any outline to dbo_role;
grant alter any procedure to dbo_role;
grant alter any sequence to dbo_role;
grant alter any snapshot to dbo_role;
grant alter any table to dbo_role;
grant alter any trigger to dbo_role;
grant alter any type to dbo_role;
grant alter session to dbo_role;
grant alter system to dbo_role;
grant analyze any to dbo_role;
grant backup any table to dbo_role;
grant comment any table to dbo_role;
grant create any cluster to dbo_role;
grant create any dimension to dbo_role;
grant create any directory to dbo_role;
grant create any index to dbo_role;
grant create any indextype to dbo_role;
grant create any library to dbo_role;
grant create any operator to dbo_role;
grant create any outline to dbo_role;
grant create any procedure to dbo_role;
grant create any sequence to dbo_role;
grant create any snapshot to dbo_role;
grant create any synonym to dbo_role;
grant create any table to dbo_role;
grant create any trigger to dbo_role;
grant create any type to dbo_role;
grant create any view to dbo_role;
grant create cluster to dbo_role;
grant create database link to dbo_role;
grant create dimension to dbo_role;
grant create indextype to dbo_role;
grant create library to dbo_role;
grant create operator to dbo_role;
grant create procedure to dbo_role;
grant create public database link to dbo_role;
grant create public synonym to dbo_role;
grant create sequence to dbo_role;
grant create session to dbo_role;
grant create snapshot to dbo_role;
grant create synonym to dbo_role;
grant create table to dbo_role;
grant create trigger to dbo_role;
grant create type to dbo_role;
grant create view to dbo_role;
grant delete any table to dbo_role;
grant drop any cluster to dbo_role;
grant drop any dimension to dbo_role;
grant drop any directory to dbo_role;
grant drop any index to dbo_role;
grant drop any indextype to dbo_role;
grant drop any library to dbo_role;
grant drop any operator to dbo_role;
grant drop any outline to dbo_role;
grant drop any procedure to dbo_role;
grant drop any sequence to dbo_role;
grant drop any snapshot to dbo_role;
grant drop any synonym to dbo_role;
grant drop any table to dbo_role;
grant drop any trigger to dbo_role;
grant drop any type to dbo_role;
grant drop any view to dbo_role;
grant drop public database link to dbo_role;
grant drop public synonym to dbo_role;
grant execute any indextype to dbo_role;
grant execute any library to dbo_role;
grant execute any operator to dbo_role;
grant execute any procedure to dbo_role;
grant execute any type to dbo_role;
grant force any transaction to dbo_role;
grant global query rewrite to dbo_role;
grant insert any table to dbo_role;
grant lock any table to dbo_role;
grant query rewrite to dbo_role;
grant select any dictionary to dbo_role;
grant select any sequence to dbo_role;
grant select any table to dbo_role;
grant update any table to dbo_role;

create role dsa_role not identified;

grant select on sys.all_users to dsa_role;
grant select on sys.aud$ to dsa_role;
grant delete on sys.aud$ to dsa_role;
grant select on sys.dba_profiles to dsa_role;
grant select on sys.dba_ts_quotas to dsa_role;
grant select on sys.dba_users to dsa_role;
grant select on sys.resource_cost to dsa_role;
grant select on sys.user_password_limits to dsa_role;
grant select on sys.user_resource_limits to dsa_role;
grant select on sys.user_ts_quotas to dsa_role;
grant select on sys.user_users to dsa_role;
grant select on sys.v_$session to dsa_role;
grant alter profile to dsa_role;
grant alter user to dsa_role;
grant audit any to dsa_role;
grant audit system to dsa_role;
grant become user to dsa_role;
grant create profile to dsa_role;
grant create role to dsa_role;
grant create session to dsa_role;
grant create user to dsa_role;
grant drop any role to dsa_role;
grant drop profile to dsa_role;
grant drop user to dsa_role;
grant grant any privilege to dsa_role;
grant grant any role to dsa_role;
grant select any dictionary to dsa_role;
grant select_catalog_role to dsa_role;

REM 4.2.Object Roles
create role app_role not identified;
create role sup_role not identified;
create role read_role not identified;

REM 5.Grants
revoke execute on utl_file from public;
revoke execute on utl_http from public;
revoke execute on utl_smtp from public;
revoke execute on utl_tcp from public;
revoke execute on dbms_random from public;

REM 6.Enable Audit Trail.
audit all;

spool off

set embedded off
set escape off
set verify on
set define on

set colsep " "
set underline on
set headsep on
set heading on

set pause off
set pagesize 24
set linesize 80

set termout on
set serverout off
set feedback on
set echo off