Rem  Copyright (c) Oracle Corporation 2014. All Rights Reserved.
Rem
Rem    NAME
Rem      ords_pub_synonyms_grants.sql
Rem
Rem    DESCRIPTION
Rem      Grants select and execute to certain ORDS views and packages to PUBLIC.  
Rem      Creates ORDS public synonyms.
Rem
Rem    NOTES
Rem      Assumes user with SYSDBA privilege is connected.
Rem
Rem    MODIFIED   (MM/DD/YYYY)
Rem      epaglina  06/03/2015  Removed DBA_ORDS_SCHEMAS. Reorganized public views and packages. 
Rem                            Uses a list to grant permissions and create synonyms to PUBLIC.
Rem      rhcooper  02/09/2015  Removed references to inefficient user_ords_obj_members view.
Rem      cdivilly  02/05/2015  Add missing synonyms and grants for user_ords_client_roles.
Rem      rhcooper  12/18/2014  Added ALL_* view analogs of relevant USER_* views.
Rem      cdivilly  11/17/2014  Expose the ORDS_METADATA.POOL_CONFIG view to the ORDS_PUBLIC_USER so that we can eliminate a connection pool for ORDS_METADATA.
Rem      cdivilly  11/05/2014  Add user_ords_services view. 
Rem      josmende  10/09/2014  Adding grants for the new 'privilege' views. Adding the pending approvals view.
Rem      epaglina  05/29/2014  Created.
Rem
set serveroutput on

alter session set current_schema = SYS;

declare
   type ords_public_view_list is table of varchar2(30);
   type ords_public_package_list is table of varchar2(30);
   type ords_public_type_list is table of varchar2(30);

   -- If you add a view to this list, make sure you also add it to
   -- /uninstall/core/ords_drop_pub_synonyms.sql.  List in alphabetical order.
   ords_public_views ords_public_view_list := ords_public_view_list(
                                        'ALL_ORDS_CLIENT_ROLES',
                                        'ALL_ORDS_CLIENT_PRIVILEGES',
                                        'ALL_ORDS_HANDLERS',
                                        'ALL_ORDS_MODULES',
                                        'ALL_ORDS_OBJECTS',
                                        'ALL_ORDS_OBJ_MEMBERS',
                                        'ALL_ORDS_PARAMETERS',
                                        'ALL_ORDS_PENDING_APPROVALS',
                                        'ALL_ORDS_PRIVILEGES',
                                        'ALL_ORDS_PRIVILEGE_MAPPINGS',
                                        'ALL_ORDS_PRIVILEGE_MODULES',
                                        'ALL_ORDS_PRIVILEGE_ROLES',
                                        'ALL_ORDS_ROLES',
                                        'ALL_ORDS_SCHEMAS',
                                        'ALL_ORDS_SERVICES',
                                        'ALL_ORDS_TEMPLATES',     
                                        'USER_ORDS_APPROVALS',
                                        'USER_ORDS_CLIENTS',
                                        'USER_ORDS_CLIENT_PRIVILEGES',
                                        'USER_ORDS_CLIENT_ROLES',
                                        'USER_ORDS_ENABLED_OBJECTS',
                                        'USER_ORDS_HANDLERS',
                                        'USER_ORDS_MODULES',
                                        'USER_ORDS_OBJECTS',
                                        'USER_ORDS_OBJ_MEMBERS',                                        
                                        'USER_ORDS_PARAMETERS',
                                        'USER_ORDS_PENDING_APPROVALS',
                                        'USER_ORDS_PRIVILEGES',
                                        'USER_ORDS_PRIVILEGE_MAPPINGS',
                                        'USER_ORDS_PRIVILEGE_MODULES',
                                        'USER_ORDS_PRIVILEGE_ROLES',
                                        'USER_ORDS_ROLES',
                                        'USER_ORDS_SCHEMAS',
                                        'USER_ORDS_SERVICES',
                                        'USER_ORDS_TEMPLATES'
                                        );

   -- If you add a package to this list, make sure you also add it to
   -- ords_drop_pub_synonyms.sql.  List in alphabetical order.
   ords_public_packages ords_public_package_list := ords_public_package_list(
                                        'OAUTH',
                                        'ORDS',
                                        'ORDS_SERVICES'
                                        );
                                        
   -- If you add a type to this list, make sure you also add it to
   -- ords_drop_pub_synonyms.sql.  List in alphabetical order.
   ords_public_types ords_public_type_list := ords_public_type_list(
                                        'T_ORDS_ATTR_SUPPORT_OBJ',
                                        'T_ORDS_ATTR_SUPPORT_TAB',
                                        'T_ORDS_NUM_TAB',
                                        'T_ORDS_VCHAR_TAB'
                                        );
                                        
begin   
   -- ORDS Public Views 
   for i in ords_public_views.first .. ords_public_views.last loop
      --dbms_output.put_line('grant select on ORDS_METADATA.' || ords_public_views(i) || ' to PUBLIC');
      execute immediate 'grant select on ORDS_METADATA.' || ords_public_views(i) || ' to PUBLIC';
      --dbms_output.put_line('create or replace PUBLIC synonym ' || ords_public_views(i) || ' for ORDS_METADATA.' || ords_public_views(i));
      execute immediate 'create or replace PUBLIC synonym ' || ords_public_views(i) || ' for ORDS_METADATA.' || ords_public_views(i);      
   end loop;

   -- ORDS Public Packages
   for i in ords_public_packages.first .. ords_public_packages.last loop
      --dbms_output.put_line('grant execute on ORDS_METADATA.' || ords_public_packages(i) || ' to PUBLIC');
      execute immediate 'grant execute on ORDS_METADATA.' || ords_public_packages(i) || ' to PUBLIC';
      --dbms_output.put_line('create or replace PUBLIC synonym ' || ords_public_packages(i) || ' for ORDS_METADATA.' || ords_public_packages(i));
      execute immediate 'create or replace PUBLIC synonym ' || ords_public_packages(i) || ' for ORDS_METADATA.' || ords_public_packages(i);      
   end loop;

   -- ORDS Public Types
   for i in ords_public_types.first .. ords_public_types.last loop
      --dbms_output.put_line('grant execute on ORDS_METADATA.' || ords_public_types(i) || ' to PUBLIC');
      execute immediate 'grant execute on ORDS_METADATA.' || ords_public_types(i) || ' to PUBLIC';
      --dbms_output.put_line('create or replace PUBLIC synonym ' || ords_public_types(i) || ' for ORDS_METADATA.' || ords_public_types(i));
      execute immediate 'create or replace PUBLIC synonym ' || ords_public_types(i) || ' for ORDS_METADATA.' || ords_public_types(i);      
   end loop;
end;  
/


-- Enable ORDS_PUBLIC_USER to lookup available schemas for url mapping
GRANT SELECT on ORDS_METADATA.POOL_CONFIG TO ORDS_PUBLIC_USER;
-- Enable ORDS_PUBLIC_USER to check the ORDS version number
GRANT SELECT on ORDS_METADATA.ORDS_VERSION TO ORDS_PUBLIC_USER;


GRANT SELECT ON ORDS_METADATA.ORDS_REPVERSION TO PUBLIC;
CREATE OR REPLACE PUBLIC SYNONYM USER_ORDS_REPOVERSIONS FOR ORDS_METADATA.ORDS_REPVERSION;

