Friday, June 19, 2009

Afternoon at the library

I really wish I knew how to use the 1.3 mega pixel camera on my netbook. I was at a public library today, killing an hour or two waiting until my sweetheart was off work. You know how libraries are supposed to be quiet places where people can read or study or blog without interruption and annoyance. Two problems with that.

The first is a teenage kid sitting about six feet from the sign clearly stating to refrain from using cellular phones and setting them to silent. He has been on the phone on the same call for fifty minutes while also playing on a Playstation portable. So it's "hmm. Yeah. Mumble mumble. Yeah. Umm. Mumble. The game... Yeah. Um. (voice on phone is louder than him at this point). Yeah. Yeah. Muh. Yeah. Mumble. Mumble. Broken English. Mumble. A'ight. Mumble. Yeah. Yeah. No. No. That wasn't me. Mumble. Mumble. She don't know. Laugh. Mumble. Ah yes. Yeah. No. Mumble. Mumble. Yeah. Uh huh. Nuh-huh. Yeah. Mumble mumble mumble. You get bored. Mumble. I can have that. Blah blah blah. Heh heh heh." over the clackity-clack of whatever he was playing.

Second was the one year old bouncing off everything like a pinball and squealing at the top of his/her lungs. There is no parental guidance to be found. The kid will make a pretty good running back when he or she grows up.

"Mumble. Yeah. A'ight. I just wanna go. Yeah. Yeah. Mumble mumble. Yeah. Umm. Mumble. Yeah. Um. No. Yeah. Yeah. Muh. Yeah. Mumble. Mumble. Broken English. Mumble. A'ight. Mumble. Yeah. Yeah. No. No. That wasn't me. Mumble. Mumble. Whatever she do is okay. Mumble. Yeah."

I am wondering to myself. If I grab the lamp next to me and proceed to bash this individual's skull in would a) the police be called and I hauled off to jail for murder, b) I be loudly applauded until the librarian shushes everyone, or c) I silence the entire building. I can live with the last two. So those odds are 66/33 that I will be in a happier state after clubbing this dude. Tempting.

Finally. After the 64 minute mark the douche bag with the phone hung up and left. The child is getting tired and cranky but quieter if you can believe that.

Tuesday, June 09, 2009

The Oracle schema owner user

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)
'/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
* Step 2: create a profile
CREATE PROFILE schema_owner_profile
* Step 3: create roles
-- or
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
--- Scheduler
GRANT CREATE JOB TO schema_owner_role, schema_admin_role;
--- Convenience
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 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
PROFILE schema_owner_profile
IDENTIFIED BY "30charactercomplexpassword"
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.

Oracle Scheduler

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.