35.Check the powerful network packages andcontrol thePUBLICgrants. If they were
granted to PUBLIC read the oracle support document ID 247093.1 and ID 470366.1
and revoke the privileges from PUBLIC and regrant to necessary schema accounts.
ØBe Cautious When Revoking Privileges
Granted to PUBLIC [ID 247093.1]
ØPermissions for Packages DBMS_RANDOM,
UTL_FILE, UTL_HTTP, UTL_SMTP and UTL_TCP [ID 470366.1]
·SELECT grantee,
table_name,
privilege,
grantable
FROM
dba_tab_privs
WHERE
table_name IN
('UTL_FILE',
'UTL_TCP',
'UTL_HTTP',
'UTL_SMTP',
'DBMS_RANDOM',
'DBMS_LOB',
'DBMS_SQL',
'DBMS_SYS_SQL')
AND
grantee ='PUBLIC';
36.Check the DBMS_SCHEDULER and DBMS_JOB
package privileges and revoke them from PUBLIC.
·select * from
dba_tab_privs where
table_name ='DBMS_SCHEDULER';
·select * from
dba_tab_privs where
table_name ='DBMS_JOB';
37.Check theusers except schemaaccountswho has RESOURCE privileges .
·Select * from
dba_role_privs where
granted_role ='RESOURCE'and
grantee notin(select
username from
dba_users whereprofile='SCHEMA');
38.Check the users who has CONNECT
privileges and if its note necessary REVOKE them.
·select
*from
dba_role_privs where
granted_role ='CONNECT'and
grantee notin(select
username from
dba_users whereprofile='ORAUSER');
39.Check the critical and
administrativeroleswhich has password
·select * from sys.user$
where
type#=0andpasswordisnotnull;
40.Check these parameters value and set
the minimum requirements
·SELECTdistinctlimit,resource_nameFROM DBA_PROFILES WHERE
RESOURCE_NAME IN('PASSWORD_LIFE_TIME','FAILED_LOGIN_ATTEMPTS','PASSWORD_GRACE_TIME','PASSWORD_REUSE_MAX')orderby2desc;
41.Check the users who has default
profile and check limits forprofile
default .
·select username from
dba_users whereprofile='DEFAULT';
·select * from
dba_profiles whereprofile='DEFAULT';
42.Check the SLECT ANY PRIVILEGEandIf
its granted to PUBLIC revoke THEM.
·select grantee,admin_optionfrom dba_sys_privs whereprivilege='SELECT
ANY TABLE';
43.Check the users who has defaultpassword
·select * from
dba_users_with_defpwd;
44.Check the TMP_DIR and TMPDIR
directories path and privileges .
·#cat
/home/oracle/.bash_profile |grep TMP*
45.Check the Oracle osuser id and group
id , also privileges.
46.07_DICTIONARY_ACCESSIBILITY parameter
value is important for system privileges.Its default = FALSE and not allowed the see sys and system privileges.
·select * from
dba_users where
authentication_type<>'PASSWORD';
51.Check
the privilegesINITJVMAUX andOWA_UTIL packages.If they granted to public analyze them and revoke from public give the
necessary users.
·select
table_name,privilege,grantable
from dba_tab_privs where
table_name in('INITJVMAUX','OWA_UTIL')and
grantee='PUBLIC';
52.Check the database components
andtheir status. INVALID states
requires reinstalling, deinstalling or compilingaccording to Otn documents.Older version companents requires up to date.
·select
comp_id,version,status
from dba_registry;
53.Check the trace file public parameter
and find the optimum value. (pfile)
·_trace_files_public
54.Check the CREATE EXTERNAL JOBsystem privilege and control granted to
PUBLIC.
·select
grantee,admin_option
from dba_sys_privs whereprivilege='CREATE
EXTERNAL JOB'and
grantee='PUBLIC';
·
55.SEC_RETURN_SERVER_RELEASE_BANNERand SEC_MAX_FAILED_LOGIN_ATTEMPSparameter values are very important. These
parameters are new features in 11g.
59.Control the all profiles and limit password verify
function value. Set the NULL values to Oracle 11g standard VERIFY_FUNCTION. (
which has 8 karakter ,case sensitive, minimum 1 char, 1 number, 1 punction )
·select * from
dba_profiles where
resource_name='PASSWORD_VERIFY_FUNCTION'andlimitisnull;
60.Check theSYS
user profile and set the profile ORASYS if its not.Control ORASYS profile users.
·selectprofilefrom
dba_users where
username='SYS';
Database security checklist updated for 9i,10g,11g R2 version.This documentation is prepared from Sans Institute security checklist 2003, Pete Finnigan web site and Oracle.com Oracle security documentation .
27.Check the spfile and pfile
read/write permissions. Only Oracle software owner can read / write this file.
·$ cd $ORACLE_HOME/dbs
·$ ls -ltr *.ora ( spfileSID.ora)
28.Check the system tablespace for non
sys objects. If the object owner are not ORAUSER move them the default data
tablespace.
·SELECT * FROM
dba_segments
WHERE owner NOTIN('SYS','SYSTEM')AND
tablespace_name ='SYSTEM';
29.Check the users who has DBA role
privilege.
·SELECT * FROM
dba_role_privs where granted_role='DBA';
30.Check the system privileges and find
the users and roles granted "ANY" privileges.
·SELECTDISTINCT
grantee
FROM dba_sys_privs
WHEREprivilegeLIKE'%ANY%'
AND GRANTEE NOTIN
('SYS',
'DBA',
'IMP_FULL_DATABASE',
'DATAPUMP_IMP_FULL_DATABASE',
'WMSYS',
'AQ_ADMINISTRATOR_ROLE',
'EXP_FULL_DATABASE',
'SCHEDULER_ADMIN',
'OEM_MONITOR',
'SYSTEM',
'SYSMAN',
'DBSNMP',
'EXFSYS',
'OUTLN');
31.Check the “WITH ADMIN” and “WITH
GRANT” privileges and compare all databases.
·selectcount(*)from dba_tab_privs where
grantable='YES';
·selectcount(*)from dba_role_privs where
admin_option='YES';
·selectcount(*)from
dba_sys_privs where
admin_option='YES';
32.Check the users who has“CREATE LIBRARY “ privileges.
Last week we tested Oracle UTL_DBWS package and noticed some handicaps. Calling web service from Oracle database server have some unsecure issues for auditing and database security.
First of all you must install the web service which is not "default=enable" option while installing the 11G database software. So It means Oracle doesnt suggest UTL_DBWS packages If its not necessary. You have to download latest copy of dbws-callout-utility-10131.zip from Oracle Technology Network (OTN). However Oracle software installation creating other UTL_* web services and giving their execute privileges to PUBLIC.
Important note: Its possible to install UTL_DBWS with Oracle Database 10g software. So we have to revoke execute privilege from public. Be
cautious when revoing privileges granted to PUBLIC [ID 247093.1]
conn / as sysdba
Revoke execute on UTL_DBWS from PUBLIC;
compile invalid objects with utlrp.sql
Oracle reffered installing this service into user defined schema account instead of POWER USER SYS account. So you can call web service only with this user and call java packages from this user. Bu another handicap is here; Even you install the utl_dbws under the user account you have to give "CREATE PUBLIC SYNONYM" system privilege to schema account. Creating public synonym privilege is bring sharing schema data to another users even they dont have privilege to see data.
Before you install the web service you have to load java publisher to database. For 11gR2 and 11gR1 version databases are using latest Java Publisher 10.2 and you can download it also from OTN.
Public granted object counts is increase 23886 to 29537. Loading Java publisher create approximately 6000 objects. These objects are synonyms ,classes,resources and interfaces.
You must set the Shared pool size and java pool size for using UTL_DBWS.
INSTALLATION
SQL> desc sys.utl_dbws
ERROR:
ORA-04043: object sys.utl_dbws does not exist
SQL>
SQL>
SQL> SELECT owner, status, count(*) FROM DBA_OBJECTS WHERE OBJECT_TYPE='JAVA CLASS' GROUP BY owner, status;
OWNER STATUS COUNT(*)
------------------------------ ------- ----------
MDSYS VALID 456
SYS VALID 20285
EXFSYS VALID 47
ORDSYS VALID 1871
SQL> show parameter SHARED_POOL_SIZE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 0
SQL>
SQL> show parameter JAVA_POOL_SIZE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_pool_size big integer 0
SQL>
SQL> alter system set SHARED_POOL_SIZE=132M scope=both;
System altered.
SQL> alter system set JAVA_POOL_SIZE=80M scope=both;
System altered.
SQL> show parameter JAVA_POOL_SIZE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_pool_size big integer 80M
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
[oracle@server ~]$
[oracle@server ~]$
[oracle@server ~]$
[oracle@server ~]$
[oracle@server ~]$ mv /u01/app/oracle/product/11.2.0/db_1/sqlj /u01/app/oracle/product/11.2.0/db_1/sqlj.org
[oracle@server ~]$ ls -ltr /u01/app/oracle/product/11.2.0/db_1/sqlj.org/
total 4
drwxr-xr-x 2 oracle oinstall 4096 Dec 21 2011 lib
[oracle@server ~]$
[oracle@server ~]$ ls -ltr /u01/app/oracle/product/11.2.0/db_1/sqlj.org/lib/
total 4828
-rw-r--r-- 1 oracle oinstall 438097 Aug 13 2010 runtime12.jar
-rw-r--r-- 1 oracle oinstall 438097 Aug 13 2010 runtime12ee.jar
-rw-r--r-- 1 oracle oinstall 4053906 Aug 13 2010 translator.jar
[oracle@server ~]$
[oracle@server ~]$
[oracle@server ~]$
********************************************************************************************************************************************
[oracle@server ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu Apr 25 10:51:31 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
[oracle@server ~]$
[oracle@server ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu Apr 25 10:55:49 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
SQL> alter user sys identified by "password";
User altered.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
********************************************************************************************************************************************
Oracle SQLJ Translator and Oracle JPublisher Downloads
CREATE USER SCOTT IDENTIFIED BY "tiger" DEFAULT TABLESPACE DATA TEMPORARY TABLESPACE TEMP;
grant CREATE PUBLIC SYNONYM to scott;
GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT;
select * from dba_sys_privs where GRANTEE='SCOTT';
SELECT grantee, granted_role FROM dba_role_privs where GRANTEE = 'SCOTT';
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
SQL> @utl_dbws_decl.sql
Package created.
SQL> @utl_dbws_body.sql
Package body created.
Grant succeeded.
SQL> desc utl_dbws
PROCEDURE ADD_PARAMETER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CALL_HANDLE NUMBER IN
XML_NAME VARCHAR2 IN
Q_NAME VARCHAR2(4096) IN
P_MODE VARCHAR2 IN
FUNCTION CREATE_CALL RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SERVICE_HANDLE NUMBER IN
FUNCTION CREATE_CALL RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SERVICE_HANDLE NUMBER IN
PORT_NAME VARCHAR2(4096) IN
OPERATION_NAME VARCHAR2(4096) IN
FUNCTION CREATE_SERVICE RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SERVICE_NAME VARCHAR2(4096) IN
FUNCTION CREATE_SERVICE RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
WSDL_DOCUMENT_LOCATION URITYPE IN
SERVICE_NAME VARCHAR2(4096) IN
FUNCTION GET_IN_PARAMETER_TYPES RETURNS TABLE OF VARCHAR2(4096)
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CALL_HANDLE NUMBER IN
FUNCTION GET_LOCAL_PART RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2(4096) IN
FUNCTION GET_NAMESPACE_URI RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2(4096) IN
FUNCTION GET_OPERATIONS RETURNS TABLE OF VARCHAR2(4096)
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SERVICE_HANDLE NUMBER IN
PORT VARCHAR2(4096) IN
FUNCTION GET_OUTPUT_VALUES RETURNS TABLE OF ANYDATA
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CALL_HANDLE NUMBER IN
FUNCTION GET_OUT_PARAMETER_TYPES RETURNS TABLE OF VARCHAR2(4096)
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CALL_HANDLE NUMBER IN
FUNCTION GET_PORTS RETURNS TABLE OF VARCHAR2(4096)
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SERVICE_HANDLE NUMBER IN
FUNCTION GET_PROPERTY RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CALL_HANDLE NUMBER IN
KEY VARCHAR2 IN
FUNCTION GET_RETURN_TYPE RETURNS VARCHAR2(4096)
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CALL_HANDLE NUMBER IN
FUNCTION INVOKE RETURNS XMLTYPE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CALL_HANDLE NUMBER IN
REQUEST XMLTYPE IN
FUNCTION INVOKE RETURNS ANYDATA
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CALL_HANDLE NUMBER IN
INPUT_PARAMS TABLE OF ANYDATA IN
PROCEDURE RELEASE_ALL_SERVICES
PROCEDURE RELEASE_CALL
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CALL_HANDLE NUMBER IN
PROCEDURE RELEASE_SERVICE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SERVICE_HANDLE NUMBER IN
PROCEDURE REMOVE_PROPERTY
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CALL_HANDLE NUMBER IN
KEY VARCHAR2 IN
PROCEDURE SET_HTTP_PROXY
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
HTTPPROXY VARCHAR2 IN
PROCEDURE SET_LOGGER_LEVEL
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
LEVEL VARCHAR2 IN
PROCEDURE SET_PROPERTY
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CALL_HANDLE NUMBER IN
KEY VARCHAR2 IN
VALUE VARCHAR2 IN
PROCEDURE SET_RETURN_TYPE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CALL_HANDLE NUMBER IN
RET_TYPE VARCHAR2(4096) IN
PROCEDURE SET_TARGET_ENDPOINT_ADDRESS
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CALL_HANDLE NUMBER IN
ENDPOINT VARCHAR2 IN
FUNCTION TO_QNAME RETURNS VARCHAR2(4096)
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME_SPACE VARCHAR2 IN
NAME VARCHAR2 IN
SQL> show user
USER is "SCOTT"
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
[oracle@server lib]$
[oracle@server lib]$
[oracle@server lib]$
[oracle@server lib]$
[oracle@server lib]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu Apr 25 15:24:07 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
[oracle@server lib]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu Apr 25 15:24:07 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
SQL> call dbms_java.grant_permission('SCOTT','SYS:java.lang.RuntimePermission', 'shutdownHooks', '' );
Call completed.
SQL> call dbms_java.grant_permission('SCOTT','SYS:java.util.logging.LoggingPermission', 'control', '' );
Call completed.
SQL> call dbms_java.grant_permission('SCOTT','SYS:java.util.PropertyPermission','http.proxySet','write');
Call completed.
SQL> call dbms_java.grant_permission('SCOTT','SYS:java.util.PropertyPermission','http.proxyHost', 'write');
Call completed.
SQL> call dbms_java.grant_permission('SCOTT','SYS:java.util.PropertyPermission','http.proxyPort', 'write');
Call completed.
SQL> call dbms_java.grant_permission('SCOTT','SYS:java.lang.RuntimePermission','getClassLoader','');
Call completed.
SQL> call dbms_java.grant_permission('SCOTT','SYS:java.net.SocketPermission','*','connect,resolve');
Call completed.
SQL> call dbms_java.grant_permission('SCOTT','SYS:java.util.PropertyPermission','*','read,write');
Call completed.
SQL> call dbms_java.grant_permission('SCOTT','SYS:java.lang.RuntimePermission','setFactory','');
Call completed.
SQL> call dbms_java.grant_permission('SCOTT','SYS:java.lang.RuntimePermission', 'accessClassInPackage.sun.util.calendar','');
Call completed.
SQL>
SQL>
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
[oracle@server lib]$
ref id: Using UTL_DBWS to Make a Database 11g Callout to a Document Style Web Service [ID 841183.1]
