In the part 1 of this series we looked at how to get your ADW and OCI environment ready for using Data Safe. If you missed part 1 or need a quick refresher then the blog post is here: https://blogs.oracle.com/datawarehousing/keeping-your-autonomous-data-warehouse-secure-with-data-safe-part-1.
In this post we are going to explore the process of connecting and Autonomous Data Warehouse instance to our newly deployed Data Safe environment. Remember that you deploy your Data Safe control center within a specific OCI regional data center - as is the case with all our other cloud services. Therefore, if you switch to a different data center then you will need to deploy a new Data Safe environment. Hope that makes sense!
In part 1 we got to the point of enabling Data Safe in the Frankfurt data center. Now when we login to Oracle Cloud using our newly created OCI credentials we can pop open the hamburger menu and select Data Safe:
and arrive on the Data Safe landing pad. The next step is to launch the Service Console (you may be wondering...why doesn't the Service Console just open automatically since the landing pad page is empty, apart from the Service Console button! Great question and we will come back to this towards the end of the series of posts when the landing pad page will show a lot more information).
After clicking on the Service Console button a new window pops open which looks like this:
Right now, there is no information showing on any of our graphs or any of the other pages. This is because we have not registered our data warehouse instance so that's the next step.
We need to register our ADW with Data Safe before we can generate any of the reports that are part of the Data Safe library. To do that we need to go to the tab marked "Target" in the horizontal menu at the top of the page:
Clicking on the "Register" button will pop open a form where we can input the connection details for our ADW...
Data Safe is not limited to just working with Autonomous Databases and we could register any of the following:
Nnote that Data Safe supports only serverless deployments for Autonomous Database. "Dedicated" is not currently supported. There is more information here: https://docs.oracle.com/en/cloud/paas/data-safe/udscs/supported-target-databases.html
For ADW (and ATP) we first need to change the connection type to TLS which will add some additional fields to the form - this will be most recognisable if you have been spent time configuring connections to ADW from DI/ETL or BI tools:
it looks as if a lot of information is now required to register our ADW instance but the good news is that just about all the information we need is contained within a small zip file which we can download from our OCI ADB console. Essentially we need the wallet file for our instance. But first let's quickly complete the fields in the top part of the form:
Ok, now we need the information about our ADW instance and here's how you get it:
If we flip over to the OCI console page for our ADW instance we can see that there is a line for something called "OCID" which is the first piece of information we need to collect .There are two links next to it: "Show" and "Copy".
Click on copy and then flip over to our Data Safe page and paste in the OCID reference. Now we need things like hostname, port, service name and target distinguished name along with various secure wallet files. To get this information we need to download the wallet file which can be accessed by clicking on the "DB Connection" button. On the pop-up form click the "Download Wallet" button and enter a password...note this down because we are going to need it again shortly...
Once the file has been downloaded, find the file on your filesystem and unzip it. The result will be a folder containing the following files:
Ok, back to our Target registration form on Data Safe....the data for the next four fields can all be found in the tnsnames.ora file. We are going to use the "low service" for this connection because running Data Safe reports is not an urgent, rush-rush, workload.
If you have no idea what a "low service" is then it might be a good idea to quickly read through the section on "Managing Concurrency and Priorities on Autonomous Data Warehouse" in section 12 of the ADW documentation. In simple terms...when we connect to an ADW instance we need to select a service (low, medium or high). These services map to LOW, MEDIUM, and HIGH consumer groups which have the following characteristics:
Anyway....as long as the jobs run, then we are going to be happy. Therefore, we need to find the details for our low-service connection in the tnsnames.ora file which will look something like this:
adwdemo_low = (description=(address=(protocol=tcps)(port=1522)(host=xxxxxx.oraclecloud.com))(connect_data=(service_name=xxxxx_adwdemo_low.xxxxxxx))(security=(ssl_server_cert_dn="CN=xxxxxx.oraclecloud.com,OU=Oracle,O=Oracle Corporation,L=Redwood City,ST=California,C=US")))
You can copy & paste the host, port, service_name and ssl_server_cert_dn into the four fields below the "TLS" pulldown menu entry. So now our form looks like this...
now the last few steps....make sure the wallet type is set to "JKS Wallet". For the Certificate/Wallet find the "truststore.jks" file from our downloaded and unzipped connection file. In the same directory/folder we can pick "keystone.jks" for the "Keystore Wallet". The next field needs the password we used on the OCI ADW console page when we downloaded the connection zip file so paste that in...
Lastly add the ADW instance username/password that we created in Part 1of this series of blog posts - our user was called DATASAFE.
before you click on the "Test Connection" button we need to run a PL/SQL script to give our new DATASAFE database user some privileges that will allow Data Safe to run though it's library of checks...
Click on the download button then search for the PL/SQL script dscs_privileges.sql. Using SQL Developer (or any other tool) we need to login as our standard ADMIN user and run that script (copy & paste will do the trick). Check the log for the script and you should see something like this:
Enter value for USERNAME (case sensitive matching the username from dba_users)
Setting USERNAME to DATASAFE
Enter value for TYPE (grant/revoke)
Setting TYPE to GRANT
Enter value for MODE (audit_collection/audit_setting/data_discovery/masking/assessment/all)
Setting MODE to ALL
Granting AUDIT_COLLECTION privileges to "DATASAFE" ...
Granting AUDIT_SETTING privileges to "DATASAFE" ...
Granting DATA_DISCOVERY role to "DATASAFE" ...
Granting MASKING role to "DATASAFE" ...
Granting ASSESSMENT role to "DATASAFE" ...
Disconnected from Oracle Database 18c Enterprise Edition Release 18.104.22.168.0 - Production
NOW, we can test the connection... and everything should work and we should see a nice big green tick.