Advanced Security Option Gotcha

Here is nice little licensing anomaly. Advanced Security Option (ASO) implements, among other things the 'identified globally' clause of 'create user'. It is an Enterprise Edition option, and an EE option generally means big bucks.

However it looks as if using 'identified externally' with Windows domain accounts counts as 'identified globally'.

select name,
       currently_used, 
       last_usage_date 
from   dba_feature_usage_statistics 
where  name like 'Advanced Security';

NAME               CURRE LAST_USAGE_DATE
------------------ ----- --------------------
Advanced Security  FALSE 19-MAY-2007 01:03:06

create user MYDOMAIN\JIM identifed externally;

You now have to wait for the feature audit job to run to pick up the new feature. The job appears to run weekly. If you know how to force a feature audit, please let me know.

Once the job has run, if you rerun the query, you can see that the Advanced Security Option is enabled, and you could be in breach of your license.

select name,
       currently_used, 
       last_usage_date 
from   dba_feature_usage_statistics 
where  name like 'Advanced Security';

NAME               CURRE LAST_USAGE_DATE     
------------------ ----- --------------------
Advanced Security  TRUE  26-MAY-2007 23:44:07 

My current client has only a few databases on windows, and does not generally use external authentication, but each windows server has a couple of service accounts for backups and other operations and these are normally externally authenticated domain accounts.

Beware.

Symantec releases Veritas Storage Foundation as freeware

Symantec have release a version of Veritas Storage Foundation as freeware. It seems to be aimed at small businesses running small servers as it is limited to creating 4 volumes / file systems and will only run on 2 processor machines.

It is available for Solaris 8,9,10 (SPARC), Solaris 10 (x64), AIX 5.2,5.3, RHEL4 (XEON/EM64T/Opteron/Itanium 2), SLES 9,10 (XEON/EM64T/Opteron/Itanium 2) and Windows 2000 and 2003.

This is good news for small companies as the licence cost of Storage Foundation used to be a significant overhead. If you are buying commodity hardware and putting a free operating system on it, then the $1000 or so SF used to cost is significant.

It is also good news for me as it means that my home lab set up can now include even more software I am likely to use at client sites.

More on this when I have tried it out.

OTN Single sign-on is forever

I've recently given up an old email address which I no longer used, except as my contact point for OTN. This meant that I also had to give up my OTN account because the security software for OTN does not let you change your email address. It doesn't matter that much, but it means I have lost all my brownie points and my little gold medal for being an active user.

It is very annoying however. The forum software they use allows email addresses to be changed out of the box but they have modified it to disable this. I thought at first it was part of the move to a single sign-on approach to bring it in to line with metalink where your email address is your userid. But no, metalink allows you to change your email address even though it your userid.

And don't get me started on the fact that you can't change your password either.

The "Editor-in-Chief" of OTN says it is out of his hands.

Tags:

• oracle
• otn

Fear of 11i

I'm not looking forward to 11i.

I'm sure there will be all sorts of exciting new features designed to make me redundant, but the main reason is that I'll have to upgrade my lab machines.

Each new version has increased the minumum hardware requirements, but it is usually possible to run Oracle below the minimum requirements if all you are doing is single user experimentation. There are exceptions though. I tried to run Grid Control on a machine with 512MB of RAM (the recommended minimum is 1024MB) and I spent more time dealing with OC4J crashes than tring out grid control.

Most of my lab machines are so old that they are not worth upgrading - either they are at the maximum RAM or the RAM is no longer readily available.

When I finally bite the bullet, I will probably invest in something big and state of the art which can support at least 2 VMWare virtual machines concurrently.

Tags:

• oracle
• vmware
• virtualisation

Using CVS with SQLDeveloper

This entry describes a very simple implementation of CVS as an external tool in Oracle's SQLDeveloper database ide. Integrated version control is promised for later releases, but until then an external tools implementation is the best that can be done.

The external tools interface is new in version 1.1 which is currently going through a preview cycle. There are limitations in the external tools interface which make it difficult to work with CVS in the way I would like to.

The external script

This is a windows batch file.
All it does is change directory to the source file directory and issue a commit.

@echo off
pushd %1
cvs commit -m%3 %2
popd

The external tool definition

1. Goto Tools > External Tools
   
   If this is the first time you have used the dialog, you will be prompted to set up some automatic definitions.
   
2. Click on New and fill in the fields as follows
   
   
   
   • Program Executable:  <full path of batch file>
     
   • Arguments: ${file.dir} ${file.name} "${promptl:label=Checkin comment} "
     
     The quotes round the final parameter are necessary because if can contain spaces. If your pathnames or file names contain spaces, the other parameters will need to be quoted too.
     
   • Run Directory: <directory containing batch file>
     
   
   
3. Click Next
   
   As of build 2197 there are a couple of error dialogs to click through at this stage. They don't seem to affect the functionality.
   
4. Fill in the display options as follows
   
   
   
   • Caption for Menu Items: C&VS commit
     
     This will set V as the accelerator for menu options.
     
   • ToolTip Text: Commit the current file to CVS
     
   • Icon Location: (Leave as default)
     
   
   
5. Click Next
   
6. Fill in the Integration options as appropriate. I haven't experimented with these, but you should have at least the tools menu selected. You should probably also check the Reload Open Files box.
   
7. Fill in the display options as follows
   
   
   
   • Add items to Menus: 
     
     
     • Tools Menu - checked
       
     • Navigator Context menu - unchecked
       
     • Code Editor Context Menu - checked¹
       
     
     
   • Add Buttons to Toolbars: 
     
     Main toolbar - Unchecked
     
     
   • After Tool Exits: 
     
     • Reload Open Files - Checked.
       
       This should ensure that if the file is modified during the commit (by keyword expansion for example), the new version is loaded into SQLDeveloper. NB. As of version 1.1.0.21.97, this causes the buffer to be closed
       
     
     
   
   
8. Click Next
   
9. File in the Availability options as follows.
   
   • Enabled: When specific file types are selected.
     
   • Selected types: 
     
     
     • SQL Script
       
     • Text Document
       
     • PlSqlNode¹
       
   
   
10. Click Finish
    

Note 1. These options will make the menu option available when editing PL/SQL in the database as well as in source files.

Limitations

• Only works on one file at a time
  
• Only works on FILES, not other object nodes.
  
• No access to the database. In the case of PL/SQL files I would like to be able to resubmit them to the database after checkin (so that the checkin comments and version numbers are up to date in the database. The external tools interface doesn't reliably provide connection details to allow this.
  

Modifications

• You can replace the commit command with other cvs commands, or you can make the cvs command a prompted parameter.
  
• You can replace cvs with another version control tool.
  

What is my database called? (Part 1)

Naming oracle databases - Part 1
There are many names which can be considered the database name, depending on the point of view.

• DBID - system generated database identifier
  
• SID - System IDentifier
  
• DB_NAME - The real name of the database.
  
• GLOBAL_NAME - combination of DNAME and DBDOMAIN
  
• DB_UNIQUE_NAME - used in data guard to distinguish between copies of the same database
  
• SERVICE - the 'published' name under which end users would access a databas
  

DBID

This is not strictly a name, but is the one absolutely constant identfier for a database. It is generated when you create a database and if you are lucky you will never see it again. It becomes important if you have to restore a database on to a clean machine. You can find the DBID by querying V$DATABASE.

SID

This is the system - or instance - identifier. The instance is the set of processes which manage the database. The SID is used as the environment variable ORACLE_SID and is key to database admin tasks such as starting and stopping the database. You specify the SID when you create the instance.

DB_NAME

This IS the name of the database, but for most purposes is largely irrelevant. You never connect to a database, you connect to an instance or a service. You specify the db_name when you create the database by setting the DB_NAME initialisation parameter. The DB_NAME can be different from the instance name, but (except in the case of a RAC setup) there is no real reason to make it different.

SERVICE

This is where it starts to get complicated. The service is the public face of the database and is what clients connect to. The complexity arises because the service name can be defined in 3 different places - in the database; in the listener; and in the client.

In the database

Since Oracle8i, a running instance publishes its availability by registering with a listener. The SERVICE_NAMES initialisation parameter defines the name(s) to be registered. The default is the same as SID.

In the listener

You can override the self-registered name by added service entries to the listener configuration LISTENER.ORA.

SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=BLOG)
(SID_NAME=ODBUAD1)
(ORACLE_HOME=/opt/oracle/10.2.0/db_1)))

This entry creates a "service" called BLOG, which uses the ODBUAD1 instance. The output from "lsnrctl status" shows this service and the self-registered service "ODDBUAD1"

C:\var\logs>LSNRCTL status

LSNRCTL for 32-bit Windows: Version 10.2.0.2.0 - Production on 23-NOV-2006 11:58:47

Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mercury)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc)))
Services Summary...
Service "BLOG" has 1 instance(s).
Instance "ODBUAD1", status UNKNOWN, has 1 handler(s) for this service...
Service "ODBUAD1" has 1 instance(s).
Instance "ODBUAD1", status READY, has 1 handler(s) for this service...
The command completed successfully

In the client

The services a client can connect to are defined in TNSNAMES.ORA. A typical tnsnames entry looks like this

BLAG =
(DESCRIPTION =
(ADDRESS_LIST =
  (ADDRESS = (PROTOCOL = TCP)(HOST = dbserver)(PORT = 1521))
)
(CONNECT_DATA =
  (SERVICE_NAME = BLOG)
)
)

This entry defines a "service" called BLAG, which connects to the service defined on the server as BLOG (which in turn uses the database ODBUAD1).

While it is possible to have several different names in several different places for the same database, and multiple names sometimes have their uses, for ease of management it is generally best to keep all the names the same. In a non-RAC environment the main use for different names is to isolate users from changes in the backend.

Creating a Schema Owner

What is a schema?

A schema is a collection of database objects (tables, views, stored procedures etc) owned by a single user. 10g Concepts Manual defines it here. Typically, all of an applications objects will be in a single schema and application users will access those objects through a combination of grants and synonyms.

When to use a schema.

As a rule, each application should be in its own schema. If you are used to Microsoft SQL Server or some other databases, when you would want to create a new database in a server, in Oracle you would create a schema.

The ANSI 'CREATE SCHEMA' statement.

The ANSI SQL standard includes a 'CREATE SCHEMA' statement which in theory allows the creation of all the objects in schema in a single statement. In practice it is useless because (at least as implemented by Oracle) it limits you to ANSI standard syntax within the statement. This means that any oracle extensions to the ANSI standard (for example, storage options) can not be used. Steer well clear.

Creating the schema owner.

The main difference between a schema owner and an application user is that the schema owner requires system privileges (such as create table) whereas an application user only requires object privileges (such as select, insert,delete on a table).

The short method.

The simple way to do this is as follows

C:\>sqlplus "sys/********@xe as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on Sun Nov 19 10:18:39 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

sys@XE> CREATE USER BLOG IDENTIFIED BY BLOG;

User created.

sys@XE> GRANT CONNECT,RESOURCE TO BLOG;

Grant succeeded.

This has the merit of simplicity, but not much else.
The problems with this are -

• The RESOURCE role includes the "unlimited tablespace" privilege which means that the schema owner can create objects in any part of the database, including the system tablespace. While the DBAs who will be creating the schema objects know that you shouldn't create objects just anywhere, accidents will happen.
  
• As currently defined (in 9i and 10g), the CONNECT and RESOURCE roles combined do not include all the system privileges which might be needed. If I try to use the user created above...
  
  

  
  sys@XE> CONNECT BLOG/BLOG@XE
  Connected.
  sys@XE> SELECT * FROM SESSION_PRIVS;
  
  PRIVILEGE
  ----------------------------------------
  CREATE SESSION
  UNLIMITED TABLESPACE
  CREATE TABLE
  CREATE CLUSTER
  CREATE SEQUENCE
  CREATE PROCEDURE
  CREATE TRIGGER
  CREATE TYPE
  CREATE OPERATOR
  CREATE INDEXTYPE
  
  10 rows selected.
  
  sys@XE> CREATE TABLE X ( COL1 NUMBER(10) );
  
  Table created.
  
  sys@XE> CREATE VIEW Y AS SELECT * FROM X;
  CREATE VIEW Y AS SELECT * FROM X
            *
  ERROR at line 1:
  ORA-01031: insufficient privileges
  
  

  
  The select from SESSION_PRIVs shows the privileges which are currently active and are the union of the privileges in the granted roles.
  
  This particular example would have worked in 9i because the connect role included the CREATE VIEW privilege, but there are other privileges which are missing.
  
  This table lists the system privileges which a schema owner might need, with those provided by the connect and resource roles highlighted.
  

  
  

  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  

  ALTER SESSION2

  CREATE CLUSTER12

  CREATE DATABASE LINK2

  CREATE EVALUATION CONTEXT

  CREATE EXTERNAL JOB

  CREATE INDEXTYPE1

  CREATE JOB

  CREATE LIBRARY

  CREATE MATERIALIZED VIEW

  CREATE OPERATOR1

  CREATE PROCEDURE1

  CREATE PUBLIC DATABASE LINK

  CREATE PUBLIC SYNONYM

  CREATE RULE

  CREATE RULE SET

  CREATE SEQUENCE12

  CREATE SESSION

  CREATE SYNONYM2

  CREATE TABLE12

  CREATE TRIGGER1

  CREATE TYPE1

  CREATE VIEW2

  1 Granted as part of RESOURCE role

  2 Granted as part of CONNECT role prior to 10gR2

  
  
  Note that (apart from ALTER SESSION), no ALTER <OBJECT> or DROP <OBJECT> privileges are required. An object owner can always drop or alter its own objects.
  
• In general, it is not a good idea to use system defined roles. The vendor is free to change the definition of the role at any time which could break your applications, or leave you exposed to an unknown risk. With the release of Oracle 10g, oracle drastically changed the definition of the CONNECT role. The original definition included privileges beyond those needed to simply connect and the change was a long overdue improvement. However, unless you were aware of the change, your upgrade could have gone badly wrong.
  

The right method.

There are three steps to creating the schema owner.

1. Create the user
   

   
   sys@XE> create user blog identified by blog;
   
   User created.
   

   
   
   At this stage, the user exists, but has no privileges (not even the right to connect to the database) and no resources.
   
   
2. Specify which resources it can use.
   When setting up an oracle user, you can specify what storage it can use, using tablespace quotas, and what machine resources (cpu, clock time etc) using profiles. I won't cover profiles here in any detail here. The tablespaces required should be created in advance.
   

   
   sys@XE> alter user blog
   2  temporary tablespace temp       -- specify which temp tablespace to use
   3  default tablespace blogdata1    -- ensure uncontrolled creates go somewhere safe
   4  quota unlimited on blogdata1    -- allow the creation of objects in a tablespace
   5  quota unlimited on blogdata2    -- there can be as many tablespaces here as you like.
   6  quota unlimited on blogdata3
   7  profile schemaowner_profile;     -- security and resource control
   
   User altered.
   
   

   
   
3. Grant the privileges required.
   
   You can either grant the required privileges directly to the user;
   

   
   sys@XE> grant create session to blog;
   
   Grant succeeded.
   
   sys@XE> grant create table to blog;
   
   Grant succeeded.
   
   sys@XE> grant create view to blog;
   
   Grant succeeded.
   

   
   
   or you can create a role, grant the privileges to the role and then grant the role to the user.
   

   
   sys@XE> create role schema_owner;
   
   Role created.
   
   sys@XE> grant create session to schema_owner;
   
   Grant succeeded.
   
   sys@XE> grant create table to schema_owner;
   
   Grant succeeded.
   
   sys@XE> grant create view to schema_owner;
   
   Grant succeeded.
   
   sys@XE> grant schema_owner to blog;
   
   Grant succeeded.
   

   
   
   What this does is create your own customised and more useful version of the resource role. A usable sample script is here
   
   When to use the schema owner.
   
   The schema owner account should only ever be used to create and modify the application objects. Ideally it should be locked in when not required and only unlocked as part of your change control procedures.
   

Summary.

1 A schema is a collection of objects owned by a single database user.
2 The schema owner requires physical resources and system privileges
3 The default roles provided by Oracle do not provide all the required controls and privileges

Books on a Palm

Alternatively, Books are a Pain.

There are a lot of oracle manuals (the 10gR2 database set alone has 225 pdf files), and the only practical way to read them is on a computer. Oracle will sell you hard copy manuals but they are £40 each which makes £9000 for the set. Plus the extension you will have to build to store them - at an average of an inch thick they will occupy almost 20 feet of shelving. Given all that, its no longer practical to keep up by reading a manual on the train to work or when you get a few minutes break.

If you have a Palm pilot or other PDA you can try reading the pdfs on that, but frankly the pdf viewer for Palm is rubbish and gives you about one line per screen.

Plucker is a brilliant alternative. It is designed to download websites and convert them to a Palm readable document, but you can use it to convert the html versions of the documentation. I haven't worked out the best way to deal with diagrams yet (who needs pictures anyway, we are not sql server types), but it is the most readable thing I have found in ages.

Tags:

• oracle
• plucker
• palm
• pdf