SQL Advanced: Database Administration and Management

Name: SQL Advanced: Database Administration and Management
Author: Eric Grose

Understand how to securely manage accounts and back up data

Preview the first lesson free — get full access to all 4 lessons.

Course: On-Demand

These lessons we'll learn about database administration and management in this lesson will review data integrity and security to master data validation, encryption, access controls and other techniques to protect sensitive information. Data validation involves ensuring the correctness and consistency of crucial data. One effective technique is using constraints to define rules that the data must follow to ensure that we don't accidentally add duplicates of the same products. We could add any constraint on this table. So to perform this task, we'll need an alter table statement on the products table we'll want to use to add constraint keywords. Give our constraint a name list. The type of constraint which in this case is a unique constraint. And then finally, list the column in which this is applied, which is the product name column. Let's go ahead and add this constraint by pressing the play button. And if we go to our products table, right click, click on the properties all the way at the bottom, switch over to the constraint tab. We can see our primary key constraint as well as any other constraints particularly are unique constraint that we just created here. Next, let's talk about encryption, which protects sensitive information from unauthorized users. Let's say we want to add a column to our customers table to store encrypted credit card information. Once again, we'll need an ultra table statement which will be applied to our customers table. This time we'll use the add column keywords and we'll create a column name encrypted credit card text, and we'll add a column name, encrypted credit card, which will be a text data type. Let's go ahead and add this column by pressing the play button. And now let's use one of the available functions to us within PostgreSQL to add encryption. So here we'll do an update statement on the customers table where we'll set or define our encrypted credit card column. And here's where we'll use a Postgres function, which is symmetric encryption. This will create a symmetric key that used an outside password to gain access to even put in a password. So our first parameter would be the credit card column. Now, we don't actually have this in the table, but in a real world scenario, we would have the physical value and then we'd have an encryption key in order to gain access to even see that value. This is very similar to what many cryptocurrency exchanges use for security. Now, their security protocol is to create controls on who can access, modify or even query certain data. So for this task, we'll create a role, give it the name, manager, and we'll grant select access or the ability to query. We use the on keyword to list the table. In this case, the products table then will say to what role has access to this, which is our newly created manager role. Then we'll go ahead and grant the manager and here will grant it to our administrator in order to set this product up. And here will grant access to the administrator to go ahead and execute this command. And I've just listed mine as PostgreSQL. Let's go ahead and create this role in access by pressing the play button. If we scroll down here to our groups, it refresh will now see our newly created manager role. We can even take this one step further by creating role level security policies to protect sensitive information. So this time we'll use a create policy statement. We'll go ahead and name this employee policy, will sign this policy to a table, which is the employee's table. We use the for all keywords to list all access. And here we'll use the using keyword to go ahead and create a conditional statement. In this case, I've just randomly chosen employee ID for go ahead and create this policy by pressing the play button. Now we've created our policy. Thanks for watching. Stay tuned for the next lesson or regain confidence in school by implementing backup and recovery strategies.

Advanced Provider : Eric Grose 4 Lessons · 18m in Arabic, English

Course Description

Safeguarding against unforeseen data disasters is crucial for any database, and crafting backup and recovery strategies is a big part of that process. In these lessons, you will gain experience with data integrity, encryption, and access controls, as well as how to set appropriate user privileges. Discover techniques to identify and resolve performance bottlenecks, optimizing query execution and improving system responsiveness. You will also learn tools for monitoring your database and tuning your queries for optimal performance. The skills you will learn in these lessons are important in ensuring that no business processes are interrupted by data loss or corruption.

In these lessons, we will look into the critical aspects of database security and management. You will learn key concepts including data integrity, access controls, and user privileges. We will begin to implement robust backup and recovery strategies to safeguard against data loss. These insights into database monitoring and performance tuning will help ensure optimal system functionality.

What You'll Learn

Apply the least privilege principle to fortify and secure your database
Implement backup and recovery strategies to safeguard against data loss and corruption
Use tools to monitor your database and tune queries for optimal performance
Create and manage user accounts and privileges within a database
Configure data integrity, encryption, and access controls
Identify and resolve performance bottlenecks to improve system responsiveness

Key Takeaways

Crafting backup and recovery strategies is a key part of safeguarding a database against unforeseen data disasters.
Data integrity, encryption, access controls, and appropriate user privileges are core to database security and management.
Identifying and resolving performance bottlenecks optimizes query execution and improves system responsiveness.
Database monitoring tools and query tuning help ensure optimal system functionality.
These skills help ensure that no business processes are interrupted by data loss or corruption.

Frequently Asked Questions

What does this course cover?

It covers the critical aspects of database security and management, including data integrity, encryption, access controls, user privileges, backup and recovery strategies, and database monitoring and performance tuning.

What skills will I gain from this course?

You will gain skills in backup replication, data security, and performance tuning, including setting appropriate user privileges and tuning queries for optimal performance.

What topics are taught in the lessons?

The lessons cover Data Integrity and Security, Backup and Recovery Strategies, Database Monitoring and Performance Tuning, and User Management and Privileges.

How does this course help protect business processes?

The skills taught are important for ensuring that no business processes are interrupted by data loss or corruption, through robust backup and recovery strategies and strong database security.