Database Security An Overview with some SQL Creative

Database Security An Overview with some SQL Creative Commons License – Curt Hill

Security • The protection afforded a system against unauthorized use • This includes a wide range of topics, several are covered in this presentation – One other presentation: encryption Creative Commons License – Curt Hill

The Data. Base Administrator • The security of a DBMS rests with the DBA • This person owns the account that has all the power • The DBA giveth and the DBA taketh away the permissions • When security fails, then the DBA is often fired – Or should be Creative Commons License – Curt Hill

What needs protection? • Confidentiality – Only authorized parties are allowed to view material – Includes printing and display – Sometimes just revealing the existence of a person/item is a breach of security • Integrity – The ability to modify should be rarer than the ability to view • Availability – The system can be made available to authorized parties Creative Commons License – Curt Hill

Why? • Competitive advantage – We need to protect our corporate data which may be of help to our competitors – Customers trust us with their data • Laws – The US has numerous laws concerning who may and who may not access confidential data Creative Commons License – Curt Hill

Defense • A Database is part of a much larger system • There are – Web servers – Applications – Many other pieces • A single failure may compromise all the other pieces Creative Commons License – Curt Hill

What Do We Do? • Authentication – Force users to login, have good passwords and change them occasionally • Restrict access – Permissions or privileges restrict what a particular user may do • Encryption – Prevents the database from being accessed outside of the DBMS • Logging – Helps to understand breaches Creative Commons License – Curt Hill

Control Measures • Security is established by implementing control measures • These include: – Access controls – Encryption – Flow control – Inference control Creative Commons License – Curt Hill

Types of Access Control • Mandatory – Determine groups of users at levels of security – Classify data • Discretionary – Grants or revokes certain privileges to users without any grouping • Role based – Privileges are based on roles Creative Commons License – Curt Hill

Mandatory • Usually there are several levels – Such as Top Secret, Confidential, Unclassified • Each person and object is deemed a certain level • Any person can use any object at their own level or below Creative Commons License – Curt Hill

Discretionary • Each user is granted permissions based on their individual position – May be there are no two users alike • Usually the DBA uses the SQL Grant, Deny and Revoke commands for this – How these work is seen later Creative Commons License – Curt Hill

Role Based • Similar to discretionary but users are classified according to a role • Every member of the that role has same privileges • Some users may have multiple roles • Roles are looked up at login time Creative Commons License – Curt Hill

Access Control • We can restrict what a user may see or do • This often focusses around the notion of roles and permissions – These are determined by DBA • Like much in the SQL domain, not well standardized Creative Commons License – Curt Hill

Example • A user logs in with ID and password – This creates a login session • At login the permissions that user has are considered for each action • Each action is recorded upon a log • The log may be examined in an audit at any later time Creative Commons License – Curt Hill

SQL Server • SQL Server uses roles • Each role expresses the relationship a login has to objects • Determines what the user may do • The notion of a role is to ease the management of permissions • These permissions may be given or taken away from individuals or to every user who has the same role Creative Commons License – Curt Hill

Role types • SQLServer has: – Predefined roles connected to a particular database – Fixed roles connected to entire server – User created roles which are connected to a particular database • A few of these are considered in the next screens Creative Commons License – Curt Hill

Predefined roles • db_owner: Members have full access • db_datareader: Members can read all data • db_datawriter: Members can add, delete, or modify data in the tables • db_securityadmin: Members can modify role membership and manage permissions • db_bckupoperator: Members can back up the database Creative Commons License – Curt Hill

Fixed roles • Sys. Admin: Any member can perform any action on the server • Server. Admin: Any member can set configuration options on the server • Security Admin: Any member can manage server security • Db. Creator: Any member can create, alter, drop, and restore databases. • Bulk. Admin: Any member can run the bulk insert Creative command Commons License – Curt Hill

Assigning Roles • Like many things in SQL Server things can be done by the Management Console or by SQL command – Many of the SQL commands in this area have a unique syntax for SQL Server • The SQL command is Create Role Creative Commons License – Curt Hill

Creating a role • Syntax is: Create Role role_name Authorization user • The role_name is the new role • User is the user which owns this role – This may be left out (including Authorization) then the current user owns this role • This type of role is then connected with database objects Creative Commons License – Curt Hill

Now what? • Once roles are created, we may give them permissions – The reserved word is Grant • With Grant we connect a permission with a user or role • Consider the syntax next Creative Commons License – Curt Hill

Simplified Grant Syntax GRANT permission [ ( column [ , . . . n ] ) ] [ , . . . n ] [ ON securable ] TO principal [ , . . . n ] [ WITH GRANT OPTION ] [ AS principal ] • Where – Permission is what they are able to do – Securable may be a table, database, stored procedure among others – Principal is a login or role Creative Commons License – Curt Hill

Permissions • The permissions depend on the object considered • For a table or view they include: DELETE, INSERT, REFERENCES, SELECT, UPDATE • For a stored procedure only EXECUTE • For a database there are many – One for most actions Creative Commons License – Curt Hill

An Example • Consider the following: Create Role Test. Role Grant SELECT ON Faculty TO Test. Role Grant Update ON Faculty TO Test. Role • Permissions not granted are unavailable • The table may need to be qualified by the database Creative Commons License – Curt Hill

Deny • Blocks a permission • Usually used to remove a single permission • Syntax is similar to Grant • Example: Deny Update ON Faculty TO Test. Role Creative Commons License – Curt Hill

Revoke • Removes the permission specified by a Grant or Deny • Similar syntax • Example Revoke Select on Faculty from Test. Role Creative Commons License – Curt Hill

Oracle • Not the number 1 database without cause • Has all the capabilities of the normal database • Implements: – Create Role – Grant – Deny – Revoke • Although not quite the same syntax Creative Commons License – Curt Hill

Guidelines • Restrict permissions to those who actually need them – Common mistake is for too many users to have excessive privileges • Web access is usually through a predefined login – Secure it to prevent issues Creative Commons License – Curt Hill

Privacy and Security • Security is needed for proper privacy • The individual should have the ability to control their personal or private information – What is stored – How it is used Creative Commons License – Curt Hill

Views • One of the main uses of views is to make available some information while restricting others to classes of users • As we have seen this is done by predefined query – This may restrict both rows and columns of the original data Creative Commons License – Curt Hill

Injection Attacks • A common problem is that user input will be used to construct a SQL command • An injection attack is using cleverly crafted bad input to subvert the process • Consider the next screen for an example Creative Commons License – Curt Hill

Normally • Suppose that we have a web form and it asks for an ID that is to come from the faculty table • Suppose we read that value into a Java. Script variable and then build a Java. Script string with it: stmt = ‘select * from faculty where naid = ‘ + input • Usually the user types in a number and stmt contains: select * from faculty where naid = 512 Creative Commons License – Curt Hill

Attack! • In the above you get zero or one rows depending on whether 512 may be found • Instead of typing in 512 a hacker types in: 1 or 1 = 1 • Now the statement becomes: select * from faculty where naid = 1 or 1 = 1 • The whole table is the result Creative Commons License – Curt Hill

Defense • The code in the web page cannot just blindly insert user input characters into a SQL statement • First the user input characters must be examined • Suspicious or unexpected characters need to be removed and the query rejected • Some systems have functions to automate this Creative Commons License – Curt Hill

Other Defenses • Public access, such as web access, usually uses a default login ID – Restrict with permissions what the ID can do • Protect stored procedures from being accessed by such an ID Creative Commons License – Curt Hill

Statistical Databases • The Census Bureau, among others, maintains several statistical databases – These are generally publicly available • The purpose is to provide for demographic research – Useful for governmental and market research • The inherent goal is that no personal information may be observed Creative Commons License – Curt Hill

Attacking • It is possible to make a query that shows us an individual’s personal data • Find the average salary of all the people who have a title of professor at VCSU who started in 1995 and a degree in CS – This is me and me only • The inherent confidentiality requirement has been violated Creative Commons License – Curt Hill

Inference Attack • The above is an inference attack – Attempt to get data on an individual from a statistical database • The usual approach to an inference attack is to restrict queries that end up with just a few individuals Creative Commons License – Curt Hill

Inference Attack Defense • Only allow queries with aggregate functions • Make queries return a threshold number’s worth of individuals before release • Do not allow sequence of queries operating on the same tuples – This restricts the size Creative Commons License – Curt Hill

Flow Control • If an application reads from a source table and then writes to a target table, we say that data flows from the source to the target • We wish to check that the source table is not at a higher security level than the target – This could expose secret data as confidential Creative Commons License – Curt Hill

Flow Policy • Determines how data may flow • Specifies the channels along which the data may flow • Flowing from lower security to higher is acceptable – Not the reverse Creative Commons License – Curt Hill

Challenges • In the information age keeping personal information private is a growing problem • There are multiple data warehouses where significant personal information is stored – Security has not always been well implemented there Creative Commons License – Curt Hill

Finally • There are numerous threats to database security • There are precautions that the DBA needs to implement to defend against these threats • We have another topic still needing consideration: – Encryption Creative Commons License – Curt Hill