Blockchain Tables

In this episode of the Oracle University Podcast, hosts Lois Houston and Nikita Abraham kick off a new season with a deep dive into the latest features of Oracle Database 23ai. Joined by Bill Millar, a Senior Principal Database & MySQL Instructor, they explore the new enhancements to blockchain tables, such as row versions, user chains, delegate signer, and countersignature. So, if you're curious about harnessing the power of blockchain tables for your database needs, this is the perfect episode for you!   Oracle MyLearn: https://mylearn.oracle.com/ou/course/oracle-database-23ai-new-features-for-administrators/137192/207062   Oracle University Learning Community: https://education.oracle.com/ou-community   LinkedIn: https://www.linkedin.com/showcase/oracle-university/   X: https://twitter.com/Oracle_Edu   Special thanks to Arijit Ghosh, David Wright, and the OU Studio Team for helping us create this episode.     --------------------------------------------------------   Episode Transcript:   00:00 Welcome to the Oracle University Podcast, the first stop on your cloud journey. During this series of informative  podcasts, we’ll bring you foundational training on the most popular Oracle technologies. Let’s get started! 00:26 Lois: Hello and welcome to the Oracle University Podcast. I’m Lois Houston, Director of Innovation Programs with Oracle University, and with me is Nikita Abraham, Principal Technical Editor. Nikita: Hi everyone! Thank you for joining us as we begin a new season of the podcast. For the next few weeks, we’re going to explore all the new features in Oracle Database 23ai, previously known as 23c. These episodes will be great for you if you’re a database administrator, a developer, or even a database architect. Lois: Right Niki, and while anyone can listen to the podcast, you’re probably going to get the most out of this season if you have prior knowledge or experience with the previous versions of Oracle Database and have used SQL to manage Oracle Databases. Throughout this season, we’ll discuss new features in database availability, architecture, manageability, performance, and security.  01:21 Nikita: Exactly. Today, we're diving into the world of blockchain tables and the new features introduced. First, we'll try to get an overview of blockchain tables that were introduced in 21c. Then, we'll discuss the new features in 23ai, including row versions, user chains, delegate signer, and countersignature.  Lois: So, let’s get started. To take us through all this, we are joined today by Bill Millar. Bill is a Senior Principal Database & MySQL Instructor with Oracle University. Hi Bill! Thanks for joining us. To begin, what is a blockchain table? 01:59 Bill: Well, a blockchain table provides the means for recording transactions where only insert operations are allowed. And rows are protected or restricted based on time as defined when the table is created. This makes the rows tamper-resistant with their chaining algorithms.  02:16 Nikita: Bill, take us through some common attributes of a blockchain table. Bill: They are append only, protects the current data in the table. Made tamper-resistant with their hashing algorithm. And optionally, they can be digitally signed. However, they are mandatory in blockchain platform transactions. Transaction logs, audit trails, compliance information, they can most benefit from using blockchain tables.  02:44 Lois: Bill, let’s talk for a minute about the blockchain tables being tamper-resistant. What makes a blockchain table tamper-proof?  Bill: Well, with the insert only tables, each row is going to be chained to the previous row, except the first row. There's nothing to change it to. So once a row is added, it changes it to the previous row, to the previous row. Rows are linked when the transaction commits. We don't link them beforehand because you might roll back.  03:13 Nikita: Do we have some considerations or guidelines for managing blockchain tables? Bill: One, they may be partitioned. You can specify retention at a table level, the blockchain table itself. You can use the no drop clause. And you can also define it blockchain tables at the row level when you create that blockchain table. Defining a retention period for the table itself or a retention period for the rows.  03:41 Nikita: And are there any restrictions when using blockchain tables? Bill: There are several restrictions for the blockchain table. Some of them are… There are some data types that are not supported. The row ID, long, timestamp with time zone, and so forth. And there are other operations not allowed. A few of them are updating rows, merging rows, truncating, dropping them partitions. Converting a regular table to a blockchain table or vice versa.  So you do want to make sure that you understand the restrictions if you decide that you're going to use a blockchain table. There are some things you can alter in a blockchain table. One is you can modify a retention period.  It cannot be reduced. However, you can make it longer.  04:30 Lois: Ok, I think I’ve got it. So, coming to the 23ai features, what’s new with blockchain tables? Could you give us a brief overview of them before we dive into each one? Bill: So we have the user chain, just a chain of rows based off to three user-defined columns. Previously, the system defined the chain.  The row versions…it allows me to have multiple historical views of a row that's going to be-- that is maintained with the blockchain table. We have the log history. The flashback data archive history tables are now blockchain tables.  And there's also a countersignature. So you can request the time of signing a row that it has a signature for that. That signature metadata is going to be stored within the row, within some hidden columns. And then you can also have a delegate signer. It's an alternate to the user who is allowed to sign rows inserted by that primary user.  05:31 Nikita: What are some advantages of using blockchain tables? Bill: There are benefits of using the blockchain tables in transparent from fraud protection and users don't know as they're inserting the data. You can detect it by verifying the rows in the blockchain table. They are not part of the database itself. It can be more secure when you're validating them. And it is easier than distributed blockchains where multiple blockchains with identical data is being maintained across multiple different platforms.  06:03 Lois: And what about benefits specifically from the 23ai new features?  Bill: We have allowed increased flexibility. Just the user-defined itself, instead of having it just rely on the system-defined. It can guarantee row versioning. The blockchain log history to record and protect the changes. The counter signature, along with the digital signature, can help protect it even more.  So you must specify a version. There is no default version, so you must specify whether either it's going to be version 1 or version 2 and create the table. Version 1 is the version from 21c. You have to specify version 2 if you're going to take advantage of some of the new features in 23c.  And with these two different versions, it does reduce the number of columns that you are going to have accessible. Version 1 uses 20 additional columns to maintain that blockchain information, whereas a version 2 blockchain table is going to use 40 additional columns. So that reduces the number of columns that you can use by 40.  Even though version 2 does use more columns for the hidden information, it does have its benefit. It does allow you to add, drop columns. You can drop partitions with version 2. You have distributed transactions. And you can also use with replication, such as Oracle Golden Gate and Active Data Guard.  07:32 Nikita: Are there restrictions when it comes to using blockchain tables? Bill: Again, make sure that you understand the requirements of your tables when determining if blockchain table is going to be appropriate for your application or not.  XMLTypes are not supported. Can't truncate. Doesn't work with sharded tables. Can't work with different policies such as the automatic data optimization, virtual private database, label security. Cannot use the DBMS_REDEFINITION package on a blockchain table.  08:10 Are you planning to become an Oracle Certified Professional this year? Whether you're a seasoned IT pro or just starting your career, getting certified can give you a significant boost. And don't worry, we've got your back! Join us at one of our cert prep live events in the Oracle University Learning Community. You'll get insider tips from seasoned experts and learn from other professionals' experiences. Plus, once you've earned your certification, you'll become part of our exclusive forum for Oracle-certified users. So, what are you waiting for? Head over to www.mylearn.oracle.com and create an account to jump-start your journey towards certification today! 08:53 Nikita: Welcome back! Let’s get into each of those 23ai new features, Bill. What can you tell us about the row versions feature? Bill: With the row version option, it allows you to have multiple historic views of a row corresponding to a set of user-defined columns. Previously, only the system would define the columns.  When you create these, it automatically creates a view to allow you to view information about that blockchain table with the row version. The system is going to create the view with the same columns. However, the name of that view, it's going to take whatever that table name that you create and it's going to append the _Las$ onto it for that.  And it has not only the same columns of your table, but it also has additional columns in there. One of them be that last row version. This is going to allow you to see, what is the latest version of that row? In order to use the row versions, you must specify with the row version clause when you create the table.  It is also supported with or without primary key. The primary key column must not be identical to the set of the row version column. There are some restrictions, though. So you must specify-- you must specify a row version name with it. And remember, three columns is the maximum. You don't have to have three. You can have one, two, or three. And then the fields that are restricted to the types-- number, char, varchar, and raw.  And it cannot be used with version 1 blockchain tables, meaning blockchain tables came out in 21C. So if you have 21C, you cannot create it. It's a 23C feature. That's why that is like that. So you're going to specify with the row version. And then you're going to give it that row version name because that is required. And then up to three different columns that you want to use.  10:58 Lois: What about user chains? How do they enhance blockchain tables? Bill: So with the user chains, previously again, only the system chains were available. It randomly selected how to change the tables, what columns to chain it with.  Well now a user chain can be defined by the end user. And set up one, two, or three. Well, how many rows do you want to chain? Have that chain apply to. Again, the column types that we just talked about that are only supported. The number of the char, varchar, and raw. But with the user chains and you being able to identify the columns, it adds that additional flexibility to allow you to have this tamper-resistant table to be used by your applications.  So to create that blockchain table, user chain is defined when you create the table. So you're going to define when you create the table what is going to be that chain for that. When you do create that, any rows that have the same change values will be grouped together.  For example, let's say a banking application. I have an account. I make deposits. I make withdrawals. I do balance inquiries because that's all based off of that same field, that account, it'll group those together within the chain.   It does apply the hashing value to the columns that are stored within that chain.  12:27 Lois: Bill, can you explain the blockchain table delegate signer feature?  Bill: What it is, optionally, a signature that can be applied to provide additional security against tampering for that. However, if you do use it, it does require a digital certificate when adding a signature to a row.  Signatures are validated using that digital certificate and any signature algorithm for that. The delegate is an alternate. And it can be used instead of addition to just a user signature. So when I am the user, I create a row, it adds my signature, I can add my certificate to it or now I can have a delegate to do that for me.  So it can be digitally signed by the delegate. It can be signed by the delegate instead of the user itself. So that way, it's verified. Yes, that is good. Well, maybe users are not able to sign the rows they created, but they trust the delegate.  13:32 Nikita: And the last new feature to discuss is a blockchain table countersignature. Bill: A countersignature is going to provide additional guarantees that, hey, this data has been securely stored within our table itself. You can request a countersignature. It is requested at the time of signing a row. So what it's going to do is it's going to record that signature metadata in that row and the counting signature in the signed bytes that can be returned to the caller to verify, yes, that I might want to retrieve that information to use in another source for that.  So we can use that. As we said here, that candidate signature and the sign bytes, we might put it in another data store, might put it in our Oracle blockchain platform. For this non-repudiation purposes, basically what that means is that, hey, it's proof of the origin, the authenticity of it, the integrity of that data. Well, I want to pass that information to something else, another application or source or whatever. So yes, this is trusted information for that. So it gives that additional security. So it assures that the sender that their message was delivered plus gives proof of that sender's identity.  Countersignatures are saved in the blockchain table, that happens to be a blockchain table itself. The countersignature is computed using the bytes, using that hashing algorithm. It's going to include that end user signature, the delegate, or both. Remember, the end user can sign, a delicate can, or it can use both of that information for that. Even though we do save that information in the blockchain table, we recommend if you're going to use this, you might want to store that information outside of the database for those non-repudiation purposes.  15:37 Lois: Thank you so much, Bill, for taking us though all these updates. We look forward to having you back soon to talk us through some more of these new features. Nikita: To learn more about blockchain tables, visit mylearn.oracle.com and search for the Oracle Database 23ai: New Features for Administrators course. Join us next week for a discussion on some more Oracle Database 23ai new features. Until then, this is Nikita Abraham… Lois: And Lois Houston signing off! 16:06 That’s all for this episode of the Oracle University Podcast. If you enjoyed listening, please click Subscribe to get all the latest episodes. We’d also love it if you would take a moment to rate and review us on your podcast app. See you again on the next episode of the Oracle University Podcast.