Most of this blog post is based upon this late 2006 post by Ponder Stibbons. I took those ideas and ran with it.
The usual disclaimers apply. I assume you know what a schema is and what a schema owner is. I also assume you know a little bit about Oracle SQL and Database. This post is based release 10g and will be tested on 11g. All SQL in this post can be freely used, modified, claimed as your own, whatever. All SQL here should be used as a guideline and not used verbatim.
The first steps require SYSDBA or adequate rights and create components used for all schema owners including roles, a profile and perhaps a tablespace.
* Step 1: create table space in non-ASM server (you have probably already done this)
CREATE SMALLFILE TABLESPACE crap_data
'/u02/oradata/crapdb/CRAP_DATA1.dbf' SIZE 500M AUTOEXTEND ON NEXT 250M MAXSIZE 5000M
, '/u03/oradata/crapdb/CRAP_DATA2.dbf' SIZE 500M AUTOEXTEND ON NEXT 250M MAXSIZE 5000M
, '/u04/oradata/crapdb/CRAP_DATA3.dbf' SIZE 500M AUTOEXTEND ON NEXT 250M MAXSIZE 5000M
, '/u05/oradata/crapdb/CRAP_DATA4.dbf' SIZE 500M AUTOEXTEND ON NEXT 250M MAXSIZE 5000M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
* Step 2: create a profile
CREATE PROFILE schema_owner_profile
LIMIT CPU_PER_SESSION UNLIMITED
* Step 3: create roles
CREATE ROLE schema_owner_role NOT IDENTIFIED;
CREATE ROLE schema_admin_role NOT IDENTIFIED;
CREATE ROLE schema_admin_role IDENTIFIED BY "30charactercomplexpassword";
* Step 4: grant privileges to both roles
GRANT CREATE SESSION TO schema_owner_role;
GRANT ALTER SESSION TO schema_owner_role, schema_admin_role;
* The bare minimum for a schema administrator: the ability to create source and
* user types that use the "invoker rights clause"
GRANT CREATE PROCEDURE TO schema_admin_role;
GRANT CREATE TYPE TO schema_admin_role;
* Additional privileges
GRANT CREATE JOB TO schema_owner_role, schema_admin_role;
GRANT CREATE TABLE TO schema_admin_role;
GRANT CREATE CLUSTER TO schema_admin_role;
GRANT CREATE VIEW TO schema_admin_role;
GRANT CREATE SEQUENCE TO schema_admin_role;
GRANT CREATE TRIGGER TO schema_admin_role;
--- Data warehousing
GRANT CREATE MATERIALIZED VIEW TO schema_admin_role;
GRANT CREATE DIMENSION TO schema_admin_role;
--- Data cartridge
GRANT CREATE OPERATOR TO schema_admin_role;
GRANT CREATE INDEXTYPE TO schema_admin_role;
--- Restrict synonyms to database administrators ?
GRANT CREATE SYNONYM TO schema_admin_role;
The first step in the block creates a tablespace. It is usually a good idea to give each schema its own tablespace with a similar name but not necessary. The statement will look different if you are using ASM or different mount points (of course). Some articles suggest using the "USERS" tablespace and others suggest using common tablespaces. Use whatever your organization has determined to be best practice.
Step two creates a profile for all schema owner users. Ponder's post recommends not using built-in or Oracle delivered stuff. I agree. If it comes from a vendor, do not use it; copy it and then tailor it to your specific needs.
The third step is to create a role. I chose to create two roles: a default role for all sessions and an administrative role used only for administrative tasks. To do administration on objects in the schema would then require the administrative role to be set before constructive or destructive changes took place. The downside to this approach could prohibit certain GUI tools. The upside is slightly better security and one additional barrier to careless individuals that occasional accidently screw something up repeatedly. The default role is needed to create a session and might also be helpful if you are using the Oracle scheduler and defining jobs that run as the schema owner (more on that later).
The last step (four) is to grant privileges to the two roles. The default role needs to create a session. In my opinion, the roles should be able to alter their sessions (this is a potential risk). The administrative role should have the ability to create procedures and create types. Most source and user types can be defined with an "invoker rights clause" that allows the package to execute using the privileges of the user that defined the source or object or using the privileges of the user executing or using the source or object. If doing security through stored procedures, AUTHID DEFINER is usually used when defined by the schema owner to allow the procedure full DML access to any object in the schema (and then execute privileges are given to user roles). Additional privileges are for convenience and the scheduler so define what you think is needed.
Creating schema owner users is a three step process from this point forward (assuming all use the same tablespace). Create the user, grant the roles, and then set the default role.
* Create the user, grant roles, set "owner" role to default
CREATE USER crapbase
IDENTIFIED BY "30charactercomplexpassword"
DEFAULT TABLESPACE crap_data
QUOTA UNLIMITED ON crap_data
GRANT schema_owner_role TO crapbase;
GRANT schema_admin_role TO crapbase;
ALTER USER crapbase DEFAULT ROLE schema_owner_role;
* Create roles the application might use to access data in the schema
CREATE ROLE crapbase_read_role NOT IDENTIFIED;
CREATE ROLE crapbase_full_role IDENTIFIED BY "30charactercomplexpassword";
The first step is to create the user. Use the profile created previously. Set the default tablespace and give unlimited quota on that tablespace else the schema owner will not be able to define objects that require persistence like tables. Most documents recommend leaving the account locked and then unlocking it when maintenance is to be performed.
The second step is to grant the two roles to the new user.
The last step is to alter the user so the administrative role is not a default role and must be set to obtain those privileges.
One optional step would be to create roles used for read only and full access to objects in the new schema. Individual users would be granted these roles as default or not as default and then the application would set the role(s). Instead of read and full, there could be a more elaborate role structure based upon your organizational requirements.
A schema can have jobs defined to it in addition to tables, views and procedures. Jobs can be created by the schema owner or by a user with CREATE ANY JOB privileges. Jobs created by the schema owner user or by other users in the schema are owned by the schema owner user. Jobs execute with the privileges of that user. The schema user account can be locked and jobs will continue to execute. In one of our environments, the default role has the CREATE JOB privilege so a job can create another job based upon data in other tables.
The schema owner user should have a strong password that is changed regularly and should be locked unless needed to define a source or object type with AUTHID DEFINER invoker rights. If someone gets in as schema owner they have full control over all data in that schema. Strict roles should be used to limit access to schema data. Users with administrative roles should be used to create tables, indexes, etc. within the schema.