Lots of Stuff

1. Lots of Stuff Gordon D. Brown Science & Technology Facilities Council WLCG Service Reliability Workshop CERN 26-30 November 2007

2. Overview • Database Security • Passwords • Database Security Checklist • Host Housekeeping • Grid Control • Backups Overview • Interactive • Any questions WLCG Service Reliability Workshop 2

3. Database Security WLCG Service Reliability Workshop 3

4. Database Security • Authenticating DBA logins on host • Operating system (o/s) authentication • A password file • For nonsecure remote connections • Must be authenticated by a password file • Connecting to database as a privileged user over a local connection or a secure remote connection in two ways • If have password file and have been granted the SYSDBA or SYSOPER system privilege • If no password file, then operating system authentication, username of the database administrator in a special group • OSDBA:. Users in that group are granted SYSDBA privileges • OSOPER: Users in that group are granted SYSOPER privileges WLCG Service Reliability Workshop 4

5. Database Security • Operating System Authentication • OSDBA and OSOPER • The groups are created and assigned specific names as part of the database installation process. • If you are not a member of either of these operating system groups and you attempt to connect as SYSDBA or SYSOPER, the CONNECT command fails WLCG Service Reliability Workshop 5

6. Database Security • Using Password File Authentication • To enable authentication of an administrative user using password file authentication you must do the following: • If not already created, create the password file using the ORAPWD utility: • ORAPWD FILE=filename PASSWORD=password ENTRIES=max_users • For RAC the environment variable for each instance should point to the same password file • Protect password file! WLCG Service Reliability Workshop 6

7. Database Security • Operating system authentication takes precedence over password file authentication. • Specifically, if you are a member of the OSDBA or OSOPER group for the operating system, and you connect as SYSDBA or SYSOPER, you will be connected with associated administrative privileges regardless of the username/password that you specify. i.e. sqlplus / as sysdba • If you are not in the OSDBA or OSOPER groups, and you are not in the password file, then attempting to connect as SYSDBA or as SYSOPER fails. WLCG Service Reliability Workshop 7

8. Database Security • Setting REMOTE_LOGIN_PASSWORDFILE • With password file, you must set the initialization parameter REMOTE_LOGIN_PASSWORDFILE. Values: • NONE: Database behaves as if password file does not exist. • No privileged connections are allowed over nonsecure connections. • EXCLUSIVE: (default) Used with only one instance of one database. • Only an EXCLUSIVE file can be modified • Enables you to add, modify, and delete users. It also enables you to change the SYS password with the ALTER USER command. • SHARED: Used by multiple databases running on the same server, or multiple instances of a (RAC) database. • Password file cannot be modified. • This option is useful if you are administering multiple databases or a RAC database. WLCG Service Reliability Workshop 8

9. Database Security • Login overview WLCG Service Reliability Workshop 9

10. Database Security • Viewing Password File Members • V$PWFILE_USERS view to see the users who have been granted SYSDBA or SYSOPER • USERNAME: User that is recognized by the password file • SYSDBA: If TRUE, then the user can log on with SYSDBA • SYSOPER: if TRUE, then the user can log on with SYSOPER WLCG Service Reliability Workshop 10

11. Database Security • Ensure that internal networks are inside a firewall • Oracle Advanced Security • Add on • Enables data encryption and integrity checking, enhanced authentication, single sign-on, and support for DCE • Combines network encryption, database encryption and strong authentication together to help customers address privacy and compliance requirements: WLCG Service Reliability Workshop 11

12. Database Security • Transparent Data Encryption • Protection of stored data by transparently encrypting data (using 3DES or AES with up to 256 bits) at the column or tablespace level. • Network encryption and data integrity • Supports: • RC4 (40, 56, 128, and 256 bits) • DES (40 and 56 bits) • 3DES (2 and 3 keys) • AES (128, 192, and 256 bits) WLCG Service Reliability Workshop 12

13. Database Security • Strong authentication • Two-factor (or "strong") authentication is based on something the user has (a smart card, token, etc.) and a PIN or passcode. Supports: • Kerberos • RADIUS (Remote Authentication Dial-In User Service) • Secure Sockets Layer (with digital certificates) • PKI WLCG Service Reliability Workshop 13

14. Database Passwords • Use strong passwords • Try against a password checker • Passwords stored as hashes in database • Cleartext passwords can be typically but not necessarily found at the following places • Server • Shell History files • Unix Scripts • Log Files • Dump Files • Trace Files • Application Server • JDBC-Config-Files • Trace Files • DBA Client PC • Desktop-Shortcut • Batch-Files • Tool Configuration files (connections.ini) • Trace Files WLCG Service Reliability Workshop 14

15. Database Passwords • Do not email passwords around • Where are yours written down? • Do your machine room staff have them? In emergency? • Who actually knows the passwords? A list of people? • Who has access to the host (as root? oracle?) • Credentials often in Grid Control WLCG Service Reliability Workshop 15

16. Database Passwords • We use “pwsafe” • Use SSH key to access • Password on pwsafe to use • File replicated across two sites • Usernames and passwords are stored using a unique name to identify them, normally of the form group.name. However the group name is optional. • o/s <machine name>.<o/s user> • database users: <database name>.<user name> • application express: <apex instance>.<workspace> WLCG Service Reliability Workshop 16

17. Database Passwords • pwsafe • Showing A Password: • # pwsafe up <unique name> • Adding A Password: • # pwsafe a <unique name> • Deleting A Password: • # pwsafe delete <unique name> • Can add notes too WLCG Service Reliability Workshop 17

18. Machine Ports • Check the Oracle default port list • Changing the default ports can help to stop simple attacks but not real portscans. • In Oracle it is very often not possible to change the default port because the port is hardcoded. • At least for the Oracle database (except iasdb) it's is recommended to change the TNS listener port from 1521/1526 to something else. • Options include running a local firewall then using something called SQLNet Proxy to manage the port. Otherwise we could use Connection Manager. WLCG Service Reliability Workshop 18

19. Database Security Checklist WLCG Service Reliability Workshop 19

20. Database Security Checklist – Step 1 • Install only what is required • Oracle Database Installation contains a host of options and products in addition to the database server. • Install additional products and options only as necessary. • Use the custom installation option to avoid installing unnecessary products or, perform a typical installation, and then deinstall unrequired options and products. • It is best practise to know what the database will be used for so that you can apply only what Oracle products are needed. WLCG Service Reliability Workshop 20

21. Database Security Checklist – Step 1 • Common components that you can install with each database are: • Oracle Data Mining • Oracle Text • Oracle Olap • Oracle Spatial • Oracle Ultra Search • Oracle Label Security • Sample Schemas • Enterprise Manager Repository • Oracle database extensions for .NET WLCG Service Reliability Workshop 21

22. Database Security Checklist – Step 1 • None of these are needed to get a database up and running. They are "extras" to be added for more functionality. • Check which of these have been installed by running DBCA • ACTION: Use the instructions below to deinstall any options on PRODUCTION databases that are not actually used • Dropping Sample Schemas • Sample Schemas script directories are located in $ORACLE_HOME/demo/schema. • The script xx_drop.sql, where xx is the schema abbreviation, removes all objects from a particular schema. Run this for HR/OE/PM/IX/SH/BI schemas. WLCG Service Reliability Workshop 22

23. Database Security Checklist – Step 1 • Dropping schemas (cont) # cd $ORACLE_HOME/demo/schemas # sqlplus hr/pass SQL> @human_resources/hr_drop SQL> exit # sqlplus oe/pass SQL> @order_entry/oe_drop SQL> exit # sqlplus pm/pass SQL> @product_media/pm_drop SQL> exit WLCG Service Reliability Workshop 23

24. Database Security Checklist – Step 1 • Dropping schemas (cont) # sqlplus ix/pass SQL> @info_exchange/dix_v3.sql -- ignore any errors SQL> exit # sqlplus sh/pass SQL> @sales_history/sh_drop.sql -- ignore any errors SQL> exit WLCG Service Reliability Workshop 24

25. Database Security Checklist – Step 1 • Dropping schemas (cont) • no script to run for BI • Once this has been done for all the schemas, drop the users HR/OE/PM/IX/SH/BI. SQL> drop user <user> cascade; • Manual de-install of Spatial • Spatial is installed by default when using DBCA. To de-install manually follow Metalink Note:179472.1 • Also drop the MDDATA user (the notes miss this one) WLCG Service Reliability Workshop 25

26. Database Security Checklist – Step 2 • Lock and Expire Default User Accounts • Oracle installs a number of default database server user accounts. • On DBCA installation • most default database user accounts automtically locked and expireed . • On MANUAL installation • no accounts are locked • The following two slides show a list of Oracle account and their status that is given AFTER the database is created using DBCA • ACTION: Check these accounts are still expired and locked if not being used. WLCG Service Reliability Workshop 26

27. Database Security Checklist – Step 2 WLCG Service Reliability Workshop 27

28. Database Security Checklist – Step 2 WLCG Service Reliability Workshop 28

29. Database Security Checklist – Step 3 • Review all database users every 6 months (or more often) • Contact users and check schemas are still required • If not export and drop them • If any further options or Oracle products e.g. Apex, Oracle Warehouse Builder, HTTP server have been installed, check they are still required • If not deinstall them • Run the Oracle Installer in $ORACLE_HOME/oui/bin/runInstaller to check if there are any other Oracle Homes with products in them and deinstall from there. • Other products like Oracle Warehouse Builder may have an alternative de-install process. Check the documentation for your product. WLCG Service Reliability Workshop 29

30. Database Security Checklist – Step 4 • Change default User Passwords • Unlock ONLY those accounts that need to be accessed on a regular basis and assign a strong password to each of these accounts. • Minimum password of 8 alphanumeric characters. • Make sure the passwords are different between the users • Make sure they are also different for any counterpart account on a production/development/test account. • This is mandatory for all DBA accounts on the database • sys, system, sysman, dbsnmp WLCG Service Reliability Workshop 30

31. Database Security Checklist – Step 4 • Change passwords with $ sqlplus / as sysdba SQL> alter user system identified by ********; User altered. SQL> • Basic password management rules (such as length, history, complexity etc...) as provided by the database be applied to all user passwords and that these passwords are changed periodically. • ACTION: ensure all passwords are at least 8 random alphanumeric characters. If change of password is required to satisfy this, make sure you advise users/arrange appropriate downtime as an application may need to change it's settings. WLCG Service Reliability Workshop 31

32. Database Security Checklist – Step 4 • ACTION: Change SYS, SYSTEM, SYSMAN (on Grid Control DB), DBSNMP passwords every 6 months. Remember to change preferred credentials in Grid Control, agent config and pwsafe (or your wherever you store passwords). • Changing DBSNMP password • Change DBSNMP password as above. To tell the agent that the password has changed, edit the file $AGENT_HOME/<machine>/sysman/emd/targets.xml and edit the database target section as follows: Property NAME="UserName" VALUE="dbsnmp"; Property NAME="password" VALUE="newpass" ENCRYPTED="FALSE"; • Then stop and start the agent. The password will be encrypted again when the agent restarts. WLCG Service Reliability Workshop 32

33. Database Security Checklist – Step 5 • Enable Data Dictionary Protection • To prevent regular users that have ANY system privileges e.g. DROP ANY TABLE being able to use these on the data dictionary make sure the parameter is O7_DICTIONARY_ACCESSIBILITY is set to FALSE. • Action FALSE is the default in 10g but check it anyway $ sqlplus / as sysdba SQL> show parameter O7_DICTIONARY_ACCESSIBILITY NAME TYPE VALUE ------------------------------- ------------ ----- 07_DICTIONAIRY_ACCESSIBILITY boolean FALSE WLCG Service Reliability Workshop 33

34. Database Security Checklist – Step 5 • If a user does need to access a dictionary view for non malicious information, they can be granted the "SELECT ANY DICTIONARY" privilege. WLCG Service Reliability Workshop 34

35. Database Security Checklist – Step 6 • Practise the principle of least privilege • Do not provide users with any more priviliges than are strictly necessary • Restrict the following as much as possible: • The number of SYSTEM and OBJECT privileges granted to database users • 2) The number of people who are allowed to make SYS-privileged connections to the database. • ACTION: Review the SYSTEM privs (Note most regular users should only have CREATE SESSION privilege or CONNECT role assigned (although check CONNECT role only contains "CREATE SESSION" privilege in DBA_SYS_PRIVS). WLCG Service Reliability Workshop 35

36. Database Security Checklist – Step 6 • ACTION: The RESOURCE role (allows creation of tables etc) should only be assigned on creation of the schema and for schema changes and then dropped again on production databases). Drop RESOURCE role from regular users (see DBA_SYS_PRIVS). Review the contents of the RESOURCE role in DBA_ROLE_PRIVS. • ACTION: Also "ANY" privileges should not be assigned as they allow this user access to objects in all other schemas. Change ANY system privileges to object privileges e.g. drop SELECT ANY TABLE and assign .... WLCG Service Reliability Workshop 36

37. Database Security Checklist – Step 6 • To check currently assigned system privs SELECT * from DBA_SYS_PRIVS WHERE GRANTEE NOT IN ('DBA', 'SYS', 'SYSTEM', 'OEM_MONITOR', 'OEM_ADVISOR', 'IMP_FULL_DATABASE', 'EXP_FULL_DATABASE', 'ORDSYS', 'ORDPLUGINS', 'JAVADEBUGPRIV', 'OUTLN', 'FLOWS_020200', 'FLOWS_030000', 'AQ_ADMINISTRATOR_ROLE', 'CTXSYS', 'EXFSYS', 'XDB', 'APEX_PUBLIC_USER', 'CONNECT', 'RESOURCE', 'DIP', 'SCHEDULER_ADMIN', 'DBSNMP', 'ANONYMOUS', 'WMSYS', 'RECOVERY_CATALOG_OWNER') ORDER BY GRANTEE, PRIVILEGE; WLCG Service Reliability Workshop 37

38. Database Security Checklist – Step 6 • Review and revoke unneccessary privs • GRANT CONNECT TO <user>; • REVOKE CREATE PROCEDURE FROM <user>; • REVOKE RESOURCE FROM <user>; • To see roles assigned to users • SELECT * FROM dba_role_privs WHERE USER = 'ADAM'; • ACTION: Review object privs for each regular user and revoke as necessary • select * from dba_tab_privs where grantee ='SSO'; • ACTION: Check that only SYS has DBA role WLCG Service Reliability Workshop 38

39. Database Security Checklist – Step 6 • Additionally, review the privileges grants to the PUBLIC role as you may wish to restrict what access this will give to all users. • Oracle provides execution rights to four packages on the PUBLIC role that should be removed after installation: • ACTION: Revoke the execution privilege on PUBLIC to the packages: • UTL_SMTP [EMAIL] • UTL_TCP [Network Connections] • UTL_HTTP [request/retrieve information from HTTP server] • UTL_FILE [access to files outside the DB] WLCG Service Reliability Workshop 39

40. Database Security Checklist – Step 6 • Remove with # sqlplus / as sysdba SQL> REVOKE EXECUTE ON sys.utl_smtp FROM "PUBLIC"; Revoke succeeded. SQL> REVOKE EXECUTE ON sys.utl_tcp FROM "PUBLIC"; Revoke succeeded. SQL> REVOKE EXECUTE ON sys.utl_http FROM "PUBLIC"; Revoke succeeded. SQL> REVOKE EXECUTE ON sys.utl_file FROM "PUBLIC"; Revoke succeeded. WLCG Service Reliability Workshop 40

41. Database Security Checklist – Step 7 • Enforce access controls effectively and authenticate clients stringently • Setting the initialization parameter REMOTE_OS_AUTHENT = FALSE, creates a more secure configuration that enforces proper, server-based authentication of clients connecting to an Oracle database. • You should not alter the default setting of the REMOTE_OS_AUTHENT initialization parameter, which is FALSE. Setting this parameter to FALSE does not mean that users cannot connect remotely. It simply means that the database will not trust that the client has been already authenticated, and will therefore apply its standard authentication processes. WLCG Service Reliability Workshop 41

42. Database Security Checklist – Step 7 • ACTION: Check parameter REMOTE_OS_AUTHENT is FALSE on each database instance (all on RAC): $ sqlplus / as sysdba SQL> show parameter remote_os_authent NAME TYPE VALUE ---------------------- --------------------- ----- remote_os_authent boolean FALSE WLCG Service Reliability Workshop 42

43. Database Security Checklist – Step 8 • Restrict Operating System Access • Limit the number of operating system users. • Limit the privileges of the operating system accounts (administrative, root-privileged or DBA) on the host (physical machine) to the least privileges needed for the user's tasks. • ACTION: Check with System Administrator that only default users have been created and any unused default ones are locked. Ask them to remove any old users. • ACTION: Check with System Administrator that the privileges of OS accounts have the least privileges necessary. WLCG Service Reliability Workshop 43

44. Database Security Checklist – Step 8 • ACTION: Check that any database developers have their own O/S id and are in the osinstall group but not the dba group. This allows them to access sqlplus but not to log on as SYSDBA. • Restricting the ability to modify the default file and directory permissions for the Oracle Database home (installation) directory or its contents. Even privileged operating system users and the Oracle owner should not modify these permissions, unless instructed otherwise by Oracle. WLCG Service Reliability Workshop 44

45. Database Security Checklist – Step 8 • ACTION: Check with System Administrator that no O/S users can change file/directory permissions of the Oracle Home • Restricting symbolic links. Ensure that when providing a path or file to the database, neither the file nor any part of the path is modifiable by an untrusted user. • The file and all components of the path should be owned by the DBA or some trusted account, such as root. This recommendation applies to all types of files: data files, log files, trace files, external tables, bfiles, and so on. • ACTION: Check no symbolic links against data files, log files, trace files, external tables, bfiles etc WLCG Service Reliability Workshop 45

46. Database Security Checklist – Step 9 • Restrict Network Access • Use a firewall • Oracle Listener (9i) • An Oracle Listener running without an established password may be probed for critical details about the databases on which it is listening such as trace and logging information, banner information and database descriptors and service names. • Restrict the privileges of the listener, so that it cannot read or write files in the database or the Oracle server address space. This restriction prevents external procedure agents spawned by the listener (or procedures executed by such an agent) from inheriting the ability to do such reads or writes. WLCG Service Reliability Workshop 46

47. Database Security Checklist – Step 9 • Oracle Listener • Prevent online administration by requiring the administrator to have write privileges on the LISTENER.ORA file • Use the parameter ADMIN_RESTRICTIONS_listener_name to restrict runtime administration of the listener. The parameter is useful if the listener is not password-protected. • Setting ADMIN_RESTRICTIONS_listener_name=on disables the runtime modification of parameters in listener.ora. That is, the listener will refuse to accept SET commands that alter its parameters. • To change any of the parameters in listener.ora, including ADMIN_RESTRICTIONS_listener_name itself, modify the listener.ora file manually and reload its parameters (with the RELOAD command) for the new changes to take effect without explicitly stopping and restarting the listener. WLCG Service Reliability Workshop 47

48. Database Security Checklist – Step 9 • ACTION: Add or alter this line in the LISTENER.ORA file • ADMIN_RESTRICTIONS_LISTENER=ON Then RELOAD the configuration. • If you are administering the listener remotely over an insecure network and require maximum security, configure the listener with a secure protocol address that uses the TCP/IP with SSL protocol. If the listener has multiple protocol addresses, ensure that the TCP/IP with SSL protocol address is listed first in the listener.ora file. WLCG Service Reliability Workshop 48

49. Database Security Checklist – Step 9 • ACTION: Use SSL when administering the listener, by making the TCPS protocol the first entry in the address list as follows: LISTENER= (DESCRIPTION= (ADDRESS_LIST= (ADDRESS= (PROTOCOL=tcps) (HOST = ed-pdsun1.us.oracle.com) (PORT = 8281))) WLCG Service Reliability Workshop 49

50. Database Security Checklist – Step 9 • For Oracle Database 10g Release 1 and higher the default authentication mode is local O/S authentication which requires administrator to be a member of the local dba group. • Setting a password for the TNS listener in Oracle Database 10g Release 1 and higher simplifies administration. • However, setting a password requires good password management to prevent unauthorized users from guessing the password and potentially gaining access to privileged listener operations. • Customers may wish to consider not setting a password for the TNS listener starting with Oracle Database 10g Release 1. • ACTION: No need to set a listener password for release 10.1 or higher but ensure that only the oracle O/S user (or equivalent) is a member of the dba O/S group (or equivalent) WLCG Service Reliability Workshop 50