Raval • Fichadia John Wiley & Sons, Inc. 2007 Database Management Systems Security Chapter Nine Prepared by: Raval, Fichadia
Chapter Nine Objectives • Learn the basic concepts of database management systems (DBMS) and associated terminology. • Understand the risks that impact DBMS and the controls to mitigate them. • Gain the skills to assess the security posture of a database infrastructure and make management recommendations. • Apply security principles and best practices to a database infrastructure.
The Big Picture Elements of a database management system. Some risks that impact the DBMS.
Database primer Databases: collection of related-data in an organized fashion that allows for easier access, management, and updates. • Applications need a place to store data. They could do so in application-specific files. • Doing so makes it extremely difficult to manage – especially in keeping data synched across applications. • Databases solve this problem by handling all data management issues. • Examples of popular database management systems software includes Oracle’s Oracle DBMS, Microsoft’s SQL Server, and IBM’s DB2 system.
Database primer Databases: collection of related-data in an organized fashion that allows for easier access, management, and updates. • Synchronizing files and updating all application-specific files is inefficient.
Database primer Databases: collection of related-data in an organized fashion that allows for easier access, management, and updates. • Synchronizing files and updating a common source is efficient.
Database primer Databases: Types of databases include: • Hierarchical DBs that store data in an inverted tree form and each parent node has one or more child nodes. • Network DBs is similar to hierarchical, with no parent-child constraints. • Relational DBs store data in tabular format in rows and columns. Most popular database type. • Object-oriented DBs allow storage of binary and/or complex data types (BLOBs). • Examples of popular database management systems software includes Oracle’s Oracle DBMS, Microsoft’s SQL Server, and IBM’s DB2 system.
Management concerns Concerns about database system security typically include the following: • Maximizing the database performance and availability for business transactions and queries. • Minimizing data redundancy and standardizing data management and access methods. • Keeping up with existing and upcoming security threats and implementing mitigating controls. • Having an effective backup, recovery, business resumption and a disaster recovery plan.
Risks and Controls Authentication: Process of validating a user’s identity to a database. • Users (or applications) have to prove their identity to the database before being able to access data from it. • This is typically done via user IDs and passwords – or via a “third-party authentication” wherein a operating system authenticates a user and the database takes the OS’s word for the user’s identity. • There are several ways that can expose authentication credentials.
Risks and Controls Authentication risks: • Default accounts with default passwords are often left unprotected by administrators. For example Oracle has well-known administrative user account – “sys” with a password “change_on_install”. SQL server 6.5 has no password for the administrative “sa” account. • Database often have weak password management features (when compared to operating systems) in that they may not have intruder lockouts, periodic password change requirements, etc.. • Database administrators often use batch scripts to perform administrative tasks. Often these scripts contain user IDs and password and are left unprotected.
Risks and Controls Authentication risks: • Administrators use utilities like • Database often have weak password management features (when compared to operating systems) in that they may not have intruder lockouts, periodic password change requirements, etc.. • Database administrators often use batch scripts to perform administrative tasks. Often these scripts contain user IDs and password and are left unprotected.
Risks and controls Authentication risks: • Administrators use utilities like sqlplus to connect to database. UserIDs/passwords used as parameters to these utilities may show up in process listings which can be viewed by other users.
Risks and controls Authentication risks: • Password hashes aren’t often well secured. For e.g. Oracle’s “dba_users” view, if unprotected, can disclose hashes. SQL server at times stores the password hashes in the Windows registry. At times, setup and log files have password hashes. Controls: • Default accounts should have their password changed and/or be disabled. • Companies should upgrade to newer versions of DBMS that have more robust password management features.
Risks and controls Controls: • Batch files, setup files or log files should either be deleted or protected via stronger ACLs. • Administrators should be educated to not pass credentials via command line to prevent them showing up in process listings. • ACLs on tables, views, registries that contain password hashes should be tightened and periodically reviewed.
Risks and Controls Trust relationships: DBs can be configured to trust operating systems for authenticating users. • Companies prefer reducing the number of user IDs and passwords a user has to remember. • Hence most DBs allow a “pass-through” authentication feature wherein the DB asks the OS for the user’s identity. For example, Oracle allows for two types of accounts: • External account: User accounts that have been authenticated by the OS. The DB relies on the OS for the user’s identity. • Internal account: Users that belong to a particular group (“dba”) can access the DB with administrative rights. The DB relies on the OS to ensure user belongs to administrative group.
Risks and controls Trust relationships risks: • Trusting a client OS is a really bad idea since the end user can create any account – say “oracle” – on the client OS and the DB will trust the user to be “oracle”. • Use of Internal account causes a loss of accountability since all users access the DB as the same account. Controls: • Company should ensure that DBs rely on a trusted OS – typically the server OS, not the client OS. • Disable the use of Internal account.
Risks and Controls Networking within DBs and to OSs: Pathways from a database to other databases or to operating systems. • Databases can be linked with other databases so that a users can access data from either DBs. This is done via links. For example, Oracle allows two types of links: • Public links that allow all users of a DB to access the linked DB. • Private links that allows only specific users of a DB to access the linked database. • Databases can also allow users access to the operating system hosting the database – or certain features thereof. For example, SQL server has several stored procedures such as “xp_cmdshell” that allows “shell” access within an operating system.
Risks and controls Networking within DBs and to OSs risks: • Links have embedded credentials (with which the remote DB is accessed). Views that display these are often not secured. • Links to OS or DBs allows intruders to explore more areas to find weaknesses. The higher the privileges of the embedded credentials, the greater opportunity for the intruders. • Remote databases are accessed via the credentials stored with the link. Hence, accountability of end users’ actions is lost. • Use of xp_cmdshell features makes the impact of SQL injection type attacks much worse (since the malicious SQL can now impact not only the DB, but the OS also).
Risks and controls Controls: • Company should avoid links as far as possible. • If required, private links should be used rather than public links. • The credentials used to access linked DBs should be as minimal as possible. • Limit pathways into OS as far as possible. If required, ensure qualified developers are using it in a secure fashion.
Risks and Controls Insecure DB application designs: Applications often use database as back-end tiers. • Databases often store application-related data, transaction information, user credentials, etc. • If the DB applications aren’t properly designed, the database contents can be compromised. • Poor designs can lead to bypass of application controls, identity spoofing (impersonation), and privilege escalation.
Risks and Controls Insecure DB application designs: Bypass of application controls. • Developers often design a client application that talks to a back-end database. • This client application often has several controls embedded in them (edit checks, content check, etc.) to ensure that the data reaching the DB is proper. • However this approach mistakenly assumes a user can’t talk to the DB directly. • Skilled users can submit SQL commands directly to the database, leading to a bypass of the controls embedded in the client application.
Risks and controls Insecure DB application designs: Bypass of application controls. • Relying on front-end GUI for controls is not enough.
Risks and Controls Insecure DB application designs: Identity spoofing and privilege escalation. • Developers may design a database application relies on a the client’s operating system to establish client’s identity (third-party-based authentication). • This leads to single-sign on – users logged onto their PCs don’t have to re-authenticate to the DB application. • In addition, the DB application doesn’t have to program for managing client’s user ID and passwords. • This approach often fails because the client’s operating system is not trust-worthy –end users can easily create accounts of their choice on their machines.
Risks and controls Insecure DB application designs: Identity spoofing and privilege escalation. • Relying on client OS for user credentials is a bad idea.
Risks and controls Insecure DB application designs: SQL injection attacks. • Applications, typically web-based, with back-end databases are susceptible to these attacks. • These applications convert user supplied input into SQL commands that are processed by the database. • Attackers can craft special input that make the SQL commands malicious in nature. • The database processes these malicious SQL commands and end up disclosing sensitive data or running sensitive database commands.
Risks and controls Insecure DB application designs: SQL injection attack example #1. • Consider, a web application, that allows users to change their password and asks for following inputs: UserID: pankaj Old password: reuse99 New password: simplify87 • The resulting SQL executed by the database then is: UPDATE usertable SET pwd='simplify87' WHERE userid='pankaj'; • This changes the pwd value in the usertable for the user ‘pankaj’
Risks and controls Input manipulation: SQL injection attack example #1 contd. • Now consider, if the user provides the following special input: UserID: pankaj' OR userid = 'administrator';-- Old password: reuse99 New password: simplify87 • The resulting SQL executed by the database then is: UPDATE usertable SET pwd='simplify87' WHERE userid='pankaj' OR userid = 'administrator';--'; • This changes the pwd value in the usertable for the user ‘administrator’!! (the - - ask the database to ignore any characters that follow)
Risks and controls Insecure DB application designs: SQL injection attack example #2. • Consider, a web application, that allows users to type in a keyword to search a particular product type by asking: Product keyword: antique • Say, the resulting SQL executed by the database is: SELECT product FROM product_table WHERE product_description like ‘%antique%’; • This query results in showing all products from the product_table that have the keyword ‘antique’ in it.
Risks and controls Input manipulation: SQL injection attack example #2 contd. • Now consider, if the user provides the following special input: Product keyword: antique%’ UNION SELECT username, password FROM dba_users WHERE username like ‘ • The resulting SQL executed by the database then is: SELECT product FROM product_table WHERE product_description like ‘%antique%’ UNION SELECT username, password FROM dba_users WHERE username like ‘%’; • This results in display user IDs and password hashes for all users in addition to the products!!
Risks and controls Insecure DB application designs risks: • Intruders can exploit poor application designs to bypass controls, spoof identities, escalate privileges, and compromise database security. Controls: • Application developers should be educated in the art of design secure applications. • Applications that have DB controls built into the client portion should ensure end users can’t directly connect to DBs (typically by obfuscating user credentials that are needed to connect to the database).
Risks and controls Controls contd: • Application should never rely on third-parties that aren’t controllable and trustworthy. Instead, rely on third parties such as server OS or domain credentials that aren’t in user’s control. • Protect against SQL injection attacks by: • Sanitizing user inputs by rejecting known bad data/characters, accepting only valid data, and cleaning bad data. • Not using dynamic SQL. Instead use “bind variables” or “parameterized SQL.” • Minimizing privileges of the database application user. This will limit the damage if SQL injection succeeds.
Assurance considerations An audit to assess database management system security should include the following: • Ensure quality user ID and password management techniques are used for databases. • Review authentication methods, user accounts and their privileges. • Assess the patch deployment process. All necessary patches for databases are implemented. • Ensure that the database applications have mechanisms to filter out untrusted user inputs.
Assurance considerations • Determine the business needs of links between databases. Public links should not be used. • Evaluate the need and security of all file shares. • Ensure that functional plans for backup and recovery, business resumption, disaster recovery are in place.