Thursday, June 23, 2016

Query to find users granted an ACL -- the natural question after seeing ORA-24247

You may have encountered ORA-24247: network access denied by access control list (ACL) and wondered "who has access to what from my database?" I extended a query from the Oracle documentation to give me the results I wanted: ACL Name, Username, host, lower port, upper port, and if granted connect and resolve.

with privs as (
  SELECT acl,  u.username, host, lower_port, upper_port, 
         DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, u.username, 'connect'),
            1, 'GRANTED', 0, 'DENIED', null) conn_privilege,
         DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, u.username, 'resolve'),
            1, 'GRANTED', 0, 'DENIED', null) res_privilege       
     FROM dba_network_acls a, dba_users u
select *
  from privs
  where conn_privilege is not null
     or res_privilege is not null
  order by acl, username

It's nothing special, but can be a handy query.

Monday, June 13, 2016

Super Quick Oracle REST Service with OAuth2 and client_credentials

I had the need to allow system A to talk to system B via a REST service. The data was sensitive and powers above me requested that system A use OAuth2 to connect to system B. This REST service call does not involve an end user, it's system A pulling a CSV extract from system B. There are many ways to protect this, but the decision was to use OAuth2. Below is a cookbook on how to do this. This example assumes you have ORDS 3.x installed with the ORDS_PUBLIC_USER and ORDS_METADA schemas configured.

I am running all commands as the user ANTON.

  1. For this example I will create a data source for our query:
    -- create the table in the ANTON database schema
    create table anton_table (c1 varchar2(500), c2 varchar2(500) );

    -- insert some sample data
    insert into anton_table (c1, c2)
      select owner, table_name
        from all_tables
        where rownum <= 20;

    -- commit
  2. Enable REST on the ANTON schema:


        ORDS.ENABLE_SCHEMA(p_enabled => TRUE,
                           p_schema => 'ANTON',
                           p_url_mapping_type => 'BASE_PATH',
                           p_url_mapping_pattern => 'anton',
                           p_auto_rest_auth => FALSE);


    -- check to see that it worked
    select id, parsing_schema from user_ords_schemas;

  3. Define a REST Module:

       p_module_name    => 'antonModule',
       p_base_path      => '/antonmodule',
       p_items_per_page => 25,
       p_status         => 'PUBLISHED',
       p_comments       => NULL );


  5. Define a template. This is a URL pattern associated with the module "antonModule." Bind variables are contained in squiggly brackets: {bindVariableName}.

       p_module_name => 'antonModule',
       p_pattern     => '/sqltest/{abc}/{def}',
       p_priority    => 0,
       p_etag_type   => 'HASH',
       p_etag_query  => NULL,
       p_comments    => NULL );
    This is interesting so take note! Notice my bind variables are abc and def. I tried using c1 and c2 but it seems that the bind variable names can not have numerals in them. Developer beware!
  6. Define a REST Handler based upon a sql query that takes two bind variables (abc and def) and returns a CSV file:

       p_module_name => 'antonModule',
       p_pattern     => '/sqltest/{abc}/{def}',
       p_method      => 'GET',
       p_source_type => ords.source_type_csv_query,
       p_source      => q'[select c1, c2 from anton_table where c1 = :abc and c2 = :def  ]',
       p_items_per_page  => 25,
       p_mimes_allowed   => NULL ,
       p_comments  => NULL );  


    This is worth repeating: Notice my bind variables are abc and def. I tried using c1 and c2 but it seems that the bind variable names can not have numerals in them. Developer beware!
  7. At this point you can test your service:


  8.  Define a privilege to protect it with OAuth2:


    l_roles       owa.vc_arr;
    l_patterns    owa.vc_arr;
    l_modules     owa.vc_arr;
      -- l_roles intentionally left empty
      -- populate arrays
      l_modules(1) := 'antonModule';

      p_privilege_name  => 'antonpriv',
      p_roles           => l_roles,
      p_patterns        => l_patterns,
      p_modules         => l_modules,
      p_label           => 'antonTestingPriv',
      p_description     => 'anton testing priv',
      p_comments        => null);


  9. Now you will find it protected:

  10. Create a client that is allowed to access it:

       p_name            => 'antonclient',
       p_grant_type      => 'client_credentials',
       p_owner           => 'anton',
       p_description     => NULL,
       --p_origins_allowed => NULL, -- param name depends on ORDS version
       p_allowed_origins => NULL,   -- param name depends on ORDS version
       p_redirect_uri    => 'http://localhost:8080/redirect',
       p_support_email   => '',
       p_support_uri     => 'http://localhost:8080/support',
       p_privilege_names => 'antonpriv');
  11. Get the client_id and client_secret. You will need to log in as a user that has access to select from the ords_metadata tables (e.g. ORDS_METADATA or SYSTEM).

    select * from ords_metadata.oauth_clients;
  12. If you want to be able to do this from an http (not https) URL (which you should NEVER do in production--this is just for testing!!):

    -- turn off need for SSL
    1.    Locate the folder where the Oracle REST Data Services configuration is stored.
    2.    Edit the file named defaults.xml.
    3.    Add the following setting to the end of this file just before the </properties> tag.
    4.    <entry key="security.verifySSL">false</entry>
    5.    Save the file.
    6.    Restart Oracle REST Data Services if it is running.
  13. Test getting a bearer token

    curl -i -d "grant_type=client_credentials" --user "[client_id]:[client_secret]" http://localhost:8080/ords/anton/oauth/token

    You should receive a response like this:

    HTTP/1.1 200 OK
    Content-Type: application/json
    X-Frame-Options: SAMEORIGIN
    Transfer-Encoding: chunked


    -- curl is a tool for making http(s) requests from the command line
    --  -i allows you do to this as http -- though you SHOULD use https
    --  -d allows you to pass in data
    --  --user allows you to pass a user:password for basic authentication
    --  then pass the appropriate URL to get a bearer token
  14. Test getting your CSV

    curl -i --header "Authorization: Bearer [token from step 10]" http://localhost:8080/ords/anton/antonmodule/sqltest/ANTON/ANTON_TABLE --output anton.csv