Transcript week10

Security, Transactions, and
Views
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.
GRANT Command
This “grants” a user or group of users
permission to manipulate specified data in
specified ways.
GRANT {ALL | privilege_list}
ON {table_name | view_name [(col_list)]}
TO {PUBLIC | user_list}
REVOKE Command
This “revokes” a granted permission issued by
GRANT from a specified user.
REVOKE {ALL | privilege_list}
ON {table_name | view_name [(col_list)]}
FROM {PUBLIC | user_list}
Tip:
1) You GRANT TO and REVOKE FROM.
2) The most recently issued statement
supercedes all others.
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. It’s
more like a mask of the actual table.
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
Creating a VIEW
CREATE VIEW view_name [(col_name…)]
AS
SELECT _statement
Dropping a VIEW
DROP VIEW view_name
Only drops the view… not the table.
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.
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.
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.
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.
Defining a Transaction
• A transaction starts with the keyword
BEGIN
BEGIN
SQL statement
SQL statement
SQL statement
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.