Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

SCC 201 Database > Week 10 > Flashcards

Week 10 Flashcards

1
Q

Schemas, views, access controls

A
  • All are ways of partitioning, segregating and controlling access to data within a database
  • Why do we want to do this?
    • Security – Prevent unauthorised access to read and (heaven forbid) modify data
    • Usability – Presenting a simplified view of underlying data structures can make working with data easier, particularly for non –expert users
    • Flexibility – partitioning according to users can allow experimentation without affecting other users
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Database schemas
What’s a schema?

A
  • A SQL database contains multiple objects such as tables, views, stored procedures, indexes, triggers etc
  • A schema is a logical collection of database objects
    • Loosely analogous to a namespace in languages such as Java, C#
  • Note: this material focusses on ‘full’ SQL – some features may not be implemented in certain DBMS that implement a subset of full
    SQL (such as SQLite)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

How can we organise schemas?

A
  • Logical grouping by object function
    • Sales schema contains objects pertinent to sales dept
    • Personnel schema contains staff objects
  • Logical grouping by user
    • Individual users/groups may be given their own space in a database
    • E.g. each developer in a team may have their own ‘sandpit’ in a development database where they can make changes to their own set of objects without affecting other developers|
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Schema partition by object domain

A
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Schema partition by user – developers
working on ‘Sales’

A
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Conceptual schema definition

A
  • To create a schema:
    CREATE SCHEMA [schema_name]
    Authorisation [owner_name]
  • In our user based example:
    CREATE SCHEMA Carol
    Authorisation Carol
  • [owner_name] is (initially) only user able to create and access objects within the schema
  • A user can be specified as belonging to a default schema
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Conceptual schema definition 2

A
  • To create tables for a schema explicitly
    CREATE TABLE [schema_name].[table_name]
    [table definition…]
  • To create tables for a schema implicitly
    CREATE TABLE [table_name]
    [table definition …]
    * Default/current schema for defining user is used
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Schema operations

A
  • Can perform all our usual operations within a schema
    • CREATE, DROP, ALTER
    • DROP VIEW
    • DROP TABLE
    • ALTER TABLE
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Cross schema considerations

A
  • Objects can have the same name in different schemas
    • Sue.Customer, John.Customer
  • To access an object outside of default/assigned schema specify the schema name
    • For Sue:
      • SELECT * FROM Customer, SELECT * FROM Sue.Customer are analogous
      • SELECT * FROM John.Customer accesses the [John] schema
        • Subject to access permissions
    • Similar with Joins etc
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

Schemas or databases

A
  • A schema looks like a distinct database
    • Many of the same operations can be
      performed within both
      • E.g CREATE objects, assign permissions etc
    • So why not have a separate DB instead of multiple schemas?
  • Database is the main physical container contains data and log files and all the schemas within it.
    • All backup and restore operations are performed at database level
  • Schema is a logical container
    • Enables ease of permissions setting
  • Decision whether to partition by schema or database (e.g SalesDB, PersonnelDB) needs to be taken based on specific organisational considerations
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Views

A
  • A view can be thought of as a ‘virtual table’
    • Does not necessarily exist in physical form
    • As opposed to base tables whose tuples are actually stored in database files
  • Views are used to ‘focus, simplify and customise the perception a user or group has of a database. Views can be used as security mechanisms by letting users access data through the view, without granting permissions to directly access the underlying base tables of the view
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Creating a view

A
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Building a view from base entities

A
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Views 2

A
  • If the same query is frequently executed on a database it makes sense to define a view based on results of the query and use a simpler query to retrieve tuples of interest from the view
    • Particularly useful if the original query is complex – involving a number of joins
    • Reducing need for querying users to specify join conditions reduces scope for error
  • User can simply perform following query on OrderDetails
    • SELECT * FROM OrderDetails WHERE CustNo = 12345
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

Views 3

A
  • Views can be queried using same SELECT operations as base tables
  • Join operations can be performed between views and tables
    • Should we really do this?
  • Views can improve DB security
    • Provides a projection of specified base data items
      • Can be a subset of a specific entity
        • E.g. a StaffMember view may omit social security and other sensitive fields from underlying entity
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

Access control : security in SQL
Mandatory access control

A
  • Level based access
  • Each database object is assigned a classification level
  • Levels form a strict ordering
  • E.g. top secret > secret > confidential > public > not classified
  • Each subject (users or programs) is given a clearance level
  • To access an object, a subject requires the necessary clearance to read or write…
  • See the Bell-LaPadula access control model (1974)
  • Very few (no?) DBMS use this approach
    • Why?
      • Seniority vs. appropriateness of access
17
Q

Discretionary access control

A
  • Each user is given appropriate access rights (or privileges) on specific database objects
  • Users obtain certain privileges when they create an object and can pass some or all of these privileges on to other users at their
    discretion
  • This is the approach used in SQL
18
Q

Authorisation identifier

A
  • A SQL identifier used to establish the identity of a user
  • The DBA sets up your username and usually a password
    • Other mechanisms exist for some DBMS, e.g Microsoft SQL Server trusted connections, which use underlying windows domain credentials
  • Every SQL statement executed by the DBMS is performed on behalf of a specific user
19
Q

Authorisation identifier
Access rights associated with a user determine:

A
  • What database objects a user can reference and
  • What operations can be performed by the user on which objects
20
Q

Ownership

A
  • Each object created in SQL has an owner
  • The owner is identified by the auth identifier defined in the authorization clause of the schema to which the object belongs
  • Owner is initially the only person who knows the object exists and subsequently performs operations on that object
21
Q

Privileges

A
  • ISO standard defines the following privileges among others
  • SELECT – To retrieve data from a table/view
  • INSERT – To insert new rows into a table, can be restricted to specific columns
  • UPDATE – To modify rows of data in a table, can be restricted to specific columns
  • DELETE – To delete rows of data from a table
  • REFERENCES – To reference columns of a table named in integrity constraints, can be restricted to specific columns
22
Q

Create table

A
  • When you create a table you are the owner and have full privileges
  • Other users have no access and must be granted privileges by the owner
  • When you create a view you are the owner of the view, but you may not have full privileges
  • You must have select privileges on the base table(s) to create the view in the first place
23
Q

The grant command

A
24
Q

Grant example

A

GRANT ALL PRIVILEGES
ON Staff
TO manager
WITH GRANT OPTION

  • The user manager can now retrieve rows from the Staff table and also insert, update and delete
  • The manager can pass these privileges on to other users
25
Q

Revoking privileges from users

A
  • The REVOKE statement can remove all or some of the privileges previously granted
  • REVOKE [privilege_list | ALL PRIVILEGES ]
  • ON object_name
  • FROM [Authorization_list | PUBLIC]
26
Q

Revoke example

A

REVOKE ALL PRIVILEGES
ON Staff
FROM manager

  • Remove all the privileges you gave to the manager on the Staff table
27
Q

Role based security

A
  • In most DBMS, typical approach is not to give permissions to individual users
  • Instead define groups or roles based on the activities certain types of user enact
    • E.g. Accounts, Sales, Managers, Auditors
  • Allocated user to one or more groups as appropriate
  • Grant object priviliges to each group as appropriate
  • DBA is a special role – has full privileges over entire database
28
Q

Security consideration #1

A
  • Don’t over grant permissions
  • Best practice as with all aspects of software development and system administration:
    • Start with a totally locked down database and grant access as necessary
    • Closing down an open system invariably leaves holes and security risks!

SCC 201 Database (10 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions