BOX ship - Create GEODESC project schema - GUI method
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.
Granted Roles:
Connect
System Privileges:
Advisor
Alter session
Create Credential
Create Database Link
Create Job
Create Procedure
Create Public Synonym
Create Sequence
Create Session
Create Synonym
Create Table
Create Trigger
Create Type
Create View
Create Any Procedure
Debug Connect Session
Drop Public Synonym
Select Any Dictionary
Select Any Table
Quotas
LABWARE
LABWAREIDX
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
Open the table properties in the source connection.
Click the SQL tab.
Scroll to the bottom of the SQL worksheet.
Copy the grant SQL commands.
Only the grants for WRITER, GUEST, PUBLIC need to be copied. All others are unnecessary.Paste these commands into the worksheet in the destination connection.
Modify all source schema names to destination schema name.
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.