Practical Database Security Fundamentals Every DBA Should Know

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_at_mirant.com VP
Communications Georgia Oracle User
Group communications_at_gouser.org
2
Intended Audience

• DBAs with less than 2 years experience
• DBAs with more than 2 years experience who wont
  get bored reviewing the basics

3
Ground Rules

• Please hold questions until the end
• Cell Phones Pagers - Quiet Mode

4
Speakers 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
Speakers 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
Speakers 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
Ignorance of Privileges
Fundamental Threats to the Database

• 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
• SQLgt show user
• USER IS COOK
• SQLgt select from session privs
• EXECUTE ANY PROCEDURE
• 15 rows selected.
• SQLgt exec
• dbms_repcat_admin.grant_admin_any_schema(COOK)

16
Ignorance of Privileges - Overly Permissive Grants

• Example (continued)
• SQLgt 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
• ...

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
Ignorance of Roles
Fundamental Threats to the Database

• 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
Unintentional Access to Data
Fundamental Threats to the Database

• Default Passwords
• Users 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
• Dont 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 / password What Default 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
• _at_ids ltDATAgt get list of default
  id/passwords from data at end of this file.
• open (oratab,"lt/var/opt/oracle/oratab") get
  list of sids on this system.
• while (ltoratabgt)
• 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"

28
Unintentional Access to Data - Default Passwords

• Perl script to Discover Default Passwords
  (continued)
• loop through list of ids, attempting to log
  in.
• for (idx0 idxlt_at_ids idx)
• id idsidx
• chop(id) remove newline
• _at_xsqlplus id ltltEOF
• exit
• EOF
• _at_ReturnLines grep(/Connected to/,_at_x)
• print "id\_at_sid\n" if (_at_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 - Users 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 - Users Passwords

• Perl script to Discover Trivial Passwords
• !/usr/local/bin/perl
• open (oratab,"lt/var/opt/oracle/oratab") get
  list of sids on this system.
• while (ltoratabgt)
• 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/open
  win/lib"

31
Unintentional Access to Data - Users Passwords

• Perl script to Discover Trivial Passwords
  (continued)
• _at_useridssqlplus -silent \/ as sysdbaltlteof
• 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 (idx0 idxlt_at_userids idx)
• chop useridsidx
• _at_xsqlplus useridsidx/useridsidx
  ltltEOF
• exit
• EOF
• _at_ReturnLines grep(/Connected to/,_at_x)
• print "useridsidx\_at_sid\n" if
  (_at_ReturnLines ! 0 ) print the id we got in
  with.
• for
• while 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 Oracles PL/SQL wrapper utility to prevent
  exposure of your algorithms
• Example
• wrap inamemycode.sql onamemycode.wrapped
• sqlplus scott/tiger _at_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_at_orcl
• ps -ef grep sqlplus
• oracle 633 103 0 203850 pts/4 000 sqlplus
  system/manager
• oracle 656 642 0 203911 pts/5 000 grep sqlplus
• Dont 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 ltltEOF
• scott/tiger
• _at_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
Denial of Service
Fundamental Threats to the Database

• 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

• Oracles 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

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. doesnt
  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
Physical Security
Fundamental Threats to the Database Server

• Database servers should be in a physically secure
  location
• Limit Access to authorized personnel
• Protect against fire, power failure, water, and
  heat

46
Unintended Access
Fundamental Threats to the Database Server

• 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 expimp \

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 roclientclient pathname

51
Denial of Service
Fundamental Threats to the Database Server

• Typically handled by Network or Systems
  Administration teams
• DBAs can still help
• Monitor databases and servers from a different
  machine
• Separate production server from development,
  test, and QA
• Communicate with System and Network Administrators

52
Unnecessary Unix Services
Fundamental Threats to the Database Server

• Many unix services have known security holes
• Minimize services that are started to those that
  are necessary
• I.e. do you really need ftp, finger?
• Review /etc/rc and inetd.conf and eliminate
  services that get started but are not needed
• http//www.samag.com/documents/s1152/sam0104i/010
  4i.htm

53
Unnecessary Shell Accounts
Fundamental Threats to the Database Server

• Consider every line in /etc/passwd as a potential
  entrypoint
• Remove ids that are not needed

54
Establishing a Security Mindset

• Be Curious
• Always ask questions
• Be Proactive
• Be Paranoid
• Be Cautious
• Test everything
• Stay abreast of bugs and fixes
• Start with minimal privs
• Be vigilant
• Preach the security gospel!

55
Q A
56
Thank you!

• Georgia Oracle Users Conference
• April 4-5 2005
• Practical Database Security
• Kristopher Cook
• kris.cook_at_mirant.com