Friday, September 11, 2015

SQLCL - More secure, now with REST !

A new SQLCL build was just posted go grab it and kick the tires.  There are well over 100 bug fixes in there so it's better than ever.  Also there's some new things.


More Secure 


  Imagine you have an api that is called and a password or something is used in the parameters.  We use exec MY_API(...)  and it works just fine.  However consider if someone with access to v$sql they just got anything you passed into the api and quite easily.  Enter SQLCL new thing #1 starting with this build now changes all "exec my_api(...)" into an anonymous block with binds so those literals do not show up.



Procedure SECURE_API compiled

KLRICE@orcl >exec secure_api('my password');


PL/SQL procedure successfully completed.

KLRICE@orcl >select sql_text from v$sql where sql_text like '%secure_api%';


SQL_TEXT                                                       
BEGIN secure_api(:ZSqlDevUnIq1); END;                          
select sql_text from v$sql where sql_text like '%secure_api%'  


KLRICE@orcl >




REST Support

Always check the help, the tool highlights what is new to make it easy.  It only took Alex 8 minutes from the download being available to seeing the new features !



 This build adds the ability to extract ORDS services.  Once a rest service is defined and it's time to move it from one system to another this will make it easier than ever.  Simple spool to a file, export the definition and instant install script.

KLRICE@orcl >help
    ....
    REST
    ...
KLRICE@orcl >rest

REST
------

REST allows to export ORDS 3.X services.

      REST export                     -  All modules
      REST export >module_name>    -  Export a specific module
      REST export >module_prefix>  -  Export a specific module related to the given prefix
      REST modules                    -  List the available modules
      REST privileges                 -  List the existing privileges
      REST schemas                    -  List the available schemas
KLRICE@orcl >rest modules

NAME   PREFIX  STATUS     ITEMS_PER_PAGE  
demos  demos/  PUBLISHED  25              
hr     hr      PUBLISHED  25              


KLRICE@orcl >rest export hr

declare
  l_module_id number;
  l_template_id number;
  l_handler_id number;
begin

  l_module_id := ORDS_METADATA.ORDS_SERVICES.create_module( p_name => 'hr' , p_uri_prefix => 'hr' , p_items_per_page => 25 , p_status => 'PUBLISHED' );
  l_template_id := ORDS_METADATA.ORDS_SERVICES.add_template( p_module_id => l_module_id, p_uri_template => 'everything' , p_priority => 0 , p_etag_type => 'HASH' );
  l_handler_id := ORDS_METADATA.ORDS_SERVICES.add_handler( p_template_id => l_template_id, p_source_type => 'json/query' , p_method => 'GET' , p_items_per_page => 0 , p_source => 'select d.*,' || unistr('\000a')
   || '' || unistr('\000a')
   || '   cursor( select e.*' || unistr('\000a')
   || '           from hr.employees e where e.department_id = d.department_id) emps           ' || unistr('\000a')
   || '   from hr.departments d');

commit;
end;
KLRICE@orcl >