...
- 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.
...
Code Block |
---|
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###; |
...
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
/* 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; |
...
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
-- 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. set serveroutput on; 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'; dbms_output.put_line('...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
...
Code Block |
---|
-- 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.
...