Security, Transactions, and Views

1
Security, Transactions, and Views
2
About Security

• As is the case in most shared environments, the
  DBMS also must implement a security mechanism
  that allows the setting of permissions to data
  and actions pertaining to that data.
• This is required to ensure data security.

3
GRANT Command

• This grants a user or group of users permission
  to manipulate specified data in specified ways.
• GRANT ALL privilege_listON table_name
  view_name (col_list)TO PUBLIC user_list

4
REVOKE Command

• This revokes a granted permission issued by
  GRANT from a specified user.
• REVOKE ALL privilege_listON table_name
  view_name (col_list)FROM PUBLIC
  user_list
• Tip1) You GRANT TO and REVOKE FROM.2) The most
  recently issued statement supercedes all others.

5
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.
6
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

7
Creating a VIEW

• CREATE VIEW view_name (col_name)ASSELECT
  _statement

8
Dropping a VIEW

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

9
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.

10
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.

11
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.

12
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.

13
Defining a Transaction

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

14
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.