Security and Integrity
Docsity.com
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Guidelines and tips
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Understanding DBMS Security: Database Access Control & Permissions, Slides of Database Management Systems (DBMS)
An overview of database security, focusing on dbms security, permissions, and privileges. It covers various aspects of database security, including legal issues, physical security, os/network security, security policies and protocols, encryption and passwords, and dbms security support. The document also explains how sql uses privileges to control access to tables and other database objects, and demonstrates how to grant and revoke privileges using sql statements.
Typology: Slides
2012/2013
1 / 26
This page cannot be seen from the preview
Don't miss anything!
Related documents
Partial preview of the text
Download Understanding DBMS Security: Database Access Control & Permissions and more Slides Database Management Systems (DBMS) in PDF only on Docsity!
Security and Integrity
Database Security
- Database security is
about controlling
access to
information
- Some information should be available freely
- Other information should only be available to certain people or groups - Many aspects to
consider for security
- Legal issues
- Physical security
- OS/Network security
- Security policies and protocols
- Encryption and passwords
- DBMS security
Permissions and Privilege
- SQL uses privileges
to control access to
tables and other
database objects
- SELECT privilege
- INSERT privilege
- UPDATE privilege
- DELETE privilege
- The owner (creator) of a database has all privileges on all objects in the database, and can grant these to others
- The owner (creator) of an object has all privileges on that object and can pass them on to others
Privileges in SQL
GRANT
ON
[WITH GRANT OPTION]
is a list of
SELECT
is a list of user names or PUBLIC - is the name of a table or view (later)
• WITH GRANT OPTION
means that the users can pass their privileges on to others
Removing Privileges
- If you want to
remove a privilege
you have granted
you use
REVOKE
ON
- If a user has the
same privilege from
other users then
they keep it
- All privileges
dependent on the
revoked one are also
revoked
Removing Privileges
- Example
- ‘Admin’ grants ALL privileges to ‘Manager’, and SELECT to ‘Finance’ with grant option
- ‘Manager’ grants ALL to Personnel
- ‘Finance’ grants SELECT to Personnel
Admin
Finance Manager
Personnel
SELECT
SELECT
ALL
ALL
Views
- Privileges work at
the level of tables
- You can restrict access by column
- You cannot restrict access by row
- Views, along with
privileges, allow for
customised access
- Views provide
‘derived’ tables
- A view is the result of a SELECT statement which is treated like a table
- You can SELECT from (and sometimes UPDATE etc) views just like tables
Creating Views
CREATE VIEW
AS
is the name
of the new view
- is a
query that returns
the rows and
columns of the view
- Example
- We want each user to be able to view the names and phone numbers (only) of those employees in their own department
View Example
CREATE VIEW OwnDept AS
SELECT Name, Phone FROM Employee
WHERE Department =
(SELECT Department FROM Employee
WHERE name = USER)
GRANT SELECT ON OwnDept TO PUBLIC
Using Views and Privileges
Conceptual View
External View 1
External View 2
User 1 User 2 User 3
DBA
- Views and privileges
are used together to
control access
- A view is made which contains the information needed
- Privileges are granted to that view, rather than the underlying tables
View Updating
- For a view to be updatable, the defining
query of the view should satisfy certain
conditions:
- Every element in SELECT is a column name
- Should not use DISTINCT
- View should be defined on a single table (no join, union, etc. used in FROM)
- WHERE should not have nested SELECTs
- Should not use GROUP BY or HAVING
Using Views and Privileges
To restrict someone's
access to a table
- Create a view of that table that shows only the information they need to see
- Grant them privileges on the view
- Revoke any privileges they have on the original table
ID Name Salary Department
Employee
- We want to let the user 'John' read the department and name, and be able to update the department (only)
Database Integrity
- Security vs Integrity
- Database security makes sure that the user is authorised to access information
- Database integrity makes sure that (authorised) users use that information correctly - Integrity constraints - Domain constraints apply to data types - Attribute constraints apply to columns - Relation constraints apply to rows in a single table - Database constraints apply between tables
Domains and Attributes
- Domains constraints are
data types
• SQL: CREATE DOMAIN
(not in Oracle)
CREATE DOMAIN Colour CHAR(15) CONSTRAINT checkCol CHECK (VALUE IN (‘RED’,‘Blue’…))
- Attributes are
constrained by their
domains
CREATE TABLE
Rainbow ( Rorder Int, Rcolour Colour)