Introduction to Databases for Business Analytics
Week 10 Data Control Language (DCL)
Term 2 2022
Lecturer-in-Charge: Kam-Fung (Henry) : Tutors:
Copyright By PowCoder代写 加微信 powcoder
PASS Leader:
• There are some file-sharing websites that specialise in buying and selling academic work to and from university students.
• If you upload your original work to these websites, and if another student downloads
and presents it as their own either wholly or partially, you might be found guilty of collusion — even years after graduation.
• These file-sharing websites may also accept purchase of course materials, such as copies of lecture slides and tutorial handouts. By law, the copyright on course materials, developed by UNSW staff in the course of their employment, belongs to UNSW. It constitutes copyright infringement, if not academic misconduct, to trade these materials.
Acknowledgement of Country
UNSW Business School acknowledges the Bidjigal (Kensington campus) and Gadigal (City campus) the traditional custodians of the lands where each campus is located.
We acknowledge all Aboriginal and Islander Elders, past and present and their communities who have shared and practiced their teachings over thousands of years including business practices.
We recognise Aboriginal and Islander people’s ongoing leadership and contributions, including to business, education and industry.
UNSW Business School. (2022, May 7). Acknowledgement of Country [online video]. Retrieved from https://vimeo.com/369229957/d995d8087f
W10 Learning Outcomes
❑ Data Security and Access Control
❑ Key Components in Access Control ❑ Commands of Data Control Language
Data Security
❑ Data is a valuable resource that must be strictly controlled and managed, as with any corporate resource.
❑ Part or all of the corporate data may have strategic importance and therefore needs to be kept secure and confidential.
❑ Mechanisms that protect the database against intentional or accidental threats.
❑ Security considerations do not only apply to the data held in a database. Breaches of security may affect other parts of the system, which may in turn affect the database.
Access Control
❑ Access control is a fundamental component of data security.
❑ Access control is a process that is integrated into an organization’s IT environment.
Data Access Control Access control is
• a combination of security technology and mechanism in place to protect confidential information
• to minimize the risk of access
• instantiated by authorization
• implemented with Data Control Language (DCL) in SQL
If o is an object, authorization answers the question “Who is trusted to access o?”
Authorization in Database CIA Triad for Information Security
• Confidentiality means that data, objects and other resources are protected from unauthorized viewing and other access.
• Integrity means that data is protected from unauthorized changes to ensure that it is reliable and correct.
• Availability means that authorized users have access to the systems and the resources when they need them (denial-of-service is a threat to data availability).
Key Components in Access Control
Key Components in Access Control ❑ Authentication: is the initial process of establishing the
identity of a user
❑ Authorization: specifies access rights and privileges to resources to determine whether the user should be granted access to data or make a specific transaction
Authorization Components
❑ Authorization defines who can access which object in a database.
Who – Types of users
▸ a single user or a group of users Privilege – Types of accesses:
▸ select, insert, update, delete
Types of database objects
▸ tables, attributes, views
Database Object – View
A view does not store any data. A view only behaves like a table. And it is just a named query stored in the database.
create view mybest_view as
select customer_address, customer_city from s_customer_t;
Result of mybest_view
❑create view mybest_view as
select customer_address, customer_city from s_customer_t;
❑select * from mybest_view;
❑ View: is the dynamic result of one or more relational operations operating on the base relations to produce another relation.
❑ A view is a virtual relation that does not actually exist in the database, but is produced upon request by a particular user, at the time of request.
❑ A user can have access to a view, but not to the whole table.
❑ A view selects a subset of rows & attributes. View allows a database user to have access to some but not all
rows or attributes.
Commands of Data Control Language (DCL)
DCL Commands
❑ Grant: is used to give user access privileges to a database
❑ Revoke: is used to revoke authorization (to take back permissions from the user)
❑ Deny: explicitly prevents a user from receiving a particular permission.
DCL Commands
◼ The grant statement is used to give user access privileges to a database grant
to
[WITH GRANT OPTION]
e.g., grant select on emp_view to liud22;
◼
◼ public,whichallowsallvaliduserstheprivilegegranted
◼ The grantor of the privilege must already hold the privilege on the specified item (i.e., database administrator).
Common Privileges in DCL
◼ Select: allows read access to a relation, or the ability to query using the view
◼ Insert: allows insert access to a relation
◼ Update: we can use update(column-name) to specify update on a column
◼ Delete: allows delete data from a relation
◼ Execute: only for procedure or functions (this is for PL/SQL –
Procedural Language for SQL)
◼ All: used as a short form for all the allowable privileges
DCL Commands – Privilege to
With grant option: allows a user who is granted a privilege to pass the privilege on to other users.
▸ DBA: grant select on emp to U1 with grant option
grant select on emp
DCL Commands – Revoking Database
◼ Therevokestatementisusedtorevokeauthorization(totakebackpermissions from the user)
revoke
◼ Example: revoke select from U1, U2 and U3 on emp
revoke select
from U1, U2, U3;
◼ Supposeweallowadatabaseuser“liud22”toseeallthecolumnsinEMPtableexcept the salary column.
◼ Solution
create view emp_view as
select Emp_ID, Emp_Name from EMP;
grant select on emp_view to liud22;
Suppose we allow the user “liud22” to see all columns in s_customer_t table except the customer_address column.
s_customer_t
create view customer_view as
select customer_id, customer_name, customer_city, customer_state, postal_code from s_customer_t;
grant select on customer_view to liud22;
customer_id
customer_name
customer_address
customer_city
customer_state
postal_code
Source: metlifepetinsurance.com
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com