authorization in dbms with sql as reference, Essays (university) of Database Management Systems (DBMS)

ppt for database management systems

Typology: Essays (university)

2017/2018

Uploaded on 08/27/2018

cksjfhv
cksjfhv ๐Ÿ‡ฎ๐Ÿ‡ณ

5

(1)

1 document

1 / 27

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
Built-in Data Types in SQL
โ€ขdate: Dates, containing a (4 digit) year, month and
date
โ€“Example: date โ€˜2005-07-27โ€™
โ€ขtime: Time of day, in hours, minutes and seconds.
โ€“Example: time โ€˜09:00:30โ€™ time
โ€˜09:00:30.75โ€™
โ€ขtimestamp: date plus time of day
โ€“Example: timestamp โ€˜2005-07-27 09:00:30.75โ€™
pf3
pf4
pf5
pf8
pf9
pfa
pfd
pfe
pff
pf12
pf13
pf14
pf15
pf16
pf17
pf18
pf19
pf1a
pf1b

Partial preview of the text

Download authorization in dbms with sql as reference and more Essays (university) Database Management Systems (DBMS) in PDF only on Docsity!

Built-in Data Types in SQL

  • (^) date: Dates, containing a (4 digit) year, month and

date

  • (^) Example: date โ€˜2005-07-27โ€™
  • (^) time: Time of day, in hours, minutes and seconds.
  • (^) Example: time โ€˜09:00:30โ€™ time
  • (^) timestamp : date plus time of day
    • (^) Example: timestamp โ€˜2005-07-27 09:00:30.75โ€™

Date and Time types in SQL

โ€ข current_date , current_time,

current_timestamp, localtimestamp

  • (^) SQL functions

Index Creation

  • (^) create table student ( ID varchar (5), name varchar (20) not null , dept_name varchar (20), tot_cred numeric (3,0) default 0, primary key ( ID ))
  • (^) create index studentID_index on student ( ID )

Index Creation

  • (^) Indices are data structures used to speed up access to records with specified values for index attributes - (^) e.g. **select *** from student where ID = โ€˜12345โ€™ can be executed by using the index to find the required record, without looking at all records of student

User-Defined Types

  • (^) Assigning Pounds type value to Dollars type variable is an error will be reported by the system.
  • (^) Type casting
    • (^) cast ( department.budget to numeric(12,2) )
  • (^) drop type and alter type clauses to drop or modify types

Domains

  • (^) create domain construct in SQL-92 creates user- defined domain types - (^) create domain person_name char (20) not null ;
  • (^) Types and domains are similar. Domains can have constraints, such as not null , specified on them.

Large-Object Types

  • (^) Large objects (photos, videos, CAD files, etc.) are stored as a large object : - (^) blob : binary large object -- object is a large collection of uninterpreted binary data (whose interpretation is left to an application outside of the database system) - (^) clob : character large object -- object is a large collection of character data - (^) When a query returns a large object, a pointer is returned rather than the large object itself.

Create table extensions

  • (^) create table temp_instructor like instructor ;
    • (^) temp_instructor has the same schema as instructor
  • (^) create table t1 as (select * from instructor where dept_name =โ€˜Musicโ€™) with data; By default, the names and data types of the columns are inferred from the query result.

Authorization

  • (^) Forms of authorization on parts of the database
    • (^) Select - allows reading, but not modification of data.
    • (^) Insert - allows insertion of new data, but not modification of existing data.
    • (^) Update - allows modification, but not deletion of data.
    • (^) Delete - allows deletion of data.

Grant Privileges

  • (^) A user who creates a new relation is given all privileges on that relation automatically.
  • (^) The grant statement is used to confer authorization grant on to
  • (^) is:
    • (^) a user-id
    • (^) public , which allows all valid users the privilege granted

Privileges in SQL

  • (^) select: allows read access to relation, or the ability to query using the view - Example: grant users^ U 1

, U

2 , and U 3 select authorization on the instructor relation: grant select on instructor to U 1

, U

2

, U

3

  • (^) insert : the ability to insert tuples
  • (^) update : the ability to update using the SQL update statement

Grant Privileges

  • (^) delete : the ability to delete tuples.
  • (^) all privileges : used as a short form for all the allowable privileges
  • (^) grant update ( budget ) on department to Amith ;
  • (^) SQL does not permit authorization on specific tuples of a relation.

Authorization Graph

Granting update authorization on Teaches

  • (^) A user has an authorization if and only if there is a path for the root down to the node representing the user

Revoking Authorization in SQL

  • (^) The revoke statement is used to revoke authorization. revoke on from
  • (^) Example: revoke select on branch from U 1

, U

2

, U

3

  • (^) <privilege-list> may be all to revoke all privileges the revokee may hold.