rem Name:    approl.sql
rem Purpose: Create application user/developer/owner roles
rem Usage:   @approl
rem Subject: security
rem Attrib:  sql dba
rem Descr:
rem Notes:
rem SeeAlso: @appdev @appown @appusr

rem Default Profiles for Users

rem create profile app_owner limit idle_time default;
rem create profile app_user limit idle_time 120;

rem Schema Owner Account Role

create role app_owner;
grant
alter session
,create cluster
,create database link
,create procedure
,create public synonym
,create role
,create sequence
,create session
,create snapshot
,create synonym
,create table
,create trigger
,create view
,drop public synonym
--,execute any procedure
--,select any sequence
--,select any table
--,restricted session
to app_owner;

rem Operations (batch-user) Account Role

create role app_oper;
grant
alter session
,create cluster
,create database link
,create procedure
,create public synonym
,create role
,create sequence
,create session
,create snapshot
,create synonym
,create table
,create trigger
,create view
,drop public synonym
--,execute any procedure
--,select any sequence
--,select any table
--,restricted session
to app_oper;

rem Typical User Role

create role app_user;
grant
alter session
,create session
to app_user;

rem Typical Web User Role

create role app_web_user;
grant
alter session
,create session
to app_web_user;

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips