CS计算机代考程序代写 SQL database DB Fundamentals

DB Fundamentals

DB Security
You have the power!
1

DB Security
Security
Discretionary Access Control
Views & Stored Procedures

Backup and Recovery
Integrity
Encryption
Duplication/Raid

DB Security
Discretionary Access Control (DAC)
3

DB Security – Access Control
Access control defines what a user can and cannot do
Discretionary Access Control (DAC)
Involves GRANT and REVOKE of privileges to specific database functions
Can be tailored to individual users
Can be changes by specific users with sufficient privileges

Can be different for each attribute, table or even query, view or stored procedure in a database as well as connecting to a database

System Privilege Keywords
GRANT – give access to a database object (table, attribute, view etc.…)
DENY – outright prevent access to a database object
REVOKE – remove access to a database object

Access Control
Access Control describes what a user can do via the DBMS
What resource(s) the user has access to (tables, views, stored procedures)
Make sure each user only sees the data they should have access to

What action(s) the user is allowed with the resource
Types of privilege (INSERT, UPDATE, DELETE, DROP, CREATE, ALTER)
Make sure each user can only perform the actions they are permitted to perform

If the user can pass the privilege onto other users

As the database creator, you are the owner (dbo) of every resource and can assign privileges to other users
A predefined user represents the DBA and has complete access to all resources
“dbo” (Database Owner) for MS-SQL
“system” for Oracle
Users have specific privileges and can only operate on the data for which they are authorised to access
5

This type of authorisation is very granular (finely specified) in that it dictates exactly what functions a user can perform.
5

Access Control – Privileges
SQL offers several types of privilege
INSERT – INSERT INTO R VALUES (‘x’, ‘y’, ‘z’)
UPDATE – UPDATE R SET attr1 = ‘x’ WHERE pk = ‘x’
DELETE – DELETE FROM R WHERE attr1 = ‘x’
SELECT – SELECT * FROM R WHERE attr1 = ‘x’
REFERENCES – to build a referential integrity constraint with the resource (foreign key!) as well as ALTER a table
EXECUTE – for stored procedures: GRANT EXECUTE ON GetCharacters TO testUser
CREATE – GRANT CREATE TABLE TO testUser // DROP and ALTER as well 

These are the basic privileges, others may differ between DBMS
Some DBMS even allow authorisation rules to be applied to each specific attribute
6

Access Control – Obtaining Privileges
The creator of a relation is the database owner
The dbo is a user that has permissions to perform all activities in the database

The dbo can GRANT privileges (permissions) to any other users
GRANT ON Relations TO Users [WITH GRANT OPTION]
Privileges = INSERT, UPDATE, SELECT, DELETE etc..
Users = a list of user logins or roles (eg public) that contain users
7

When a relation is created, the creator becomes the database owner (dbo) of the relation and as the owner of the relation they have all of the DBMS privileges and can further grant those privileges to other users.

We grant privileges on a particular relation to one or more users and have the option to allow those users to grant those privileges to others.

Granting privileges to public will give every user the privilege to perform a specific function

The Grant option allows a privileged user to give the same privileges or less to other users.
7

Access Control – Obtaining Privileges
Two main methods
Granted to users specifically
Previous slide
Very specific to each user

Privileges granted to roles
A role is a named group of privileges
A user can then be placed into one or more different roles
Easier to maintain for large number of similar users

Access Control – User Account
This slide is hidden.
To perform the functions in any of the following slides you will need to follow the first few steps in next weeks practical to enable SQL Logins on your database and allow SQL Permissions.
Once you have done this, you can create an example SQL login using the following command:

USE Simpsons; — tell MangStudio which DB your are using
CREATE USER testUser WITH PASSWORD = ‘abcs!@#‘
OR
CREATE USER testUser WITHOUT LOGIN

Access Control – GRANT Privileges
Privileges can be GRANTED on a per user basis
To grant a privilege to a user
GRANT ON Resource to User1, …Usern [with grant option]

GRANT SELECT ON Actors TO testUser;

GRANT SELECT, INSERT, UPDATE, DELETE ON Actors TO testUser, mary;

GRANT ALL ON Actors TO testUser; — Deprecated in SQL Server
With grant option specifies whether the grantee can pass the privilege on to other users:

GRANT SELECT, INSERT ON Actors TO testUser WITH GRANT OPTION;
10

Access Control – GRANT Privileges
Privileges can be GRANTED on a per user basis
These privileges can be very specific – they can apply to specific attributes

GRANT SELECT ON Actors(actorName) TO testUser;

GRANT UPDATE ON Actors(actorName) TO testUser, mary;
GRANT DELETE ON Actors(actorName) TO testUser;

GRANT INSERT ON Actors(actorName) TO testUser;
This is not so for DELETE/INSERT
Sub-entity lists (such as column or security expressions)
cannot be specified for entity-level permissions.
11

The same does not apply for DELETE/INSERT as these are generally dealing with whole tuples as opposed to individual attributes.
11

Access Control – Grant Privileges
Privileges can be GRANTED to other users
These privileges can be the same or less

GRANT SELECT ON Actors TO testUser WITH GRANT OPTION;

–Run following queries as testUser:
EXECUTE AS USER = ‘testUser’

–testUser SQL Query:
GRANT SELECT ON Actors TO testUserNo2

–Or lesser priviledges:
GRANT SELECT ON Actors(actorName) TO testUserNo2
12

The same does not apply for DELETE/INSERT as these are generally dealing with whole tuples as opposed to individual attributes.
12

Access Control – Revoke Privileges
Privileges can also be REVOKED on a per user basis
To revoke a privilege (permission) from a user
REVOKE ON Resource to User1, …Usern [CASCADE]

REVOKE SELECT ON Actors TO testUser;

REVOKE SELECT, INSERT, UPDATE, DELETE ON Actors TO testUser, mary;

REVOKE ALL ON Object::Actors TO testUser; — Deprecated in SQL Server

This is the default behaviour/state of new users
A permission revoked from a user can still be inherited from other users (or roles) via the GRANT command
13

Access Control – Revoke Privileges
Where the grant option was specified, the removal of privileges must be cascaded to other affected users:

REVOKE SELECT, INSERT ON Actors TO testUser CASCADE;
testUser

User2
User3
User4
User5

testUser

User2
User3
User4
User5

Access Control – Revoke Privileges
Where the grant option was specified, the removal of privileges must be cascaded to other affected users:

GRANT SELECT, INSERT ON Actors TO User2;
REVOKE SELECT, INSERT ON Actors TO testUser CASCADE;
testUser

User2
User3
User4
User5

testUser

User2
User3
User4
User5

What if the same privilege was granted by another user to User2?
CASCADE will revoke privileges even if they are duplicated (granted) by other users

OtherUser

Note that this is the default behaviour in MS-SQL.
In other systems Cascade will revoke privileges granted by a user to another, unless the privilege was granted from another source. – this is not safe as it means we don’t know how many other users may have the privileges we revoked from the original parent user!!

Access Control – Revoke Privileges
Where the grant option was specified, the removal of privileges must be cascaded to other affected users:

GRANT SELECT, INSERT ON Actors TO User2;
REVOKE SELECT, INSERT ON Actors TO testUser RESTRICT;
testUser

User2
User3
User4
User5

What if the same privilege was granted by another user to User2?
RESTRICT will throw an error if the grantee has delegated the specified privileges to other users

OtherUser

DEFAULT BEHAVIOUR OF MS-SQL Server and does not need to be specified
> The keyword RESTRICT is not recognised

Using this method, you must then find each affected user and revoke the permissions one by one from the bottom up.

Restrict is different from Cascade will disallow the privilege from being revoked if it affects any other privileges
16

Access Control – Revoke Privileges
Privileges can be assigned to VIEWS and Stored Procedures

GRANT SELECT ON SimpsonsView TO testUser, mary; — for a view
Privileges can also be assigned to Stored Procedures

GRANT EXECUTE ON GetSimpsons TO testUser; — for a stored
procedure
17

Access Control – DENY Privileges
Access can be denied

DENY SELECT ON People(taxFileNo) TO userGroup1;

DENY DELETE ON Actors TO userGroup1;

DENY SELECT ON SimpsonsView TO userGroup1;

DENY EXECUTE ON GetSimpsons TO userGroup1;
Unlike revoke, DENY revokes a permission so that it cannot be inherited
DENY takes precedence over all permissions
Even if another user GRANTs the permission, it is still denied!
18

Access Control – DENY Privileges
Regaining a denied access
REVOKE the original access so that it may later be GRANTED by others
GRANT the access via dbo/owner or sa

REVERT
REVOKE SELECT ON Characters TO testUser2
OR
GRANT SELECT ON Characters TO testUser2
DENY prevents others from using GRANT to give permission to a user…
Note that MS-SQL allows the grant but when the user goes to execute a denied action it fails
19

SECURITY
Setting minimum permissions
20

Developing an application using a least-privileged user account (LUA) approach is an important part of a defensive, in-depth strategy for countering security threats. The LUA approach ensures that users follow the principle of least privilege and always log on with limited user accounts. Administrative tasks are broken out using fixed server roles, and the use of the sysadmin fixed server role is severely restricted.
20

Least-Privileged User Account
LUA – Least privileged User Account
Important strategy to ensuring users only have access to the data and objects in the database that they should have access too

New users have all privileges to all objects REVOKED until otherwise GRANTED

An existing user (either one with GRANT OPTION) or the owner/sa must GRANT the necessary permissions to a new user

Example – Determine Min Privileges
EXECUTE AS User=’testUser’;
SELECT CharacterName, DateAired, EpisodeName
FROM Characters AS C JOIN Episodes AS E
ON C.EpisodeID = E.EpisodeID
Query to Execute
Min Permissions Required
22

Example – Determine Min Privileges
INSERT INTO CharacterVoices (CharacterID, ActorID)
SELECT C.CharacterID, A.ActorID FROM Characters C
JOIN CharactersImport CI ON
C.CharacterName = CI.Character
JOIN Actors a ON CI.ActorName = CI.[Voice actor(s)]
CharacterVoices: INSERT // no attribute list
Characters: SELECT(CharacterID, CharacterName)
CharactersImport: SELECT(Character, [Voice actor(s)])
Actors: SELECT(ActorName, ActorID)
Query to Execute
Min Permissions Required
25

Example – Determine Min Privileges
UPDATE Characters SET EpisodeID = E2.EpisodeID
FROM Characters C2 JOIN CharactersImport CI ON
C2.CharacterName = CI.Character
JOIN Episodes E2 ON E2.EpisodeName = CI.Episode
AND E2.DateAired = CI.originalAirDate;
Characters: UPDATE(EpisodeID)
Characters: SELECT(CharacterName)
Episodes: SELECT(EpisodeName, EpisodeID)
CharactersImport: SELECT(originalAirDate, Character, Episode)
Query to Execute
Min Permissions Required
27

Example – Determine Min Privileges
DELETE FROM Customers WHERE CustomerID > 149
Query to Execute
Min Permissions Required
28

Example – Determine Min Privileges
DELETE FROM Customers WHERE CustomerID > 149
Customers: DELETE
Customers: SELECT (CustomerID)
Query to Execute
Min Permissions Required
29

Security
VIEWS

DB Security – VIEWS
Each user can be given the privilege to access the database only through a small set of views that contain the data appropriate for that user
Views can restrict what a user knows to exist in a database
If it’s not displayed then the user doesn’t know the data exists

Views can redefine the attributes a user sees
Every attribute can be renamed
The user does not know the actual attribute names
31

DB Security – VIEWS
CREATE VIEW SimpsonsCharacters (ID, Name, [Role], Episode, AiredDate)
AS

SELECT
CharacterID, CharacterName, CharacterRole,
EpisodeName, DateAired
FROM Characters AS C
JOIN Episodes AS E
ON C.EpisodeID = E.EpisodeID
What the user sees as the tables:
SimpsonsCharacters(ID, Name, Role, Episode, DateAired)
Do not know that there are two tables (Characters and Episodes)
Do not know any of the real attribute names
32

Security
Stored Procedures

DB Security – Stored Procedures
Stored Procedures can be used to restrict the types of queries a user can execute
Like views, they can be used to mask the names of underlying attributes
The can also specify the exact queries a user can utilise
34

DB Security – Stored Procedures
CREATE Procedure GetSimpsonsCharacters
@name varchar(100)
AS

IF (@name IS NOT NULL)
BEGIN
SELECT TOP 5
CharacterID AS ID,
CharacterName AS Name,
CharacterRole AS [Role],
EpisodeName AS Episode,
DateAired AS DateAired
FROM Characters AS C
JOIN Episodes AS E
ON C.EpisodeID = E.EpisodeID
RETURN — return the results
END

ELSE
BEGIN
RETURN– give them nothing!!
END

Using a stored procedure you can specify:
The user must have a search term
How many records the user gets back
The name of the attributes
The exact search a user can perform
35

DB Security – Stored Procedures
CREATE Procedure InsertCharacter3
@name varchar(100),
@role varchar(100),
@episode varchar(100),
@airedDate date,
@ID int OUTPUT
AS

— check if character does not already exist
SET @ID = (SELECT characterID
FROM Characters
WHERE CharacterName = @name
)

— check if epsiode does not already exist
DECLARE @episodeID int = (
SELECT EpisodeID FROM Episodes
WHERE EpisodeName = @episode
)
IF(@episodeID IS NULL)
BEGIN
INSERT INTO Episodes (EpisodeName, DateAired)
VALUES (@episode, @airedDate)
END

IF (@ID IS NULL)
BEGIN
INSERT INTO Characters (
CharacterName,
CharacterRole,
EpisodeID
) VALUES (
@name,
@role,
@episodeID
);
SET @ID = SCOPE_IDENTITY();
END
RETURN
36

DB Security – Stored Procedures
Users can be granted access to execute a Stored Procedure (SP) but nothing else
Even if the SP contains INSERT, UPDATE, DELETE, SELECT queries on various tables the EXECUTE privilege can be granted to the SP only
This gives a user permission to use the SP regardless of the tables affected

If the user tries to write a query (even if it exists in the SP) and execute it, the query will be denied
The privilege to execute an SP does not give a user the privileges to access to any base tables.
37

Table: //

/docProps/thumbnail.jpeg

Related Posts