[BioSQL-l] BioSQL with Oracle 10g

[Hilmar Lapp]

On Aug 24, 2004, at 12:01 PM, John Legato wrote:

>
> 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

Creating public synonyms needs a special privilege. Execute

	SQL> GRANT CREATE PUBLIC SYNONYM TO <whatever your schema owner is>;

as SYS/SYSDBA. (according to your BS-defs-local, SGOWNER is the schema 
user; if you have granted this user a specific role reserved for users 
that create and own schemas [which is what CB_MEMBER stands for here], 
you can also put the name of that role instead. note that you need to 
re-connect for this to take effect.)

>
> 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.

You should not be running this as sysdba. Run it as the designated 
schema owner. If you uncomment the tablespace creation script then it 
will connect as SYS first.

The command you quoted above should actually read

	 CREATE PUBLIC SYNONYM SGLD_BIOENTRIES FOR SGOWNER.SGLD_BIOENTRIES

Check the dynamically created file _syns.lst for whether the schema 
owner got substituted in here.

	-hilmar

-- 
-------------------------------------------------------------
Hilmar Lapp                            email: lapp at gnf.org
GNF, San Diego, Ca. 92121              phone: +1-858-812-1757
-------------------------------------------------------------