Security, Transactions, and Views

1
Security, Transactions, and Views
2
Security

• Achieved through GRANT REVOKE
• Assumes the database can recognize its users and
  verify their identity
• can also be controlled through the use of views -
  subsets of data
• usually maintained by the database administrator
  or DBA

3
Views
A view is representation of an existing table
which corresponds to the SELECT statement that
created it. The view can then be manipulated much
like an actual table. A view is not a separate
table or entity. Its more like a mask of the
actual table.
4
Uses of a VIEW

• Hiding sensitive data from users
• Preserving a previous table schema
• Presenting data to users in a desired format.
• Simplify a complex query

5
Creating a VIEW

• CREATE VIEW view_name (col_name)ASSELECT
  _statement

6
Dropping a VIEW

• DROP VIEW view_name
• Only drops the view not the table.

7
More about Views

• The view displays like any table and the data you
  see is the actual data in the table(s).
• A view is more for viewing rather than updating
  since an update could disqualify a record from
  the view.
• Updates made to a view are made to the table(s)
  and any changes made to the table(s) are
  reflected in the view.

8
Naming View Columns

• Column names are inherited from the underlying
  tables.
• New names can be assigned
• Columns must be renamed when using arithmetic
  expressions or when more than one column has the
  same name.

9
Formatting

• It is sometimes necessary to alter the output of
  a query for the sake of readability such as in
  report generation. This can also be applied to a
  view creation which users will share.

10
SUBSTRING

• Returns a part of a character or binary string.
• SUBSTRING (expression, start, length)
• SUBSTRING (SQL Programming, 1, 3) SQL

11
CONVERT

• Changes one datatype to another.
• CONVERT(datatypelength, expression)
• CONVERT(char(2), SQL) SQ
• CONVERT(int, 10) 10

12
Using them together...
Select substring(title_id, 1, 2) as
alpha convert(int, substring(title_id, 3, 4)) as
numfrom titles
13
Transactions are...

• Transaction
• - Logical unit of work
• Transaction Management
• - ensuring that a set of SQL statements is
  treated as a unit - an indivisible entity

14
Transactions

• A transaction is a set of SQL statements that
  represent a unit of work or a procedural
  operation.
• A transaction is not complete unless all off its
  steps are followed through.
• This can be critical to maintaining data
  integrity such as when an account must be
  credited while debiting another.

15
Why transactions?

• Transactions are necessary for the purpose of
  concurrency control and recovery
• concurrency control - allowing multiple users
  simultaneous access
• recovery- allowing the database system to return
  the database to a reliable state after a failure.

16
Concurrency

• Lost-update problem
• Locking
• database system puts a lock on accessed data so
  it cannot be altered until lock is released.

17
Locking

• Since many users may be trying to access the same
  data simultaneously the DBMS has a locking
  mechanism which locks data which is in use.
• This provides a solution to concurrency problems
  which would arise if locking were not available.

18
2 Types of Locks

• Exclusive- for UPDATE, INSERT, and DELETE (write
  operations)- no other transaction can acquire
  lock until original is released
• Shared- applied during non-update or read
  operations - usually SELECT- prevents write
  operations from acquiring lock- allows other
  read operations to share lock

19
Recovery

• Allows a database to bounce back after a system
  failure
• must decide
• what transactions are incomplete
• which transactions completed but were not written
  and must be redone

20
User-defined Transactions

• Allows user to define any number of SQL
  statements as a transaction and instruct the
  database to process them as one unit.

21
Defining a Transaction

• A transaction starts with the keyword BEGIN
• BEGINSQL statementSQL statementSQL statement
• COMMIT

22
Finishing the Transaction

• If the transaction goes successfully then the
  COMMIT command will commit the changes to the
  database.
• However, if an error occurs the ROLLBACK command
  can be used to restore the database to its state
  prior to the transaction.