In my case, self-signed certification is used.
High level steps are;
1. Relink SE Oracle binary in order to support TCPS. Skip this step for EE
2. Create wallet to store self-signed cert
3. Create self-signed cert
4. Verify TCPS connection over SQL*NET
5. Configure http2 for APEX Embedded web server
Below are details.
Step 1 - Enable SE TCPS
Follow Oracle doc - How To Enable TCPS Support For Oracle Standard Edition (Doc ID 1457854.1)
-- current lib file
/software/oracle/product/12.2.0/lib> ls -l libntcps*
-rw-r--r-- 1 oracle oracle 356034 Dec 15 2016 libntcps12.a
--installer jar files
:/software/oracle/admin/INSTALLER/database/stage/Components/oracle.network.rsf/12.2.0.1.0/1/DataFiles> ls -l
total 4540
-rwxr-xr-x 1 oracle oracle 722400 Jan 26 2017 filegroup1.jar
-rwxr-xr-x 1 oracle oracle 3708288 Jan 26 2017 filegroup2.jar
-rwxr-xr-x 1 oracle oracle 212871 Jan 26 2017 filegroup3.jar
> pwd
/software/oracle/admin/INSTALLER/database/stage/Components/oracle.network.rsf/12.2.0.1.0/1/DataFiles
> $ORACLE_HOME/jdk/bin/jar tvf filegroup2.jar |grep libntcps
356034 Thu Dec 15 07:31:24 SGT 2016 lib/libntcps12_ee.a.dbl
> $ORACLE_HOME/jdk/bin/jar xvf ~/filegroup2.jar lib/libntcps12_ee.a.dbl
inflated: lib/libntcps12_ee.a.dbl
> ls -la libntcps*
-rw-r--r-- 1 oracle oracle 356034 Dec 15 2016 libntcps12.a
-rw-r--r-- 1 oracle oracle 356034 Jan 24 14:37 libntcps12_ee.a.dbl
> cp -p libntcps12.a libntcps12.a.orig
-- relink
> sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Wed Jan 24 14:39:17 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production
> ps -f |grep tns
oracle 20494 20272 0 14:39 pts/0 00:00:00 grep --color=auto tns
> ps -fu oracle
UID PID PPID C STIME TTY TIME CMD
oracle 14048 14046 0 11:20 ? 00:00:01 sshd: oracle@pts/1
oracle 14049 14048 0 11:20 pts/1 00:00:00 -ksh
oracle 19604 1 0 14:04 ? 00:00:00 /software/oracle/product/12.2.0/bin/tnslsnr LISTENER_ORCL -inherit
oracle 20271 20269 0 14:28 ? 00:00:00 sshd: oracle@pts/0
oracle 20272 20271 0 14:28 pts/0 00:00:00 -ksh
oracle 20500 20272 0 14:40 pts/0 00:00:00 ps -fu oracle
> lsnrctl stop LISTENER_ORCL
LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 24-JAN-2018 14:40:10
Copyright (c) 1991, 2016, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1588)))
The command completed successfully
> relink all
writing relink log to: /software/oracle/product/12.2.0/install/relink.log
> ls -la libntcps*
-rw-r--r-- 1 oracle oracle 356034 Dec 15 2016 libntcps12.a
-rw-r--r-- 1 oracle oracle 356034 Dec 15 2016 libntcps12.a.orig
-rw-r--r-- 1 oracle oracle 356034 Jan 24 14:37 libntcps12_ee.a.dbl
> ls -l /software/oracle/product/12.2.0/install/relink.log
-rw-r--r-- 1 oracle oracle 169637 Jan 24 14:41 /software/oracle/product/12.2.0/install/relink.log
#highlighted are updated files
ls -lrt
...
-rw-r--r-- 1 oracle oracle 22215298 Jan 24 12:01 libcommon12.a
-rw-r--r-- 1 oracle oracle 36700774 Jan 24 12:01 libgeneric12.a
-rwxr-xr-x 1 oracle oracle 2032800 Jan 24 12:01 libasmclntsh12.so
-rw-r--r-- 1 oracle oracle 356034 Jan 24 14:37 libntcps12_ee.a.dbl
-rw-r--r-- 1 oracle oracle 14683 Jan 24 14:40 libskgxn2.so
-rw------- 1 oracle oracle 137 Jan 24 14:40 ldflagsO
-rw-r--r-- 1 oracle oracle 137 Jan 24 14:40 ldflags
-rw-r--r-- 1 oracle oracle 2872 Jan 24 14:40 ntcontab.o
-rw-r--r-- 1 oracle oracle 1968 Jan 24 14:40 nnfgt.o
-rw-r--r-- 1 oracle oracle 9619442 Jan 24 14:40 libn12.a
-rwxr-xr-x 1 oracle oracle 8033736 Jan 24 14:40 libclntshcore.so.12.1
-rw-r--r-- 1 oracle oracle 1687689 Jan 24 14:40 clntshcore.map
lrwxrwxrwx 1 oracle oracle 21 Jan 24 14:40 libclntshcore.so -> libclntshcore.so.12.1
-rwxr-xr-x 1 oracle oracle 71653096 Jan 24 14:40 libclntsh.so.12.1
-rw-r--r-- 1 oracle oracle 5080271 Jan 24 14:40 clntsh.map
lrwxrwxrwx 1 oracle oracle 17 Jan 24 14:40 libclntsh.so -> libclntsh.so.12.1
lrwxrwxrwx 1 oracle oracle 12 Jan 24 14:40 libclntsh.so.11.1 -> libclntsh.so
lrwxrwxrwx 1 oracle oracle 12 Jan 24 14:40 libclntsh.so.10.1 -> libclntsh.so
-rwxr-xr-x 1 oracle oracle 2222528 Jan 24 14:40 libocci.so.12.1
lrwxrwxrwx 1 oracle oracle 15 Jan 24 14:40 libocci.so -> libocci.so.12.1
-rwxr-xr-x 1 oracle oracle 2553264 Jan 24 14:40 libagtsh.so.1.0
lrwxrwxrwx 1 oracle oracle 15 Jan 24 14:40 libagtsh.so -> libagtsh.so.1.0
-rwxr-xr-x 1 oracle oracle 194936 Jan 24 14:40 libsrvmm2.so
-rw-r--r-- 1 oracle oracle 1192447 Jan 24 14:40 libskgxp12.so
Step 2 - create wallet
Use oracle wallet manager - owm to create an empty wallet.
Step 3 - create self-signed cert
ORCL:localhost:/software/oracle/admin/ORCL/wallet> orapki wallet add -wallet ./ -pwd Oracle123$ \
> -dn "CN=`hostname`, OU=DBA, O=mm, L=SG, ST=SG, C=SG" \
> -keysize 1024 -self_signed -validity 3650
Oracle PKI Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
Operation is successfully completed.
ORCL:localhost:/software/oracle/admin/ORCL/wallet> orapki wallet display -wallet ./ -pwd Oracle123$
Oracle PKI Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
Requested Certificates:
Subject: C=SG,ST=SG,L=SG,O=mm,OU=dba,CN=dbmate-cert
User Certificates:
Subject: CN=localhost,OU=DBA,O=mm,L=SG,ST=SG,C=SG
Trusted Certificates:
Subject: CN=localhost,OU=DBA,O=mm,L=SG,ST=SG,C=SG
Step 4 - verify TCPS
-- config sqlnet
ORCL:localhost:/software/oracle/product/12.2.0/network/admin> cat sqlnet.ora
# sqlnet.ora Network Configuration File: /software/oracle/product/12.2.0/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
SSL_CLIENT_AUTHENTICATION = FALSE
WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = /software/oracle/admin/ORCL/wallet )
)
)
ORCL:localhost:/software/oracle/product/12.2.0/network/admin> cat listener.ora
# listener.ora Network Configuration File: /software/oracle/product/12.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER_ORCL =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1588))
(ADDRESS = (PROTOCOL = TCPS)(HOST = localhost)(PORT = 1589))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SSL_CLIENT_AUTHENTICATION = FALSE
WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = /software/oracle/admin/ORCL/wallet )
)
)
--tnsnames.ora
ORCL_SSL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCPS)(HOST = localhost)(PORT = 1589))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL)
)
)
ORCL:localhost:/software/oracle/product/12.2.0/network/admin> sqlplus perfstat@ORCL_SSL
SQL*Plus: Release 12.2.0.1.0 Production on Fri Jan 26 11:00:54 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Enter password:
Last Successful login time: Fri Jan 26 2018 11:00:14 +08:00
Connected to:
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production
SQL> select sys_context('userenv','network_protocol') from dual;
SYS_CONTEXT('USERENV','NETWORK_PROTOCOL')
--------------------------------------------------------------------------------
tcps
SQL> exit
Disconnected from Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production
ORCL:localhost:/software/oracle/product/12.2.0/network/admin> tnsping ORCL_SSL
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 26-JAN-2018 11:01:17
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
/software/oracle/product/12.2.0/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCPS)(HOST = localhost)(PORT = 1589)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL)))
OK (10 msec)
Step 5. Configure http2 for APEX Embedded web server
Set http2-port and http2-protocol in the XDB configuration:
See Note 942945.1
5.1 Set dispatcher for TCPS in database parameter file
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)','(PROTOCOL=TCPS) (SERVICE=ORCLXDB)'
5.2 set apex /Manage Instance/Requires HTTPS = always.
My database is 12.2c , the APEX http port was 8888, I am using same port for https , as no more HTTP will be supported.
SQL> select dbms_xdb_config.gethttpport() from dual;
DBMS_XDB_CONFIG.GETHTTPPORT()
-----------------------------
8888
SQL> exec dbms_xdb_config.sethttpport(8080);
PL/SQL procedure successfully completed.
SQL> select dbms_xdb_config.gethttpport() from dual;
DBMS_XDB_CONFIG.GETHTTPPORT()
-----------------------------
8080
SQL> call DBMS_XDB_CONFIG.SETLISTENERENDPOINT(2, null, 8888,2);
Call completed.
SQL> select dbms_xdb_config.gethttpport() from dual;
DBMS_XDB_CONFIG.GETHTTPPORT()
-----------------------------
8080
select extractValue(value(x),'/httpconfig/http2-protocol', 'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"') "Protocol"
2 , extractValue(value(x),'/httpconfig/http2-port', 'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"') "Port#"
3 from table(xmlsequence(extract(xdburitype('/xdbconfig.xml').getXML(),'/xdbconfig/sysconfig/protocolconfig/httpconfig'))) x
4 /
Protocol
--------------------------------------------------------------------------------
Port#
--------------------------------------------------------------------------------
tcps
8888
Cheers!
Showing posts with label security. Show all posts
Showing posts with label security. Show all posts
Sunday, January 28, 2018
Tuesday, August 08, 2017
SQL server audit related links
Collected some related to SQL server audit setup, writing to windows event log.
Auditing Failed Logins in SQL Server
How-To Write SQL Server Audit Events To Windows Security Log
After following these steps, you should successfully start seeing your Microsoft SQL Server Audit Logs in the Windows Security Log. They’re usually listed as Event Code 33205, so you can filter your Security Logs to only show those events.
SQL Server Audit (Database Engine)
All editions of SQL Server support server level audits. All editions support database level audits beginning with SQL Server 2016 SP1. Prior to that, database level auditing was limited to Enterprise, Developer, and Evaluation editions
Any authenticated user can read and write to the Windows Application event log. The Application event log requires lower permissions than the Windows Security event log and is less secure than the Windows Security event log.
Attaching a database that has an audit specification and specifies a GUID that does not exist on the server will cause an orphaned audit specification. Because an audit with a matching GUID does not exist on the server instance, no audit events will be recorded. To correct this situation, use the ALTER DATABASE AUDIT SPECIFICATION command to connect the orphaned audit specification to an existing server audit. Or, use the CREATE SERVER AUDIT command to create a new server audit with the specified GUID.
You can attach a database that has an audit specification defined on it to another edition of SQL Server that does not support SQL Server audit, such as SQL Server Express but it will not record audit events.
Members of the sysadmin fixed server role are identified as the dbo user in each database. To audit actions of the administrators, audit the actions of the dbo user.
Creating and Managing Audits with SQL Server Management Studio
Audit-related nodes are located as follows in the Object Explorer hierarchy in Management Studio.
SQL Server Instance
---- Security
-------- Audits
-------- Server Audit Specifications
SQL Server Instance
---- Databases
-------- Database Name
------------ Security
---------------- Database Audit Specifications
Create a Server Audit and Server Audit Specification
Create a Server Audit and Database Audit Specification
SQL Server Audit Action Groups and Actions
Write SQL Server Audit Events to the Security Log
SQL Server Audit Support in Different Editions and Versions ( info for version prior to sql 2016)
Sat, 23 Nov 2013 14:02:05 GMT
Here is a summary of the SQL Server Audit support in the different editions:
sql server ports 445 and 1433
445 is not a SQL port, is a SMB port. It is involved in SQL Server only if you use named pipes protocol, as named pipes are over SMB and this in turn uses 445 for 'SMB over IP', aka. as SMB 'NETBIOSless' as opposed to the old NetBIOS based SMB, which uses 137-139.
To configure the server to listen on specific protocols, use SQL Server configuration Manager. To configure the client allowed protocols, see Configuring Client Network Protocols.
Auditing Failed Logins in SQL Server
How-To Write SQL Server Audit Events To Windows Security Log
After following these steps, you should successfully start seeing your Microsoft SQL Server Audit Logs in the Windows Security Log. They’re usually listed as Event Code 33205, so you can filter your Security Logs to only show those events.
SQL Server Audit (Database Engine)
All editions of SQL Server support server level audits. All editions support database level audits beginning with SQL Server 2016 SP1. Prior to that, database level auditing was limited to Enterprise, Developer, and Evaluation editions
Any authenticated user can read and write to the Windows Application event log. The Application event log requires lower permissions than the Windows Security event log and is less secure than the Windows Security event log.
Attaching a Database with an Audit Defined
Attaching a database that has an audit specification and specifies a GUID that does not exist on the server will cause an orphaned audit specification. Because an audit with a matching GUID does not exist on the server instance, no audit events will be recorded. To correct this situation, use the ALTER DATABASE AUDIT SPECIFICATION command to connect the orphaned audit specification to an existing server audit. Or, use the CREATE SERVER AUDIT command to create a new server audit with the specified GUID.
You can attach a database that has an audit specification defined on it to another edition of SQL Server that does not support SQL Server audit, such as SQL Server Express but it will not record audit events.
Auditing Administrators
Members of the sysadmin fixed server role are identified as the dbo user in each database. To audit actions of the administrators, audit the actions of the dbo user.
Creating and Managing Audits with SQL Server Management Studio
Audit-related nodes are located as follows in the Object Explorer hierarchy in Management Studio.
SQL Server Instance
---- Security
-------- Audits
-------- Server Audit Specifications
SQL Server Instance
---- Databases
-------- Database Name
------------ Security
---------------- Database Audit Specifications
Create a Server Audit and Server Audit Specification
Create a Server Audit and Database Audit Specification
SQL Server Audit Action Groups and Actions
Write SQL Server Audit Events to the Security Log
SQL Server Audit Support in Different Editions and Versions ( info for version prior to sql 2016)
Sat, 23 Nov 2013 14:02:05 GMT
Here is a summary of the SQL Server Audit support in the different editions:
| click me | click me | click me |
|---|---|---|
| Edition | SQL Server 2008 and 2008 R2 | SQL Server 2012 and 2014 |
| Enterprise | Server- and database-level | Server- and database-level |
| Evaluation | Server- and database-level | Server- and database-level |
| Developer | Server- and database-level | Server- and database-level |
| Datacenter | Server- and database-level | N/A |
| Business Intelligence | None | Server-level |
| Standard | None | Server-level |
| Web | None | Server-level |
| Express | None | Server-level |
sql server ports 445 and 1433
445 is not a SQL port, is a SMB port. It is involved in SQL Server only if you use named pipes protocol, as named pipes are over SMB and this in turn uses 445 for 'SMB over IP', aka. as SMB 'NETBIOSless' as opposed to the old NetBIOS based SMB, which uses 137-139.
To configure the server to listen on specific protocols, use SQL Server configuration Manager. To configure the client allowed protocols, see Configuring Client Network Protocols.
Friday, June 30, 2017
Use full links related to SQL server baselines
Marked some useful links during my review of SQL server security baselines, so as to close my precious resource of Chrome browser tabs. As for each google, I commonly open 3 links ...
Review the old copy of this baseline had been my dream, to make it easier for DBA.
Server Configuration Option related
audit
remote admin connections Server Configuration Option
Configure the remote access Server Configuration Option (2008)
xp_cmdshell Server Configuration Option
How to check trustworthy property of the databases
scan for startup procs Option
Permission
Security announcement
User logins
select * from sys.sql_logins;
Server configuration
Extended Stored Procedure
Sunday, July 11, 2010
Sunday, April 18, 2010
11g sqlplus doesn't hide password
Prior to 10g Oracle database, anyone can see the database account password using 'ps -ef |grep sqlplus ' , if it is hard coded on same command line with sqlplus, e.g sqlplus username/password@DBNAME
In 10g , Oracle "solved" this problem ,.
However, it comes back again in 11g. Searched metalink, no more document says password will be removed. :(
Too bad. Lucky, we have alternative way.
> ps -ef |grep sqlplus
oravas11 4997 4748 0 13:41:31 pts/0 0:00 ied sqlplus perfstat/xxxxxxxx@VAS11GP
oravas11 4998 4997 0 13:41:31 ttyp1 0:00 sqlplus perfstat/xxxxxxxx@VAS11GP
In 10g , Oracle "solved" this problem ,.
However, it comes back again in 11g. Searched metalink, no more document says password will be removed. :(
Too bad. Lucky, we have alternative way.
> ps -ef |grep sqlplus
oravas11 4997 4748 0 13:41:31 pts/0 0:00 ied sqlplus perfstat/xxxxxxxx@VAS11GP
oravas11 4998 4997 0 13:41:31 ttyp1 0:00 sqlplus perfstat/xxxxxxxx@VAS11GP
Saturday, January 02, 2010
Saturday, May 17, 2008
remove external procedures
By default, Oracle creates a service for external procedures in the listener. For the majority
of users, this service is never used. Therefore, the service should be removed from the listener.
of users, this service is never used. Therefore, the service should be removed from the listener.
Subscribe to:
Posts (Atom)
