Security on Oracle Autonomous Database
Author: Donatien MBADI OUM, Oracle Consultant
1.
Introduction
In this section, we are going to show
you how to manage users in Autonomous Database (ADB). After that, we are going
to talk about Database Alarms and Events. Access Control List (ACL) and Private
Endpoints to secure access to the database from remote IPs addresses, will be
take place and at the end, we will cover Data Safe and How to connect to ADB
using several tools.
2.
Managing users
When you provisioning your Autonomous
Database (ADB) the ADMIN user is already pre-created for you and you would have
provided the password.
After that, you are going to want to
create additional users either as end users who access to the database or for
your application. On ADB, Oracle simplifies this task by creating a role called
DWROLE or Data Warehouse Role for developers and data warehouse users. You only
simply specify a password and that password will adhere to Oracle’s password complexity
rules.
DWROLE includes all the necessary
privileges for a DW developer/user. There is no need to specify default
tablespaces, a temporary tablespaces or others.
You can create a new user using Cloud
Console, by clicking on Database Actions/Database Users under Administration
Tab.
By default, the password will not expire for
360 days.
You can also use SQL Developer tools
or other regular client utility.
You may
find that you need to change the password for the ADMIN user. That can done through
the regular client tools, however, Oracle also provide a mechanism to do it
within the Cloud Console. It’s simply by clicking the More Actions menu and
then select Administrator Password.
And
when you click that, it will bring up a pop-pup which prompts for the new
password that you wish to update the system with.
3.
Alarms and Events in ADB
Events
can be used for variety of notifications, including ADMIN password expiration,
ADB services going down and wallet expiration warnings. This service is called
Notifications service and it is part of Oracle Cloud Infrastructure (OCI).
This service
provides you the ability to broadcast messages to distributed components using
a publish-subscribe model. These notifications can be used to notify you when
event rules or alarms are triggered or simply to directly publish a message. In
addition to this, there is also something called a topic. This is a
communication channel for sending messages to subscribers in the topic.
To
define an event, you can simply search notifications on the bar menu.
This
will bring you to the page for notifications. To create your notifications, you
may follow the bellow steps:
-
Create a topic (Choose your compartment first)
-
On the topic page, create a subscription (for example, if
protocol is Email, provide your e-mail address.
-
Confirm a subscription sent by e-mail
-
Create and alarm (for example CPU Utilization, under Metrics
Options)
4.
Access Control List (ACL) and Private Endpoints
Access Control List provides a
mechanism to block all IPs addresses that are not in a specified list from
accessing the database. By default, no ACL is specified; the database is
accessible from any IP address. Specify the ACL means that services that are
the subsets, for example things like SQL Developer Web, Application Express (APEX),
OML(Oracle Machine Learning) Notebook will also blocked by these ACL. ACL con
be configured for ADB at any time, either during provisioning or it can be
added to an existing ADB.
ACL can be configured for individual
IP addresses, CIDR blocks ranges or Virtual Cloud Network (VCN). All of these
can be done simultaneously.
To configure ACL, through the Clod
Console, Go to Network section and click on Edit Link, in the front of Access
Control List and then provides the IPs, CIDR or VCN on the IP notation Type
dropdown list. Click on Save Changes button. The status of ACL will change to
Enabled.
Note: The database restore does not
overwrite the existing ACL configuration.
Some users may not want to allow to ADB
to be publicly routable at all. This would be one way of ensuring that access
or traffic to the ADB is completely kept of the public internet. This can be
done by using the Private Endpoints. You can configure a private endpoint when
you provision a new database or when you clone an existing database. So you
cannot convert an existing ADB to use private endpoints but you can a clone of
your ADB into a VCN and convert it to use private endpoints as long as you meet
your requirements.
Before you can specify using a private
Endpoint for you ADB, you need to make sure:
-
There must be a VCN within the region for ADB, this cannot be
change after provisioning
-
You have a private subnet with default DHCP settings
-
At least one Network Security Group (NSG) within your VCN,
This can be changed or edited after provisioning.
To manage private endpoints, you must
have permission to manage private endpoints in the tenancy, as well as to use
virtual network resources, such as VCNs and subnets
Example:
Allow group <group_name> to manage orm-private-endpoints in
tenancy
Allow group <group_name> to use virtual-network-family in
tenancy
where any
request.operation={'CreatePrivateEndpoint'}"
5.
Compartment Quotas
Compartment helps to organize and isolate
your Cloud resources. You can create sub-compartment in compartments to create
hierarchies that are 6 levels deep. Most resources can be moved between
compartments. Compartments also inherits any policies from their parent
compartments. If you are an Administrator, you will have permissions to view
all compartments and work with any compartment’s resources. But if you are user
with limited access, you probably won’t.
Compartment quotas are very similar to
Service limits. However, whereas the service limits are set by Oracle,
compartment quotas are set by customer administrators; in dedicated
infrastructure, this is managed by Fleet Administrator and it is just one of
their responsibility.
Compartment quotas are set using
policy statements that are similar to the Identity and Access Management. There
are 3 types of quota policy statements to limit the availability of a given
resource in a given compartment:
-
set : Sets the maximum number of a cloud
resource that can be used for a compartment
-
unset: Resets quotas back to the default
service limits
-
zero: removes access to a cloud resource
for a compartment
Quota policy statements have the
following format:
For example:
Allow group QuotaAdmin to {QUOTA_READ, QUOTA_CREATE,
QUOTA_DELETE, QUOTA_UPDATE, QUOTA_INSPECT} in tenancy.
Quotas can have a scope:
-
Availability Domain
-
Region
-
Globally
When setting the quota at the
availability domain or region level, the quota is allocated at each AD or
region. You can use WHERE clause to target a specific AD or
region.
The language components for a quota
policy statement are:
-
The action keyword, which correspond to the type of quota
being defined (set, unset or zero)
-
The name of service family; for example compute-core
-
The quota or quotas keyword
-
The name of the quota, which varies by service family. For
example, a valid quota in the compute-core family is standard-e4-core-count
-
For set statements, the value of quota
-
The compartment hat the quota covers
-
An optional condition. For example where request.region=’ ca-montreal-1’
Examples:
-
Limit the ADW CPU core count to 2 in compartment MyCompatment:
set
database quota adw-ocpu-count to 2 in compartment MyCompartment;
-
Limit the number of ATP-Dedicated OCPU to 20 in compartment
MyCompatment:
set
database quota atp-dedicated-ocpu-count to 20 in compartment MyCompartment;
-
Disallow all resources in a service family except specific
resources
zero compute-core quotas in tenancy
unset
compute-core quota standard2-core-count in tenancy
6.
Data Safe
Oracle Data Safe is unified control
center for ADB to manage and assess the security of all your enterprise
database services. It gives a quick and easy way to identify configurations
that may not be compliant of if there are sensitive data that are not been
cataloged correctly. It also provides easy tools to perform data masking
operations.
Data Safe is a free service with all
Oracle Cloud Database (ADB, ExaCS, DBaaS). Oracle only charges you if you
exceed 1 million audit vectors per month per target and if that limit is met
then the pricing is very minimal at 0.08$ per 10 000 per target per month.
The
features of Data Safe includes:
-
Assess: Security Assessment, User
assessment, Data discovery, Data masking, Activity auditing. Security
assessment helps to assess the security of your database configurations and
analyzes the user accounts.
-
Detect: Activity auditing, the security
controls and reports the findings and recommendations for remediation. In data
recovery, it helps you find sensitive data and return to you a list of
sensitive columns.
-
Prevent: Data masking provide you a way to
mask sensitive data, so the data is data for non-production purposes. So like
the most organizations the often to need to copy the production data down to
lower level environments or for testing purpose. Activity auditing lets you
audit user activities so you can monitor database and be alerted by unusual
activities.
7.
Connecting on Autonomous Database
After you create database users, applications and tools connect to
Autonomous Databases using Oracle Net Services (also known as SQL*Net). Oracle
Net Services enables a network session from a client application to an Oracle
Database server.
When a network session is established, Oracle Net Services acts as the
data courier for both the client application and the database. It is
responsible for establishing and maintaining the connection between the client
application and the database, as well as exchanging messages between them.
Oracle Net Services supports a variety of connection types to connect to
an Autonomous Database instance, including:
·
JDBC Thin Driver: for Java applications, the JDBC Thin Driver is a pure
Java driver. Many applications, including Oracle SQL Developer support JDBC
Thin Driver connections.
·
JDBC OCI: which is used by Java language applications. JDBC OCI adds a
layer over Oracle Call Interface for Java applications. The Oracle SQLcl
command-line interface uses JDBC OCI.
·
Oracle Call Interface (OCI): used by many applications written in C
language. Examples that use Oracle Call Interface include Oracle utilities such
as Oracle SQL*Plus, SQL*Loader, and Oracle Data Pump.
·
ODBC Drivers: used by applications running on Microsoft Windows, that
are layered over Oracle Call Interface (OCI).
The following figure shows client
secure connections to Oracle Autonomous Database over the public Internet using
Mutual TLS connections. If you configure your database to use private
endpoints, then the public internet is not used and the connection uses a
private endpoint inside a Virtual Cloud Network (VCN) in your tenancy.
To secure the connection to your ADB
instance a service administrator downloads the client credentials (wallet
files) from ADB. If you are not an ADB service administrator, your
administrator provides you with the client credentials.
The credential zip file contains the
key and other information needed to connect to ADB like:
-
truststore.jks
-
tnsnames.ora
-
sqlnet.ora
-
ojdbc.properties
-
keystore.jks
-
ewallet.p12
-
cwallet.sso
-
ewallet.pem
Oracle ADB uses always-on encryption
that protects data at rest and in transit. All data stored in OCI and network
communication with Oracle Cloud is encrypted by default and encryption cannot
be turned off.
ADB uses certificates authentication
and Secure Socket Layer (SSL). By default, Oracle creates and manages all the
master encryption keys used to protect data, storing them in a secure PKCS12
key stored on the same Exadata Systems, where the database resides. Customer
can control key generation and rotation of the keys.
The default port number for Autonomous
Database mTLS connections is 1522
As the ADB only accepts secure
connections, you first need to download the wallet credentials file containing
your credentials. The wallet is downloaded from the Database
Actions/Administration or from DB Connection8 button or trough APIs calls.
-
The wallet contains several files in a compressed zip file.
-
After downloading the zip file, store it in a secure location
and share the location only with authorized users.
There are two types of wallet.
-
For ADB shared provided either the:
o
Instance wallet file (Contains only credentials and keys for a single ADB)
o
Regional wallet file (Contains credentials and keys for all
ADB in a specified region. Regional wallet should be used by database
administrator only)
-
For ADB dedicated
o
The wallet file contains only credentials and keys for a
single ADB
o
There is no regional file
You can rotate the wallet either for
the instance or for the region. If you do it for the instance, all the keys for
that associated instance are discarded and it generates a completely new
wallet. If you do it for the region, the regional wallet will continue to work,
however, any new connection will be terminated and it will to be re-established
using that new wallet.
There are two connectivity options to
establish a connection to the ADB:
-
Through the public Internet directly
-
Using Fast Connect with a public peering (this option provides
private connections from on-premises networks)
Example 1: Connecting to ADB using SQL
Developer
1.
Download the Client wallet credentials from Cloud Console
using Database actions or DB connection
Click on Download wallet button
2.
Open SQL Developer connection on your local computer, in the
connection panel click on new connection in the dropdown list.
Enter the following information:
-
Connection Name, the name of you cloud connection
-
Username, the database name (ADMIN or other
created user)
-
Password, The password you specified when
creating the user
-
Connection type, Select Clout wallet
-
Configuration File, Browse and select the downloaded credential
zip file or a file given to you by the administrator
-
Keystore, If using SQL Developer 18.2.0 or
newer, no need to provide a keystore password. If using SQL Developer older
than 18.2.0, enter the password that was use when you download the credentials
zip file.
-
Service, in the drop-down lit menu, service
selections are prepended with database names. Select the service depending of
your requirement and priority for your session.
Click Test. Status
Success displays at the button left of Select Database Connection dialog.
Example 2: Connecting to ADB using Database
Actions
After select your ADB instance, you
can access to SQL Developer Web Console. You will be prompted to log in.
Note
that, only the ADMIN user is explicitly granted access to Database Actions. So
if this is your first time accessing on your ADB, you must log in as the admin.
The admin account password here is what you specified during the creation of
your ADB.
The
SQL Page enables you to enter and execute SQL and PL/SQL statements, and create
database objects. You can also save data to a file. Some other features are:
-
Syntax highlighting
-
Error detection
If
you wish to grant new user access to SQL Developer Web, you can in and run the ords_admin.enable_schema
package. It allow you to provide any additional developer to have access to
that URL that they will be connecting to.
BEGIN
ords_admin.enable_schema (
p_enabled
=> TRUE,
p_schema
=>'SCHEMA_NAME',
p_url_mapping_type
=> 'BASE_PATH',
p_url_mapping_pattern
=> 'schema_alias',
p_auto_rest_auth
=> NULL
);
commit;
END;
Aucun commentaire:
Enregistrer un commentaire