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.

Wednesday, June 10, 2009

Sleep

I have trouble getting to sleep. I have had this problem for years. It was never a problem until my new neighbors moved into the apartment behind mine and then began having sex at all hours of the night and morning. There is nothing worse than awakening from a decent sleep to the sound of a dog whimpering in heat. The situation changed as I began living with someone. She could fall asleep in minutes while it took me sometimes up to an hour. Of course, she snored. It was hard to sleep with an outboard motor next to me so I would simply escape to the relative silence of the couch with the nearby aquariums. I tried numerous things to help me get to sleep faster, here are the results.

Over-the-counter sleep aids do not work very well with me. Most are just a double dose of Benadryl (diphenhydramine). If it did work, great. If it did not or I was awakened during the night I would spend hours in a haze.

I tried herbal supplements like melatonin and Valerian root. Neither supplement helped me get to sleep. The valerian sometimes had the same effect the diphenhydramine would have. I tried Valerian in capsule form and in Sleepy Time Extra tea. I also tried zinc supplements via tablets and also through ZMA. There were no long term effects.

I tried limiting and eliminating caffeine. That would make a short term difference but nothing would happen long term (more than a couple days). Caffeine intake did not affect how long it took to get to sleep. I also tried varying my exercise and as with the caffeine, no long term changes.

I tried turkey, eating a banana, and drinking a protein shake. None of them worked.

I tried reading before bed and that did not work.

I tried different pillows including full sized, wheat husk and memory foam but there was really no change.

I tried laying flat on my back on a hard floor. I tried cooling my wrists under running water.

There was only one thing that seemed to have benefit: background noise. I had a noisy desk fan I would use to provide background noise. My girlfriend at that time hated that fan. There were times where the fan would be louder than normal or would make squeaking noises as it got older. I would have to take it apart and clean in. My current love of my life has a white noise generator that we use on the “night” setting to get a constant sound of crickets in a field or something like that.

My fiancee also has problems getting to sleep. She has taken Ambien (Zolpidem), Lunesta (Eszopiclone) and ?. She had a prescription for the Ambien and samples of the Lunesta and suggested that I try both and see what happens.

Both drugs were very effective. I could get to sleep regardless of noise from snoring or a television. I stayed asleep, and was able to awaken in time for work.

The Ambien was too effective. Apparently I received an alert on my work phone at around three in the morning, got up, acknowledged the message and then went back to sleep. I do not remember that ever taking place. Once on Ambien I could not get off Ambien without having a really bad night or two or five. Since I was borrowing from my fiancee's prescription and not remembering alerts from work I had to take the Ambien off my list.

The Lunesta was not addictive and I was able to leave the drug, have trouble falling asleep as usual, but still get sleep. The Lunesta left an awful taste in my mouth but I prefer sleep over funny tasting water any night. At this time, there is no generic equivalent for Lunesta.

I went to my physician and had him write a prescription for the Lunesta. I have been taking the Lunesta for a week now in half doses (cutting the pill in half). Sleep has been more than adequate so the drug is doing the job I am asking it to do. Eventually, I want to only use the drug from time to time when I need it (usually Sunday nights). Our feline inhabitants have slightly altered sleeping arrangements in the Grouchy household so the couch and spare bedroom are not realistic options. Eventually the spare bedroom will be available but until then I will probably continue taking the Lunesta when I feel I will need to.

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)
*/
CREATE SMALLFILE TABLESPACE crap_data
LOGGING
DATAFILE
'/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
CPU_PER_CALL UNLIMITED
CONNECT_TIME UNLIMITED
IDLE_TIME UNLIMITED
SESSIONS_PER_USER UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
PRIVATE_SGA UNLIMITED
COMPOSITE_LIMIT UNLIMITED
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_GRACE_TIME UNLIMITED
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL;
/*
* Step 3: create roles
*/
CREATE ROLE schema_owner_role NOT IDENTIFIED;
CREATE ROLE schema_admin_role NOT IDENTIFIED;
-- 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 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
PROFILE schema_owner_profile
IDENTIFIED BY "30charactercomplexpassword"
DEFAULT TABLESPACE crap_data
QUOTA UNLIMITED ON crap_data
ACCOUNT LOCK;
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.

Security

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.

Friday, June 05, 2009

Grouchy gripe day today

I have a couple things I would like to get off my chest this morning.

One. I cannot stand the pseudo techno-intellectuals that make statements based on superior hardware and then assume everything applies downward. Windows 7 runs great on my Intel Core i7 Extreme Edition with 12 GB of DDR3 1866 memory and two Western Digital Raptors in a RAID 0 configuration. No ****? Startup time is fantastic. Well, it should be. You have hardware that God has to wait in line for and everything runs smooth and performance is fantastic. Just because your newest version of Windows or Linux runs incredibly fast on your mega box it does not mean it will perform just as well on a more standard machine given a one or two year upgrade time frame.

Two, I cannot stand web sites that cram every unnecessary Web 2.0 component on their home pages. What works well on your developer workstation with its Core 2 Quad Extreme with 16GB of memory might not work as well on my AMD 754 socket processor and 1 GB of memory or my netbook with its hamster sized screen, Atom processor and 2GB of memory. I just drug my parents kicking and screaming out of the Windows 98 world where processors were named by number and their megahertz clock speed into the land of Windows XP and processors named after sissy, woodland creatures. They do not give a rat's ass if your tag cloud rotates or if that multi-gigabyte, centralized news / ad / feature thing in the middle of your page looks good. They would be more interested in a dancing baby or Jesus.

Sorry. Today I offer zero solutions, only gripes.