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

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
KLRICE@orcl >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

demos  demos/  PUBLISHED  25              
hr     hr      PUBLISHED  25              

KLRICE@orcl >rest export hr

  l_module_id number;
  l_template_id number;
  l_handler_id number;

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

KLRICE@orcl >