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

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

The script may be run from SQL Developer via a connection with DBA privilege.

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. LABWARE
    2. LABWAREIDX
    3. USERS |  31457280 | K

2. Clone Database from Existing Project

DDL for the schema above may be copied from a prior (preferrably local) GEODESC project. Oracle SQL Developer IDE provides at tool to assist.

Establish source and target schema connections

In the Connections panel establish connections for your

  • source schema (typically last expeditions GEODPnnn); and
  • target schema (created in step 1)

Sign into both.

Run the database copy tool


Select menu item Tools → Database copy. The following dialog is presented.


Supply the following settings.

  • Source connection. Connection to existing GEODESC project schema.
  • Destination connection. Connection to new GEODESC project schema.
  • Copy Options. Select Objects Copy
  • Copy DDL. Leave box checked.
    • Do Not Replace Existing Destination objects. (leave selected)
    • Ignore Storage, Partitioning and Tablespace (leave checked)
  • Copy Data. Uncheck this control--we do not want to copy any data from the source database.

Click Next > button.


The second page of the dialog will provide a complete set of database objects to be copied, leave all objects checked as in the example below.

Click Next > button


Since we are copying all objects there is nothing to do on the "Specify Objects" page, as shown below.

Click Next > button.


Review the information in the Copy Summary page as shown below.  Pay special attention to the source and destination connections.

Click the Finish button.


SQLDeveloper will display a small progress window as it runs a script to copy the DDL from the source to the destination connection.  When the script is complete it will display a log of the actions completed.  Review this log to look for potential warnings or error messages.  If no errors are reported proceed with the procedures to ensure the new project schema is ready for use.


3. Table Grants

The above SQL Developer process omits table GRANTS required for GEODESC application operations. Appropriate grants to WRITER, GUEST, and PUBLIC must be established for the GEODESC applications to function correctly. This method is supported by SQL Developer.

For each table in the project schema

  1. Open the table properties in the source connection.
  2. Click the SQL tab.
  3. Scroll to the bottom of the SQL worksheet.
  4. Copy the grant SQL commands.
    Only the grants for WRITER, GUEST, PUBLIC need to be copied. All others are unnecessary.
    Copy grant statements from destination connection
  5. Paste these commands into the worksheet in the destination connection.
  6. Modify all source schema names to destination schema name.

  7. Execute and commit the grant SQL commands.
    This is the set that must be repeated for each table in the GEODP### schema.

    -- Add required Grants to Tables in New Project Schema
GRANT SELECT, INSERT, UPDATE, DELETE ON "NEW-PROJ-SCHEMA"."TABLE_NAME" TO "WRITER";
GRANT SELECT ON "NEW-PROJ-SCHEMA"."TABLE_NAME" TO "GUEST";
GRANT SELECT ON "NEW-PROJ-SCHEMA"."TABLE_NAME" TO PUBLIC;

-- Example for the specific table P_CAPTURE_FILES in schema GEODPTST123
GRANT SELECT, INSERT, UPDATE, DELETE ON "GEODPTST123"."P_CAPTURE_FILES" TO "WRITER";
GRANT SELECT ON "GEODPTST123"."P_CAPTURE_FILES" TO "GUEST";
GRANT SELECT ON " GEODPTST123"." P_CAPTURE_FILES " TO PUBLIC;

    At this point the new project should be ready for use by GEODESC applications.

4. Register the new project in table GEODCAT.PROJECTS

Via SQL Developer. Navigate to the GEODCAT.PROJECTS table. Review and edit its entries. Example below is taken from Expedition 398.

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