Oracle Database 18c,
often simply called Oracle for
short, is one of the three most popular enterprise-class DBMS packages.
This topic is the third step in a four topic sequence showing a complete
installation and then utilization of Oracle 18c XE from the very beginning.
Please see the Install
Oracle topic for basic info on Oracle XE.
Disclaimer:
These topics were accurate when written, using the versions of installation
software indicated. Third party packages can and do change, so this
topic may be out-of-date. It is provided as an example of how such
installations can be approached.
Installing Oracle XE and readying it for use in typical installations involves four steps:
Open a Firewall Port for Oracle
Configure Oracle
This is the third topic in the above sequence of steps.
In this topic, we are using a 64-bit Windows 10 system to host the Oracle XE installation, to which we have connected via Remote Desktop (RDP). The first part of the topic was conducted on that machine via RDP. The second part of the topic was conducted using Manifold on a remote machine, also running 64-bit Windows 10, on which we have installed Oracle Instant Client software, connecting to the Oracle server through the network.
In this topic we will configure our new Oracle XE installation by creating and opening for use a pluggable database. We will also create an administrative user for that pluggable database and a special user with database administrator privileges for more convenient configuration.
Some of what follows may seem arbitrary, but in a highly sophisticated system like Oracle there are good reasons for what may seem to be puzzling complexities at first glance. The complexity of user roles, for example, is driven by the need to maintain security and administrative hierarchies in extremely complex systems that can involve thousands of users across worldwide organizations. The Oracle XE we are using may have some size limitations, but it is basically a fully functional equivalent to Oracle's flagship Enterprise Edition, with all the same deep and useful sophistication for such demanding applications.
In an ideal world we could connect to Oracle using Manifold and then issue commands in the Command Window for that data source. Unfortunately, some commands Oracle uses for configuration are quasi-SQL in that they must be executed within Oracle's own SQL Plus command line interface, or within other Oracle tools. Since the SQL Plus command line utility was automatically installed when we installed Oracle XE in the Install Oracle topic, we will use SQL Plus.
We connect to the Windows machine on which Oracle XE was installed using Remote Desktop (RDP). We launch a Windows Command Prompt window as Administrator by entering Command Prompt in the Windows taskbar search box for launching commands, and then right-clicking the Command Prompt app that is offered and choosing Run as administrator.
In the Command Prompt window enter
sqlplus
and press Enter.
That launches the SQL Plus command line program. For a user name, enter
SYS AS SYSDBA
We are telling SQL Plus we want to login as the SYS user in the SYSDBA role. Logging in as a user within a given role is not something we can do via a connection string, so this is one of the reasons we are using SQL Plus instead of Manifold.
Enter the password we provided in the Install Oracle topic, in this case 12345xy. The password will not appear.
The SQL> prompt indicates we have successfully logged into SQL Plus as the SYS user in the SYSDBA role. Commands we enter at the command line will now be executed by Oracle's SQL Plus command system.
We often use the word database casually, as a synonym for Database Management System (DBMS), or, in the case of file databases, in the sense of connecting to a particular database file, such as a particular GPKG database file or a particular Microsoft Access database .mdb file. In this topic we use the special Oracle meaning of database, to mean a collection of tables, queries and other infrastructure grouped together under a name within a particular Oracle DBMS installation.
Oracle XE follows modern Oracle enterprise architecture in that a particular Oracle installation has an overall container database (CDB) within which one or more pluggable databases (PDBs) are created. Each pluggable database is its own world, separate from other pluggable databases. When connecting to a Oracle server, Oracle allows us to specify the pluggable database within that server to which we want to connect.
They are called pluggable databases because they may be plugged and unplugged from the overall container database. Oracle XE allows up to three pluggable databases in a container database.
We could, in theory, connect to and use the overall container database without ever creating pluggable databases. But that would be disorderly, somewhat like keeping hundreds of files in a Windows system in the root of the C: drive without ever organizing those hundreds of files into sensible folders. It is much more orderly and efficient to create one or more pluggable databases within which we group tables and other components. For example, within our Oracle server we might create a pluggable database called gispdb within which we will keep all of our GIS data. Oracle pluggable databases are often named by their users with a name that ends in pdb. Case is not significant in pluggable database names.
We also create pluggable databases so we can control user access and privileges better. For example, we can create a user account that has database administrator (DBA) privileges within that pluggable database but which has no ability to change anything in the overall container database or in other pluggable databases.
Back to our SQL Plus command line session, where we will create a pluggable database.
This requires several lines for clarity, so at the end of each line we press Enter. At the end of the final line we will add a semicolon ; character and then press Enter. Our first line is:
CREATE PLUGGABLE DATABASE gispdb
When we enter a line that does not end in a semicolon ; character, the next line starts with a line number, in this case 2. The next line we enter is
ADMIN USER gis_adm IDENTIFIED BY "12345xy"
The syntax of the CREATE command allows us to specify and create an administrative user and password associated with the gispdb pluggable database. We choose a name for the admin user, gis_adm, that is similar to that of the pluggable database gispdb, to help remind us which user does what. We could create other user names, such as gis_fred or gis_parcels, for other roles that are named in some standard way to indicate their association with the gispdb pluggable database.
Creating the gis_adm
user in this way does not give
any privileges to that user. We will do that later on in this topic.
The syntax uses an odd choice of words to say the password "identifies" the user (most people might think the login name "identifies" the user...) instead what might be the more logical choice of "password" or similar. No worries... after doing this a few hundred times it seems perfectly normal.
For the third line we enter
CREATE_FILE_DEST='C:\Oracle18xe\oradata';
This tells Oracle XE where to put files, such as the data file, associated with the new gispdb pluggable database we will create. Setting CREATE_FILE_DEST also turns on Oracle File Management (OFM) so Oracle automatically handles housekeeping related to files, such as deleting files if a pluggable database is dropped, that is, deleted.
The terminal semicolon ; character tells SQL Plus that the SQL we have constructed is finished, and should be executed when we press Enter.
Oracle XE works for a while and then reports the pluggable database has been created. However, simply creating the pluggable database is not enough. For now, it is created and mounted, but it is not yet open. We must open it in Read, Write, or Read Write mode.
We can do that with the ALTER statement, entering
ALTER PLUGGABLE DATABASE gispdb OPEN READ WRITE;
Press Enter. The ALTER command to open a pluggable database for reading and writing is one of the commands that for execution requires SYSDBA privileges for our SYS login, so this is one of the reasons we are using SQL Plus.
Further down in this example we will issue another ALTER command, to save the open state of the pluggable database. We could do that command right now if we wanted and not wait until later.
SQL Plus reports success, having altered the gispdb pluggable database to Open status for reading and writing.
For all of the right reasons, Oracle XE provides an extremely extensive framework of privileges and roles that can be combined in endless permutations to achieve whatever security or administrative effect is desired. When those privileges and roles operate within the context of the overall container database or are restricted to the sandbox environment of a pluggable database, we can achieve very fine control over how we allow people and processes to work with our Oracle installation, if we have the know-how to operate all that correctly.
In simple situations, where only a few people might need to utilize an Oracle XE installation, it is tempting to cut through the need to learn intricate administrative details by simply creating a superuser account that has all privileges both in the overall container and within any pluggable database. We can then login with that superuser account from Manifold sessions to accomplish whatever administrative task we want, without having to use RDP to connect to the server machine on which Oracle was installed, and then launching SQL Plus and logging in using SYS as SYSDBA.
It is obviously a risky move to create a superuser account that has all privileges which can be used from remote machines. We want to ensure that the name of that account and the password are not visible to other people, and we should use that account only to create or to alter privileges from actual, working administrative accounts, such as the gis_adm account, that are limited to only a single pluggable database and within that database have only the minimum number of privileges they require to do the job.
We now will create a common user called C##manifold to which we will give all privileges. Common users in Oracle are users which exist both in the overall container database context as well as within the contexts of all of the pluggable databases. The name of a common user must begin with C## (case not significant). We could call the user anything we like, such as C##SuperSys, or c##Ringo, but for this documentation we will use C##manifold.
Once we create and grant all privileges to the new C##manifold user, we can login with that C##manifold user from a remote Manifold session to finish our configuration of Oracle XE. We will also have a convenient superuser login at hand should we need to do any other exceptionally potent administrative work from a remote machine.
We create the new user with the command
CREATE USER C##manifold IDENTIFIED BY "12345xy" ;
Next, we grant all privileges to that user with
GRANT ALL PRIVILEGES to C##manifold container=all ;
The container=all part is necessary to ensure the common user has all privileges not just in the context of the overall container database but also in the context of all pluggable databases as well. Pluggable databases are also containers, hence the container=all syntax.
We now have created an exceptionally powerful common user called C##manifold that has all privileges throughout our entire Oracle XE installation. We can use that superuser to login from a remote Manifold session to issue administrative commands within Oracle XE. Our first such command will be to grant DBA (database administrator) role privileges within our gispdb pluggable database to our fledgling gis_adm admin user.
A few steps back we opened our gispdb pluggable database in read/write mode. That is fine for our current session, but if we reboot our server or otherwise restart Oracle, the gispdb pluggable database will not automatically be opened in read/write mode. We need to save the currently open state in a persistent way. That is easy to do.
Before we close the SQL Plus window, we will issue one last command.
ALTER PLUGGABLE DATABASE gispdb SAVE STATE;
This saves the currently open read/write state of the gispdb pluggable database so that the next time Oracle is started the gispdb pluggable database will automatically be opened read/write. If we neglect this command and Oracle restarts for any reason, the next time we try to connect to gispdb we will not be able to do so, because the pluggable database will be mounted, but not yet opened.
Every machine running Manifold which
will be used to connect to Oracle must
have Oracle Instant Client software installed. If we want
to connect to our Oracle XE server from a remote machine running Manifold,
we must install Oracle Instant
Client software on that remote machine. That is easy to do.
We have downloaded the x64 Instant Client for Windows package from https://www.oracle.com/technetwork/database/database-technologies/instant-client/downloads/index.html That page provides both 64-bit and 32-bit versions of Instant Client software for Windows.
That page provides a zip file, called instantclient-basic-windows.x64-18.5.0.0.0dbru.zip, that when unzipped creates a folder called instantclient_18_5 that contains the required .dll files. Place the instantclient_18_5 folder, with all the files it contains, in the extras folder in the Manifold installation folder. When Manifold needs Oracle client software it will drill into the instantclient_18_5 folder to find the .dll files that are required.
On the remote system we normally use as our desktop machine, we launch Manifold.
Choose File - Create - New Data Source.
In the dropdown menu we then choose More... to launch the New Data Source dialog.
Enter a descriptive name to help us remember what this data source is for, Oracle GISPDB as c##manifold, and choose Database: oracle as the type. Click the browse [...] button to launch the Database Login dialog.
Enter the server connection string, which starts IP address of the server, which in this example is 192.168.2.44, to which after a colon : character the port 1521 is appended, followed by a forward slash / character and the name of the pluggable database, GISPDB, to which we would like to connect. Case is not significant, but many Oracle users prefer to capitalize the names of databases while using lower case for user names.
Choose Use login and password and then enter c##manifold as the login. Enter the password we provided for the c##manifold user when creating the user in SQL Plus.
It is a good idea to check for errors by pressing the Test button.
If that does not report a connection, we should fix any mistakes. When we see Connection established we press OK.
Back in the Database Login dialog, press OK.
Back in the New Data Source dialog we press Create Data Source.
A new data source called Oracle GISPDB as c##manifold appears in our Project pane. We can expand it as seen above, to see the large number of tables that are part of the infrastructure of an Oracle pluggable database. That data source is a connection to the pluggable database called gispdb within our Oracle XE server, a connection that has been made by the c##manifold user with all the privileges of that user for any operations within that data source.
A risk element for security is the
plain text use of passwords in connection strings. We should not leave this project unattended
where somebody else can right-click
on the data source to see the Properties
of it, which would reveal in plain text the password for c##manifold in
the JSON string that gives the connection string. We
should only create such data sources for as long as we need to use the
mighty powers of this special superuser we have created. When our
work is done, we should delete
the data source from the project. When other people might have access
to our computer and our files, we should not
save the project with the data source in it, since someone else might
open that project to find the password string inside.
We can avoid such risks by using integrated security, where the Windows login through which a connection is made is mapped into user accounts within the DBMS, to which all the power and sophistication of roles and privileges can apply. To keep the presentation as simple as possible, except for the SQL Server example, integrated security is not used in these case studies.
We will now use the mighty powers of our c##manifold user account to issue commands in a Command Window.
In the Project pane we right-click on the Oracle GISPDB as c##manifold data source and choose New Command Window.
That launches a Command Window that operates in the context of the gispdb pluggable database within the Oracle XE server. Whatever SQL and other commands we enter into that command window will be executed within the Oracle XE server using the native SQL of Oracle, and executed with the "all privileges" power of the c##manifold user.
We enter the SQL statement
GRANT DBA TO gis_adm
without a semicolon ; character at the end. We press the ! button to run the query.
When a Command Window is running native SQL within an Oracle server, Oracle will issue an "SQL command not properly ended" error when an SQL statement is ended with a semicolon ; character, the usual SQL standard. Statements execute without error without a semicolon ; character at the end. Most databases decompose native SQL statements within the server, while Oracle does so within Oracle Instant Client software client side, causing this effect.
The result of the query is that the collection of privileges within the DBA role have been added to the gis_adm user in the context of the gispdb pluggable database.
We issued this command within the data source created by connecting to the gispdb pluggable database as the c##manifold user. Using the mighty powers of the c##manifold user ensured our Command Window had the privileges required to grant privileges to other users, and issuing the command within the context of the gispdb pluggable database had two effects: first, that ensured that users created within the pluggable database, such as gispdb, were visible and reachable by the Command Window, and second, that privileges within the gispdb pluggable database for those users could be assigned.
So far, we have created a pluggable database, called gispdb, to provide a safe and isolated environment within which we can keep all our GIS data. We have used SQL Plus to open that pluggable database for read/write use. We have also created a common user, c##manifold, with all privileges both in the overall container database as well as in all of the pluggable databases within our Oracle XE installation. We have also created and given database administrator privileges within the gispdb pluggable database to a user called gis_adm
We now have a fully functioning pluggable database set aside for GIS work and an administrator user account for that pluggable database. Our next step is to show how to work with that pluggable database by loading it with Manifold drawings and tables.
Continue this case study on installing Oracle with the Connect to Oracle topic.
See the preceding topic: Open a Firewall Port for Oracle
Jump to the beginning of the case study: Install Oracle
File - Create - New Data Source
Open a Firewall Port for Oracle
Big List of Formats and Data Sources
Example: Switching between Manifold and Native Query Engines