SET VERIFY OFF connect "SYS"/"&&sysPassword" as SYSDBA set echo on spool /oracle/admin/mydb01/scripts/lockAccount.log append BEGIN FOR item IN ( SELECT USERNAME, AUTHENTICATION_TYPE FROM DBA_USERS WHERE ACCOUNT_STATUS IN ('OPEN', 'LOCKED', 'EXPIRED') AND USERNAME NOT IN ( 'SYS','SYSTEM') ) LOOP IF item.AUTHENTICATION_TYPE='PASSWORD' THEN dbms_output.put_line('Locking and Expiring: ' || item.USERNAME); execute immediate 'alter user ' || sys.dbms_assert.enquote_name( sys.dbms_assert.schema_name( item.USERNAME),false) || ' password expire account lock' ; ELSE dbms_output.put_line('Locking: ' || item.USERNAME); execute immediate 'alter user ' || sys.dbms_assert.enquote_name( sys.dbms_assert.schema_name( item.USERNAME),false) || ' account lock' ; END IF; END LOOP; END; / spool off
Minimalistic Oracle contains a collection of practical examples from my encounters with Oracle technologies. When relevant, I also write about other technologies, like Linux or PostgreSQL. Many of the posts starts with "how to" since they derive directly from my own personal experience. My goal is to provide simple examples, so that they can be easily adapted to other situations.
Showing posts with label Security. Show all posts
Showing posts with label Security. Show all posts
Tuesday, October 29, 2024
Lock and expire users
An anonymous PL/SQL block to lock and expire open accounts:
Thursday, September 5, 2024
How do I store multiple connections to the same database in my wallet?
If you have a wallet which already contains an alias for a database, you cannot add another one pointing to the same alias. If you do, mkstore will give you the following error:
Workaround:
Use multiple aliases in tnsnames.ora pointing to the same database service.
List the current contents of the wallet:
The tnsnames.ora knows which service to connect to whenever "mydb01" is called:
Let's add another entry to the wallet for user jim, so that he can connect without supplying his password:
Secret Store error occurred: oracle.security.pki.OracleSecretStoreException: Credential already existsThis is actually documented:
Each user account must have its own unique connection string; you cannot create one connection string for multiple users.
So what to do, then, if you find yourself in a situation where you need two different users to access the database in a passwordless manner using a wallet?Workaround:
Use multiple aliases in tnsnames.ora pointing to the same database service.
List the current contents of the wallet:
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, 2024, Oracle and/or its affiliates. All rights reserved. Enter wallet password: List credential (index: connect_string username) 1: mydb1_scott scottSo user scott can log into the database passwordless using his own password which is securly stored in the wallet.
The tnsnames.ora knows which service to connect to whenever "mydb01" is called:
mydb1_scott = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = server1.oric.no)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = mydb1.oric.no) (INSTANCE_NAME = cdb) ) )Let's add another connect_identifier in the tnsnames.ora file:
mydb1_jim = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = server1.oric.no)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = mydb1.skead.no) (INSTANCE_NAME = cdb) ) )Let's test it, using a random user, for example, system:
sqlplus system@mydb1_jim Enter password: SQL>show user USER is "SYSTEM"So the connect identifier is working, it points towards a valid service being serviced by the listener on the server.
Let's add another entry to the wallet for user jim, so that he can connect without supplying his password:
mkstore -wrl $TNS_ADMIN/wallet -createCredential mydb1_jim jim Oracle Secret Store Tool Release 19.0.0.0.0 - Production Version 19.4.0.0.0 Copyright (c) 2004, 2024, Oracle and/or its affiliates. All rights reserved. Your secret/Password is missing in the command line Enter your secret/Password: <-- password for user jim Re-enter your secret/Password: <-- reenter password for user jim Enter wallet password: <-- wallet passwordJim can now login using his own password stored in the wallet:
sqlplus /@mydb1_jim SQL>show user USER is "JIM"The contents of the wallet is now:
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, 2024, Oracle and/or its affiliates. All rights reserved. Enter wallet password: List credential (index: connect_string username) 2: mydb1_jim jim 1: mydb1_scott scott
Wednesday, June 29, 2022
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 walletIt 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 /@testdbto 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 dbamasterYou 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)
Tuesday, June 21, 2022
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
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
Tuesday, August 18, 2020
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=LEGACYIf 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 charactersThe 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:
Friday, April 17, 2020
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:
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
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
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
Tuesday, February 25, 2020
How to debug an ssh connection
ssh -X -v myserver.mydomain.com
You can also use -vv and -vvv for more detailed debug information.
Friday, October 11, 2019
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;
INACTIVE_ACCOUNT_TIME 30;
ALTER PROFILE my_profile LIMIT
INACTIVE_ACCOUNT_TIME 30;
INACTIVE_ACCOUNT_TIME 30;
Sources:
Oracle 12.2 New Features guide
Automatically Locking Inactive Database User Accounts
Wednesday, September 4, 2019
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;
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;
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;
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;
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;
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;
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.
Wednesday, January 2, 2019
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
Wednesday, November 28, 2018
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.
Thursday, October 18, 2018
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.
Wednesday, March 14, 2018
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:
You will be prompted for a password, which has to be verified.
If you are located in the parent directory, use
The .zip extension will be added to myzipfile automatically.
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 mydirwhere 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.
Thursday, January 18, 2018
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.
Wednesday, June 7, 2017
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
An online decryption would executed like this:
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.
Friday, June 2, 2017
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:
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.
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
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
Friday, April 7, 2017
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.
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);
Thursday, April 6, 2017
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
Wednesday, March 29, 2017
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; /
Subscribe to:
Posts (Atom)