• Created by Unknown User (fackler), last modified on Feb 06, 2024

Future change - This whole process can be templated. Once a copy of the base schema is created: import it into place via the data pump facility; reset credential; apply grants--should simplify this process.

1. Create the project owner schema

On shore the Oracle schema account is created by the DBA or the IT Ops Manager.
On ship this account is created by an application developer using their DBA account.

This schema account must use the pattern

GEODPnnn

  • nnn generally designates an expedition. 
  • nnn may be replaced by characters denoting a special-purpose project, examples: 397T, _TST360, TEACHERSATSEA
  • The prefix GEODP is required. Coding of the several GEODESC applications depend on this prefix to reference project-specific content.
  • The TEACHERSATSEA example project code/name is excessive. Some of our Oracle instances may still be configured to support the pre-Oracle 12c limit of 30-character object names.
    For now, be brief.

Account password

  • When the account is created, a password is established for the account.
    Please record these credentials in the local development password safe.
  • The credential is not used by any applications--the use of GRANTS ensures the WRITER schema has all the proxy access it needs: select, insert, update, delete.
    Make it as long, complicated, and obtuse as you like (not really--Oracle limits credentials to 30 bytes). We'll only cut and past it the few times we need it.

SQL script to establish the GEODESC project user

Before running this script, please

  • replace ### with the appropriate project code/name
  • supply (and record) the selected stored-secret


create user geodp### identified by "stored-secret-30-bytes" profile long_password;
alter user geodp###
default tablespace geodesc
temporary tablespace temp
quota unlimited on geodesc
quota unlimited on geodescidx
;

grant create session to geodp###;
grant create sequence to geodp###;
grant create table to geodp###;
grant create trigger to geodp###;


Historical Note

During initial development, these roles, privileges, and storage configurations were applied.

  • Note that the above are sufficient for operational use.
  • Note that storage is moved to dedicated tablespaces (geodesc, geodescidx) for operational use.
  1. Granted Roles:
    1. Connect
  2. System Privileges:
    1. Advisor
    2. Alter session
    3. Create Credential
    4. Create Database Link
    5. Create Job
    6. Create Procedure
    7. Create Public Synonym
    8. Create Sequence
    9. Create Session
    10. Create Synonym
    11. Create Table
    12. Create Trigger
    13. Create Type
    14. Create View
    15. Create Any Procedure
    16. Debug Connect Session
    17. Drop Public Synonym
    18. Select Any Dictionary
    19. Select Any Table
  3. Quotas
    1. GEODESC
    2. GEODESCIDX

2. Run DDL to create project tables, sequences, and triggers

Use any of SQL Developer worksheet, SQLcl, or SQL*PLUS environments to

  • Log into the account just created.
    • It has just enough privilege to run the DDL provided.
    • It should not be run as your DBA user or via some other account.
    • The script is written to do the right thing only when logged in as the schema created above.
  • Run the DDL below to establish the structure for a new GEODESC project.


Oracle DDL for a GEODESC project (aka GEODPnnn) 
/* Establish the schema account first.
   GEODESC project schemas own DB objects, therefore must have the privileges to manage them
*/
-- Examples: GEODP397, GEODP398, GEODPTST360, GEODP_TST123, GEODP999, etc.
define xschema=GEODP395

/* Sequences */
CREATE SEQUENCE &&xschema..P_COLUMNS_COLKEY_SEQ MINVALUE 0 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE GLOBAL ;
CREATE SEQUENCE &&xschema..P_TEMPLATES_TPKEY_SEQ MINVALUE 0 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE GLOBAL ;
CREATE SEQUENCE &&xschema..P_WORKINGSET_DATA_ROWKEY_SEQ MINVALUE 0 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE GLOBAL ;
CREATE SEQUENCE &&xschema..P_WORKINGSET_WSKEY_SEQ MINVALUE 0 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE GLOBAL ;


/* Tables, constraints, triggers, indexes */
--------------------------------------------------------
--  DDL for Table P_COLUMNS
--------------------------------------------------------
CREATE TABLE &&xschema..P_COLUMNS
( "COLKEY" NUMBER(38,0), 
  "TPKEY" NUMBER(38,0), 
  "TYPE" VARCHAR2(20 BYTE), 
  "HEADER" VARCHAR2(200 BYTE), 
  "JSONHEADER" VARCHAR2(4000 BYTE), 
  "JSONDISPLAY" VARCHAR2(4000 BYTE), 
  "JSONTMLOCKS" VARCHAR2(1000 BYTE), 
  "JSONCAPLOCKS" VARCHAR2(4000 BYTE), 
  "SORTORDER" NUMBER(4,0), 
  "IGNORE_FLAG" VARCHAR2(5 BYTE), 
  "INVALID_FLAG" VARCHAR2(5 BYTE), 
  "JSONKEYWORD" VARCHAR2(4000 BYTE), 
  "COMMENTS" VARCHAR2(1000 BYTE), 
  "JSONENTRY" CLOB
) TABLESPACE "GEODESC" 
  LOB ("JSONENTRY") STORE AS SECUREFILE (TABLESPACE "GEODESC")
;

COMMENT ON COLUMN &&xschema..P_COLUMNS.HEADER IS 'unique within project';
COMMENT ON COLUMN &&xschema..P_COLUMNS.JSONCAPLOCKS IS 'locks used in capture sheet as defined in TM';

--------------------------------------------------------
--  DDL for Index P_COLUMNS_PK
--------------------------------------------------------
CREATE UNIQUE INDEX &&xschema..P_COLUMNS_PK ON &&xschema..P_COLUMNS ("COLKEY") 
TABLESPACE "GEODESCIDX" ;

--------------------------------------------------------
--  DDL for Trigger P_COLUMNS_COLKEY_TRG
--------------------------------------------------------
CREATE OR REPLACE EDITIONABLE TRIGGER &&xschema..P_COLUMNS_COLKEY_TRG 
before insert on &&xschema..P_COLUMNS
for each row 
  WHEN (new.colkey < 0 or new.colkey is null)
begin
  select &&xschema..P_COLUMNS_COLKEY_SEQ.nextval into :new.colkey from dual;
end;
/
ALTER TRIGGER &&xschema..P_COLUMNS_COLKEY_TRG ENABLE;

--------------------------------------------------------
--  Constraints for Table P_COLUMNS
--------------------------------------------------------
ALTER TABLE &&xschema..P_COLUMNS ADD CONSTRAINT "P_COLUMNS_JSONENTRY" CHECK (JSONENTRY IS JSON) ENABLE;
ALTER TABLE &&xschema..P_COLUMNS MODIFY ("COLKEY" NOT NULL ENABLE);
ALTER TABLE &&xschema..P_COLUMNS ADD CONSTRAINT "P_COLUMNS_PK" PRIMARY KEY ("COLKEY") USING INDEX &&xschema..P_COLUMNS_PK  ENABLE;
ALTER TABLE &&xschema..P_COLUMNS ADD CONSTRAINT "P_COLUMNS_JSONHEADER" CHECK (JSONHEADER IS JSON) ENABLE;
ALTER TABLE &&xschema..P_COLUMNS ADD CONSTRAINT "P_COLUMNS_JSONDISPLAY" CHECK (JSONDISPLAY IS JSON) ENABLE;
ALTER TABLE &&xschema..P_COLUMNS ADD CONSTRAINT "P_COLUMNS_JSONKEYWORD" CHECK (JSONKEYWORD IS JSON) ENABLE;
ALTER TABLE &&xschema..P_COLUMNS ADD CONSTRAINT "P_COLUMNS_JSONCAPLOCKS" CHECK (JSONCAPLOCKS IS JSON) ENABLE;
ALTER TABLE &&xschema..P_COLUMNS ADD CONSTRAINT "P_COLUMNS_JSONTMLOCKS" CHECK (JSONTMLOCKS IS JSON) ENABLE;
ALTER TABLE &&xschema..P_COLUMNS MODIFY ("TYPE" NOT NULL ENABLE);
ALTER TABLE &&xschema..P_COLUMNS MODIFY ("HEADER" NOT NULL ENABLE);
ALTER TABLE &&xschema..P_COLUMNS MODIFY ("TPKEY" NOT NULL ENABLE);


--------------------------------------------------------
--  DDL for Table P_DATA
--------------------------------------------------------
CREATE TABLE &&xschema..P_DATA 
( "TPKEY" NUMBER(38,0), 
  "LIMSHOLE" NUMBER(38,0), 
  "CORE" VARCHAR2(20 BYTE), 
  "CHANGED_ON" TIMESTAMP (6), 
  "CHANGED_BY" VARCHAR2(100 BYTE), 
  "JSONDATA" CLOB
) TABLESPACE "GEODESC" 
  LOB ("JSONDATA") STORE AS SECUREFILE ( TABLESPACE "GEODESC" )
;

--------------------------------------------------------
--  DDL for Index P_DATA_PK_IDX1
--------------------------------------------------------
CREATE INDEX &&xschema..P_DATA_PK_IDX1 ON &&xschema..P_DATA ("LIMSHOLE", "CORE") 
TABLESPACE "GEODESC" ;

--------------------------------------------------------
--  DDL for Index P_DATA_PK1
--------------------------------------------------------
CREATE UNIQUE INDEX &&xschema..P_DATA_PK1 ON &&xschema..P_DATA ("TPKEY", "LIMSHOLE", "CORE") 
TABLESPACE "GEODESC" ;

--------------------------------------------------------
--  Constraints for Table P_DATA
--------------------------------------------------------
ALTER TABLE &&xschema..P_DATA MODIFY ("TPKEY" NOT NULL ENABLE);
ALTER TABLE &&xschema..P_DATA MODIFY ("LIMSHOLE" NOT NULL ENABLE);
ALTER TABLE &&xschema..P_DATA MODIFY ("CORE" NOT NULL ENABLE);
ALTER TABLE &&xschema..P_DATA MODIFY ("JSONDATA" NOT NULL ENABLE);
ALTER TABLE &&xschema..P_DATA ADD CONSTRAINT "JSONDATA_JSON_CONSTRAINT" CHECK (JSONDATA IS JSON) ENABLE;


--------------------------------------------------------
--  DDL for Table P_LIST_VALUES
--------------------------------------------------------
CREATE TABLE &&xschema..P_LIST_VALUES 
( "COLKEY" NUMBER(38,0), 
  "FIRSTORDER" VARCHAR2(100 BYTE), 
  "SECONDORDER" VARCHAR2(100 BYTE), 
  "THIRDORDER" VARCHAR2(100 BYTE), 
  "AVALUE" VARCHAR2(100 BYTE), 
  "COMMENTS" VARCHAR2(4000 BYTE), 
  "RANK" NUMBER(3,0), 
  "SORTORDER" NUMBER(4,0), 
  "VLKEY" NUMBER(38,0)
) TABLESPACE "GEODESC" ;

--------------------------------------------------------
--  Constraints for Table P_LIST_VALUES
--------------------------------------------------------
ALTER TABLE &&xschema..P_LIST_VALUES MODIFY ("SORTORDER" NOT NULL ENABLE);
ALTER TABLE &&xschema..P_LIST_VALUES MODIFY ("COLKEY" NOT NULL ENABLE);


--------------------------------------------------------
--  DDL for Table P_TAXA
--------------------------------------------------------
CREATE TABLE &&xschema..P_TAXA 
( "COLKEY" NUMBER(38,0), 
  "TAXKEY" NUMBER(38,0), 
  "FOSSIL_GROUP" VARCHAR2(100 BYTE), 
  "GENUS" VARCHAR2(200 BYTE), 
  "SUBSPECIES_MOD" VARCHAR2(20 BYTE), 
  "SPECIES" VARCHAR2(200 BYTE), 
  "SPECIES_MOD" VARCHAR2(20 BYTE), 
  "FOSSIL_SIZE" VARCHAR2(20 BYTE), 
  "SUFFIX" VARCHAR2(20 BYTE), 
  "ADDED_TEXT" VARCHAR2(200 BYTE), 
  "REFERENCE" VARCHAR2(1000 BYTE), 
  "SENSU_REFERENCE" VARCHAR2(1000 BYTE), 
  "AGE" VARCHAR2(200 BYTE), 
  "MARKER_SPECIES" VARCHAR2(200 BYTE), 
  "GEO_REGION" VARCHAR2(200 BYTE), 
  "KEYWORDS" VARCHAR2(2000 BYTE), 
  "SUBSPECIES" VARCHAR2(200 BYTE), 
  "FAMILY_GROUP" VARCHAR2(100 BYTE), 
  "MODIFIED" VARCHAR2(20 BYTE)
) TABLESPACE "GEODESC" ;

--------------------------------------------------------
--  Constraints for Table P_TAXA
--------------------------------------------------------
ALTER TABLE &&xschema..P_TAXA MODIFY ("COLKEY" NOT NULL ENABLE);
ALTER TABLE &&xschema..P_TAXA MODIFY ("TAXKEY" NOT NULL ENABLE);
ALTER TABLE &&xschema..P_TAXA MODIFY ("FOSSIL_GROUP" NOT NULL ENABLE);
ALTER TABLE &&xschema..P_TAXA MODIFY ("MODIFIED" NOT NULL ENABLE);


--------------------------------------------------------
--  DDL for Table P_TEMPLATES
--------------------------------------------------------
CREATE TABLE &&xschema..P_TEMPLATES 
( "TPKEY" NUMBER(38,0), 
  "TYPECODE" VARCHAR2(50 BYTE), 
  "OBJECTIVE" VARCHAR2(50 BYTE), 
  "SCALE" VARCHAR2(50 BYTE), 
  "EXTENSION" VARCHAR2(200 BYTE), 
  "ALLOWED_EXP" VARCHAR2(4000 BYTE), 
  "OFFSET_FLAG" VARCHAR2(5 BYTE) DEFAULT 'T', 
  "SECTION_FLAG" VARCHAR2(5 BYTE) DEFAULT 'T', 
  "SAMPLE_TYPES" VARCHAR2(4000 BYTE), 
  "PERMISSION" VARCHAR2(5 BYTE) DEFAULT 'T', 
  "CHANGED_BY" VARCHAR2(100 BYTE), 
  "CHANGED_ON" TIMESTAMP (6) DEFAULT SYSTIMESTAMP, 
  "IGNORE_FLAG" VARCHAR2(5 BYTE), 
  "INVALID_FLAG" VARCHAR2(5 BYTE), 
  "SAMPLE_GROUP" VARCHAR2(5 BYTE) DEFAULT 'L', 
  "HEADER_HEIGHT" NUMBER(38,0) DEFAULT 120, 
  "ROW_HEIGHT" NUMBER(38,0) DEFAULT 22, 
  "PROJECT" VARCHAR2(50 BYTE)
) TABLESPACE "GEODESC" ;

COMMENT ON COLUMN &&xschema..P_TEMPLATES.EXTENSION IS 'used for names';
COMMENT ON COLUMN &&xschema..P_TEMPLATES.ALLOWED_EXP IS 'JSON array of expeditions which can use the template';
COMMENT ON COLUMN &&xschema..P_TEMPLATES.OFFSET_FLAG IS 'T=true (allow offset change) F=false';
COMMENT ON COLUMN &&xschema..P_TEMPLATES.SECTION_FLAG IS 'T=true (change bottom for length changes); F=false';
COMMENT ON COLUMN &&xschema..P_TEMPLATES.SAMPLE_TYPES IS 'JSON array of allowed sample types';
COMMENT ON COLUMN &&xschema..P_TEMPLATES.PERMISSION IS 'T=locked to template manager; A=all';
COMMENT ON COLUMN &&xschema..P_TEMPLATES.CHANGED_BY IS 'user who made the last change';
COMMENT ON COLUMN &&xschema..P_TEMPLATES.CHANGED_ON IS 'time of the last change';
COMMENT ON COLUMN &&xschema..P_TEMPLATES.IGNORE_FLAG IS 'I=ignore; used by review';
COMMENT ON COLUMN &&xschema..P_TEMPLATES.INVALID_FLAG IS 'I=invalid; used by review';
COMMENT ON COLUMN &&xschema..P_TEMPLATES.SAMPLE_GROUP IS 'L=large; S=small';

--------------------------------------------------------
--  DDL for Index P_TEMPLATES_PK
--------------------------------------------------------
CREATE UNIQUE INDEX &&xschema..P_TEMPLATES_PK ON &&xschema..P_TEMPLATES ("TPKEY") 
TABLESPACE "GEODESCIDX" ;

--------------------------------------------------------
--  DDL for Trigger P_TEMPLATES_TPKEY_TRG
--------------------------------------------------------
CREATE OR REPLACE EDITIONABLE TRIGGER &&xschema..P_TEMPLATES_TPKEY_TRG 
before insert on &&xschema..P_TEMPLATES
for each row
  WHEN (new.tpkey < 0 or new.tpkey is null)
begin
  select &&xschema..P_TEMPLATES_TPKEY_SEQ.nextval into :new.tpkey from dual;
end;
/
ALTER TRIGGER &&xschema..P_TEMPLATES_TPKEY_TRG ENABLE;

--------------------------------------------------------
--  Constraints for Table P_TEMPLATES
--------------------------------------------------------
ALTER TABLE &&xschema..P_TEMPLATES MODIFY ("TPKEY" NOT NULL ENABLE);
ALTER TABLE &&xschema..P_TEMPLATES MODIFY ("TYPECODE" NOT NULL ENABLE);
ALTER TABLE &&xschema..P_TEMPLATES MODIFY ("OBJECTIVE" NOT NULL ENABLE);
ALTER TABLE &&xschema..P_TEMPLATES MODIFY ("SCALE" NOT NULL ENABLE);
ALTER TABLE &&xschema..P_TEMPLATES MODIFY ("OFFSET_FLAG" NOT NULL ENABLE);
ALTER TABLE &&xschema..P_TEMPLATES MODIFY ("SECTION_FLAG" NOT NULL ENABLE);
ALTER TABLE &&xschema..P_TEMPLATES ADD CONSTRAINT "P_TEMPLATES_PK" PRIMARY KEY ("TPKEY") USING INDEX &&xschema..P_TEMPLATES_PK  ENABLE;
ALTER TABLE &&xschema..P_TEMPLATES ADD CONSTRAINT "P_TEMPLATES_EXP_JSON" CHECK (ALLOWED_EXP IS JSON) ENABLE;
ALTER TABLE &&xschema..P_TEMPLATES MODIFY ("PERMISSION" NOT NULL ENABLE);
ALTER TABLE &&xschema..P_TEMPLATES MODIFY ("SAMPLE_GROUP" NOT NULL ENABLE);
ALTER TABLE &&xschema..P_TEMPLATES ADD CONSTRAINT "P_TEMPLATES_SAMPLE_TYPES" CHECK (SAMPLE_TYPES IS JSON) ENABLE;



--------------------------------------------------------
--  DDL for Table P_WORKINGSET
--------------------------------------------------------
CREATE TABLE &&xschema..P_WORKINGSET
( "WSKEY" NUMBER, 
  "OWNER_NAME" VARCHAR2(100 BYTE), 
  "OWNER_SESSION" VARCHAR2(100 BYTE), 
  "UPLOAD" VARCHAR2(5 BYTE) DEFAULT 'F', 
  "LAST_UPLOAD_ON" TIMESTAMP (6), 
  "LAST_UPLOAD_BY" VARCHAR2(100 BYTE), 
  "CREATED_ON" TIMESTAMP (6) DEFAULT SYSTIMESTAMP, 
  "CREATED_BY" VARCHAR2(100 BYTE), 
  "CHANGED_ON" TIMESTAMP (6), 
  "CHANGED_BY" VARCHAR2(100 BYTE), 
  "TPKEY" NUMBER, 
  "HOLE" VARCHAR2(20 BYTE), 
  "EXPEDITION" VARCHAR2(20 BYTE), 
  "SITE" VARCHAR2(20 BYTE), 
  "LIMSHOLE" NUMBER, 
  "FIRST_CORE" NUMBER(3,0) DEFAULT 0, 
  "LAST_CORE" NUMBER(3,0) DEFAULT 999, 
  "TIMEOUT" NUMBER DEFAULT 90
) TABLESPACE "GEODESC" ;

COMMENT ON COLUMN &&xschema..P_WORKINGSET.WSKEY IS 'primary key generated when working set is created';
COMMENT ON COLUMN &&xschema..P_WORKINGSET.UPLOAD IS 'T or F ';
COMMENT ON COLUMN &&xschema..P_WORKINGSET.TPKEY IS 'key to the template table';
COMMENT ON COLUMN &&xschema..P_WORKINGSET.TIMEOUT IS 'Time in minutes before upload warning';

--------------------------------------------------------
--  DDL for Index P_WORKINGSET_PK
--------------------------------------------------------
CREATE UNIQUE INDEX &&xschema..P_WORKINGSET_PK ON &&xschema..P_WORKINGSET ("WSKEY") 
TABLESPACE "GEODESCIDX" ;

--------------------------------------------------------
--  DDL for Trigger P_WORKINGSET_WSKEY_TRG
--------------------------------------------------------
CREATE OR REPLACE EDITIONABLE TRIGGER &&xschema..P_WORKINGSET_WSKEY_TRG 
before insert on &&xschema..P_WORKINGSET
for each row
    WHEN (new.wskey < 0 or new.wskey is null)
begin
  select &&xschema..P_WORKINGSET_WSKEY_SEQ.nextval into :new.wskey from dual;
end;
/
ALTER TRIGGER &&xschema..P_WORKINGSET_WSKEY_TRG ENABLE;

--------------------------------------------------------
--  Constraints for Table P_WORKINGSET
--------------------------------------------------------
ALTER TABLE &&xschema..P_WORKINGSET MODIFY ("WSKEY" NOT NULL ENABLE);
ALTER TABLE &&xschema..P_WORKINGSET ADD CONSTRAINT "P_WORKINGSET_PK" PRIMARY KEY ("WSKEY") USING INDEX &&xschema..P_WORKINGSET_PK  ENABLE;
ALTER TABLE &&xschema..P_WORKINGSET MODIFY ("HOLE" NOT NULL ENABLE);
ALTER TABLE &&xschema..P_WORKINGSET MODIFY ("TPKEY" NOT NULL ENABLE);
ALTER TABLE &&xschema..P_WORKINGSET MODIFY ("EXPEDITION" NOT NULL ENABLE);
ALTER TABLE &&xschema..P_WORKINGSET MODIFY ("SITE" NOT NULL ENABLE);
ALTER TABLE &&xschema..P_WORKINGSET MODIFY ("FIRST_CORE" NOT NULL ENABLE);
ALTER TABLE &&xschema..P_WORKINGSET MODIFY ("LAST_CORE" NOT NULL ENABLE);


--------------------------------------------------------
--  DDL for Table P_WORKINGSET_DATA
--------------------------------------------------------
CREATE TABLE &&xschema..P_WORKINGSET_DATA
( "WSKEY" NUMBER, 
  "ROWKEY" NUMBER, 
  "ROWDATA" CLOB
) TABLESPACE "GEODESC" 
  LOB ("ROWDATA") STORE AS SECUREFILE ( TABLESPACE "GEODESC" )
;

COMMENT ON COLUMN &&xschema..P_WORKINGSET_DATA.ROWKEY IS 'keys from project data start at 20000.  Keys from capture should always be less  than that and are provided by the capture tool.';

--------------------------------------------------------
--  DDL for Trigger P_WORKINGSET_DATA_ROWKEY_TRG
--------------------------------------------------------
CREATE OR REPLACE EDITIONABLE TRIGGER &&xschema..P_WORKINGSET_DATA_ROWKEY_TRG
before insert on &&xschema..P_WORKINGSET_DATA
for each row
  WHEN (new.rowkey < 0 or new.rowkey is null)
begin
  select &&xschema..P_WORKINGSET_DATA_ROWKEY_SEQ.nextval into :new.rowkey from dual;
end;
/
ALTER TRIGGER &&xschema..P_WORKINGSET_DATA_ROWKEY_TRG ENABLE;

--------------------------------------------------------
--  Constraints for Table P_WORKINGSET_DATA
--------------------------------------------------------
ALTER TABLE &&xschema..P_WORKINGSET_DATA MODIFY ("WSKEY" NOT NULL ENABLE);
ALTER TABLE &&xschema..P_WORKINGSET_DATA MODIFY ("ROWKEY" NOT NULL ENABLE);
ALTER TABLE &&xschema..P_WORKINGSET_DATA MODIFY ("ROWDATA" NOT NULL ENABLE);


/* Gather reference constraints together at the end due to inter-table dependencies. */
--------------------------------------------------------
--  Ref Constraints for Table P_COLUMNS
--------------------------------------------------------
ALTER TABLE &&xschema..P_COLUMNS ADD CONSTRAINT "P_COLUMNS_TPKEY" FOREIGN KEY ("TPKEY") REFERENCES &&xschema..P_TEMPLATES ("TPKEY") ON DELETE CASCADE ENABLE;

--------------------------------------------------------
--  Ref Constraints for Table P_DATA
--------------------------------------------------------
ALTER TABLE &&xschema..P_DATA ADD CONSTRAINT "P_DATA_TPKEY" FOREIGN KEY ("TPKEY") REFERENCES &&xschema..P_TEMPLATES ("TPKEY") ENABLE;

--------------------------------------------------------
--  Ref Constraints for Table P_LIST_VALUES
--------------------------------------------------------
ALTER TABLE &&xschema..P_LIST_VALUES ADD CONSTRAINT "P_LIST_VALUES_COLKEY" FOREIGN KEY ("COLKEY") REFERENCES &&xschema..P_COLUMNS ("COLKEY") ON DELETE CASCADE ENABLE;

--------------------------------------------------------
--  Ref Constraints for Table P_TAXA
--------------------------------------------------------
ALTER TABLE &&xschema..P_TAXA ADD CONSTRAINT "P_TAXA_FK1" FOREIGN KEY ("COLKEY") REFERENCES &&xschema..P_COLUMNS ("COLKEY") ON DELETE CASCADE ENABLE;

--------------------------------------------------------
--  Ref Constraints for Table P_WORKINGSET
--------------------------------------------------------
ALTER TABLE &&xschema..P_WORKINGSET ADD CONSTRAINT "P_WORKINGSET_FK1" FOREIGN KEY ("TPKEY") REFERENCES &&xschema..P_TEMPLATES ("TPKEY") ENABLE;

--------------------------------------------------------
--  Ref Constraints for Table P_WORKINGSET_DATA
--------------------------------------------------------
ALTER TABLE &&xschema..P_WORKINGSET_DATA ADD CONSTRAINT "P_WORKINGSET_DATA_FK1" FOREIGN KEY ("WSKEY") REFERENCES &&xschema..P_WORKINGSET ("WSKEY") ON DELETE CASCADE ENABLE;

3. Table Grants

GEODESC web applications are coded to use proxy accounts WRITER, GUEST, PUBLIC. The

This SQL is explicit and sufficient. Run from a DBA account

Anonymous procedure to establish GEODESC project schema table grants 
-- Anonymous procedure to establish above grants.
-- Project Code must be revised for each new GEODESC project created.
-- This procedure must be run by an account sufficiently privileged
-- to manage GRANTs.
declare
  -- GEODESC project code (to be prefixed by "GEODP")
  -- Change this before you run it.
  project_code varchar2(30) := 'nnn';
  
  type WorkList is table of varchar2(50);
  tableList WorkList := WorkList(
    -- List of tables within GEODESC project schema
      'p_columns'
    , 'p_data'
    , 'p_list_values'
    , 'p_taxa'
    , 'p_templates'
    , 'p_workingset'
    , 'p_workingset_data'
    );
    
    fullyQualifiedName varchar2(100);

begin
  -- For the new GEODESC schema, generate the appropriate grants for each table.
  for t in tableList.FIRST .. tableList.LAST
  loop
    fullyQualifiedName := 'GEODP' || upper(project_code) || '.' || upper(tableList(t));
    dbms_output.put('Grants on '||fullyQualifiedName);
    execute immediate 'grant select, insert, update, delete on ' || fullyQualifiedName || ' to writer';
    execute immediate 'grant select on ' || fullyQualifiedName || ' to guest';
    execute immediate 'grant select on ' || fullyQualifiedName || ' to public';
    insert into transfer.log (entry) values ('BOX for GEODESC project creation. Grants on '||fullyQualifiedName||' applied to WRITER, GUEST, PUBLIC.');
	commit;
  end loop;
end;
/


4. Register the new project in table GEODCAT.PROJECTS

Adapt one of SQL statements below to register the new GEODESC project.

-- Example 
insert into GEODCAT.PROJECTS (name, used_by_expedition, schema_name)
values ('398', '["398","999"]', 'geodp398';

-- Example
insert into GEODCAT.PROJECTS (name, used_by_expedition, schema_name)
values ('TST999', '["999"]', 'GEODPTST999';

-- Example
update GEODCAT.PROJECTS set name='399', used_by_expedition=replace(used_by_expedition,'397','399'), schema_name=replace(schema_name,'397','399') where name='xyz';


Please review GEODCAT.PROJECTS. Keep it to the minimum required number of entries.

Miscellaneous notes

  • Column USED_BY_EXPEDITION supports the specification of a JSON array.
    • Some GEODESC projects are collaborations across multiple expeditions.
    • In the shipboard environment it is (already) conventional to provide access to both the primary project and the 999 test project.
  • Upper vs. lower case makes no difference for columns NAME, SCHEMA_NAME. Upper, lower, mixed case are accepted and functional.
  • Column CAPTURE_LOCATION is deprecated. It is not used and will be removed in a future revision of the data model.


5. Set Up Auther Privileges

After setting up the project schema it is necessary to create a set of privileges and roles in Auther.

Create new Auther Privilege

Sign into Auther and click on PRIVILEGES button. Click Add (+) button.  Fill in the following values:

      • Application:    GEODESC
      • Keyword:        GEODESCPROJECT
      • Qualifier:         nnn  (where nnn is the name of the project, normally an expedition number).
      • Description:     Allow access to GEODESC project nnn.

Create New Auther Role

Click on ROLES button.  Click Add (+) button. Fill in the following values:

      • Name:            GEODP_nnn (where nnn is project name, usually expedition number).
      • Description:    Allow access to GEODESC project nnn.
      • Applications:  GEODESC

Click SAVE button.  New role should now appear in roles table.  Find the newly created GEODESC project role and click privileges button on corresponding line.  Add the previously created GEODESC project privilege to the set of privileges.



  • No labels