[BioSQL-l] BioSQL with Oracle 10g

[John Legato]

I am having trouble installing the BioSQL schema under Oracle 10g. I am
running into permission problems.

I have modified BS-defs.sql and created BS-defs-local.sql. I have created
CB_MEMBER,sg_user,sg_loader,sg_admin and cb_user roles, I have not created
any users as I assumed BS-create-all would take care of that.  I have
given biosql_owner SYSDA privs in an attempt to diagnose the problem but
I am still getting permission errors such as:

CREATE PUBLIC SYNONYM SGLD_BIOENTRIES FOR SGLD_BIOENTRIES
*
ERROR at line 1:
ORA-01031: insufficient privileges

I am using biosql checked out from CVS as of today. I suspect I am missing
some permissions I need to set before running BS-create-all, which I am
running as sysdba. I've include BS-defs-local and BS-create-all below.

What have I overlooked?

John

BS-defs-local:
-- where do the datafiles for the tablespaces go
define datalocation='/u02/oradata/coredb'

-- how do you want to name the table tablespace
define biosql_data=SYMGENE_DATA

-- how do you want to name the index tablespace
define biosql_index=SYMGENE_INDEX

-- how to you want to name the LOB tablespace
define biosql_lob=SYMGENE_LOB

-- what is the name of the role enabling all permissions necessary
-- for schema creation
define schema_creator=CB_MEMBER

-- what shall be name and (initial) pwd of the schema owner
define biosql_owner=sgowner
define biosql_pwd=sgbio

-- the user role (usually read-only, on views) to be created for the
schema
define biosql_user=sg_user

-- the upload-permitted role (INSERT permissions for load API views) to be
-- created for the schema
define biosql_loader=sg_loader

-- the admin-permitted role (INSERT, UPDATE, DELETE on most things) to be
-- created for the schema
define biosql_admin=sg_admin

-- the base role you have for users connecting to the database
define base_user=cb_user

-- load definitions
@BS-defs-local

-- 1) login as DBA
connect sysdba/password as sysdba

-- 2) create the tablespaces
@BS-create-tablespaces

-- 3) create the schema user
@BS-create-schema-user

-- 4) Now we're ready to create our own schema. Connect as the schema
owner.
connect &biosql_owner/&biosql_pwd

-- 5) create the schema
@BS-DDL

-- 6) create the PL/SQL package API and the load API
@BS-create-API

-- 7) create select-views
@BS-create-views
-- 8) Security: create roles and synonyms, issue grants
@BS-create-roles
@BS-create-synonyms
@BS-grants

-- 9) create additional users
--connect &sysdba/&dbapwd as sysdba
-- at BS-create-users
--connect &biosql_owner/&biosql_pwd

-- 10) pre-populate database as necessary
-- Note: there is a high chance that the seed data is not suitable for you
-- or is not exactly what you want. Check out the script and make sure you
-- really want the seed data, possibly after editing it, before you
uncomment
-- the following command.
--
-- at BS-prepopulate-db
----------------------------