-- 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';
    insert into dbms_output.put_line('...transfer.log (entry) values ('BOX for GEODESC project creation. Grants on '||fullyQualifiedName||' applied to WRITER, GUEST, PUBLIC.');
	commit;
  end loop;
end;
/  

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