copyright © 2013 curt hill database security an overview with some sql

Copyright © 2013 Curt Hill

Database Security

An Overviewwith some SQL

The DataBase 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 fired


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


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


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


Access Control

• We can restrict what a user may see or do

• This often focusses around the notion of roles and permissions

• Like much in the SQL domain, not very standard


SQL Server• SQL Server believes in 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 a permissions• These permissions may be given or

taken away from individuals or to every user who has the same role


Role types

• There are:– 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


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


Fixed roles

• SysAdmin: Any member can perform any action on the server

• ServerAdmin: Any member can set configuration options on the server

• Security Admin: Any member can manage server security

• DbCreator: Any member can create, alter, drop, and restore databases.

• BulkAdmin: Any member can run the bulk insert command


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


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


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


Simplified Grant SyntaxGRANT 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


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


An Example

• Consider the following:Create Role TestRole Grant SELECT ON Faculty TO TestRoleGrant Update ON Faculty TO TestRole

• Permissions not granted are unavailable

• The table may need to be qualified by the database


Deny

• Blocks a permission• Usually used to remove a single

permission• Syntax is similar to Grant• Example:Deny Update ON Faculty TO TestRole


Revoke

• Removes the permission specified by a Grant or Deny

• Similar syntax• ExampleRevoke Select on Faculty from TestRole


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


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


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


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 JavaScript variable and then build a JavaScript 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


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


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


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


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


Defense

• 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

• Make queries return a threshold number’s worth of individuals before release


copyright © 2013 curt hill database security an overview with some sql

Documents

curt hillcopyright

curt hillwhat

curt hillwhy

curt hillcreating

curt hillassigning roleslike

curt hillpredefined

curt hillrole typesthere

curt hillaccess controlwe