程序代写代做 C finance go concurrency database clock graph 3/3/2019

程序代写 CS代考 / concurrency, C语言代写, database, finance

3/3/2019
Security and integrity
CMT207 Information modelling & database systems
1
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
 view data
 modify data
 modify the database structure
4
Lecture
 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
2
Privileges
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
3
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
6
1

3/3/2019
Privileges in SQL
 SELECT  INSERT  DELETE
 UPDATE  orALL
GRANT ON  TO
[WITH GRANT OPTION]
What?
Who?
7
table or view
 list of users
 or PUBLIC
 allows the specified users to pass their privileges to other users
Example
1. Admin grants ALL to Manager WITH GRANT OPTION
2. Admin grants SELECT to Finance WITH GRANT OPTION
3. Manager grants ALL to Personnel
4. Finance grants SELECT to Personnel
Admin
ALL
Manager
ALL
SELECT
Finance
SELECT
Personnel
10
Examples
GRANT ALL
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
8
Example
1.
2.
Manager revokes ALL from Personnel
 Personnel still has SELECT privilege from Finance
Admin revokes SELECT from Finance
 Personnel loses SELECT privilege
Admin
ALL
Manager
ALL
SELECT
Finance
SELECT
Personnel
11
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
9
Views
2

3/3/2019
Views
 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
13
Views and privileges
 views and privileges are combined to control access
 create a view that contains the information needed
 grant privileges to that view, rather than the underlying tables
 views are virtual tables
 their content depends on the underlying tables  we can select from views just like a table
 … but what about update, insert, and delete?
16
Creating views
the name of the newly created view
CREATE VIEW
AS