Database Security PDF

Database Security Chapter 6 Password, Profiles, Privileges, and Roles  Objectives Define authentication and then implement with SQL Server, MySQL, and Oracle Define authorization and then implement with SQL Server, MySQL, and Oracle Manage users based o...

Database Security Chapter 6 Password, Profiles, Privileges, and Roles  Objectives Define authentication and then implement with SQL Server, MySQL, and Oracle Define authorization and then implement with SQL Server, MySQL, and Oracle Manage users based on security best practices using SQL Server, MySQL, and Oracle Identify and apply password best practices using SQL Server, MySQL, and Oracle Database Security 2  Objectives (cont’d.) Define and create roles using SQL Server, MySQL, and Oracle Define, grant, deny, and revoke privileges using SQL Server, MySQL, and Oracle Database Security 3  Authentication Several layers of security must be put into place to ensure that data is only granted to those authorized individuals and applications. There are two main steps to controlling access to data: authentication and authorization. Authentication – Authentication is the process of confirming the identity of those individuals or applications that request access to a secure environment – Confirming the identity of a person or an application is done by verifying that the login and credentials match those that have been created within that same environment. Database Security 4  Authentication (cont’d.) Credential – Piece of information used to verify identity Examples of credentials – Person’s username and password – Application’s secure ID – Host’s network name and address Types of credentials used to verify identity: – Depend on the authentication processes of a particular system or environment Database Security 6  Authentication can be verified a few times and at different levels during a single attempt at logging in to a system before permission is granted to a user. Database Security 7  Authentication (cont’d.) Third-party applications can also be used within a database environment to further add security to the authentication of users because they often take additional steps, such as password encryption, to keep a network environment secure. Three levels of authentication in a database environment – OS level, database level, third-party support Database Security 8  Combined, these levels make for a secure environment, but using one alone can bring great advantages and disadvantages to the security of the environment. Database Security 9  Operating System Authentication Credentials authenticated primarily through the OS – Account must reside on operating system – OS account credentials must be used to access the system – In some cases, the OS login alone can be used to authenticate users to the database – Advantages: convenience to the user, centralized account administration Database Security 10  Database Authentication Credentials that are checked against the database require that the user attempting to access the system has a local database account therein. In this situation, the user may be required to access a few different systems prior to reaching the database. This means that users must keep different account credentials for different systems. This often leads to poor password practices, such as writing passwords down and choosing weak passwords, for memory’s sake. Administration of this type of environment is also more difficult. Database Security 11  Network or Third-Party Authentication Authentication for a database can also be conducted using third-party applications and at the network level of the environment. Third-party applications and network account authentication systems can be used for remote and physical environments. These users are not required to have an account created on the operating system or the database; however, they are required to have a network account or be recognized by the third-party application. Database Security 13  Network or Third-Party Authentication (cont’d.) Third-party or external authentication not recommended for use alone – Can be combined with OS and server authentication Database Security 15  Database Vendor–Specific Authentication Components SQL Server authentication information – Server uses Windows Authentication and Mixed Mode Authentication Windows Authentication – Users logging in to the database must have a Windows login to access – Known as trusted authentication – Recommended authentication mode for SQL Server Database Security 16  Database Vendor–Specific Authentication Components (cont’d.) Mixed Mode Authentication – Allows both Windows Authentication and SQL Server authentication to be used to obtain database access – Known as an untrusted connection Not as secure as Windows Authentication Protocols such as Kerberos cannot be used Database Security 17  MySQL Authentication MySQL Authentication Information MySQL uses an authentication protocol for access to the server and it identifies users slightly differently than SQL Server and Oracle do. A MySQL user’s identity is verified using three pieces of information: The host name for which the server is running The user-supplied MySQL username The user-supplied password To access the database, the identity credentials must match those credentials stored in the database. Database Security 18  Oracle authentication information Oracle supports many options for authenticating users, applications, and machines, and it provides customization options to support almost any environment. Database servers, database links, and environment passwords can all be used as credentials for authentication within Oracle 11g, and several additional applications are available to be purchased to further enhance the security of the database infrastructure. Database Security 19  One of the more notable services that can be purchased and added to Oracle as a way to further enhance authentication is Advanced Security. Advanced Security is a comprehensive security application. It offers encryption of both information transmitting across the network and stored within the database and provides strong authentication strategies that support and integrate with the industry-standard authentication methods (e.g., Kerberos, PKI, and SSL). Database Security 20  Database Vendor–Specific Authentication Components (cont’d.) Middleware applications – Designed to monitor external requests for database access Database linking – Feature that enhances authentication support – Link between two databases resulting in one logical storage unit – Enables applying common policies – Links can be public or private Database Security 22  Password Policies Most intrusions originate from a cracked or stolen password Password policy implementation – Organization’s first defense against compromised passwords – Can be enforced within database server application – More effective than written policy Both written and server-defined policies should be used for maximum effectiveness Database Security 23 Database-Enforced Password Policies Password policy options are often vendor specific – Most server applications share similar configuration settings Four password attributes can be enforced in almost every database server – Complexity – Failed attempts – Expired passwords – Password reuse Database Security 24  Written Password Policies Included in equipment usage agreement between an organization and its employees – Usage agreement must be flexible enough to be consistently enforced – And strict enough to ensure users abide by the policy Common standards likely to be included in an equipment usage agreement – Password sharing – Password storage Database Security 25  Database Vendor–Specific Password Management SQL Server password policy – Available password policy methods Password complexity, password expiration, and enforcing password policy SQL Server password complexity requirements – Passwords should be unique and not include common or reserved words, or usernames – Length between 8 and 128 characters – Can include underscore, dollar sign, and number sign Database Security 26  Database Vendor–Specific Password Management (cont’d.) SQL Server password complexity requirements (cont’d.) – Must include at least one digit and one alphabetic character – Cannot begin with a number MySQL password policy – Administrators must rely on operating system and third-party applications – Stored in 45-bit encryption in user table – Passwords are case sensitive, vary in length, and can include special characters Database Security 27  Database Vendor–Specific Password Management (cont’d.) Oracle password policy – Stored encrypted in DBA_USER table – Several built-in password protection services Examples: case sensitivity, password hashing Oracle password complexity requirements – Passwords should be unique and cannot include simple words, server names, usernames, or server/usernames with numbers appended – Length between 8 and 128 characters Database Security 28  Database Vendor–Specific Password Management (cont’d.) Oracle password complexity requirements (cont’d.) – A new password must differ from previous password by at least three letters – Must include at least one digit and one alphabetic character – Cannot begin with a number – Can include an underscore, dollar sign, and number sign – Can begin with a special character or contain characters other than _, $, and #, if password is surrounded by quotation marks Database Security 29  Table 6-1 Oracle password-related functions Database Security 30  Authorization Process of applying permissions to a user – Ensures users requesting access have permission to do so Determined prior to a user obtaining authentication credentials Choosing the most appropriate privileges for each user helps maintain a healthy and secure database Database Security 31  User Account Management User management tasks – Add, remove, and assign privileges to users Administrator must understand: – Default user accounts and privileges created during installation of database management system Database Security 32  Default User Accounts Default user accounts are created with predefined user access – True for virtually every type of database Most default users are the system or administration accounts Default passwords, usernames, rights and privileges can easily be found online Need to secure the default accounts to protect data Database Security 33  Default User Accounts (cont’d.) Default users installed with SQL Server – Two administrator accounts SA and BUILT-IN\Administration – One general PUBLIC account Guest Default users installed with MySQL – Two root accounts – Two anonymous user accounts – No passwords are set immediately Should be assigned during installation Database Security 34  Default User Accounts (cont’d.) Default users installed with Oracle – Number and type of default accounts can vary greatly Depend on installed options, features, and additions – Most accounts created to expire and be locked after installation – Three accounts remain open for use after installation SYS SYSMAN SYSTEM Database Security 35  Adding and Removing Users Always change default password of a new user – Or force password change prior to server entry Save user passwords in an encrypted file Enforce strong password policies Use different logins and passwords for different applications Ensure users read and agree to database usage policies Before removing user, perform inventory of user’s created objects Database Security 36  Adding and Removing Users (cont’d.) Recommended to disable a user account instead of deleting it – Always document removals of database user accounts Documentation – Most important component of adding or deleting accounts Database Security 37  User Privileges Privilege – Smallest unit of authorization – Ability to access a specific resource to perform a specific action Examples of privileges – Deleting a row – Creating a table – Executing a procedure Privileges should be planned out in early stages of database planning Database Security 38  User Privileges (cont’d.) Principle of least privilege – Security standard – Each user given minimum set of privileges needed to conduct legitimate business within the system Managing user privileges – Granting a privilege – Denying a privilege – Revoking a privilege Two ways to grant a privilege Fixed—Fixed privileges are predefined by the server. They are often grouped together as one group to which users are assigned. Single statement—Single statement privileges are assigned individually to individual specific users of the database. Database Security 39  User Privileges (cont’d.) Assigning privileges in SQL Server – Three levels of permissions can be granted Server-level Database-level Object-level – Can grant object permissions to individual users or roles – Privileges can be single statements Database Security 40  User Privileges (cont’d.) Assigning privileges in MySQL – Five levels of privilege Global privileges Database privileges Table object privileges Column object privileges Routine privileges – GRANT command used to provide access to a privilege Will create a new user if nonexistent Database Security 41  Table 6-2 Grant tables for privilege administration Database Security 42  User Privileges (cont’d.) Assigning privileges in Oracle – Two levels of privilege System-level Object-level – Administrators can grant system-level privilege – Object privileges granted by schema owner of an object – Privileges can be granted to PUBLIC Grants privilege to all database users Not recommended for security reasons Database Security 43  Roles A role is a set of related privileges that are combined to provide a centralized unit from which to manage similar users or objects of a database. Roles can be created for users, objects, and applications – Single role can be assigned to many users – Single user can be assigned many roles Advantages of using roles – Saves time and resources – Provides a central location for administration Database Security 44  Roles (cont’d.) Defining roles in SQL Server – Roles defined at either server or database level Server roles – Grant rights to manipulate the server environment – Rights granted to login accounts Database roles – Grant access to database objects – Rights granted to user accounts Database Security 45  Roles (cont’d.) Five types of roles available within SQL Server – Fixed server, fixed database, user-defined, application, and public Fixed server roles – Provide server-level privileges – Cannot be changed or deleted – Users can be added to them Fixed database roles – Provides privileges specific to the database – Cannot be altered, yet users can be added Database Security 46  Table 6-3 Fixed server roles for SQL Server Database Security 47  Table 6-4 Fixed database roles for SQL Server Database Security 48  Roles (cont’d.) User-defined roles – Built to control access of objects within the database Application roles – Created to support security requirements of applications PUBLIC role – Special role in which every database user is a member – Members cannot be removed – Provides a way to assign privilege for all users Database Security 49  Roles (cont’d.) Defining roles in MySQL – Roles are not included in MySQL Server alone – Roles may be created using scripting and third-party applications Defining roles in Oracle – Several roles are built-in – Roles provide privileges at system and object levels – Roles can be granted to other roles Database Security 50  Table 6-5 Common predefined Oracle roles Database Security 51  Table 6-6 Locating roles in Oracle Database Security 52  Inference Method for unauthorized users to obtain sensitive information – Making assumptions based on database’s reactions or query responses Unauthorized users can draw conclusions about the database – Enables knowledge or understanding of the data – Users may be internal or external Inference is a great security threat – Difficult to predict, detect, and eliminate Database Security 53  Examples of Inference Two primary means of inference – Using logic – Using statistics Logic, relationship, and constraint interference – Well organized, logical tables are vulnerable to inference Example of logical inference – Hotel database table includes customer ID, last name, first name, and profile level – Customer ID is primary key Database Security 54  Examples of Inference Example of logical inference (cont’d.) – Security rule or constraint ensures only hotel managers can view information about high profile guests’ rooms – Desk clerk cannot see room 4001 in the table – Desk clerk tries to book but cannot – Can infer room 4001 is occupied by a high profile guest Database Security 55  Table 6-7 Guest table view Table 6-8 Secured available room view Database Security 56  Examples of Inference Statistical inference – Statistical queries analyze the data but do not return actual data – Can be easily manipulated to retrieve sensitive information Example of statistical inference – Database user queries average of her salary and a co-worker’s – Uses basic arithmetic to determine co-worker’s salary Database Security 57  Minimizing Inference Techniques to limit a person’s ability to infer – Polyinstantiation – Log, monitor, and alert of events – Limit user capability – Limit query responses Polyinstantiation – Strategy that allows database to contain multiple instances of a record – Creates “fake” records – Downside: confusing false records with real ones Database Security 58  Table 6-9 Polyinstantiation view Database Security 59  Minimizing Inference (cont’d.) Other ways to minimize – Less disruptive to database environment than polyinstantiation Log, monitor, and alert of events – Monitor activities – Set baseline and threshold alert for unusual user activity – Capture and analyze database activity logs Database Security 60  Minimizing Inference (cont’d.) Limit user capability – Limit user’s query size – Allow only aggregate operators Limit query responses – Return classes and ranges instead of exact numbers Database Security 61  Summary Authentication: process of verifying user’s identity Authorization: process of verifying user’s permission to access a resource Credentials are used to authenticate and authorize – Can be required at different levels of an environment Operating system authentication requires user to have an account local to the server’s OS Database authentication checks user’s credentials against account residing in the database Database Security 62  Summary (cont’d.) Third-party applications can be used to verify a user’s identity – Use security protocols such as Kerberos and PKI Types of authentication vary between database vendors Server-enforced password policies are vital to data security Related user privileges can be combined to create roles – Allows for centralized management and security Database Security 63