Mathias Magnusson

An old truth in Oracle and just about all relational databases has been that an update on one row by one session will block an update by another session on the same row. Until the first session commits or rolls back. That is until 23ai. Or at least in some very specific scenarios. Let’s consider a situation where we need to track changes to a numeric value up or down by some quantity. This could be the amount in a […]

My friend and Oracle maestro Daniel Ekberg wrote a post about ways to manage errors when converting data. Go read his post if you want to see how the errors you get can be better managed. I think he may be the person that first pointed out the feature to me that I want to show my use of. One common way to get data into APEX is to load a spreadsheet into a table. But often you get data […]

In the last post I talked about a form of syntactic sugar I’m not convinced we really needed. This post on the other hand is about one that I think I’ve needed since the first time I logged on to a SQL-based database. When you put together a test case it is often setup to create misc objects and populate them. The first time you run it it works fine but when you rerun it your create commands fail with […]

Now that Oracle DB 23ai has been GA for a while I figure it is time to dig into it and talk about the features för DBA and developers that I find most interesting to talk about. Yes, you’ve read about some of them as info has seeped out during the beta-test and and pre-GA. I however think most people are only looking at it when it is GA as that is when they start looking at when and how […]

Having installed the Graph Server in the last post, lets set up and create your very first graph. This follows Oracles documentation. It however had different ways to do it and I struggled to figure out which would be the easiest. Having abandoned a couple for different reasons this is how I did it and I think it is pretty straight forward and uses the tool interactively as much as possible. We first need a user we’ll use to log […]

Installing Graph Server is pretty straight forward but there are a few things to consider. This will show how to install in Docker, but you’ll be able to adopt for whatever deployment model you want to use. Begin with downloading the Graph SAerver package and a the latest and greatest of Java 11. https://www.oracle.com/java/technologies/downloads/#java11https://www.oracle.com/database/graph/downloads.html Now with this in place you unzip and put both rpms in the same directory. Create a “Dockerfile” in the same directory with this content. A […]

Having installed this on my mac I ran into a few things worth pointing out for others. The software download will pull down a zip-file named x86 instead of the expected aarch64. That seems wrong so I try again. I made numerous attempts in different ways to get it to download a file named for the architecture I wanted. Finally giving up and wondering if it could be due to the contents being the same for both. It is, even […]

Quite a few releases ago in 12c the new that the Graph and Spatial option was now included with the database license. Pretty neat, more stuff included is always nice. So in the database we can use the feature and even run queries against it. But then waht, we get a result back showing all the connections in a graph with data that does not conform to relational modelling. To understand it one has to view it graphically, or it […]

We’ve all been there. You have an application bought from some vendor that has less than adequate knowledge about the Oracle Database. The solution underperforms even though the SQL itself looks OK. You dig into it and it turns out the solution retrieves a lot of data but each fetch gets too few rows from the database and thus the latency of getting more data is 99% of the time it takes. The result is of course that there is […]

These days I meet many people who talk about how they have a project to find an AI-plattform to use on-premises. Using it in the cloud on Autonomous it is pretty obvious that you use one in a handful of AI-services Oracle has at your disposal there. The question is what you should use to build AI-services on the data in your on-prem Oracle database. You don’t need to get anything. This blog post assumes you have an Oracle database […]

This tip is especially useful when migrating to autonomous. The reason is that to enable the magic of the the autonomous database there are things that cannot be done. One such thing is that there are a lot of session settings you cannot use. To see what APIs you can no longer use you can look at the documentation. If you look at alter session there are a number of “alter session set …” that are available. But many you […]

Sometimes new things that are really useful show up in new versions and you can miss it if you’r not paying close attention. EZ Connect Plus is one such feature for me. You may think it is not new and that it has been there for a long time. You’d be correct EZ Connect is not new, but the Plus moniker is. In version 19 that was added. I never noticed and then I was looking for how to connect […]

This turned into a miniseries. First it was about focusing on a cell and then on entering the cell in edit mode, but I figure there is a related thing one might want to do. You could want to read the data in a cell once the Interactive Grid (IG) has been loaded. Maybe you need it for some other processing or you want to present something in the IG also in another region. Say that you show the user […]

A recent post talked about setting up a Base DB Service in OCI and finished with logging into the OS of the srvice and there connecting to the DB. What we often want to do is to be able to connect to it directly from our own computer to use the tools and workflow we have in place. This post is about how to set that up. First you head back into OCI and look at the properties of your […]

I recently wrote about how you set focus on a certain cell in an interactive grid. As often is the case, a solution may work well in many cases but there are some cases where something additional is needed. Say that getting one cell in an interactive grid is what you of want, but some cases are so obvious that the user would want to edit a certain cell once the grid is loaded with data. In this case we […]

You may already have one or two free autonomous databases at OCI. Why then would you want a lower tier database offering, a database that is not autonomous? It turns out there are many reasons why you may want this. No matter what your reason is, setting it up is pretty easy. One thing before you start though, you canot stop it to stop spending money. You have to then terminate the service and later recreate it if you want […]

When an interactive grid is populated with data, most of the time the default setup is great. But for certain applications you know that the user will want to have a certain cell on a certain row in the grid being in focus. Sure you can leave it and just let the user click on it every time. It’s not like a single click is very hard. But helping them be the most productive they can is what most of […]

If you are working with Python and using the oracledb driver you may suddenly encounter DPI -1032 – “user name and password cannot be set when using external authentication”. The issue is with using a wallet for authentication. I encountered it on a server where the version of the oracedb-driver had been slightly upgraded. It was developed and working fine on oraceldb 1.2.2. On 1.3.0 I could ot get it to work. Upgrading to 1.3.2 got it back in working […]

This post is part of a series that starts with this post. Withy having looked at a bunch of things we can do with something as simple as an Auto-REST enabled view up to and including adding security, I just want to finish off with showing that having a view in the first place is not needed If all you want is a GET-service for a SQL. AUTO-Rest gives you many more things automatically, both like all forms of DML. […]

This post is part of a series that starts with this post.

Having gone through much of what can be done with a basic REST-service earlier in this series, it is time to look at securing the service. When you can access a service with noting noire than just the URL, then so can anyone else that has access to sen toe ORDS-server the URL. Not only can they read, but if the view allows writing then they can do that too as an autoREST view has DML capability too in the generated API.

In the example used for this series it will not work as the view goes against a view Oracle has defined and that cannot be updated by you as a user. However, you will typically want to protect read as well as write for your services. That is what we’ll achieve with securing the service. In this post we’ll secure it from access and in the next we’ll look at accessing the secured service.

To secure a service we create a role, and a privilege and then define what it will protect, in our case we’re protecting a path in the URL.

Let’s start with creating a role. It is done by just giving it a name, nothing else is needed.

Remember, we protect the service in the source database/schema. That is rest_demo with the alias rest_demo_schema in the setup for this series.

exec ords.create_role(p_role_name => 'gnome_role');

Why gnome you ask? Well, there is a certain theme to the images in this series.

Now, the next step is to set up a privilege that is used to protect the service.

begin
  ords.create_privilege(
      p_name        => 'gnome_priv',
      p_role_name   => 'gnome_role',
      p_label       => 'Gnome Data',
      p_description => 'Give access till demo data.');
end;
/

With that all there is left is to define what it should protect. With an AutoREST-enabled view our only option is to define a path for the URL to protect.

begin
  ords.create_privilege_mapping(
      p_privilege_name => 'gnome_priv',
      p_pattern        => '/vw_rest_svc/*');
end;
/

With that the service on that path is now protected. Note that the pattern is within the schema-alias. It starts from that point in the URL so it does not work to have /ords/… That is good as it allows the alias for the schema to be changed without the security being side stepped.

All that is left now is to verify that the service is in deed not available anymore.

curl https://.../ords/rest_demo_schema/vw_rest_svc/ | python3 -m json.tool 

{
    "code": "Unauthorized",
    "message": "Unauthorized",
    "type": "tag:oracle.com,2020:error/Unauthorized",
    "instance": "tag:oracle.com,2020:ecid/039ed419abad226de418d37c6f146756"
}

Great, the service is now protected. In fact, it is so well protected that there is no way to access it. Setting up to allow access is where we pick up in the next post.