程序代写 www.cardiff.ac.uk/medic/irg-clinicalepidemiology

程序代写 CS代考 / database, finance, SQL代写代考

www.cardiff.ac.uk/medic/irg-clinicalepidemiology

Security and integrity

Copyright By PowCoder代写 加微信 powcoder

Information modelling
& database systems

in this lecture we will consider briefly a range of issues concerning database applications
database security
aspects of security
access to databases
privileges and views
database integrity
view updating
integrity constraints

Database security
database security is about controlling access to information
some information should be available freely
other information should only be available to certain users
many different aspects of security:
legal issues
physical security
OS/network security
security policies and protocols
encryption and passwords
DBMS security

DBMS security support
DBMS can provide some security
each user has an account name and password
these are used to identify a user and control their access to information
DBMS verifies user’s password and checks their permissions whenever they try to
modify data
modify the database structure

Privileges

Privileges
SQL uses different 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 privileges to other users
the owner (creator) of an object has all privileges on that object and can pass them on to others

Privileges in SQL
GRANT ON
TO
[WITH GRANT OPTION]
SELECT INSERT UPDATE

table or view
list of users
allows the specified users to pass their privileges to other users

ON Employee
TO Manager
WITH GRANT OPTION
the user Manager can do anything to the Employee table, and can allow other users to do the same (by using GRANT statements)
GRANT SELECT, UPDATE(Salary)
ON Employee
TO Finance
the user Finance can view the entire Employee table, and can change values in its Salary column, but cannot change any other values or pass on their privilege to other users

Removing privileges
a previously granted privilege can be revoked using the following statement:
REVOKE ON
FROM
if a user was granted the same
privilege from some other user,
then they will still keep it
all other privileges that depend on the revoked one will be revoked automatically

Admin grants ALL to Manager WITH GRANT OPTION
Admin grants SELECT to Finance WITH GRANT OPTION
Manager grants ALL to Personnel
Finance grants SELECT to Personnel

Manager revokes ALL from Personnel
Personnel still has SELECT privilege from Finance
Admin revokes SELECT from Finance
Personnel loses SELECT privilege

privileges work at the level of tables
access can be restricted by columns
access cannot be restricted by row!
views, together with privileges, allow for customised access control
a view is a table that is derived as the result of a SELECT statement
SELECT statement can then be used with views in the same way tables can
UPDATE statement can sometimes be used with views

Creating views
CREATE VIEW
AS