WebDAV

Category: Linux

  • Wireguard continues .. Adding a Peer

    Wireguard continues .. Adding a Peer

    It quickly became apparent that I would like to add more than one peer to the configuration and stopping and restarting services seemed a bit of an overkill to solve the problem.

    Obviously Wireguard is still pretty much a manual solution, lets face it, it is supposed to be. If it was going to be a mammoth, then we might as well stick with the more standard solutions out there.

    Anyway, to add a Peer and for it to be available immediately, then this is what you have to do.

    Edit your config, usually /etc/wireguard/wg0.conf and add your new Peer to the configuration.

    SSH Config
    [Peer]PublicKey = 09m4KVG6uJ8tz7bW8vVsLiWTcnTePx8cafnucxLQdlM=AllowedIPs = 10.0.0.X/32

    Now we just need to force the reload of the config.

    ShellScript
    > wg syncconf wg0 <(wg-quick strip wg0)

    And now your server has a new peer that can connect to it.

    Now to create a Windows 11 Wireguard Peer, you have two choices, you can either add one manually (which I did, keeping with the theme and all that). See the Screenshot (redacted obviously, but you get the idea).

    A config file is pretty much the same thing, a simple text file with the same information in.

    SSH Config
    [Interface] PrivateKey = XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXS3I=Address = 10.0.0.X/32DNS = 8.8.8.8 #extras can be added but must be comma separated[Peer]PublicKey = XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXJDI=AllowedIPs = 0.0.0.0/0, ::/0Endpoint = A.B.C.D:51820

    Then just click Add Tunnel and point it at the text file to import.

    Anyway, signing off now, hope that helps.

  • WireGuard – The quick and the dirty VPN

    WireGuard – The quick and the dirty VPN

    I have always been an ardent user of OpenVPN and StrongSWAN for the more standardised VPN solutions, but these come with their own headaches. Headaches such as client housekeeping, maintenance of a certificate authority, management of any external resources that may provide service to one of the elements, Directory Server or other LDAP Server and the numerous related protocols, Radius, TACACS, etc.

    I was discussing with a friend how nice it would be to have a simple knock it up and go VPN service, something that could be deployed very quickly for small teams and only had to be set up once and then forgotten about. Then the reply came, “Oh, you mean like WireGuard”?

    How had I not heard of this, there I was stuck in my routine of setting up Certificate Authorities and access services, I had completely missed the coming of WireGuard. So, without further ado I set about quickly setting up a VPN that I could attach my phones to while I was out and about.

    I created an Ubuntu server specifically for the task, then had a conversation in my head about ports (for a number of reasons, but mainly because where the server was situated, who knows what stuff the connectivity supplier blocks from little people like me using), and set about starting the installation of WireGuard.

    >sudo apt install wireguard

    Well that was tricky, not sure even I can cope with that level of complexity to be honest (ed. Let’s be honest, some days it’s remarkable you even remember you’re own name Dave).

    Then we need to have some available IP addresses for our little pool, I wrote a little script to make some fake IP ranges, well a fixed IPv4 range and a generated IPv6. I borrowed the concept from a post on Digital Ocean. It needs streamlining, but one of you lot can do that, I was just testing WireGuard done what I was imagining in my head.

    Bash
    #!/bin/sh
# Script to create a random IPv6 Range based upon the time and machine ID
# written by Dave Wise - 2024
today=`date +%s%N`
id=`cat /var/lib/dbus/machine-id`
sha=`printf $today$id | sha1sum`
rng=`printf $sha | cut -c 31-`
fore=`printf $rng | cut -c 1-2`
mid=`printf $rng | cut -c 3-6`
end=`printf $rng | cut -c 7-`
echo "IPv4 Network|10.8.0.0/24"
echo "Server IPv4|10.8.0.1/24"
echo "IPv6 Network|fd$fore:$mid:$end::/64"
echo "Server IPv6|fd$fore:$mid:$end::1/64"

    This should give an output similar to the following

    Bash
    dave@royo:~# ./create-ips.sh 
IPv4 Network|10.8.0.0/24
Server IPv4|10.8.0.1/24                                                                                                                                  
IPv6 Network|fd1c:3351:fcad::/64                                                                                                                         
Server IPv6|fd1c:3351:fcad::1/64

    So there, we have some numbers to work with, now all we need to do is create the config file, create some allowable connections, referred to as “Peers” in WireGuard terminology and we should be good to go. In its most basic form, it is simply a case of creating public and private keys for every device (including the server) and then saying what is and what isn’t allowed to connect.

    So, lets do it. First we create the main configuration file for WireGuard on the server. For this we need to generate a Private and Public key pair

    Bash
    #!/bin/sh
# A simple script that creates a private and public key inside the
# WireGuard configuration directory.
#
# Author: Dave Wise (c) 2024
# This script is required to be run as root
wg genkey | tee /etc/wireguard/private.key | wg pubkey | tee /etc/wireguard/public.key &> /dev/null

    Now we create our main configuration file using the information we have just created.

    /etc/wireguard/wg0.conf
    [Interface]
PrivateKey = qD7teL1xPmQuGFJ034TkvKqo7HNB1GGGFpsi1fgcLFE=
Address = 10.8.0.1/24, fd1c:3351:fcad::1/64
# ListenPort = 51820
ListenPort = 989
SaveConfig = true
PostUp = iptables -A FORWARD -i wg0 -j ACCEPT; iptables -t nat -A POSTROUTING -o eth1 -j MASQUERADE
PostDown = iptables -D FORWARD -i wg0 -j ACCEPT; iptables -t nat -D POSTROUTING -o eth1 -j MASQUERADE
DNS = 8.8.8.8 8.8.4.4 2001:4860:4860::8888 2001:4860:4860::8844

    A couple of points to note, I have changed the default port to hide behind the secure ftp port so those pesky blocking ISP’s will miss our packets, some will still be a pain, but this should get around most of them. I left the default port there so you can see it, who knows, you might want it. In theory you can also use port 123, the NTP port. I didn’t because I use mine.

    We must also make sure we have turned on net forwarding in sysctl.conf

    /etc/sysctl.conf
    net.ipv4.ip_forward=1
net.ipv6.conf.all.forwarding=1

    You should then see the following output from sysctl.

    >sudo sysctl -p
    net.ipv6.conf.all.forwarding = 1
net.ipv4.ip_forward = 1

    You can also see I added the masquerading to the WireGuard configuration which makes sure traffic gets routed when it needs to get routed. I don’t use UFW for anything, so I haven’t included any config info for it here. I will let you all do some more googling if you want to add support for it.

    Now to enable the service and run it, yes, I know there are currently no peers, but I like to know stuff loads and runs.

    Bash
    sudo systemctl enable wg-quick@wg0.service
    Bash
    sudo systemctl start wg-quick@wg0.service

    Now to check whether it is running and doing its thing.

    sudo systemctl status wg-quick@wg0.service
    wg-quick@wg0.service - WireGuard via wg-quick(8) for wg0
     Loaded: loaded (/lib/systemd/system/wg-quick@.service; enabled; vendor preset: enabled)
     Active: active (exited) since Wed 2021-08-25 15:24:14 UTC; 5s ago
       Docs: man:wg-quick(8)
             man:wg(8)
             https://www.wireguard.com/
             https://www.wireguard.com/quickstart/
             https://git.zx2c4.com/wireguard-tools/about/src/man/wg-quick.8
             https://git.zx2c4.com/wireguard-tools/about/src/man/wg.8
    Process: 3245 ExecStart=/usr/bin/wg-quick up wg0 (code=exited, status=0/SUCCESS)
   Main PID: 3245 (code=exited, status=0/SUCCESS)

Aug 25 15:24:14 wg0 wg-quick[3245]: [#] wg setconf wg0 /dev/fd/63
Aug 25 15:24:14 wg0 wg-quick[3245]: [#] ip -4 address add 10.8.0.1/24 dev wg0
Aug 25 15:24:14 wg0 wg-quick[3245]: [#] ip -6 address add fd1c:3351:fcad::1/64 dev wg0
Aug 25 15:24:14 wg0 wg-quick[3245]: [#] ip link set mtu 1420 up dev wg0
Aug 25 15:24:14 wg0 wg-quick[3279]: Rule added (v6)
Aug 25 15:24:14 wg0 wg-quick[3245]: [#] iptables -A FORWARD -i wg0 -j ACCEPT; iptables -t nat -A POSTROUTING -o eno1 -j MASQUERADE
Aug 25 15:24:14 wg0 wg-quick[3245]: [#] ip6tables -A FORWARD -i wg0 -j ACCEPT; iptables -t nat -A POSTROUTING -o eno1 -j MASQUERADE
Aug 25 15:24:14 wg0 systemd[1]: Finished WireGuard via wg-quick(8) for wg0.

    So everything on the server is running, a quick check to make sure it is actually listening to the real world for connections using netstat.

    netstat -tunlp
    Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0 127.0.0.1:6010          0.0.0.0:*               LISTEN      6346/sshd: dave@pts 
tcp        0      0 127.0.0.53:53           0.0.0.0:*               LISTEN      695/systemd-resolve 
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      983/sshd: /usr/sbin 
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      786/mariadbd        
tcp6       0      0 :::22                   :::*                    LISTEN      983/sshd: /usr/sbin 
tcp6       0      0 :::80                   :::*                    LISTEN      1017/apache2        
tcp6       0      0 ::1:6010                :::*                    LISTEN      6346/sshd: dave@pts 
tcp6       0      0 :::443                  :::*                    LISTEN      1017/apache2        
udp        0      0 0.0.0.0:989             0.0.0.0:*                           -                           
udp        0      0 127.0.0.53:53           0.0.0.0:*                           695/systemd-resolve 
udp6       0      0 :::989                  :::*                                -

    There it is listening on UDP/UDP6 port 989, obviously the “nosey” among you can see I am also running Apache and MariaDB on there, but that’s for something else which I may or more likely, may not document.

    Now we have to move onto creating our clients or Peers, our peers also require key pairs, so we extend out tiny little script a little to create specific pairs in the current directory by adding a parameter to the call, otherwise it does the default (original) behaviour of creating a key pair in the WireGuard config directory. (ed. You should probably do more safety checks here Dave, you don’t want to accidentally overwrite something like a muppet!)

    Bash
    #!/bin/sh
# A simple script that creates a private and public key inside the
# WireGuard configuration directory.
#
# Author: Dave Wise (c) 2024
# This script is required to be run as root

if [ -n "$1" ]; then
  wg genkey | tee "wg-client-$1-private.key" | wg pubkey | tee "wg-client-$1-public.key" &> /dev/null
else
  wg genkey | tee /etc/wireguard/private.key | wg pubkey | tee /etc/wireguard/public.key &> /dev/null
fi

    For the purposes of this exercise, we will create a test client key pair and then add them to the WireGuard configuration.

    Bash
    > create-keys test
> ls -l
-rw------- 1 root root   90 May  6  2024 wg-client-test-private.key
-rw-r--r-- 1 root root   90 May  6  2024 wg-client-test-public.key

    I should probably create a script for creating a client that adds it to the WireGuard peer configuration, should be a simple process really, it should create the keys, add them to the wg0.conf, create a config file for the client end, and if you’re using a phone to connect (as I am in this case) create a QR Code for the WireGuard phone app to read the configuration from. The Linux app QR Encode should do that. Obviously this becomes a major software project to keep track of allocated IP addresses etc etc, which is not what this exercise is about. It’s mainly to prove it works, start the process of automating stuff with scripts. Tell everyone about it and let them use whatever they need from my ramblings and findings to extend their own tools and projects. But remember people, I do love to hear from everyone that finds this stuff interesting and useful. I just created a client directory to run everything in and then keep a track of the last number added. Some housekeeping would obviously be required, but really, how complicated do I want to make it? (ed. Seriously? Half a job Dave pfff)

    Bash
    #!/bin/sh
# Create WireGuard Client script
# Author: Dave Wise © 2024
#
# Edit the variables in the environment section to suit your own requirements
# for your endpoints, servers and available IP ranges.
#

# [Environment Section] --------
endpoint="a.b.c.d:989"
dns="8.8.8.8 8.8.4.4 2001:4860:4860::8888 2001:4860:4860::8844"
ip4prefix="10.8.0."
ip6prefix="fd1c:3351:fcad::"
serverkey="91UNV/Xq7r801XzrWIhUsLuwWLIJn4kS6svnax9fRGw="
# [/Environment Section] -------

if [ ! -f ./.lastadded ]; then
  # Never start at 1 .. always assume the server is at 1 
  toadd=2
else
  lastadded=`cat ./.lastadded`
  toadd=$((lastadded + 1))
fi

ip4="$ip4prefix$toadd"
ip6="$ip6prefix$toadd"

if [ -n "$1" ]; then
  wg genkey | tee "wg-client-$1-private.key" | wg pubkey | tee "wg-client-$1-public.key" > /dev/null
  private=`cat wg-client-$1-private.key`
  public=`cat wg-client-$1-public.key`
  if [ $(id -u) -ne 0 ]
  then echo "Please run as root"
  else
    echo "Adding $1 to WireGuard"
    echo "wg set wg0 peer $public allowed-ips $ip4 $ip6"
    # wg set wg0 peer $public allowed-ips $ip4 $ip6

    # Create config file
    echo "Creating Config file for $1"
    printf "[Interface]\nAddress = $ip4/32\nAddress = $ip6/64\nPrivateKey = $private\nDNS = $dns\n\n[Peer]\nPublicKey = $serverkey\nEndpoint = $endpoint\nAllowedIPs = 0.0.0.0/0, ::/0\n" > wg-client-$1-config.conf

    # IF there is a config file, then create the QR Code for it
    configfile="wg-client-$1-config.conf"
    if [ -f "$configfile" ]; then
      echo "Creating QR Code for $1"
      qrcodefile="wg-client-$1-qrcode.png"
      qrencode --read-from="$configfile" -o $qrcodefile
      if [ ! -f "$qrcodefile" ]; then
        echo "[Failed] Could not create QR Code $qrcodefile"
      fi
    else
      echo "[Failed] Could not create config file $configfile"
    fi

    echo $toadd > .lastadded
  exit
fi
else
  echo "You must supply a client name as the parameter, otherwise, whats the point?"
fi

    That’s everything on the server side done, now to install WireGuard on the phones, scan the QR Codes and connect and that is pretty much all there is to it. So there you have it, it really is a quick and simple VPN implementation that is still encrypted, has a low impact and works. I connect to mine via iPhone, Android Phone (gotta have something test stuff with), Linux VM and Windows 11 without issue.

    If you haven’t got qrencode on your system, then simply add it.

    >sudo apt install qrencode

    You really can’t screw that one up either. Here’s some iphone screenshots I have found online because I forgot to grab mine as I added it.

    I hope this helps someone, I hope it inspires someone to create their own simple VPN’s with WireGuard. It really is a cool thing.

    #peaceout

  • Oracle versus Oracle … and yes it is as sad as it sounds

    Oracle versus Oracle … and yes it is as sad as it sounds

    Well, well, well … a chat to a colleague and I find myself fighting with Oracle Linux .. I started fighting with version 9.3, but alas, 9.3 doesn’t actually run Oracles own f’ing database … well of course it wouldn’t, why would it? I had to downgrade to 8.9, just time wasted, lots of my time wasted to nonsense. This isn’t meant to be a “how-to” or “what if” set of instructions, this is just an encounter I have had with apparently unbreakable software.

    My internet speed isn’t the best here (if I got any further from the exchange, I would be signing up for star-link :/) and 9.3 was 10gb and 8.9 was 12.3gb. Bigger than an average 4.7gb DVD size, but DVD image’s they were, it said so in the file names.

    ShellScript
    > ls downloads/>   OracleLinux-R8-U9-x86_64-dvd.iso

    Thankfully installing the OS’s into Virtual Box (of course, another Oracle product) was relatively painless. However, there were glitches installing the VBOX guest additions. The required kernel headers aren’t installed by default … go figure … I need someone to remind me why Oracle have this market share.

    But after that, it all just goes downhill. As I mentioned, after my mistake of assuming something simple, the latest version of the Oracle Database Free (23C) doesn’t actually run, neigh, doesn’t even install on the latest version of Oracle’s own operating system. I fought with it for a while thinking I must be crazy or something, who would develop and release an operating system that did not run their very own championed and celebrated product. Clearly Oracle do, so upon conceding an undesirable defeat, I deleted the VM and started again with the Oracle Linux 8.9 DVD image.

    Then there is more time lost hunting around for the RPMs you are going to need to install. Here I needed the database install file and the RPMs for the clients, e.g. sqlplus.

    You can get them from here: https://www.oracle.com/database/technologies/free-downloads.html

    ShellScript
    ls -l *.rpm-rw-r--r--. 1 1751573448 Sep 11 14:59 oracle-database-free-23c-1.0-1.el8.x86_64.rpm-rw-rw-r--. 1 55788060 Oct 19 09:52 oracle-instantclient-basic-21.12.0.0.0-1.el8.x86_64.rpm-rw-rw-r--. 1 728100 Oct 19 09:51 oracle-instantclient-sqlplus-21.12.0.0.0-1.el8.x86_64.rpm

    I truly do not understand why are they not included with the OS, these bits should come as standard with the operating system because I bet dollars to doughnuts, that if someone just wants a Linux distribution they will not be installing Oracle’s offering out of personal choice. So Oracle needs to sort that out, and on top of that, they need to be making sure all of their products work together before releasing it into the wild.

    When I first wrote up my notes (yup, these are my notes, I remember things by making them feel funnier than they actually are), I wrote a whole section about installing the database using dnf, using sqlplus to look inside what had just installed etc … however … I seem to have deleted those parts of my notes when I changed the order around of some of the funnies … of course, this adds to the whole scenario somewhat and is perfectly in keeping with everything that has happened thus far in the process.

    But to give an oversight of what’s what, we need to understand that Oracle uses the term Database somewhat differently to what has now become “normalised” thinking. It is actually more akin to the good old days of DBase3+/Clipper or the beef cake IBM Db2 (although this is still in existence but significantly improved). Anyway, the term Database in Oracle terms is the server/software combo, upon with is going to sit the data store. It is this data store that is more akin to the usual suspects such as MySQL, PostgreSQL, MariaDB etc.

    In Oracle Free 23c’s case, it creates a FREE file store, with a CDB and the ability to create up to 16 Plug-able Databases. It is these plug-able databases that are you equivalent to a default MySQL installation. Inside the PDB you then create your table spaces and then your data structures and stored procedures. Hopefully that all makes sense to you, if not, perhaps you should not be reading further, just in case you break something? (LOL Of course I am only kidding, like I care if you break something or not, gotta keep breaking things to be able un-break them and learn after all.) Anyway, when you install the software there are two ways you can go, create an oracle user and build its environment based directly, or you can create an overriding admin (instead of sys as sysdbm). I of course decided to make it more complicated for myself by taking the second of the two paths.

    So, first add your nice shiny new PDB with your chosen user/pass combo that you specified at install.

    ShellScript
    sqlplus sys/adminpassword@//localhost:1521/FREE
    SQL
    CREATE PLUGGABLE DATABASE monopdb  ADMIN USER monouser IDENTIFIED BY monopassword    ROLES = (dba)  DEFAULT TABLESPACE library  DATAFILE '/opt/oracle/product/23c/dbhomeFree/dbs/monopdb/library01.dbf' SIZE 250M AUTOEXTEND ON  STORAGE (MAXSIZE 2G)  PATH_PREFIX = '/opt/oracle/product/23c/dbhomeFree/dbs/monopdb/';

    That then creates a plugable database that can be used like any other database, with it’s own files, users, etc without interference. It’s quite interesting that you can still create separate paths for database files, well, maybe interesting is not the right word. I am not sure it is entirely required any more with seamless storage and cloud storage facilities, but still natty all the same. Now it is possible to log in as that user, but there are some other hurdles we need to overcome first. Of course there are, this is an Oracle install after all.

    Plugable databases, or PDB’s, are created as mounted only, they aren’t open for access in any way shape or form. In version 12 of oracle you had to create a trigger on database start to reopen the pdb’s every time, however the newer versions do allow for saving the state, so you only have to open it once and save the state. the trigger does also still work though if you want to add it anyway.

    SQL
    SQL> show pdbs;
    SQL
    CON_ID  CON_NAME                     OPEN MODE  RESTRICTED------  ---------------------------  ---------  ----------     4  MONOPDB                      MOUNTED
    SQL
    ALTER PLUGGABLE DATABASE monopdb OPEN;
    SQL
    CON_ID  CON_NAME                     OPEN MODE  RESTRICTED------  ---------------------------  ---------  ----------     4  MONOPDB                      READ WRITE NO
    SQL
    ALTER PLUGGABLE DATABASE monopdb SAVE STATE;

    As I said, the trigger still works if you would rather implement that over doing things manually, not sure I understand why, but here it is anyway.

    SQL
    CREATE OR REPLACE TRIGGER open_pdbs  AFTER STARTUP ON DATABASEBEGIN  EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN';END open_pdbs;/

    Right, so we have an accessible PDB . woohoo! We need to adjust that users’ permissions so it can create things in the nice shiny new database.

    We can check out user’s privileges by logging into sqlplus as them, presumably we remember the new password we assigned when we created the PDB? You must have written it down on a post-it note and stuck it to your screen surely? Wait, what year is it again?

    SQL
    sqlplus user/password@//localhost:1521/monopdb SQL> select * from session_roles;ROLE ---------------------------------------------------PDB_DBAWM_ADMIN_ROLE

    As you can see, it is missing the ability to do anything other than log in, really, really useful default state. Anyway …

    Firstly, as the CBD system user, you need to make sure you are in the PDB domain for this session otherwise when you try and do anything it will say “no such user”. It’s helpful like that, it has very intelligent error messages that are almost impossible to misinterpret … that is largely to do with the fact that they are completely unintelligible to start with.

    SQL
    SQL> alter session set container=monopdb Session altered.

    Now let us add some useful abilities for this pup, like connecting and creating stuff, also being able to store stuff in the said things created would probably be a good idea to be fair, but you never really can tell.

    Now we need to reconnect as the CDB system user and let the magic begin.

    SQL
    SQL> grant connect resource …………………………

    Wait, I am not sure what is or isn’t needed, I think I will be better, in the first instance, to grant everything and then reverse out stuff that’s not needed.

    SQL
    SQL> grant ALL privileges to monouser;Grant succeeded.

    Note:It complained with ALL was in lowercase, but worked when it was capitalised, go figure, that will be another little idiosyncrasy of Oracle then I guess.

    I am not entirely sure that which one’s are needed at this point, so I am currently taking a belts and braces approach and just making stuff work, I can always revoke stuff after the fact.

    In case you needed to know, revoking is as simple as granting.

    SQL
    SQL> revoke create session from PDBUSER;Revoke succeeded. // See, f&*k you PDB user .. Muwahahahaha

    Right, so we have now updated our user responsible for the PDB, we can check what they can now do inside their little world.

    SQL
    > sqlplus monouser/password@//localhost:1521/monopdb SQL*Plus: Release 23.0.0.0.0 - Production on Mon Feb 5 13:39:10 2024Version 23.3.0.23.09Copyright (c) 1982, 2023, Oracle.  All rights reserved.Last Successful login time: Mon Feb 05 2024 13:23:34 +00:00Connected to:Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for FreeVersion 23.3.0.23.09SQL> show user;USER is "MONOUSER"SQL> select * from session_roles;ROLE--------------------------------------------------------------------------------CONNECTRESOURCESODA_APPPDB_DBAWM_ADMIN_ROLE

    Of course, we can also check the privileges granted, but bare in mind we granted everything to the user and haven’t as yet, revoked anything.

    SQL
    SQL> select * from session_privs;
    PRIVILEGE

    EXECUTE ANY DOMAIN
    DROP ANY DOMAIN
    ALTER ANY DOMAIN
    CREATE ANY DOMAIN
    CREATE DOMAIN
    ADMINISTER REDACTION POLICY
    ADMINISTER FINE GRAINED AUDIT POLICY
    ADMINISTER ROW LEVEL SECURITY POLICY
    DROP ANY MLE
    ALTER ANY MLE
    CREATE ANY MLE

    PRIVILEGE

    CREATE MLE
    READ ANY PROPERTY GRAPH
    DROP ANY PROPERTY GRAPH
    ALTER ANY PROPERTY GRAPH
    CREATE ANY PROPERTY GRAPH
    CREATE PROPERTY GRAPH
    DROP LOGICAL PARTITION TRACKING
    CREATE LOGICAL PARTITION TRACKING
    DROP ANY ANALYTIC VIEW
    ALTER ANY ANALYTIC VIEW
    CREATE ANY ANALYTIC VIEW

    PRIVILEGE

    CREATE ANALYTIC VIEW
    DROP ANY HIERARCHY
    ALTER ANY HIERARCHY
    CREATE ANY HIERARCHY
    CREATE HIERARCHY
    DROP ANY ATTRIBUTE DIMENSION
    ALTER ANY ATTRIBUTE DIMENSION
    CREATE ANY ATTRIBUTE DIMENSION
    CREATE ATTRIBUTE DIMENSION
    READ ANY TABLE
    ALTER ANY CUBE BUILD PROCESS

    PRIVILEGE

    SELECT ANY CUBE BUILD PROCESS
    ALTER ANY MEASURE FOLDER
    SELECT ANY MEASURE FOLDER
    EXECUTE DYNAMIC MLE
    USE ANY JOB RESOURCE
    LOGMINING
    CREATE ANY CREDENTIAL
    CREATE CREDENTIAL
    ALTER LOCKDOWN PROFILE
    DROP LOCKDOWN PROFILE
    CREATE LOCKDOWN PROFILE

    PRIVILEGE

    SET CONTAINER
    CREATE PLUGGABLE DATABASE
    FLASHBACK ARCHIVE ADMINISTER
    DROP ANY SQL TRANSLATION PROFILE
    USE ANY SQL TRANSLATION PROFILE
    ALTER ANY SQL TRANSLATION PROFILE
    CREATE ANY SQL TRANSLATION PROFILE
    CREATE SQL TRANSLATION PROFILE
    ADMINISTER SQL MANAGEMENT OBJECT
    UPDATE ANY CUBE DIMENSION
    UPDATE ANY CUBE BUILD PROCESS

    PRIVILEGE

    SET CONTAINER
    CREATE PLUGGABLE DATABASE
    FLASHBACK ARCHIVE ADMINISTER
    DROP ANY SQL TRANSLATION PROFILE
    USE ANY SQL TRANSLATION PROFILE
    ALTER ANY SQL TRANSLATION PROFILE
    CREATE ANY SQL TRANSLATION PROFILE
    CREATE SQL TRANSLATION PROFILE
    ADMINISTER SQL MANAGEMENT OBJECT
    UPDATE ANY CUBE DIMENSION
    UPDATE ANY CUBE BUILD PROCESS

    PRIVILEGE

    DROP ANY CUBE BUILD PROCESS
    CREATE ANY CUBE BUILD PROCESS
    CREATE CUBE BUILD PROCESS
    INSERT ANY MEASURE FOLDER
    DROP ANY MEASURE FOLDER
    DELETE ANY MEASURE FOLDER
    CREATE ANY MEASURE FOLDER
    CREATE MEASURE FOLDER
    UPDATE ANY CUBE
    SELECT ANY CUBE
    DROP ANY CUBE

    PRIVILEGE

    CREATE ANY CUBE
    ALTER ANY CUBE
    CREATE CUBE
    SELECT ANY CUBE DIMENSION
    INSERT ANY CUBE DIMENSION
    DROP ANY CUBE DIMENSION
    DELETE ANY CUBE DIMENSION
    CREATE ANY CUBE DIMENSION
    ALTER ANY CUBE DIMENSION
    CREATE CUBE DIMENSION
    COMMENT ANY MINING MODEL

    PRIVILEGE

    ALTER ANY MINING MODEL
    SELECT ANY MINING MODEL
    DROP ANY MINING MODEL
    CREATE ANY MINING MODEL
    CREATE MINING MODEL
    EXECUTE ASSEMBLY
    EXECUTE ANY ASSEMBLY
    DROP ANY ASSEMBLY
    ALTER ANY ASSEMBLY
    CREATE ANY ASSEMBLY
    CREATE ASSEMBLY

    PRIVILEGE

    ALTER ANY EDITION
    DROP ANY EDITION
    CREATE ANY EDITION
    CREATE EXTERNAL JOB
    CHANGE NOTIFICATION
    CREATE ANY SQL PROFILE
    ADMINISTER ANY SQL TUNING SET
    ADMINISTER SQL TUNING SET
    ALTER ANY SQL PROFILE
    DROP ANY SQL PROFILE
    SELECT ANY TRANSACTION

    PRIVILEGE

    MANAGE SCHEDULER
    EXECUTE ANY CLASS
    EXECUTE ANY PROGRAM
    CREATE ANY JOB
    CREATE JOB
    ADVISOR
    EXECUTE ANY RULE
    DROP ANY RULE
    ALTER ANY RULE
    CREATE ANY RULE
    CREATE RULE

    PRIVILEGE

    IMPORT FULL DATABASE
    EXPORT FULL DATABASE
    EXECUTE ANY RULE SET
    DROP ANY RULE SET
    ALTER ANY RULE SET
    CREATE ANY RULE SET
    CREATE RULE SET
    EXECUTE ANY EVALUATION CONTEXT
    DROP ANY EVALUATION CONTEXT
    ALTER ANY EVALUATION CONTEXT
    CREATE ANY EVALUATION CONTEXT

    PRIVILEGE

    CREATE EVALUATION CONTEXT
    GRANT ANY OBJECT PRIVILEGE
    FLASHBACK ANY TABLE
    DEBUG ANY PROCEDURE
    DEBUG CONNECT ANY
    DEBUG CONNECT SESSION
    RESUMABLE
    ON COMMIT REFRESH
    MERGE ANY VIEW
    ADMINISTER DATABASE TRIGGER
    ADMINISTER RESOURCE MANAGER

    PRIVILEGE

    DROP ANY OUTLINE
    ALTER ANY OUTLINE
    CREATE ANY OUTLINE
    DROP ANY CONTEXT
    CREATE ANY CONTEXT
    DEQUEUE ANY QUEUE
    ENQUEUE ANY QUEUE
    MANAGE ANY QUEUE
    DROP ANY DIMENSION
    ALTER ANY DIMENSION
    CREATE ANY DIMENSION

    PRIVILEGE

    CREATE DIMENSION
    UNDER ANY TABLE
    EXECUTE ANY INDEXTYPE
    GLOBAL QUERY REWRITE
    QUERY REWRITE
    UNDER ANY VIEW
    DROP ANY INDEXTYPE
    ALTER ANY INDEXTYPE
    CREATE ANY INDEXTYPE
    CREATE INDEXTYPE
    EXECUTE ANY OPERATOR

    PRIVILEGE

    DROP ANY OPERATOR
    ALTER ANY OPERATOR
    CREATE ANY OPERATOR
    CREATE OPERATOR
    EXECUTE ANY LIBRARY
    DROP ANY LIBRARY
    ALTER ANY LIBRARY
    CREATE ANY LIBRARY
    CREATE LIBRARY
    UNDER ANY TYPE
    EXECUTE ANY TYPE

    PRIVILEGE

    DROP ANY TYPE
    ALTER ANY TYPE
    CREATE ANY TYPE
    CREATE TYPE
    DROP ANY DIRECTORY
    CREATE ANY DIRECTORY
    DROP ANY MATERIALIZED VIEW
    ALTER ANY MATERIALIZED VIEW
    CREATE ANY MATERIALIZED VIEW
    CREATE MATERIALIZED VIEW
    GRANT ANY PRIVILEGE

    PRIVILEGE

    ANALYZE ANY
    ALTER RESOURCE COST
    DROP PROFILE
    ALTER PROFILE
    CREATE PROFILE
    DROP ANY TRIGGER
    ALTER ANY TRIGGER
    CREATE ANY TRIGGER
    CREATE TRIGGER
    EXECUTE ANY PROCEDURE
    DROP ANY PROCEDURE

    PRIVILEGE

    ALTER ANY PROCEDURE
    CREATE ANY PROCEDURE
    CREATE PROCEDURE
    FORCE ANY TRANSACTION
    FORCE TRANSACTION
    ALTER DATABASE
    AUDIT ANY
    ALTER ANY ROLE
    GRANT ANY ROLE
    DROP ANY ROLE
    CREATE ROLE

    PRIVILEGE

    DROP PUBLIC DATABASE LINK
    CREATE PUBLIC DATABASE LINK
    CREATE DATABASE LINK
    SELECT ANY SEQUENCE
    DROP ANY SEQUENCE
    ALTER ANY SEQUENCE
    CREATE ANY SEQUENCE
    CREATE SEQUENCE
    DROP ANY VIEW
    CREATE ANY VIEW
    CREATE VIEW

    PRIVILEGE

    DROP PUBLIC SYNONYM
    CREATE PUBLIC SYNONYM
    DROP ANY SYNONYM
    CREATE ANY SYNONYM
    CREATE SYNONYM
    DROP ANY INDEX
    ALTER ANY INDEX
    CREATE ANY INDEX
    DROP ANY CLUSTER
    ALTER ANY CLUSTER
    CREATE ANY CLUSTER

    PRIVILEGE

    CREATE CLUSTER
    REDEFINE ANY TABLE
    DELETE ANY TABLE
    UPDATE ANY TABLE
    INSERT ANY TABLE
    SELECT ANY TABLE
    COMMENT ANY TABLE
    LOCK ANY TABLE
    DROP ANY TABLE
    BACKUP ANY TABLE
    ALTER ANY TABLE

    PRIVILEGE

    CREATE ANY TABLE
    CREATE TABLE
    DROP ROLLBACK SEGMENT
    ALTER ROLLBACK SEGMENT
    CREATE ROLLBACK SEGMENT
    DROP USER
    ALTER USER
    BECOME USER
    CREATE USER
    UNLIMITED TABLESPACE
    DROP TABLESPACE

    PRIVILEGE

    MANAGE TABLESPACE
    ALTER TABLESPACE
    CREATE TABLESPACE
    RESTRICTED SESSION
    ALTER SESSION
    CREATE SESSION
    AUDIT SYSTEM
    ALTER SYSTEM

    250 rows selected.

    This now looks infinitely more promising, the new user and PDB should be able to be used with abandon. Let us start by creating some tablespace for our new database tables, procedures and whatever else we may need along the way.

    So we are now in a position where we can create our very first table space, well, we can hope that we are at least. Without further ado.

    SQL
    SQL> create tablespace monodb  2  datafile 'monodb.dbf' size  500k reuse  3  autoextend on next 500k maxsize 100m;Tablespace created.SQL> alter pluggable database default tablespace monodb;Pluggable database altered.

    A simple database that can grow as needed, that will do for these purposes and making it work and giving us somewhere to store stuff.

    But what we have now confirmed at least, with the free version of Oracle 23c, we can make use of up to 16 PDB’s with their own user space without risk of bleed into other PDB spaces. More of the model we are used to with the usual suspects, MySQL, Maria, Postgres etc …

    Of course the testing continues …

    We will have to now create some of the usual tables etc in which we can store some stuff, why have a database with nothing in it? Well who knows, someone out there might want one, perhaps it looks pretty?

    We will make a Book Library, because it was either that or a contact database, but as you can probably tell from my ramblings, I don’t know that many people to put in it LOL!

    SQL
    SQL> create table books (  2    id      number     generated by default on NULL as IDENTITY,  3    title   varchar(256) not null,  4    genre   varchar(20),  5    author  varchar(100),  6    isbn    varchar(13) unique  7  ) tablespace monodb;Table created.SQL> describe books; Name                                      Null?    Type ----------------------------------------- -------- ---------------------------- ID                                        NOT NULL NUMBER TITLE                                              VARCHAR2(256) GENRE                                              VARCHAR2(20) AUTHOR                                             VARCHAR2(100) ISN                                                VARCHAR2(13)

    We have, despite Oracle’s best efforts to thwart my progress, a database with a single table exists and it is ready to populate. I don’t want to use WebLogic … I didn’t like it in 2000 … I am not about to start liking it now. I suspect there must be a PHP PDO driver out there waiting for me to find it and install to test it further.

    But before we do all of that, I want to be able to use Ansible to create backups of the databases and server, but interestingly, I should have thought about this before starting anything because … it needs to have ARCHIVELOG enabled for backups and restores to work … of course it does.

    SQL
    SQL> alter database close;SQL> alter database archivelog;SQL> alter database open;

    In my case, for some unknown reason I had to restart the oracle services at this point, this managed to get the databases and PDBs opened in READ / WRITE mode again, but with ARCHIVELOG on.

    SQL
    SQL> archive log listDatabase log mode              Archive ModeAutomatic archival             EnabledArchive destination            /opt/oracle/product/23c/dbhomeFree/dbs/archOldest online log sequence     21Next log sequence to archive   21Current log sequence           20

    So with that done, it should now be possible to use rman ro create backups, so let’s test that theory manually. If this works then creating a script similar to this that can be called by an Ansible script (sorry, ffs, Playbook, so many new words for the same shit, in this case YAML because why use one mark-up language when you can invent another one to do the same thing?) should be relatively straight forward.

    ShellScript
    >rman target sys/password@//localhost:1521/free> backup database2>  format "/home/oracle/backups/backup_%U";Starting backup at 23-FEB-24using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01Finished backup at 23-FEB-24[oracle@mono backups]$ ls -latotal 3324920drwxr-xr-x. 2 oracle oinstall       4096 Feb 23 15:58 .drwx------. 8 oracle oinstall       4096 Feb 23 14:59 ..-rw-r-----. 1 oracle oinstall 1634172928 Feb 23 15:58 backup_012jse1u_1_1_1-rw-r-----. 1 oracle oinstall  627597312 Feb 23 15:58 backup_022jse21_2_1_1-rw-r-----. 1 oracle oinstall  635641856 Feb 23 15:58 backup_032jse22_3_1_1-rw-r-----. 1 oracle oinstall  507281408 Feb 23 15:59 backup_042jse23_4_1_1

    And there we have a backup of everything, happy days.

    Please note I want to backup everything, so I use the system user and password and connect to the actual core instance, this then allows me to backup every PDB and option.

    To be quite honest, I got extremely bored by this point, I have managed to get Oracle Free 23c installed on Oracle Linux 8.9, I have created a PDB instance with its own admin user (granted the user is all powerful at this point and needs to have its wings clipped) and created a table space and basic data structure within it to store data in. Followed by making it work with rman to create backups that can be restored and in such a way as a script (sorry, playbook) can be written in YAML for Ansible to be able to do the backups remotely and in it’s own time without relying on cron or any other local system.

    If you want me to provide a script and YAML example, or indeed a PHP example using the PDO library (which incidentally needs to be recompiled onto the PHP system, because it also requires the oracle clients to be installed on whichever environment is calling the library), then please do let me know, I can happily ignore as many requests as you want to send LOL!

    I hope this experience has helped you along your way with Oracle Linux, Oracle FREE 23c, rman and Ansible … if not … I hope it has just entertained you for the last few minutes of reading.

    #peaceout

    Sources (in no particular order):

    A note about the sources, most of them are oracles somewhat dubious documentation, but there are amongst them, a couple of diamond resources that you would be good to take note of. First of all, kewl.org. The wiki is full of really useful stuff, make sure you check it out. kewl.org is written and maintained by someone with a big brain! Then, then there is the Stack Overflow link, that proved absolutely invaluable in showing the order of things and to coin someone else's catch phrase, "This is the way".