OLIVIA FINAL FORM IMF V1

0.0(0)
Studied by 0 people
call kaiCall Kai
learnLearn
examPractice Test
spaced repetitionSpaced Repetition
heart puzzleMatch
flashcardsFlashcards
GameKnowt Play
Card Sorting

1/39

encourage image

There's no tags or description

Looks like no tags are added yet.

40 Terms

1
New cards

Data Control Language (DCL)

  • A category of SQL commands used to manage privileges that determine what database users can do.

  • Helps database administrators control access to database objects (such as tables) and reduce unauthorized activities.


2
New cards

Why DCL is important:

  • Protects confidential information

  • Limits unauthorized changes

  • Separates the responsibilities of users

  • Supports accountability and security management


3
New cards

GRANT

gives a user specified privileges on database objects.

4
New cards

REVOKE

removes specified privileges that were previously granted.

5
New cards

GRANT and REVOKE

Command

Purpose

When to use it

GRANT

Assigns privileges to a database user

When a user needs permission to perform an operation

REVOKE

Removes specified privileges previously assigned

When access is no longer needed or must be restricted


6
New cards

GRANT and REVOKE MySQL syntax

GRANT privilege_name
ON database_name.table_name
TO 'username'@'host';


REVOKE privilege_name
ON database_name.table_name
FROM 'username'@'host';

7
New cards

ON

identifies the database object where the privilege applies.

8
New cards

TO

identifies the account receiving a privilege (GRANT).

9
New cards

FROM

identifies the account whose privilege is removed (REVOKE).

10
New cards

Creating a Database User and Checking Privileges

  • A database account must exist before privileges are assigned.

  • In MySQL, CREATE USER creates the account.


CREATE USER 'app_user'@'localhost'
IDENTIFIED BY 'StrongExamplePassword!';

SHOW GRANTS FOR 'app_user'@'localhost';


  • app_user is the username.

  • localhost is the permitted connection host.

  • IDENTIFIED BY sets the account password.

  • SHOW GRANTS displays the privileges assigned to the specified account.

  • Creating an account does not mean it should automatically get full administrative access.


11
New cards

Authentication vs. Authorization

Concept

Main question

Meaning and examples

Authentication

Who are you?

Verifies a user’s identity before access is provided. Examples: username and password, fingerprint, security key, multifactor authentication

Authorization

What are you allowed to do?

Determines which resources and actions an authenticated user may access. Examples: a student views their grades; a teacher enters grades for assigned classes

Remember: Authentication verifies identity. Authorization determines permitted access and actions.

12
New cards

Database User Roles

Role

Typical access / responsibility

Example

Administrator (DBA)

Broad administrative privileges; manages database structure, accounts, access privileges, backups, and maintenance

Manages database users and backups

Application User

Has the required CRUD privileges on selected tables for an application

A school application views students and adds enrollment records without managing accounts

Read-Only / Reporting User

Retrieves information, typically through SELECT on permitted tables or views, but does not modify underlying records

Generates enrollment reports

Guest / Limited User

Restricted access to approved information

Visitors can check available courses but cannot view private student records


13
New cards

Principle of Least Privilege

Each user, application, or process should receive only the permissions necessary to perform its assigned tasks.

Benefits:

  • Reduces accidental data modification or deletion

  • Limits the impact of compromised accounts

  • Helps protect confidential information

  • Makes user activity and permissions easier to manage

  • Reduces unnecessary administrative access

Example: A reporting account that only needs to view student records should not be allowed to update or delete them.

14
New cards

Password Hashing vs. Plain-Text Passwords

  • Plain-text password: Stored in readable form. If an attacker obtains the database, the actual credentials may be exposed immediately.

  • Password hashing: Transforms a password into a derived value using a one-way cryptographic process. A properly designed hash is computationally expensive to reverse or guess.

Plain-text storage

Password hashing

The actual password is stored in readable form

A derived hash value is stored instead of the readable password

Good security practices:

  • Do not store passwords in plain text

  • Protect user credentials

  • Assign only the privileges users need

  • Avoid giving every account full administrative access


15
New cards

Stored Procedures: Meaning and Purpose

A stored procedure is a named group of SQL statements saved inside the database and executed by calling its name.

Why use them:

  • Reusability: write the logic once, use it again

  • Consistency: follow the same process each time

  • Organization: keep related SQL logic together

  • Parameters: accept input values and can provide output values

They help avoid repeating long SQL statements and centralize common database tasks.

16
New cards

Creating and Calling a Stored Procedure

DELIMITER //

CREATE PROCEDURE EnrollStudent(

IN p_StudentID VARCHAR(10),

IN p_CourseID VARCHAR(10)

)

BEGIN

INSERT INTO Enrollment

(StudentID, CourseID, EnrollDate)

VALUES

(p_StudentID, p_CourseID, CURDATE());

END //

DELIMITER ;

CALL EnrollStudent('S01', 'C101');


Key parts:

  • DELIMITER // temporarily changes the statement ending so the procedure can contain semicolons.

  • CREATE PROCEDURE gives the procedure its name and defines its parameters.

  • IN parameters receive values supplied when the procedure is called.

  • BEGIN ... END marks the block of SQL statements inside the procedure.

  • CALL executes the saved procedure.

The example enrolls a student using the supplied student ID and course ID. CALL runs it with the values 'S01' and 'C101'.

17
New cards

What Is a Trigger?

A trigger is a database program that runs automatically when a specified event happens on a table. Unlike a stored procedure, it does not need to be called manually.

Table event

When it occurs

INSERT

When a new row is added

UPDATE

When an existing row is changed

DELETE

When a row is removed


18
New cards

BEFORE and AFTER Triggers

Timing

Meaning

BEFORE

Executes before the table operation takes place

AFTER

Executes after the table operation takes place


19
New cards

OLD and NEW Values in Triggers

In the lesson’s UPDATE trigger example:

Reference

Meaning

Example in an UPDATE trigger

OLD

Previous or original row value

OLD.StudentName is the name before the update

NEW

Updated or new row value

NEW.StudentName is the name after the update


20
New cards

Audit Logging: Student Name Update Example

An audit log keeps a record of important changes. When a student’s name is updated, an AFTER UPDATE trigger automatically inserts the student ID, old name, new name, and change date into a StudentAudit table.

Sequence:

  1. Create the StudentAudit table to store the change details.

  2. Create an AFTER UPDATE trigger on the Students table.

  3. When a student’s name is changed, the trigger runs automatically.

  4. The trigger records OLD.StudentName and NEW.StudentName, plus the student ID and current date/time.

  5. View the audit record with SELECT * FROM StudentAudit;

Important SQL elements:

  • OLD.StudentID: the student ID from the row before the update

  • OLD.StudentName: the name before the update

  • NEW.StudentName: the updated name

  • NOW(): returns the current date and time

  • StudentAudit: the table that stores the audit information


21
New cards

When Should You Use a Trigger?

  • Student details updated: save old and new values in an audit log

  • Product stock changes: check or respond to inventory changes

  • Employee record deleted: keep a record of the deletion

  • New transaction added: automatically update related information

Use a trigger when an action must happen automatically in response to a database event.

22
New cards

Stored Procedures vs. Triggers

Feature

Stored Procedure

Trigger

Execution

Executed manually using CALL

Executed automatically

When it runs

Can run whenever needed

Runs when a table event occurs

Parameters / values

Can accept parameters

Uses OLD and NEW row values

Main use

Reusable database operations

Automatic actions and business rules

Lesson example

Enroll a student

Log a student update

Memory guide: PROCEDURE → CALL. TRIGGER → EVENT.

23
New cards

Benefits and Cautions

Benefits

Cautions

Automation

Too many triggers can be hard to manage

Consistency

Automatic actions may be less visible and harder to debug

Data integrity

Inefficient logic can slow operations

Reusability

Extra SQL may run unexpectedly

Audit tracking


Best practice: Use automation thoughtfully, document it, and test it.

24
New cards

QUICK REVIEW: Key Distinctions

  • GRANT vs. REVOKE: GRANT gives specified privileges; REVOKE removes them.

  • Authentication vs. Authorization: Authentication asks “Who are you?”; authorization asks “What are you allowed to do?”

  • Plain text vs. Hashing: Plain text is readable; hashing transforms a password into a derived value using a one-way cryptographic process.

  • Procedure vs. Trigger: A procedure is called when needed; a trigger runs automatically when a specified table event happens.

  • BEFORE vs. AFTER: BEFORE runs before the table operation; AFTER runs after it.

  • OLD vs. NEW: OLD is the previous row value; NEW is the new or updated row value.

  • INSERT / UPDATE / DELETE: INSERT adds a row; UPDATE changes an existing row; DELETE removes a row.


25
New cards

1. What is the main purpose of DCL?

To manage privileges that determine what database users can do. It lets administrators control access to database objects like tables and reduce unauthorized activities.

26
New cards

2. How are GRANT and REVOKE different?

GRANT gives specified privileges to a user. REVOKE removes privileges that were previously granted.

27
New cards

3. What does SHOW GRANTS display?

The privileges assigned to a specified account, e.g. SHOW GRANTS FOR 'app_user'@'localhost';

28
New cards

4. What is the difference between authentication and authorization?

Authentication verifies identity (“Who are you?”), such as a username and password, fingerprint, or MFA. Authorization determines what an authenticated user is allowed to access or do (“What are you allowed to do?”).

29
New cards

5. Which database role is appropriate for a user who only generates reports?

The Read-Only / Reporting User, who can retrieve data (SELECT on permitted tables or views) but cannot modify records.

30
New cards

6. What does the principle of least privilege require?

Each user, application, or process should receive only the permissions necessary to perform its assigned tasks, nothing more. For example, a reporting account should not be able to update or delete records.

31
New cards

7. Why is storing passwords in plain text risky?

The passwords are stored in readable form, so if an attacker gets the database, the actual credentials are exposed immediately.

32
New cards

8. What is password hashing?

Transforming a password into a derived value using a one-way cryptographic process. The hash is stored instead of the readable password, and a properly designed hash is computationally expensive to reverse or guess.

33
New cards

9. What is a stored procedure, and how is it executed?

A named group of SQL statements saved inside the database. It is executed manually by calling its name with CALL, e.g. CALL EnrollStudent('S01', 'C101');

34
New cards

10. What does DELIMITER do when defining a procedure in MySQL?

It temporarily changes the statement ending (e.g. to //) so the procedure body can contain semicolons without ending the whole definition early. It is then set back with DELIMITER ;

35
New cards

11. What is the purpose of an IN parameter?

It receives a value supplied by the caller when the procedure is run, such as the student ID and course ID in EnrollStudent.

36
New cards

12. How does a trigger differ from a stored procedure?

A stored procedure runs manually when you CALL it, can accept parameters, and is used for reusable operations. A trigger runs automatically when a table event occurs, uses OLD and NEW row values, and is used for automatic actions and business rules. (PROCEDURE → CALL, TRIGGER → EVENT.)

37
New cards

13. What are the INSERT, UPDATE, and DELETE events?

  • INSERT: a new row is added.

  • UPDATE: an existing row is changed.

  • DELETE: a row is removed.


38
New cards

14. How are BEFORE and AFTER triggers different?

A BEFORE trigger executes before the table operation takes place. An AFTER trigger executes after it takes place.

39
New cards

15. What do OLD and NEW represent in an UPDATE trigger?

OLD is the row value before the update (e.g. OLD.StudentName is the original name). NEW is the row value after the update (e.g. NEW.StudentName is the updated name).

40
New cards

16. How can an audit trigger help a school database?

It automatically records changes without anyone having to do it manually. For example, when a student’s name is updated, an AFTER UPDATE trigger saves the student ID, old name, new name, and date/time into a StudentAudit table. This gives the school a trackable history of changes, supporting accountability and data integrity.