×
Start where you are. Use what you have. Do what you can.
--Your friends at LectureNotes
Close

Chapter 3 SQL notes

  • Other
  • 120 Views
  • Uploaded 5 months ago
0 User(s)
Download PDFOrder Printed Copy

Share it with your friends

Leave your Comments

Text from page-1

CREATE USER Syntax For each account, CREATE USER creates a new row in the mysql.user table CREATE USER user_specification [, user_specification] ... user_specification: user [ auth_option ] auth_option: { IDENTIFIED BY 'auth_string' | IDENTIFIED BY PASSWORD 'hash_string' | IDENTIFIED WITH auth_plugin | IDENTIFIED WITH auth_plugin AS 'hash_string' } Example 1: Create an account that uses the default authentication plugin and the given password. Mark the password expired so that the user must choose a new one at the first connection to the server: CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'new_password' PASSWORD EXPIRE; Example 2: Create an account that uses the sha256_password authentication plugin and the given password. Require that a new password be chosen every 180 days: CREATE USER 'jeffrey'@'localhost' IDENTIFIED WITH sha256_password BY 'new_password' PASSWORD EXPIRE INTERVAL 180 DAY; Database System Concepts, 5th Ed., June 2006 3.1 ©Silberschatz, Korth and Sudarshan

Text from page-2

Authorization Forms of authorization on parts of the database: Read - 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. Forms of authorization to modify the database schema (covered in Chapter 8): Index - allows creation and deletion of indices. Resources - allows creation of new relations. Alteration - allows addition or deletion of attributes in a relation. Drop - allows deletion of relations. Database System Concepts, 5th Ed., June 2006 3.2 ©Silberschatz, Korth and Sudarshan

Text from page-3

Authorization Specification in SQL The grant statement is used to confer authorization grant <privilege list> on <relation name or view name> to <user list> <user list> is: a user-id public, which allows all valid users the privilege granted A role (more on this in Chapter 8) Granting a privilege on a view does not imply granting any privileges on the underlying relations. The grantor of the privilege must already hold the privilege on the specified item (or be the database administrator). Database System Concepts, 5th Ed., June 2006 3.3 ©Silberschatz, Korth and Sudarshan

Text from page-4

Privileges in SQL select: allows read access to relation,or the ability to query using the view Example: grant users U1, U2, and U3 select authorization on the branch relation: grant select on branch to U1, U2, U3 insert: the ability to insert tuples update: the ability to update using the SQL update statement delete: the ability to delete tuples. all privileges: used as a short form for all the allowable privileges more in Chapter 8 Database System Concepts, 5th Ed., June 2006 3.4 ©Silberschatz, Korth and Sudarshan

Lecture Notes