Online DBA

<<Back to Oracle DB Security Main Page ORA-28447: insufficient privilege to execute ALTER DATABASE DICTIONARY statement SQL> ALTER DATABASE DICTIONARY ENCRYPT CREDENTIALS; ALTER DATABASE DICTIONARY ENCRYPT CREDENTIALS * ERROR at line 1: ORA-28447: insufficient privilege to execute ALTER DATABASE DICTIONARY statement Solution: Login as syskm and re-run  the command $ sqlplus admin/password@PDB01 as syskm SQL*Plus: Release 18.0.0.0.0 - Production on Tue Feb 19 13:06:09 2019 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle.  All rights reserved. Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.3.0.0.0 SQL> SQL> ALTER DATABASE DICTIONARY ENCRYPT CREDENTIALS; Database dictionary altered.

<<Back to Oracle DB Security Main Page Error ORA-39395 Introduced To Support Encrypted Passwords For Database Links Assuming that your have keystore and configured Step by Step instruction for the same is provided in  Step by Step How to Configure Software Keystore/ Oracle Wallet post. In Oracle Database 12c, passwords for database links are stored obfuscated in the database, and when an export with Data Pump (expdp) is performed, the obfuscated value is stored in the dump file In Oracle Database 18c, when the feature “credentials encryption in the dictionary” is enabled, passwords for database links are not exported, they are replaced with an invalid value; thus, the password has to be reset after import. Lets See with an example.   Step1> Check the Keystore Status SQL> select STATUS,WRL_PARAMETER WALLET_DIR,WALLET_TYPE from V$ENCRYPTION_WALLET; STATUS                WALLET_DIR...

<<Back to Oracle DB Security Main Page How Transparent Data Encryption Works with Oracle Data Guard If the primary database uses TDE, then each standby database in a Data Guard configuration must have an encryption keystore with the keystore from the primary database merged into it. If you reset the TDE master encryption key in the primary database, then you must merge the keystore on the primary database that contains the TDE master encryption key to each standby database. Step by Step Instructions t o synchronize the TDE kyes between Primary and Physical Standby  has been provided in post  How to Synchronize Primary Database Keystore with Physical Standby Instructions to configure KeyStore is provided in Post  Step by Step How to Configure Software Keystore/ Oracle Wallet

<<Back to Oracle DB Security Main Page Oracle Database Advanced Security New Features in 18c Following are interesting new features added in oracle 18c to enhance the database security and while keeping the PDB's isolated Ability to Create a Keystore for Each Pluggable Database Ability to Create a User-Defined Master Encryption Key Ability to Use Encrypted Passwords for Database Links with Oracle Data Pump Ability to Create a Keystore for Each Pluggable Database Until before 18c the keystore was configured at container database (CDB) level. Each PDB in this container then,  was able to access this Keystore. This keystore stored the master encryption keys for the CDB as well as all the PDBs. Starting from 18c a PDB can either continue to share the same keystore with the entire CDB as before or have a separate (its own) keystore. Having the Dedicated Keystore for each PDB offers following advantages It offers greater isolation between PDBs Each separate keystor...

<<Back to Oracle DB Security Main Page Configuring Oracle Software Keystore for United Mode Enable United Mode Step1> Log in to the DB server Step2> If necessary, create a wallet directory to hold the wallet $mkdir -p /u01/dbatst1/stage/wallet Step3> Set WALLET_ROOT and TDE_CONFIGURATION parameter as shown below Create Pfile SQL> create pfile='/tmp/initTEST.ora' from spfile; File created. Enter the following parameters in Pfile /tmp/initTEST.ora wallet_root=/u01/dbatst1/stage/wallet tde_configuration="keystore_configuration=file" Start the database using Pfile /tmp/initTEST.ora SQL> shut immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup nomount pfile='/tmp/initTEST.ora'; ORACLE instance started. Total System Global Area 2147482464 bytes Fixed Size                  8899424 bytes Variable Size       ...

<<Back to Oracle DB Security Main Page Finding out the Master Encryption Key and  Table  Encrypt for an Encrypted Table SQL> SELECT ENC.OBJ#,ENC.MKEYID,ENC.COLKLC, OBJ.OBJECT_NAME,OBJ.OWNER FROM ENC$ ENC,DBA_OBJECTS OBJ WHERE ENC.OBJ#=OBJ.OBJECT_ID AND OBJ.OBJECT_NAME='TABLE_NAME'

<<Back to Oracle DB Security Main Page oracle.security.pki.OracleSecretStoreException: Credential already exists $ mkstore -wrl /u01/dbatst2/admin/wallet/TST1T/ -createCredential PDB01 test1 Oracle Secret Store Tool : Version 12.2.0.1.0 Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved. Your secret/Password is missing in the command line Enter your secret/Password: Re-enter your secret/Password: Enter wallet password: Secret Store error occurred: oracle.security.pki.OracleSecretStoreException: Credential already exists $ mkstore -wrl /u01/dbatst2/admin/wallet/TST1T/ -listCredential Oracle Secret Store Tool : Version 12.2.0.1.0 Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved. Enter wallet password: List credential (index: connect_string username) 1: PDB01 test Solution: Note that you can have only ONE user per TNS alias stored in the wallet. If you want more user per TNS then create additional wallet step by step...

<<Back to Oracle DB Security Main Page How to avoid Keying in the Password or Saving the Password in an Script file Are you really concerned about Security. Are you not willing to save the password in a file or not willing to key in as and when required. Then you must read this blog until the end Step1> Create directory to hold wallet $mkdir -p /u01/dbatst2/admin/wallet/TST1T/ Step2> Enter wallet information in your sqlnet.ora WALLET_LOCATION =    (SOURCE =      (METHOD = FILE)      (METHOD_DATA =        (DIRECTORY = /u01/dbatst2/admin/wallet/TST1T/)      )    ) SQLNET.WALLET_OVERRIDE = TRUE SSL_CLIENT_AUTHENTICATION = FALSE SSL_VERSION = 0 Step3> Create wallet to hold the credentials $  mkstore -wrl /u01/dbatst2/admin/wallet/TST1T/ -create Oracle Secret Store Tool : Version 12.2.0.1.0 Copyright (c) 2004, 2016, Oracle and/or its affiliates. All ri...

<<Back to Oracle DB Security Main Page ORA-46665: master keys not activated for all PDBs during REKEY SQL> ADMINISTER KEY MANAGEMENT SET KEY FORCE KEYSTORE IDENTIFIED BY xxxx WITH BACKUP CONTAINER = ALL ; ADMINISTER KEY MANAGEMENT SET KEY FORCE KEYSTORE IDENTIFIED BY xxxx WITH BACKUP CONTAINER = ALL * ERROR at line 1: ORA-46665: master keys not activated for all PDBs during REKEY I found following in the trace file REKEY: Create Key in PDB 3 resulted in error 46658 *** 2019-02-06T15:27:04.667485+01:00 (CDB$ROOT(1)) REKEY: Activation of Key AdnU5OzNP08Qv1mIyXhP/64AAAAAAAAAAAAAAAAAAAAAAAAAAAAA in PDB 3 resulted in error 28374 REKEY: Keystore needs to be restored from the REKEY backup.Aborting REKEY! Cause: All this hassle started because I accidently deleted the wallet and all wallet backup files too and also forgot the keystore password. There was no way to restore the wallet back. Fortunately in my case the PDB which had encrypted data was supposed to be deco...

<<Back to Oracle DATAPUMP Main Page ORA-39173: Encrypted data has been stored unencrypted in dump file set $ expdp test@PDB01 parfile=exp.par Export: Release 12.2.0.1.0 - Production on Wed Nov 28 11:30:14 2018 Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved. Password: Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Starting "TEST"."SYS_EXPORT_TABLESPACE_01":  test/********@PDB01 parfile=exp.par Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/COMMENT Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT . . exported "T...

<<Back to Oracle DB Security Main Page Oracle 12c Wallet/KeyStore Administration Commands Creating a Keystore Examples The following statement creates a password-protected software keystore in directory /etc/ORACLE/WALLETS/orcl: SQL>ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/etc/ORACLE/WALLETS/orcl' IDENTIFIED BY password; The following statement creates an auto-login software keystore from the keystore created in the previous statement: SQL> ADMINISTER KEY MANAGEMENT   CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/etc/ORACLE/WALLETS/orcl'   IDENTIFIED BY password; Opening a Keystore Examples The following statement opens a password-protected software keystore SQL>ADMINISTER KEY MANAGEMENT   SET KEYSTORE OPEN   IDENTIFIED BY password; If you are connected to a CDB, then the following statement opens a password-protected software keystore in the current container: SQL>ADMINISTER KEY MANAGEMENT   SET KEYSTORE OPEN   IDENTIFIED BY pa...