1 / 56

Practical Database Security Fundamentals Every DBA Should Know

Practical Database Security Fundamentals Every DBA Should Know. Georgia Oracle Users Conference April 4 - 5, 2005 Kennesaw, GA Kristopher Cook, Lead DBA Mirant Corporation kris.cook@mirant.com VP Communications Georgia Oracle User Group communications@gouser.org. Intended Audience.

nizana
Download Presentation

Practical Database Security Fundamentals Every DBA Should Know

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Practical Database SecurityFundamentals Every DBA Should Know Georgia Oracle Users Conference April 4 - 5, 2005 Kennesaw, GA Kristopher Cook, Lead DBA Mirant Corporation kris.cook@mirant.com VP Communications Georgia Oracle User Group communications@gouser.org

  2. Intended Audience • DBAs with less than 2 years experience • DBAs with more than 2 years experience who won’t get bored reviewing the basics

  3. Ground Rules • Please hold questions until the end • Cell Phones & Pagers - Quiet Mode

  4. Speaker’s Background • 19 years IT experience, primarily in Unix environments • Current position • Lead DBA • Previous positions (last 8-9 years) • Principal DBA • Database Administration Manager • Senior DBA • Advanced Systems Engineer

  5. Speaker’s Environment • Business • Competitive energy company that produces and sells electricity in the U.S., the Caribbean, and the Phillippines. • Owns or leases more than 17,000 megawatts of electric generating capacity globally. • Operates an integrated asset management and energy marketing organization from our headquarters in Atlanta. • www.mirant.com

  6. Speaker’s Environment(continued) • Technical • Primarily Solaris shop, ~21 DB servers • 200+ Oracle instances total; 50 in production • Sizes range from 1 to 150+ GB • 24x7 operations • Databases are local, remote, and at hotsite • Current standard is 9.2.0.4+ • Trading Systems, Power Scheduling, Plant Operations, Financials, HR, Intra/Inter-net • 5 full time DBAs

  7. Topics of Discussion • Fundamental Threats to the Database • Fundamental Threats to the Database Server • Establishing a Security Mindset

  8. Fundamental Threats to the Database • Ignorance of Privileges • Ignorance of Roles • Unintentional Access to Data • Denial of Service

  9. Fundamental Threats to the Database Ignorance of Privileges • Unlimited Tablespace • Quota Unlimited • Create • Drop/Alter • Knowing Where to Look

  10. Ignorance of Privileges - Unlimited Tablespace • Users with Unlimited Tablespace can create tables anywhere and can potentially insert unlimited rows • Determine who has unlimited tablespace select grantee from dba_sys_privs where privilege = ’UNLIMITED TABLESPACE’; • Determine which tablespaces have unlimited extents select tablespace_name, file_name, maxbytes from dba_data_files where autoextensible = ’YES’;

  11. Ignorance of Privileges -Quota Unlimited • Similar to Unlimited Tablespace but only applies to tablespace on which it has been granted • Determine who has unlimited quota and on which tablespace select username, tablespace_name, max_bytes from dba_ts_quotas; Look for a value of -1 for max_bytes which indicates unlimited

  12. Ignorance of Privileges -Create • Solid DBAs will understand the many different Create privileges and the implications of granting each • Discover create privileges available and consult Oracle doco on each (48 in 10.1) select distinct privilege from dba_sys_privs where privilege like ‘CREATE%’; • Examples: • create public synonym, database link • create any *

  13. Ignorance of Privileges -Create • Determine who has create privileges select * from dba_sys_privs where privilege like ‘CREATE%’; • For each user returned in the above query, ask if they Really need the privilege; grant the minimum required (I.e. principle of least privilege)

  14. Ignorance of Privileges -Drop / Alter • Users with Drop or Alter privileges can delete or modify objects; use care when granting • Determine who has these privileges select * from dba_sys_privs where privilege like ’DROP%’ or privilege like ’ALTER%’; • For each user returned in the above query, ask if they Really need the privilege; grant the minimum required

  15. Ignorance of Privileges - Overly Permissive Grants • Consider this example; it speaks for itself SQL> show user USER IS “COOK” SQL> select * from session privs; … EXECUTE ANY PROCEDURE … 15 rows selected. SQL> exec dbms_repcat_admin.grant_admin_any_schema(‘COOK’);

  16. Ignorance of Privileges - Overly Permissive Grants • Example (continued) SQL> select * from session_privs; PRIVILEGE ---------------------------------------- ... DROP ANY TABLE UPDATE ANY TABLE DROP ANY CLUSTER DROP ANY INDEX CREATE ANY SYNONYM DROP ANY SYNONYM DROP PUBLIC SYNONYM CREATE ANY VIEW DROP ANY VIEW ALTER ANY PROCEDURE CREATE ANY TRIGGER ... 54 rows selected.

  17. Ignorance of Privileges -Knowing Where to Look • The data dictionary has many tables related to privileges select * from dictionary where table_name like ’PRIV%’; • Examples Table_Name Comments ALL_COL_PRIVS_MADE Grants on columns for which the user is owner or grantor ALL_TAB_PRIVS_MADE User's grants and grants on user's objects DBA_COL_PRIVS All grants on columns in the database DBA_ROLE_PRIVS Roles granted to users and roles DBA_SYS_PRIVS System privileges granted to users and roles DBA_TAB_PRIVS All grants on objects in the database ROLE_ROLE_PRIVS Roles which are granted to roles ROLE_SYS_PRIVS System privileges granted to roles ROLE_TAB_PRIVS Table privileges granted to roles

  18. Fundamental Threats to the Database Ignorance of Roles • Default Roles • Admin Option • Password Protected Roles

  19. Ignorance of Roles - Default Roles • Understand the Default Roles created in a new database select * from sys.dba_role_privs; • Default roles differ by Oracle version • Understand what privileges are granted to each role select * from sys.dba_sys_privs where grantee in (select role from sys.dba_roles);

  20. Ignorance of Roles -Default Roles • For example, Creating a new user? Grant Create Session (system priv) or Grant Connect (a default role) • Create Session: user can logon • Connect: select privilege from sys.dba_sys_privs where grantee = ’CONNECT’; ALTER SESSION CREATE CLUSTER CREATE DATABASE LINK CREATE SEQUENCE CREATE SESSION CREATE SYNONYM CREATE TABLE CREATE VIEW

  21. Ignorance of Roles -Admin Option • Users with Admin Option can grant the role to other users at will • If role is later revoked from original grantee, the subsequent user still has the role and must explicitly be revoked • Some DBA tools’ wizards default to yes • Use care when granting admin option

  22. Ignorance of Roles -Password Protected Roles • Role that requires a password to be enabled • Frequently used in application development for logon security • Prevents users gaining access to application tables with Sqlplus, or other tools • See Oracle Security by Marlene Theriault and W. Heney for a good discussion on implementation

  23. Fundamental Threats to the Database Unintentional Access to Data • Default Passwords • User’s Passwords • Protecting Backups • Protecting Stored Procedures • Protecting Development/Test Environments • Making Passwords Visible

  24. Unintentional Access to Data -Default Passwords • Default passwords are widely known and publicized • Automate pw change as part of database creation • Don’t run utlsampl in production • Understand which ids get created when installing additional Oracle products • Users with shell access to the DB server can find ids: grep -i ”identified by” $ORACLE_HOME/admin/*

  25. Unintentional Access to Data -Default Passwords • A few classics: Id / passwordWhatDefault Privileges system/manager DBA DBA sys/change_on_install Data Dictionary Pretty much everything dbsnmp/dbsnmp Intelligent Agent Connect, resource, (OEM) unlimited tablespace, … mdsys/mdsys Oracle Spatial Pretty much everything outln/outln Supports Plan Stability Unlimited tablespace, resource, execute any proc tracesvr/trace Trace Server Create session, (OEM) select any table

  26. Unintentional Access to Data -Default Passwords • 10g has made improvements • For the most part, default accounts are setup as locked and expired • however, sys/system are not • dbca prompts for passwords • if using home grown scripts, your mileage may vary

  27. Unintentional Access to Data -Default Passwords • Perl script to Discover Default Passwords #!/usr/local/bin/perl @ids = <DATA>; # get list of default id/passwords from data at end of this file. open (oratab,"</var/opt/oracle/oratab"); # get list of sids on this system. while (<oratab>) { chomp; # remove newline s/#.*//; # remove comments s/^\s+//; # remove leading white space s/\s+$//; # remove trailing white space s/^\*.*//; # remove leading * next unless length; # anything left? ($sid, $home, $yn) = split ":"; # parse out the fields $ENV{"ORACLE_SID"} = $sid; $ENV{"ORACLE_HOME"} = $home; $ENV{"PATH"} = $home."/bin:/bin:/usr/bin"; $ENV{"LD_LIBRARY_PATH"} = $home."/lib:/usr/openwin/lib";

  28. Unintentional Access to Data - Default Passwords • Perl script to Discover Default Passwords (continued) # loop through list of ids, attempting to log in. for ($idx=0; $idx<@ids; $idx++) { $id = $ids[$idx]; chop($id); # remove newline @x=`sqlplus $id <<EOF exit EOF`; @ReturnLines = grep(/Connected to:/,@x); print "$id\@$sid\n" if (@ReturnLines != 0 ); # print the id we got in with. } # for idx } # while oratab close (oratab); __END__ system/manager sys/change_on_install dbsnmp/dbsnmp tracesrv/trace

  29. Unintentional Access to Data -User’s Passwords • Users take the path of least resistance • Password same as userid • Write password on yellow sticky notes • Beginning with Oracle 8 you can: • Limit # of sessions per user • Limit # of failed login attempts • Limit # of times password can be reused • Limit lifetime of password (password aging) • Develop custom verification functions • Expire passwords after a certain length of time

  30. Unintentional Access to Data -User’s Passwords • Perl script to Discover Trivial Passwords #!/usr/local/bin/perl open (oratab,"</var/opt/oracle/oratab"); # get list of sids on this system. while (<oratab>) { chomp; # remove newline s/#.*//; # remove comments s/^\s+//; # remove leading white space s/\s+$//; # remove trailing white space s/^\*.*//; # remove leading * next unless length; # anything left? ($sid,$home,$yn) = split ":"; # parse out the fields $ENV{"ORACLE_SID"} = $sid; $ENV{"ORACLE_HOME"} = $home; $ENV{"PATH"} = $home."/bin:/bin:/usr/bin"; $ENV{"LD_LIBRARY_PATH"} = $home."/lib:/usr/openwin/lib";

  31. Unintentional Access to Data -User’s Passwords • Perl script to Discover Trivial Passwords (continued) @userids=`sqlplus -silent \/ as sysdba<<eof set sqlprompt "" set pagesize 0 set trimspool on set echo off set feedback off select username from dba_users order by username; exit eof`; for ($idx=0; $idx<@userids; $idx++) { chop $userids[$idx]; @x=`sqlplus $userids[$idx]/$userids[$idx] <<EOF exit EOF`; @ReturnLines = grep(/Connected to:/,@x); print "$userids[$idx]\@$sid\n" if (@ReturnLines != 0 ); # print the id we got in with. } # for } # while oratab close (oratab);

  32. Unintentional Access to Data -Protecting Backups • Deny users read access to disks that contain backup scripts, data, exports, etc. • Use Care when sharing out directories via NFS. Limit to specific hosts • Protect physical access to tape media • Put in place process, procedure, & control • When using off-site storage facilities, limit who can request tapes • Common sense goes a long way

  33. Unintentional Access to Data -Protecting Stored Procedures • Stored Procedures may contain sensitive business logic or other code that may need to be protected • Use Oracle’s PL/SQL wrapper utility to prevent exposure of your algorithms • Example : $ wrap iname=mycode.sql oname=mycode.wrapped $ sqlplus scott/tiger @mycode.wrapped

  34. Unintentional Access to Data -Protecting Dev/QA Environments • Production data is frequently used to populate development and QA environments • Use care to ensure only authorized people are allowed to view this data

  35. Unintentional Access to Data -Making Passwords Visible • Interactive shell account users can view passwords entered on command lines by using “ps -ef” command • sqlplus • exp/imp • sqlldr • Example: sqlplus system/manager@orcl ps -ef | grep sqlplus oracle 633 103 0 20:38:50 pts/4 0:00 sqlplus system/manager oracle 656 642 0 20:39:11 pts/5 0:00 grep sqlplus • Don’t put passwords on command lines!

  36. Unintentional Access to Data -Making Passwords Visible • Alternative to putting passwords on the command line - Redirect command input inside shell script sqlplus <<EOF scott/tiger @mysql.sql exit EOF

  37. Unintentional Access to Data -Making Passwords Visible • Use care with database links too • Users with select any table can see passwords of links select userid, password, host from sys.link$;

  38. Fundamental Threats to the Database Denial of Service • Limiting DB Resources via Profiles • UTL_FILE_DIR Parameter • Production Should Stand Alone • Listeners & Name Servers

  39. Denial of Service -Limiting DB Resources via Profiles • Oracle’s default profile is wide open RESOURCE_NAME LIMIT -------------------------------- --------- COMPOSITE_LIMIT UNLIMITED SESSIONS_PER_USER UNLIMITED CPU_PER_SESSION UNLIMITED CPU_PER_CALL UNLIMITED LOGICAL_READS_PER_SESSION UNLIMITED LOGICAL_READS_PER_CALL UNLIMITED IDLE_TIME UNLIMITED CONNECT_TIME UNLIMITED PRIVATE_SGA UNLIMITED FAILED_LOGIN_ATTEMPTS UNLIMITED PASSWORD_LIFE_TIME UNLIMITED PASSWORD_REUSE_TIME UNLIMITED PASSWORD_REUSE_MAX UNLIMITED PASSWORD_VERIFY_FUNCTION UNLIMITED PASSWORD_LOCK_TIME UNLIMITED PASSWORD_GRACE_TIME UNLIMITED Select resource_name, limit from sys.dba_profiles where profile_name = ‘DEFAULT’;

  40. Denial of Service -Limiting DB Resources via Profiles • Create specific profiles for each user class • Use reasonable limits rather than unlimited • Init parm RESOURCE_LIMIT must be set to enable the ability to set limits or alter system set resource_limit = true;

  41. Denial of Service -UTL_FILE_DIR Parameter • Used for PL/SQL file I/O • Limit to only directories necessary • Avoid setting to * • Review code writing to UTL_FILE_DIR paths to ensure efficiency and correctness (I.e. doesn’t fill up disk) • Ensure directories have appropriate permissions

  42. Denial of Service -Production Should Stand Alone • Shell accounts on production systems can: • Search filesystem for scripts with passwords • fill up disk drives • execute programs, hog memory and/or cpu • locate and read export and/or sqlldr files • Refuse or limit/control shell accounts on production systems

  43. Denial of Service -Listeners & Names Servers • Password protect Listeners and Names Servers * • Before 10g, client machines with listener control or names control utility can shutdown these services • set PASSWORDS_listenername in listener.ora • lsnrctl change_password [listener_name] • Set NAMES.PASSWORD in names.ora *note - onames is deprecated in 10g, move to OID

  44. Fundamental Threats to the Database Server • Physical Security • Unintended Access • Denial of Service • Unnecessary Unix Services • Unnecessary Shell Accounts

  45. Fundamental Threats to the Database Server Physical Security • Database servers should be in a physically secure location • Limit Access to authorized personnel • Protect against fire, power failure, water, and heat

  46. Fundamental Threats to the Database Server Unintended Access • World Readable Files • Set-UID Scripts • NFS Shares

  47. Unintended Access -World Readable Files • Protect sensitive files by not allowing world read access • Set UNIX File Permissions • Owner (read,write,execute) • Group (read,execute) • World (none) • Use chmod command to alter permissions • Use umask command to set default file creation permissions for oracle account

  48. Unintended Access-World Readable Files • Examples of things to protect against • scripts where new users are created find $ORACLE_HOME –type f –exec grep –il “identified by” {} \; • sqlplus scripts that might have passwords find /u01/app/oracle/admin –type f –exec grep –il “sqlplus” {} \; • sqlldr scripts that might have passwords find /u01/app/oracle/admin –type f –exec grep –il “sqlldr” {} \; • scripts that have either import or export find /u01/app/oracle/admin –type f –exec egrep –il “exp|imp” {} \;

  49. Unintended Access -Set-UID Scripts • Risk • Potential to allow users to become root cp /usr/bin/sh /tmp/.mysh chmod 4755 /tmp/.mysh ls $* -rwsr-xr-x 1 root other 88620 .mysh • Prevention • minimize non-admins who have shell accounts • Keep your terminal locked when away • Do not put dot “.” in your PATH • Audit system for Set UID scripts find / -local –perm –004000 –type f –print

  50. Unintended Access - NFS Shares • NFS is a mechanism to make file systems on one server available to many others • Use care when sharing: • only share to specific hosts, not entire network • share as read-only if possible • share at the lowest level required, not entire disk • Example • share -o ro=client[:client]… pathname

More Related