Rem  Copyright (c) Oracle Corporation 2014. All Rights Reserved.
Rem
Rem    NAME
Rem      ords_seed.sql
Rem
Rem    DESCRIPTION
Rem      This script creates seed data in the ORDS_METADATA schema used to 
Rem      define roles and privileges available to all schemas
Rem    NOTES
Rem      Assumes user with SYSDBA privilege is connected.
Rem
Rem    REQUIREMENTS
Rem      - Oracle Database Release 11.1 or later
Rem
Rem    MODIFIED    (MM/DD/YYYY)
Rem     epaglina    01/28/2014 - Created.
Rem     cdivilly    11/18/2014 - Add seed data for ORDS_METADATA.
Rem

alter session set current_schema = SYS;

delete from ords_metadata.ords_url_mappings where id = 10;
delete from ords_metadata.ords_schemas where id = 10;
delete from ords_metadata.sec_privilege_roles where schema_id = 10;
delete from ords_metadata.sec_roles where schema_id = 10;
delete from ords_metadata.sec_privileges where schema_id = 10;

-- Do not map the internal schema
insert into ORDS_METADATA.ORDS_URL_MAPPINGS (id,type,pattern) values(10,'NONE','/_/');
-- Define the internal schema
insert into ORDS_METADATA.ORDS_SCHEMAS (id,parsing_schema,url_mapping_id,status) values(10,'ORDS_METADATA',10,'DISABLED');

-- Required to register OAuth2 Clients
insert into ORDS_METADATA.SEC_ROLES (id,name,schema_id) values (1,'OAuth2 Client Developer',10);
-- Required to edit RESTful Services
insert into ORDS_METADATA.SEC_ROLES (id,name,schema_id) values (2,'SQL Developer',10);
-- Generic role representing any user authorized to invoke a RESTful Service
insert into ORDS_METADATA.SEC_ROLES (id,name,schema_id) values (3,'RESTful Services',10);
-- Role representing a user authorized to perform administration operations on a schema
insert into ORDS_METADATA.SEC_ROLES (id,name,schema_id) values (4,'Schema Administrator',10);
-- Role representing a user authorized to access any autorest object
insert into ORDS_METADATA.SEC_ROLES (id,name,schema_id) values (5,'oracle.dbtools.autorest.any.schema',10);
-- Role representing a user authorized to access a SODA resource
insert into ORDS_METADATA.SEC_ROLES (id,name,schema_id) values (6,'SODA Developer',10);

-- Privilege to control access to editing RESTful Services
insert into ORDS_METADATA.SEC_PRIVILEGES (id,name,label,description,schema_id) values(1,'oracle.dbtools.sqldev','RESTful Service Editing','Provides access to edit RESTful Service definitions',10);
-- Require 'SQL Developer' role for 'oracle.dbtools.sqldev' privilege
insert into ORDS_METADATA.SEC_PRIVILEGE_ROLES (id,privilege_id,role_id,schema_id) values (1,1,2,10);
-- Or Require 'Schema Administrator' role for 'oracle.dbtools.sqldev' privilege
insert into ORDS_METADATA.SEC_PRIVILEGE_ROLES (id,privilege_id,role_id,schema_id) values (2,1,4,10);

commit;
