ORA-01017: incorrect username/password, login denied when using a wallet

If you find yourself in the situation where you cannot make a connection using your wallet because of the error

ORA-01017: incorrect username/password, login denied when using a wallet

It might be worth checking that the user you have created an entry in your wallet for, also is granted the sysdba role.
Example:
I want to use a database user called "dbamaster" to performa a clone from active database over the network.
Since I do not wish to reveal the password of the "dbamaster" user, I add the user to a wallet, and then use the notation

connect target /@proddb
connect auxiliary /@testdb

to connect.

Listing the content of the wallet shows that everything is in order:

mkstore -wrl $TNS_ADMIN/wallet -listCredential
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2021, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:
List credential (index: connect_string username)
2: testdb dbamaster
1: proddb dbamaster

You can connect successfully to both databases using the "dbamaster" user, using TNS:

-- From auxiliary to target
sqlplus dbamaster@proddb

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 29 20:03:09 2022
Version 19.15.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Enter password:

Koblet til:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0

DBAMASTER@proddb>

-- From target to auxiliary
sqlplus dbamaster@testdb

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 29 20:43:43 2022
Version 19.15.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0

DBAMASTER@testdb SQL>

Everything seems in order, but incorrect password nevertheless.

Check the v$pwfile_users view, which holds the current users that have sysdba privilege:

col account_status format a20
col username format a20
col password_profile format a20
select username,sysdba,sysoper,account_Status,password_profile from v$pwfile_users;

USERNAME             SYSDB SYSOP ACCOUNT_STATUS       PASSWORD_PROFILE
-------------------- ----- ----- -------------------- --------------------
SYS                  TRUE  TRUE  OPEN                 NOEXP

In this case user "dbamaster" was missing.

Add it:

grant sysdba to dbamaster;

Check the list of sysdbas again. You should now have two entries there:

 select username,sysdba,sysoper,account_Status,password_profile from v$pwfile_users;

USERNAME             SYSDB SYSOP ACCOUNT_STATUS       PASSWORD_PROFILE
-------------------- ----- ----- -------------------- --------------------
SYS                  TRUE  TRUE  OPEN                 NOEXP
DBAMASTER            TRUE  FALSE OPEN                 NOEXP

Your connections should now work:

RMAN> connect target /@proddb

connected to target database: proddb (DBID=253631174)

RMAN> connect auxiliary /@testdb

connected to auxiliary database: TESTDB (DBID=296823958)

What is the difference between mkstore and orapki?

The difference is mainly that the orapki tool is dealing with certificates rather than the wallet itself. The mkstore is more of a tool for administrating privileged users and their passwords, so that you can set up connections without exposing their passwords in your scripts.

Can both of these tools be used to manage my wallets?
Yes.
From MOS Doc ID 2044185.1 "What is an Oracle Wallet?":

If you configure TDE the database creates the wallet for you when you issue the ALTER SYSTEM command to initialize TDE, the other tools to create and inspect wallets are Oracle Wallet Manager (owm) which is a GUI tool, then we also have command line tools orapki to setup certificates and mkstore more suited to store so called secret store entries like the above mentioned user credentials, often the tools can be used interchangeably, for example if you create a wallet for TDE using the database with SQL you can inspect the contents later using mkstore etc.

What exactly is the orapki tool?

From the latest Oracle documentation:
The orapki utility manages public key infrastructure (PKI) elements, such as wallets and certificate revocation lists, from the command line.

What exactly is the mkstore tool?

From the latest Oracle Documentation:
The mkstore command-line utility manages credentials from an external password store.

What exactly is a wallet?

A wallet is a password-protected container that is used to store authentication and signing credentials, including private keys, certificates, and trusted certificates needed by SSL.
It can be stored directly on the server, wherever suits the DBA. The path must be pointed out in the client's sqlnet.ora file, using the directive WALLET_LOCATION

For an example on how to set up a wallet, see this post

How to check the password file version

oracle@oric01.oric.no:[PRODDB01]# orapwd describe file=/u01/oracle/product/12201/dbs/orapwPRODDB01
Password file Description : format=LEGACY

If you try to create the password file with a password that is too simple, you may see this error from orapwd:

OPW-00029: Password complexity failed for SYS user : Password must contain at least 8 characters

The format is set to 12.2 by default, and from the orapwd help text there doesn't seem to be anything lower than this.
If you for some reason need to keep an old password which is too simple for today's default settings, you can create a "legacy" password by using the format "legacy". This is not documented in the help text:

orapwd file=u01/oracle/product/12201/dbs/orapwPRODDB01 format=legacy force=Y

Enter password for SYS:

How to use mkstore to set up passwordless duplication

Oracle provides a method called "Secure External Password Store" to hide your passwords in a wallet, instead of using them directly in your scripts.

The feature Secure External Password Store can be used without any restriction in all product editions, you do not require a license for the Advanced Security Option (ASO).

/

What exactly is a "secure external password store"?
Oracle describes it as "a secure software container that stores authentication and signing credentials"
You need to use the utility mkstore to manage secure external password store.

Personally, I find them very useful when performing duplication. Here is how I set things up when I was cloning a database for standby.


NOTE: If you intend to clone for standby, you need to add the SYS user and nothing else to your wallet!
Any other privileged user will give you trouble during cloning.


On the standby server, create directory where you want the wallet to reside. I normall put it in the $TNS_ADMIN directory:

mkdir -p $TNS_ADMIN/wallet

Create the wallet:

mkstore -wrl $TNS_ADMIN/wallet -create
Oracle Secret Store Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

Enter password:
Enter password again:

Add a credential to one or more database

mkstore -wrl $TNS_ADMIN/wallet -createCredential hdal_primary dbaadmin
mkstore -wrl $TNS_ADMIN/wallet -createCredential hdal_stby dbaadmin

Note!
If you need to include a domain in your credential, use " " around your database name:

mkstore -wrl $TNS_ADMIN/wallet -createCredential "hdal_primary.mydomain.com" dbaadmin

If you later need to modify the same credential:

mkstore -wrl $TNS_ADMIN/wallet -modifyCredential hdal_stby dbaadmin

If you later need to delete a credential:

mkstore -wrl $TNS_ADMIN/wallet -deleteCredential hdal_stby

Verify that the credentials were added:

mkstore -wrl $TNS_ADMIN/wallet -listCredential
Oracle Secret Store Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:
List credential (index: connect_string username)
2: hdal_stby dbaadmin
1: hdal_primary dbaadmin

Add the following in the server's $TNS_ADMIN/sqlnet.ora:

#Point out the server ("client") wallet
WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
  (DIRECTORY = /orasw/product/12201/network/admin/wallet)
  )
 )
 
#This setting causes all CONNECT /@db_connect_string statements to use the information in the wallet at the specified location to authenticate to databases:
SQLNET.WALLET_OVERRIDE = TRUE
 
#BUG 20721271 DUPLICATE FOR STANDBY FROM ACTIVE DATABASE HANGS WHILE RESTORING CONTROL FILE
#Turning this parameter on disables the ability to send and receive "break" messages using urgent data provided by the underlying protocol.
#This would apply to all protocols used by the client.
DISABLE_OOB=on

It works fine with an environmental variable directly in the sqlnet.ora file, too, instead of an absolute path:

WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
  (DIRECTORY = $TNS_ADMIN/wallet)
  )
 )

You should now be able to connect to both databases using rman. Test with a simple script called conntest.cmd.

connect target /@hdal_primary
connect auxiliary /@hdal_stby
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate auxiliary channel aux1 type disk;
allocate auxiliary channel aux2 type disk;
configure device type disk parallelism 2;
}
exit

Output in my case was:

connected to target database: HDAL (DBID=1893290026)

connected to auxiliary database: HDAL (not mounted)

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=1192 device type=DISK

allocated channel: c2
channel c2: SID=52 device type=DISK

allocated channel: aux1
channel aux1: SID=1152 device type=DISK

allocated channel: aux2
channel aux2: SID=1150 device type=DISK

old RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters are successfully stored
released channel: c1
released channel: c2
released channel: aux1
released channel: aux2

How to debug an ssh connection

ssh -X -v myserver.mydomain.com

You can also use -vv and -vvv for more detailed debug information.

New security feature in Oracle 12.2: INACTIVE_ACCOUNT_TIME

A profile in Oracle 12.2 can now be configured with the setting INACTIVE_ACCOUNT_TIME, which specifies the maximum number of days an account can remain unused. Unless a new login occur within the specified number of days, the account will be automatically locked.

If not set, any custom-made profile will inherit the setting of the DEFAULT profile, which is UNLIMITED.

Syntax:

CREATE PROFILE my_profile LIMIT
INACTIVE_ACCOUNT_TIME 30;

ALTER PROFILE my_profile LIMIT
INACTIVE_ACCOUNT_TIME 30;

Sources:
Oracle 12.2 New Features guide

Automatically Locking Inactive Database User Accounts

How to create a proxy authenticated user in Oracle

Create a general user for authentication:

CREATE USER APP_POOL
IDENTIFIED BY app_pool_users123
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE APP_USERS
ACCOUNT UNLOCK;

GRANT CREATE SESSION TO APP_POOL;

To illustrate how proxy authentication can be used efficiently, I create two roles:

create role app_pool_role1 not identified;
create role app_pool_role2 not identified;

Grant object privileges on two different tables to the two new roles:

grant select on SCOTT.DEPT to app_pool_role1;
grant select on SCOTT.EMP to app_pool_role2;

Create a user:

CREATE USER VEGARD
IDENTIFIED BY vegard123
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE APP_USERS
ACCOUNT UNLOCK;

Grant the ability to create sessions, and both the previously created roles to the new user, and enable them by default:

GRANT CREATE SESSION TO VEGARD;
GRANT APP_POOL_ROLE1 TO VEGARD;
GRANT APP_POOL_ROLE2 TO VEGARD;
ALTER USER VEGARD DEFAULT ROLE ALL;

Change the user so it connects through a proxy user.
Make sure that only the role APP_POOL_ROLE1 is enabled whenever the user connects through the proxy:

ALTER USER VEGARD
GRANT CONNECT THROUGH APP_POOL
WITH ROLE APP_POOL_ROLE1;

Let's connect to the database:

sqlplus app_pool[vegard]/app_pool_users123@pdb01

Verify the connection and session settings by using sys_context:

select sys_context('USERENV','AUTHENTICATED_IDENTITY') "AuthIdentity used", 
       decode(sys_context('USERENV', 'AUTHENTICATION_METHOD'), 'NONE', 'proxy') "auth method",
       sys_context('USERENV','CURRENT_SCHEMA') "current schema",
       sys_context('USERENV','CURRENT_USER') "grantee of privileges used",
       decode(sys_context('USERENV','IDENTIFICATION_TYPE'), 'LOCAL', 'Password') "identification type",
       sys_context('USERENV','PROXY_USER') "proxy user"
from dual;

Results shows that user VEGARD is authenticated by proxy, the current schema is VEGARD, the privileges for the session belongs to user VEGARD, the identification type is password and the proxy user is APP_POOL:

AuthIdentity used	auth method	current schema	grantee of privileges used	identification type	proxy user
VEGARD	proxy	VEGARD	VEGARD	Password	APP_POOL

Which roles are enabled?

select 'APP_POOL_ROLE1: ' || sys_context('sys_session_roles','APP_POOL_ROLE1') "Role granted?"
from dual
union
select 'APP_POOL_ROLE2: ' || sys_context('sys_session_roles','APP_POOL_ROLE2')
from dual
;

Result shows that only APP_POOL_ROLE1 is enabled:

Role granted?
APP_POOL_ROLE1: TRUE
APP_POOL_ROLE2: FALSE

Let's verify that the roles are actually working.
Logged in as user VEGARD, I now expect to be able to query the table scott.dept and nothing else:

VEGARD@pdb01 SQL> select count(*) from scott.emp;
select count(*) from scott.emp
                           *
ERROR at line 1:
ORA-00942: table or view does not exist


VEGARD@pdb01 SQL> select count(*) from scott.dept;

  COUNT(*)
----------
         4

There is nothing preventing the user VEGARD from connecting directly to the database:

sqlplus vegard/vegard123@pdb01
VEGARD@pdb01 SQL> show user
USER is "VEGARD"

When bypassing the proxy user, the users have access to both roles APP_POOL_ROLE1 as well as APP_POOL_ROLE2, and may now access both scott.emp and scott.dept:

VEGARD@vegdb01 SQL> select count(*) from scott.emp;

  COUNT(*)
----------
        14

Some advantages with using proxy connections:

* No more password administration for individual users
* Proxy authentication allows for role based access control
* Identity preservation of the real user behind the proxy user is automatically set up

Keep in mind that you still have to create and provision the end user accounts.

How to setup Oracle Network Integrity

Encryption will protect your data from being read in clear text, when in transition.
However, data can still be tampered with and arrive modified at the receiver's end.

To protect against modification, you need to ensure the integrity of the data being sent over the network.

Data integrity protection from Oracle works independently from the encryption process. In other words, you can use both encryption and checksuming together or either one separately.

To implement network integrity, add the following to your server’s sqlnet.ora file:

SQLNET.CRYPTO_CHECKSUM_SERVER = requested
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER= (SHA256, SHA384, SHA512, SHA1, MD5)

All of the hash algorithms in the list create a checksum that changes if the data is altered in any way.

The value "requested" in the CRYPTO_CHECKSUM_SERVER instructs the Oracle server that calculating checksums when sending network packets to the client is desirable, but not required. Other valid settings are "required", "accepted" and "rejected". See the documentation for details.

The list of available encryption algorithms are stated in the CRYPTO_CHECKSUM_TYPE_SERVER, and the first one common to both client and server in the list will be selected. During my tests, the SHA256 was selected.

More information is to be found in the official documentation

Changes in privilege "SELECT ANY DICTIONARY" in Oracle 12c

From version 12.1 and onwards, Oracle has introduced some changes to enhance security when granting the system privilege "SELECT ANY DICTIONARY".

In the New Features guide for version 12.1, the authors explain:

The SELECT ANY DICTIONARY privilege no longer permits access to security sensitive data dictionary tables DEFAULT_PWD$, ENC$, LINK$, USER$, USER_HISTORY$, and XS$VERIFIERS. This change increases the default security of the database by not allowing access to a subset of data dictionary tables through the SELECT ANY DICTIONARY privilege.

The access to USER$ has also been excempt when granting the system privilege SELECT ANY TABLE and the role SELECT_CATALOG_ROLE, but I have not yet found the documentation that verifies this.

What is the meaning of the "10G 11G 12C" value in DBA_USERS.PASSWORD_VERSIONS?

Since I wrote my post What is the meaning of the "10G 11G" value in DBA_USERS.PASSWORD_VERSIONS?, Oracle 12c added yet another value to this column.

It's now common to see the string

PASSWORD_VERSIONS
10G 11G 12C

when you query the password_versions column of dba_users view.

What does it mean?

It's a list of password versions that was generated at the time the account was created.
This list will look a little different depending on your setting of the parameter SQLNET.ALLOWED_LOGON_VERSION_SERVER in your $TNS_ADMIN/sqlnet.ora at the time of account creation.

Oracle explains:

"The PASSWORD_VERSIONS column shows the list of password versions that exist for the account. 10G refers to the earlier case-insensitive Oracle password DES-based version, 11G refers to the SHA-1 version, and 12C refers to the SHA-2-based SHA-512 version."


In my 12.2 database, I have set the following parameter in my $TNS_ADMIN/sqlnet.ora file:

SQLNET.ALLOWED_LOGON_VERSION_SERVER=11

to allow older clients to connect.

When I then create a user with the CREATE USER statement, it will automatically generate all three password versions. Since my SQLNET.ALLOWED_LOGON_VERSION_SERVER was set to 11 at the time of creation, my password will indeed by case-sensitive, since case sensitive password was introduced in version 11.1 of the Oracle software.

If I adjust the parameter sqlnet.ora parameter:

SQLNET.ALLOWED_LOGON_VERSION_SERVER=12

and drop/recreate the user, my password version will have changed:

PASSWORD_VERSIONS
11G 12C

The setting of SQLNET.ALLOWED_LOGON_VERSION have the following effects:

SQLNET.ALLOWED_LOGON_VERSION_SERVER=11 will keep generating 10G, 11G and 12c password versions
SQLNET.ALLOWED_LOGON_VERSION_SERVER=12 will generate both 11G and 12C password versions, and also remove the 10G password version.
SQLNET.ALLOWED_LOGON_VERSION_SERVER=12a will generate only 12c password versions

Oracle call these three settings Greatest level of compatibility, Medium level of security, and Highest level of security, respectivly.

If you for some reason want the old-school case-insensitive password versions to apply, set your SQLNET.ALLOWED_LOGON_VERSION_SERVER to 10 or lower, and make sure the parameter sec_case_sensitive_logon is set to FALSE.

Any user created after setting SQLNET.ALLOWED_LOGON_VERSION_SERVER to 10 or lower, will be able to logon using case-insensitive passwords.

How to recursively zip a folder and its subfolders and add password protetion+encryption

Below I am compressing all files and subfolders in the folder /home/oracle/outputfiles:

cd /home/oracle/outputfiles
zip -r --encrypt myzipfile.zip *

You will be prompted for a password, which has to be verified.
If you are located in the parent directory, use

zip -r -q myzipfile mydir

where myzipfile is the name of the resulting zip file, and mydir is the name of the directory.
The .zip extension will be added to myzipfile automatically.

How to work around ORA-01017 in a migrated 12c database

You may see some users in your 12c database that have the password versions set to 10G:

select username, password_versions from dba_users where username='SCOTT';

USERNAME             PASSWORD_VERSIONS
-------------------- -----------------
SCOTT                10G

At the same time, most other users have their password_versions set to the value 10G 11G 12C.

Oracle uses different password versions in all these three versions:

* Oracle 11g it uses SHA1 password based version
* Oracle 10g uses DES based version.
* Oracle 12c uses SHA-2-based SHA-512 password version

Since the Oracle 12c database runs in exclusive mode by default, users with passwords generated in previous versions
will not be able to login (exclusive mode means that the SQLNET.ALLOWED_LOGON_VERSION_SERVER is set either to 12 or 12a).

Workaround is to force a password reset so that the password is generated for the current version.
But before you do that, you need to change the database's minimum allowed authentication protocol.
This is done by editing the file $TNS_ADMIN/sqlnet.ora.
The parameter controlling the sqlnet authentication protocol is SQLNET.ALLOWED_LOGON_VERSION_SERVER.

Here is how I did it:

1. Find the user(s) with password versions of 10G

select username, password_versions 
from dba_users 
where password_versions = '10G';

I have found cases where there is a space after the string '10G' so that you need to actually search for the string '10G '.

2. Edit the $TNS_ADMIN/sqlnet.ora file so that the database doesn't run in exclusive mode. Add

SQLNET.ALLOWED_LOGON_VERSION_SERVER = 11

3. Restart database

4. Expire the user(s) that you want to force a password reset for:

alter user SCOTT password expire;

5. Try to connect as the user:

connect SCOTT
Enter password:
ERROR:
ORA-28001: the password has expired


Changing password for SCOTT
New password:
Retype new password:
Password changed
Connected.

Check that the users now has the correct password version:

select username, password_versions,account_status from dba_users where username='SCOTT';

USERNAME             PASSWORD_VERSIONS ACCOUNT_STATUS
-------------------- ----------------- --------------------------------
SCOTT                10G 11G 12C       OPEN

6. When all the affected users have been changed, set the database to run in exclusive mode.
Change the SQLNET.ALLOWED_LOGON_VERSION_SERVER from 11 to 12 in $TNS_ADMIN/sqlnet.ora, and restart once more.

Can I decrypt a tablespace that has previously been encrypted? Can I disable TDE?

Question: Can I decrypt a tablespace that has previously been encrypted? Can I disable TDE?

Answer:
In versions prior to 12.2, you cannot.
From Version 12.2, you can.

In Oracle Versions prior to 12.2, this is what Oracle writes about decrypting a tablespace?

"You cannot decrypt a tablespace that has been created encrypted. You must create an unencrypted tablespace and re-create the database objects in the unencrypted tablespace."

In version 12.2, Oracle has added support to decrypt both online and offline tablespaces.

The syntax for an offline tablespace decryption would be

administer key management set keystore open identified by software_keystore_password; 
alter tablespace TEST_TABSPC offline;
alter tablespace TEST_TABSPC encryption offline decrypt;
alter tablespace TEST_TABSPC online;

An online decryption would executed like this:

alter tablespace TEST_TABSPC encryption online decrypt file_name_convert = ('test_tabspc.dbf', 'test_tabspc_decrypted.dbf');

For the online method to work, compatible should be set to 12.2, and there must be enough storage available for the database server that you can hold a copy of the entire tablespace to be decrypted.

What is the Software keystore used by Oracle Transparent Data Encryption?

The Software keystore is the container that stores the TDE master key.
There is one keystore per database.

From the Oracle Documentation:

"A software keystore is a container that stores the Transparent Data Encryption master encryption key."

By "container", Oracle means either

* a software keystore
* a hardware module security (HSM) keystore
* Oracle Key Vault keystore (separately licenced)

Personally I have only used software keystores, which is the equivalent of keeping the keystore as a file on a file system, or a file on an ASM disk group.


How is the keystore located by by the database?

"the database locates this keystore by checking the keystore location that you define in the sqlnet.ora file."

Example of sqlnet.ora from my own environment:

# WALLET_OVERRIDE is only applicable when using auto login, set to FALSE
SQLNET.WALLET_OVERRIDE = FALSE
SQLNET.AUTHENTICATION_SERVICES = (BEQ, TCPS)
SSL_VERSION = 0
SSL_CLIENT_AUTHENTICATION = FALSE
ENCRYPTION_WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /u01/oracle/admin/proddb01/wallet)
    )
  )
SSL_CIPHER_SUITES = (SSL_RSA_WITH_3DES_EDE_CBC_SHA, SSL_RSA_WITH_DES_CBC_SHA)

Now that the keystore is defined, you can log into the database to create and open the keystore.
Finally, you set the TDE master encryption key.

After that, you can encrypt the data.

See my post Quick guide to set up Transparent Data Encryption for a tablespace for examples on how to set it up.

What is Transparent Data Encryption and when should it be used?

Use TDE to protect data on disk, or "data at rest".
In other words, TDE adds another layer of security to your database setup, but is by no means sufficent to protect your data by itself.

Inside the database, Oracle uses different methods to protect the data against unauthorized access.

For example, you can set up auditing to trace Access to specific tables in the database.
Another example would be redaction, introduced in Oracle 12cR1, which can hide sensitive data from the users.

However, none of these methods would protect data on disk, or "data at rest" as Oracle calls it.

From the documentation:

"TDE helps protect data stored on media (also called data at rest) in the event that the storage media or data file is stolen."

and

"To protect these data files, Oracle Database provides Transparent Data Encryption (TDE). TDE encrypts sensitive data stored in data files."

How does Oracle prevent unauthorized decryption?

"To prevent unauthorized decryption, TDE stores the encryption keys in a security module external to the database, called a keystore."

Source: Oracle Documentation

How to generate CREATE TABLESPACE statements for both encrypted and non-encrypted tablespaces

I used the following statement when I was supposed to pre-create encrypted tablespaces as part of a migration project. We were supposed to move a database using TDE from one server to another, from AIX to Linux. The database wasn't big so import could be used.

The statement will pre-create all tablespaces as they were in the old database with the exception of SYSTEM, SYSAUX etc.

SELECT 'create bigfile tablespace ' ||T.NAME || ' datafile ''/u02/oradata/proddb01' || LOWER(T.NAME) || '.dbf'' size 256M autoextend on next 128M maxsize unlimited extent management local segment space management auto encryption using ''' || E.ENCRYPTIONALG || ''' default storage(encrypt);' 
FROM  V$TABLESPACE T, V$ENCRYPTED_TABLESPACES E
WHERE T.TS# = E.TS#
UNION
SELECT 'create bigfile tablespace ' ||T.NAME || ' datafile ''/u02/oradata/proddb01' || LOWER(F.NAME) || ''' size 256M autoextend on next 128M maxsize unlimited extent management local segment space management auto;' 
FROM  V$TABLESPACE T, V$DATAFILE F
WHERE T.TS# = F.TS#
AND T.NAME NOT IN ('SYSTEM','SYSAUX','UNDOTBS1','USERS')
AND F.TS# NOT IN (SELECT TS# FROM V$ENCRYPTED_TABLESPACES);

How to find encrypted tablespaces

SELECT t.name, NVL(e.encryptionalg, 'Not encrypted') algorithm
FROM  v$tablespace t LEFT OUTER JOIN  v$encrypted_tablespaces e
ON(  t.ts# = e.ts# )
WHERE t.name not in ('SYSTEM','SYSAUX','UNDOTBS1','USERS');

To generate "CREATE TABLESPACE" statements for the tablespaces, see this post

How to create a startup-trigger

In this example, I needed to write a simple trigger that opens the wallet upon startup of the instance:

create or replace trigger
   open_wallet
after startup on database
begin
   execute immediate 'ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "mysecretpassword"';
end;
/

Errors ORA-39083 and ORA-28365 during import

During an import you get:

ORA-39083: Object type TABLE:"SCOTT"."EMP" failed to create with error:
ORA-28365: wallet is not open

Action:
Open the wallet:

SQL> administer key management set keystore open identified by "mysecretpassword";

keystore altered.

Check status of the wallet:

select status from  v$encryption_Wallet;

STATUS
------
OPEN

You can now perform your import as you normally would.

After import you can Close the wallet:

administer key management set keystore close identified by "mysecretpassword";

What is the INHERIT PRIVILEGES granted to PUBLIC in 12c?

I was checking which object privileges my user had made, and I found the following:

select * from user_tab_privs_made;

GRANTEE	TABLE_NAME	GRANTOR	PRIVILEGE	GRANTABLE	HIERARCHY	COMMON	TYPE
PUBLIC	MYUSER	MYUSER	INHERIT PRIVILEGES	NO	NO	NO	USER

I couldn't understand when this privilege had been made, and why the type was set to USER.

Searching the internet, I soon found that this is a new privilege in Oracle 12c. By default the privilege INHERIT PRIVILEGES is granted to PUBLIC, and it secures a weakness where a malicious user may accidently get access to a privilege that user shouldn't have.

For backward compability INHERIT PRIVILEGES is granted to PUBLIC in Oracle 12c, but it can and probably should be revoked.

Tim Hall has setup a simple example on how this new privilege can secure your database. Instead of trying to mimic his work, I will simply refer you to his site.

You can find his article at oracle-base.com

See also the release changes section for Oracle 12c