Retrieve complex information from databases - multiple related tables, Advanced SQL Queries, Combine rows from two or more tables based on a related column - separate tables to reduce redundancy - reconstruct relationships between tables, JOIN, - matching values in both tables, INNER JOIN, - all rows from the left table - matching rows from the right table - NULL if no match on right, LEFT JOIN, - all rows from the right table - matching rows from the left table, RIGHT JOIN, - all rows from both tables, matched where possible - NULL values where no match exists, FULL JOIN, Query inside another query — also called a nested query - inner query executes first - computes an intermediate result used by the outer query, Subquery, - used when subquery returns multiple values, IN, - checks if the subquery returns any rows, EXISTS, Calculations on multiple rows — return a single value - COUNT() — counts rows - SUM() — adds values - AVG() — calculates average - MAX() — largest value - MIN() — smallest value, Aggregate Functions, Groups rows with same values — used with aggregate functions, GROUP BY, - filters groups after grouping - WHERE filters rows; HAVING filters groups, HAVING, retrieve data from several related tables., Multi-table queries, Writing SQL that executes faster and uses fewer resources - use indexes on frequently searched columns - select only needed columns — avoid SELECT * - avoid unnecessary subqueries — prefer JOINs - use WHERE early to reduce rows processed, Query Optimization, Named structure stored inside the database - tables, views, indexes, sequences, functions, procedures, triggers, Database Object, Allow the database to perform logic, automation, and event-driven actions - functions, procedures, triggers, PL/pgSQL blocks, Procedural Extensions, Virtual table based on a SELECT query — stores the query definition, not the data - simplify complex queries - hide unnecessary columns - provide limited/secure data access - reflects base table — result changes when source data changes, View, Improves speed of data retrieval — avoids full table scan - useful for: WHERE, JOIN, ORDER BY, GROUP BY - tradeoff: extra storage; INSERT/UPDATE/DELETE slightly slower, Index, - prevents duplicate values in the indexed column, Unique Index, Named block of SQL and procedural code saved in the database — executed via CALL - insert, update, delete, validate, process multiple statements - centralizes logic — reduces repeated code, Stored Procedure, - Function — returns a value; used in SQL expressions - Procedure — does not have to return a value; called with CALL, Function vs Procedure, Automatically executes when INSERT / UPDATE / DELETE happens on a table - used for: audit logging, validation, enforcing business rules - two parts: Trigger Function (logic) + Trigger (when to run), Trigger, - runs before the operation — validate or modify values before saving, BEFORE Trigger, - runs after the operation — log activity or update related records, AFTER Trigger, - NEW — new row data (INSERT, UPDATE) - OLD — old row data (UPDATE, DELETE), NEW / OLD, Group of SQL statements treated as a single unit of work - begins with BEGIN, Transaction, Permanently saves all changes made in the current transaction, COMMIT, Cancels the entire transaction — undoes all changes, ROLLBACK, A checkpoint inside a transaction — marks a recovery point - syntax: SAVEPOINT sp_name, SAVEPOINT, Undoes only changes made after the savepoint — earlier changes stay - transaction can continue and be committed after rollback to savepoint, ROLLBACK TO SAVEPOINT, checkpoint, SAVEPOINT, undo part only, ROLLBACK TO SAVEPOINT, save remaining valid work, COMMIT, Atomicity Consistency Isolation Durability, ACID Properties, all operations succeed or none — no partial transactions, Atomicity, - database stays in a valid state before and after the transaction, Consistency, - concurrent transactions do not interfere with each other, Isolation, - committed changes are permanent — survive system failures, Durability, Concurrency Problems, Lost Update, Dirty Read,  Non-repeatable Read, - two transactions overwrite each other's changes, Lost Update, - reading uncommitted data from another transaction, Dirty Read, - data changes between two reads within the same transaction, Non-repeatable Read, Locking, LockingShared Lock (Read), Exclusive Lock (Write), Isolation Levels, Read Committed, Repeatable Read, Serializable, Protection of a database against unauthorized access, misuse, alteration, destruction, or disclosure, Database Security, Goals of Database Security, Confidentiality, Integrity, Availability, Accountability, - only authorized people should see the data, Confidentiality, - data must remain accurate and not be changed improperly, Integrity, - authorized users can access the database when needed, Availability, - system records who did what and when, Accountability, Regulates who can access the database and what actions they can perform - answers two questions: Who are you? + What are you allowed to do?, Access Control, Verifies the identity of a user — confirms who is logging in - username and password (most common) - multi-factor authentication (MFA) — password + one-time code - biometric — fingerprint or face recognition - certificate-based — digital certificates - OS / integrated — database trusts the OS login, Authentication, Determines what an authenticated user is allowed to do - read data, insert records, update, delete, create/drop tables, manage users, Authorization, Authentication vs Authorization, - Authentication = identity check ("Are you really Maria?") - Authorization = permission check ("What can Maria do?"), Common authentication methods, Username and password, Multi-factor authentication (MFA), Biometric authentication, Certificate-based authentication, OS or integrated authentication, A specific permission given to a user or role - SELECT — read data - INSERT — add new data - UPDATE — modify existing data - DELETE — remove data - CREATE / DROP / ALTER — manage database objects - EXECUTE — run procedures or functions, Privilege, A named collection of privileges — assigned to users based on job function - permissions are organized and easier to manage - users with similar jobs share the same permissions, Role, Privilege vs Role -, Privilege = single permission (e.g. SELECT on students) - Role = group of permissions (e.g. registrar_role = SELECT + INSERT + UPDATE), user accountis created so a person or application can access the database, user account, Assigns a privilege or role to a user or role - GRANT SELECT ON students TO instructor1; - GRANT instructor_role TO teacher_anna;, GRANT, Removes a previously granted privilege from a user or role - REVOKE DELETE ON students FROM instructor1;, REVOKE, Rules that define how the database must be protected and how users must behave, Security Policies, Common security policies, Strong password policy, Least privilege policy, Separation of duties, Account management policy, Audit and monitoring policy, - minimum length, uppercase/lowercase, numbers, special characters, periodic changes, Strong Password Policy, - give users only the minimum permissions needed to do their job - most important principle in database security, Least Privilege, Security also includes making sure data can be restored after failure, attack, or disaster., Backup and recovery policy, - critical tasks split among different people — reduces fraud and abuse, Separation of Duties, - disable or remove unused/inactive accounts immediately, Account Management, - log login attempts, data changes, privilege changes, table deletions, Audit and Monitoring, - public / internal / confidential / highly sensitive — stronger protection for sensitive data, Data Classification, - create a view that exposes only safe columns — grant access to the view, not the base table, Views for Restricted Access, - restricts access to specific rows — user sees only their own data, Row-Level Security, - GRANT UPDATE (col1, col2) ON table TO role; — restricts which columns can be modified, Column-Level Privileges, - records who logged in, what was updated/deleted, who changed privileges, Auditing, - data at rest — protects stored files, disks, backups - data in transit — SSL/TLS connection between app and database server, Encryption, Assign privileges to roles, then assign roles to users — not permissions one by one - easier to manage, consistent, easier to audit, follows least privilege - adding a new user = just assign the existing role, Role-Based Access Control (RBAC), - unauthorized data viewing or modification - accidental deletion of important data - privilege abuse by employees - data leakage and compliance violations, Risks of Poor Access Control.

DAD

oleh
Cetakan
Benamkan

Papan mata

Gaya visual

Pilihan

Tukar templat

)
Pulihkan autosimpan: ?