Simple Oracle Plsql Package for password encryption and decryption

Introduction:

The script provided demonstrates how to create a simple password encryption and decryption package in Oracle using the DBMS_OBFUSCATION_TOOLKIT. Below is a step-by-step breakdown of each part of the code:

1. Setting Up the Environment:

Before running the package, it is recommended to connect to the database as SYSDBA and run the script ?/rdbms/admin/catobtk.sql. This script installs the DBMS_OBFUSCATION_TOOLKIT package necessary for encryption and decryption functionalities.

2. Creating the Table USERS_INFO:

The table USERS_INFO is created to store usernames and encrypted passwords.

CREATE TABLE USERS_INFO

(

  USERNAME VARCHAR2(20 BYTE),

  PASS VARCHAR2(20 BYTE)

);

3. Creating the Package Specification PASSWORD:

The package specification defines two functions: encrypt and decrypt. These functions will handle the encryption and decryption of passwords.

CREATE OR REPLACE PACKAGE PASSWORD AS
   function encrypt(i_password varchar2) return varchar2;
   function decrypt(i_password varchar2) return varchar2;
END PASSWORD;
/
show errors

4. Creating the Package Body PASSWORD:

The package body implements the actual logic for the encrypt and decrypt functions using Oracle's DBMS_OBFUSCATION_TOOLKIT.

Key Points:

• The encryption key (c_encrypt_key) must be exactly 8 bytes long.
• Input data for encryption must have a length divisible by eight, achieved using RPAD to pad the input string.

CREATE OR REPLACE PACKAGE BODY PASSWORD AS

  -- Key for encryption and decryption

  c_encrypt_key VARCHAR2(8) := 'key45678';

  -- Encrypt function

  FUNCTION encrypt (i_password VARCHAR2) RETURN VARCHAR2 IS

    v_encrypted_val VARCHAR2(38);

    v_data          VARCHAR2(38);

  BEGIN

     -- Pad the input data to a length divisible by eight

     v_data := RPAD(i_password, (TRUNC(LENGTH(i_password)/8) + 1) * 8, CHR(0));

     -- Encrypt the data

     DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT(

        input_string     => v_data,

        key_string       => c_encrypt_key,

        encrypted_string => v_encrypted_val);

     RETURN v_encrypted_val;

  END encrypt;

  -- Decrypt function

  FUNCTION decrypt (i_password VARCHAR2) RETURN VARCHAR2 IS

    v_decrypted_val VARCHAR2(38);

  BEGIN

     -- Decrypt the data

     DBMS_OBFUSCATION_TOOLKIT.DESDECRYPT(

        input_string     => i_password,

        key_string       => c_encrypt_key,

        decrypted_string => v_decrypted_val);

     RETURN v_decrypted_val;

  END decrypt;

END PASSWORD;

/

SHOW ERRORS;

5. Testing the Encryption and Decryption:

The following SQL statements test the encryption and decryption functions:

select password.encrypt('PASSWORD1') from dual;
select password.decrypt(app_password.encrypt('PASSWORD1')) from dual;
select password.encrypt('PSW2') from dual;
select password.decrypt(app_password.encrypt('PSW2')) from dual;

6. Inserting and Retrieving Encrypted Data:

The script demonstrates inserting an encrypted password into the USERS_INFO table and then retrieving and decrypting it.

--- Insert encrypted Password ---

insert into USERS_INFO values ('redddy',( select password.encrypt('REDDY1') from dual) );
select password.decrypt((pass)) from USERS_INFO where USERNAME='redddy';

Important Notes:

• Security Considerations: The DBMS_OBFUSCATION_TOOLKIT uses DES encryption, which is considered outdated and insecure by modern standards. It’s recommended to use DBMS_CRYPTO for more robust encryption methods, such as AES.
• Error Handling: The script should include error handling to manage issues that may arise during encryption or decryption processes.
• Testing and Validation: Always test encryption and decryption thoroughly, especially when dealing with sensitive data like passwords, to ensure the correct implementation.

This setup demonstrates basic encryption and decryption using Oracle's built-in toolkit, providing a foundation for enhancing database security.

How Secure Can We Make Our Oracle Databases?

How Secure Can We Make Our Oracle Databases?

As a database administrator, one of the most critical questions that constantly arises is: How secure are our databases, and can we make them any more secure? Ensuring the security of your Oracle databases is crucial, as they are often the backbone of an organization’s data infrastructure. Over the years, I’ve adopted several best practices to enhance database security. Below, I share some key tips and strategies that I follow to secure my databases:

1. Grant Access on a Need-to-Know Basis

Always follow the principle of least privilege by granting access only to users who genuinely need it. This minimizes exposure and reduces the risk of unauthorized actions within the database.

    2. Remove Unnecessary Grants and Privileges

Regularly review and clean up unnecessary permissions from users and roles. Privileges that are no longer needed should be revoked to prevent misuse or accidental data exposure.

    3. Audit Failed Logins Regularly

Implement frequent audits of Failed Logins attempts to detect unauthorized access attempts. Monitoring failed logins helps identify suspicious activities and potential security threats. 

    4. Evaluate Requests for Elevated Privileges

When a user requests elevated privileges, always engage in a conversation to understand their requirements. Assess if the elevated access is genuinely necessary and grant it only if justified.

    5. Grant the Minimum Required Access

Ensure that users are granted no more access than what is absolutely necessary for their tasks. Avoid giving broad permissions, as this increases the risk of accidental or malicious actions.

    6. Manage Temporary Access Carefully

Sometimes, users need access temporarily for specific tasks. Make sure to track and revoke these temporary permissions immediately after the task is completed to maintain security.

    7. Define Clear Boundaries for Data Access

Clearly define which users can access specific data. Implement fine-grained access control to restrict access to sensitive data based on roles and responsibilities.

    8. Use User Profiles and Audit Features

Leverage Oracle’s User profiles & Audit capabilities to monitor and enforce security policies. Track all user activities to ensure accountability and detect any unauthorized actions. 

    9. Enforce Complex Password Policies

Implement strong password policies that require complexity and regular changes. Complex passwords significantly enhance security by making unauthorized access more difficult. Here is the Link on how to do it 

    10. Use Triggers to Track User Activity

Utilize database triggers to monitor user actions such as logins, DML operations, and changes to critical tables. This helps in tracking user behavior and detecting anomalies. Use Triggers to track user activity.

    11. Encrypt Passwords in Applications

Ensure that passwords used in applications are encrypted. Storing plain-text passwords poses a significant security threat, especially if application code is compromised.

    12. Secure the Oracle Listener with a Password

Protect your Oracle Listener with a password to prevent unauthorized access and control over database connections. This adds an additional layer of security to your database environment.

    13.  Restrict Access to Known Servers and Clients

Use Oracle’s VALID_NODE_CHECKING feature to restrict database access to specific, known servers or clients. This helps protect your database from unauthorized network access. Use this Valid_node_checking Link on how to restrict access to servers/clients.

Conclusion

Securing your Oracle databases is an ongoing process that requires continuous vigilance, regular audits, and updates to security protocols. By implementing these best practices, you can significantly enhance the security of your Oracle environments and protect your data from unauthorized access and potential threats. Remember, security is not a one-time setup but a continuous practice that evolves with emerging threats and changes in your database infrastructure.

Using special characters in ORACLE DB passwords

Using special characters in ORACLE DB passwords

 
Using special characters in ORACLE DB passwords. Most times we restrict our selves from using complicated special characters as we think that we can't use them in passwords, but in oracle DB world we are allowed to use most special characters as passwords. See this simple example to see how to use special char's in passwords and login.




To log into DB using cmdline we need to use single quote ''. else oracle some times doesn't recognize the password. See below example :

I created a user with password using special characters.

SQL> alter user C##atest identified by "atest113..";

User C##ATEST altered.

[oracle@Linux03 admin]$ sqlplus C##atest/atest113..

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 20 16:39:56 2016

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

ERROR:
ORA-01017: invalid username/password; logon denied


Solution to this is to use single quote:

[oracle@Linux03 admin]$ sqlplus C##atest/"atest113$$"

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 20 16:41:44 2016

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

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name: ^C

press ctrl+c to escape from this line


[oracle@Linux03 admin]$ sqlplus C##atest/'atest113$$&&'

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 20 16:43:20 2016

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

Last Successful login time: Wed Jul 20 2016 16:42:54 -05:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show user
USER is "C##ATEST"

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@Linux03 admin]$ sqlplus C##atest/atest113$$&&

configure complex password (password verify function) in oracle database

 configure password verify function in oracle database

We can enable the oracle provided "password verify function" to enforce strong password restrictions for our DB users. This function with other profile parameters can create a strong security for the database.To enable the oracle password verification function you need to execute the utlpwdmg.sql file from ORACLE_HOME/rdbms/admin as sysdba.


[oracle@Linux03 home]$ cd /u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/
[oracle@Linux03 admin]$ ls utlpwdmg.sql

[oracle@Linux03 admin]$ sqlplus / as sysdba
SQL> @/u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/utlpwdmg.sql

SQL> @/u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/utlpwdmg.sql

Function created.

Grant succeeded.

Function created.

Grant succeeded.

Function created.

Grant succeeded.

Profile altered.

Note : You can query current default profile settings using as below

select * from DBA_PROFILES where profile='DEFAULT';

Starting from 12c this file (utlpwdmg.sql) creates four functions under user SYS. Here we have an option to select one from four functions.

ORA12C_STRONG_VERIFY_FUNCTION
ORA12C_VERIFY_FUNCTION
VERIFY_FUNCTION
VERIFY_FUNCTION_11G

This function makes the minimum complexity checks like the minimum length of the password, password not same as the
username, etc. The user may enhance this function according to your need.

You can change the password verify function by using below sql :

ALTER PROFILE "DEFAULT" LIMIT PASSWORD_VERIFY_FUNCTION ORA12C_VERIFY_FUNCTION

IT SETS UP FOLOWING PARAMETERS IN DEFAULT PROFILE:

ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 180
PASSWORD_GRACE_TIME 7
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX  UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LOCK_TIME 1

-- This script alters the default parameters for Password Management. This means that all the users on the system have Password Management enabled and set to the following values unless another profile is created with parameter values set to different value or UNLIMITED  is created and assigned to the user.

LETS TEST THE PASSWORD FUNCTION BY CREATING A NEW USER.

SQL> create user C##atest identified by atest11;

Error starting at line : 2 in command -
create user C##atest identified by atest11
Error report -
SQL Error: ORA-28003: password verification for the specified password failed
ORA-20001: Password length less than 8
28003. 00000 -  "password verification for the specified password failed"
*Cause:    The new password did not meet the necessary complexity
           specifications and the password_verify_function failed
*Action:   Enter a different password. Contact the DBA to know the rules for
           choosing the new password

          
While using length of 8 chars I was able to create user.

SQL> create user C##atest identified by atest113;

User C##ATEST created.


Follow this link on how to disable Password verify function  : 

http://arvindasdba.blogspot.com/2013/06/disable-password-verify-function-in.html

TNS-00583: Valid node checking: unable to parse configuration parameters

 TNS-12560: TNS:protocol adapter error  TNS-00583: Valid node checking: unable to parse configuration parameters

I  Received following errors when trying to startup my listener. I verified the listener.ora and sqlnet.ora files and everything seemed to look normal.
 
[oracle@Linux03 encryption_keystore]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 12-JUL-2016 10:05:32

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/Linux03/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))
TNS-12560: TNS:protocol adapter error
 TNS-00583: Valid node checking: unable to parse configuration parameters


Listener failed to start. See the error message(s) above...

[oracle@Linux03 encryption_keystore]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 12-JUL-2016 10:07:41

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/Linux03/listener/alert/log.xml
Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))
TNS-12560: TNS:protocol adapter error
 TNS-00583: Valid node checking: unable to parse configuration parameters


Listener failed to start. See the error message(s) above...


I had below line in my sqlnet.ora file.

[oracle@Linux03 admin]$ cat sqlnet.ora_bak
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

ENCRYPTION_WALLET_LOCATION =
  (SOURCE =(METHOD = FILE)(METHOD_DATA =
    (DIRECTORY = /u01/app/oracle/admin/$ORACLE_SID/encryption_keystore/)     

Solution (In my case):

1. Tried to remove the ENCRYPTION_WALLET_LOCATION info and it worked for me. I knew there was somthing wrong with the syntax.
2. Copied the content from ENCRYPTION_WALLET* into notepad ++ . It helped me realize that I was missing two closing parenthesis )) at end.
3. Added them at end. Started the listener and worked.


[oracle@Linux03 encryption_keystore]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 12-JUL-2016 10:31:41

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/Linux03/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Linux03)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                12-JUL-2016 10:31:41
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/Linux03/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Linux03)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully


Also try these if above trick doesn't help.

1.  Oracle listener.ora or sqlnet.ora file contains any special characters.
2.  Oracle Listener.ora or sqlnet.ora file are in wrong format or syntax.
3.  Oracle listener.ora or sqlnet.ora file have some left justified parenthesis which are not accepted by oracle parser.

Enable Database Auditing in oracle

ENABLE AUDITING IN ORACLE DATABASE

Enabling Auditing in Oracle Database

Auditing in Oracle Database helps track and monitor database activities, which is essential for security and compliance. By default, auditing is disabled, but you can enable it and configure its settings based on your requirements.

1. Understanding Auditing Parameters

To begin with, check the current auditing configuration using SQL*Plus:

sql

    SQL> SHOW PARAMETER AUDIT

Expected Output:

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      C:\ORACLE\PRODUCT\10.2.0\ADMIN
                                                 \DB10G\ADUMP
audit_sys_operations                 boolean     FALSE
audit_trail                          string      NONE

2. Auditing Parameters and Their Values

The AUDIT_TRAIL parameter controls the type of auditing and can be set to one of the following values:

• NONE: Auditing is disabled.
• db: Auditing is enabled, with records stored in the database audit trail (SYS.AUD$).
• db,extended: As db, but includes SQL_BIND and SQL_TEXT columns in the audit records.
• xml: Auditing is enabled, with records stored as XML files in the operating system.
• xml,extended: As xml, but includes SQL_BIND and SQL_TEXT columns.
• OS: Auditing is enabled, with records directed to the operating system’s audit trail.

3. Enabling Auditing in the Database

To enable auditing and store audit records in the database audit trail, follow these steps:

1. Set the AUDIT_TRAIL Parameter:

   Execute the following command to set the parameter:

   sql

   This command updates the AUDIT_TRAIL parameter in the server parameter file (SPFILE).

            SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;

1. Restart the Database:

   For the changes to take effect, restart the database:

   sql:
   SQL> SHUTDOWN
   Database closed.
   Database dismounted.
   ORACLE instance shut down.
   
   SQL> STARTUP
   ORACLE instance started.
   
   Total System Global Area  289406976 bytes
   Fixed Size                  1248600 bytes
   Variable Size              71303848 bytes
   Database Buffers          213909504 bytes
   Redo Buffers                2945024 bytes
   Database mounted.
   Database opened.
   

   Note: The auditing changes will only take effect after restarting the database.

4. Verifying Auditing Configuration

After the restart, confirm that the auditing settings are applied:

sql

    SQL> SHOW PARAMETER AUDIT

Expected Output:

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      C:\ORACLE\PRODUCT\10.2.0\ADMIN
                                                 \DB10G\ADUMP
audit_sys_operations                 boolean     FALSE
audit_trail                          string      DB

5. Additional Considerations

• Auditing Specific Actions: To audit specific actions (e.g., DDL commands, logins), use the AUDIT command in SQL*Plus.

• Review Audit Records: Query the SYS.AUD$ table to review audit records.

  sql:

    SELECT * FROM SYS.AUD$;

• Managing Audit Data: Regularly manage and archive audit data to prevent excessive growth of audit tables.

By enabling and configuring auditing, you can enhance security and ensure that your database activities are properly monitored and recorded.

Note: Enabling and Disabling the auditing in the database will only take effect after a db restart.

select grants on tables to users or roles

select grants on tables to users or roles

SELECT
  OWNER,
  GRANTEE,
  GRANTOR,
  TABLE_NAME,
  PRIVILEGE
FROM
  DBA_TAB_PRIVS
WHERE
  table_name   = 'TABLENAME'
AND PRIVILEGE IN ('DELETE','INSERT','SELECT','UPDATE')
AND GRANTEE   IN ('ROLE1','ROLE2')
ORDER BY
  1,2;

select grants on table to a user thru role or direct priv

select Grantee,'Granted Through Role' as Grant_Type, role, table_name
from role_tab_privs rtp, dba_role_privs drp
where rtp.role = drp.granted_role
and table_name = 'TABLENAME'
union
select Grantee,'Direct Grant' as Grant_type, null as role, table_name
from dba_tab_privs
where table_name = 'TABLENAME' ;

Restricting database access to IP addeess using sqlnet.ora

**************************************************************

                      FROM MY CLIENT MACHINE:

**************************************************************

C:\Windows\System32>tnsping pdborcl1

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 07-MAR-2016 15:32:20

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:

C:\app\oracle\product\11.2.0\client_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.215.XX4.XX)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = pdbo

cl1)))

OK (0 msec)

**************************************************************

NOW I want my DB to be only be accessible from this machine and restrict all other. For this I will need to make changed to my DB server sqlnet.ora :

**************************************************************

ON DB SERVER:

oracle@Linux03 admin]$ cat sqlnet.ora

# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora

# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

tcp.validnode_checking = yes  ## I have just added this line to test if it works

[oracle@Linux03 admin]$ vi sqlnet.ora

[oracle@Linux03 admin]$ lsnrctl reload

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-MAR-2016 15:20:03

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))

TNS-00584: Valid node checking configuration error

 TNS-12560: TNS:protocol adapter error

As expected it doesn't work as listener needs to know where to accept or deny connections from.

Now I tweak the sqlnet.ora file to below

[oracle@Linux03 admin]$ cat sqlnet.ora

# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora

# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

tcp.validnode_checking = yes

tcp.invited_nodes = (Linux03)  ## I only want to accept connections from my localhost i.e Linux03

RESTART THE LISTENER NOW.

[oracle@Linux03 admin]$ lsnrctl reload

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-MAR-2016 15:21:06

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))

The command completed successfully

 (OR)

[oracle@Linux03 admin]$ lsnrctl stop

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-MAR-2016 15:28:36

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))

The command completed successfully

[oracle@Linux03 admin]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-MAR-2016 15:28:41

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production

System parameter file is /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora

Log messages written to /u01/app/oracle/diag/tnslsnr/Linux03/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Linux03)(PORT=1521)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production

Start Date                07-MAR-2016 15:28:41

Uptime                    0 days 0 hr. 0 min. 0 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora

Listener Log File         /u01/app/oracle/diag/tnslsnr/Linux03/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Linux03)(PORT=1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

The listener supports no services

The command completed successfully

****************************************************

NOW I TRY TO CONNECT FROM CLIENT AGAIN :

****************************************************

C:\Windows\System32>tnsping pdborcl1

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 07-MAR-2016 15:32:57

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:

C:\app\oracle\product\11.2.0\client_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.215.XX4.XX)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = pdbo

cl1)))

TNS-12537: TNS:connection closed

C:\Windows\System32>

Nope it doesn't allow me to connect.

*************************************************************

Lets try to remove the tcp.validnode_checking & tcp.invited_nodes and see

**************************************************************

[oracle@Linux03 admin]$ cat sqlnet.ora

# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora

# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

[oracle@Linux03 admin]$ lsnrctl reload

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-MAR-2016 15:31:58

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))

The command completed successfully

[oracle@Linux03 admin]$ vi sqlnet.ora

[oracle@Linux03 admin]$ lsnrctl reload

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-MAR-2016 15:32:16

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))

The command completed successfully

C:\Windows\System32>tnsping pdborcl1

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 07-MAR-2016 15:32:36

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:

C:\app\oracle\product\11.2.0\client_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.215.XX4.XX)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = pdbo

cl1)))

OK (0 msec)

**************************************************************************

SUCCESS YOU HAVE SUCCESFULLY IMPLEMENTED ANOTHER LAYER OF SECURITY ON DB SERVER

*************************************************************************

tcp.validnode_checking = yes

tcp.invited_nodes = (Linux03,HOSTNAME1,HOSTNAME2 ...)

YOU CAN ALSO EXPLICITLY EXCLUDE NODE LIKE THIS :

tcp.excluded_nodes = (192.168.100.101,HOSTNAME2,)

But : If you only specify invited nodes with tcp.invited_nodes, all other nodes will be excluded, so there is really no reason to do both. The same is true for excluded nodes. If you put tcp.excluded_nodes = (192.168.100.101,192.168.100.160) then IP containing 192.168.100.101 and 192.168.100.160 will be excluded/denied to connect to database as a database user while allowing others to connect.