1/59
Looks like no tags are added yet.
Data Administrator
What does DA stands for?
Database Administrator (DBA)
What does DBA stands for?
DA
What is the management-oriented role that concerns controlling the overall corporate data resources, both computerized and manual?
DBA
What is the person who maintains a successful DB environment by directing or performing all related activities to keep the data secure?
DA
Who has the responsibility to determine the contents and logical boundaries of each database?
DBA
Who are responsible for the design, implementation, maintenance, and security of physical structures (DB)?
DA then DBA
‘Who’ first builds a logical model of the DB then ‘who’ later implements it?
DA
Whose activities and tasks are these:
Builds logical design
Has a managerial orientation
Analyze and perform business data reqs
Define policies and standards
DBA
Whose activities and tasks are these:
Facilitates the dev-ent and DB use
Has a tech-al orientation
Analyze data volumes and space reqs in DBMS
Enforces policies and prog-ing standards
DBA
When the org does not include a DA position, who executes some of DA’s functions? They also must have a diverse mix of tech-al and managerial skills.
DBA
Whose managerial services include supporting end-users, defining and enforcing policies and prog-ing standards for DB, providing data backup and recovery services, and monitoring distribution and data use in DB?
DB Security
What refers to DBMS features and other related measures that comply with the org’s security reqs?
DB Security
From the DBA’s point of view, what should be implemented to protect the DBMS against service degradation and to protect the database against loss, corruption, or mishandling?
DB Security
It is essential to implement […] within the org to make sure that the right people have access to the right data. Without these in place, you might find someone destroying your valuable data, or selling your company's secrets to your competitors, or someone invading the privacy of others.
SQL Security Model
What provides a basic syntax used to specify security restrictions? The DBMS will then implement the security system and enforce the required restrictions.
Privileges
Users
Roles
Objects
What are the four common concepts of SQL security?
Privileges
What refers to the rights of users to manipulate objects?
Privileges
What do starts with SELECT, INSERT, DELETE and UPDATE, ALTER, INDEX, AND REFERENCES for DB objects?
Users
What represents people or programs performing actions on objects in the DB?
Users
Who does DBMS grants an ID for authentication and privileges to perform specific actions on specific tables/rows?
Roles
What is the named collection of DB access privileges that authorize a user to connect to the DB and use its system resources?
Objects
What are the things defined by SQL standards in the DB that users can manipulate?
Objects
What includes rows, columns, tables, indexes, and views?
Authentication and Authorization
Setting security begins with […].
Server-level
What level includes logins and server roles?
DB-level
What level include users and DB roles?
Login
What allows you to connect to the SQL Server service (also called
an instance),?
Users
Permissions inside the DB are granted to the DB […].
Login
If your […] is not mapped to any DB user, you can still connect to SQL Server instance using SSMS, but you are not allowed to access any objects in the DB.
CREATE LOGIN login_name_test
WITH PASSWORD = ‘Mypassword’;
Write down the syntax: create a new login in T-SQL.
Object Explorer
To check the newly created login or any changes in login, open the […] window under the SQL server, expand the security folder, and expand the login folder.
ALTER LOGIN login_name_test
WITH NAME = newlogin_name;
Write down the syntax: change credentials for login name in T-SQL.
ALTER LOGIN newlogin_name
WITH PASSWORD = ‘Newpassword’;
Write down the syntax: change credentials for password in T-SQL.
CREATE USER user_name
FOR LOGIN login_name;
Write down the syntax: create a new user.
Privileges
When multiple users can access database objects, authorization can be controlled to these objects with […].
Privileges
[…] control if a user can modify an object owned by another user.
GRANT and REVOKE
Access privileges in relational DBs are assigned through SQL […] and […] commands.
GRANT
What is the command used to provide access or privileges on the DB objects to the users?
REVOKE
What is the command used to remove privileges from a specific user or role or from all users to perform actions on DB objects?
GRANT SELECT, UPDATE, DELETE
ON table_name
TO user_name
Write down the syntax: using GRANT command to user.
REVOKE SELECT, UPDATE, DELETE
ON table_name
TO user_name
Write down the syntax: using REVOKE command to user.
Roles
What is the collection of privileges that can be granted to one or more users or other roles?
Roles
What helps you grant and manage sets of privileges for various categories of users, rather than grant those privileges to each user individually?
Roles
The main benefit of […] is efficient management.
Fixed-DB Roles
User-Defined DB Roles
What are the two DB-level roles?
Fixed-DB Roles
What DB-level role is predefined in the DB?
User-Defined DB Roles
What is the DB-level role that you can create on your own based on your preferences?
owner
securityadmin
accessadmin
backupoperator
ddladmin
datawriter
datareader
denydatawriter
denydatareader
List down examples of fixed-DB role name: [db_…]
db_owner
What Fixed-DB Role is this:
“Can perform all config and maintenance activities on DB and can also drop the DB in SQL Server.”
db_securityadmin
What Fixed-DB Role is this:
“Can modify role membership for custom roles only and manage permissions. Members of this role can potentially elevate their privileges.”
db_accessadmin
What Fixed-DB Role is this:
“Can add or remove access to the database for Windows logins, Windows groups, and SQL Server logins.”
db_backupoperator
What Fixed-DB Role is this:
“Can back up the DB.”
db_ddladmin
What Fixed-DB Role is this:
“Can run any Data Definition Language (DDL) command in a DB.”
db_datawriter
What Fixed-DB Role is this:
“Can add, delete, or change data in all user tables.”
db_datareader
What Fixed-DB Role is this:
“Can read all data from all user tables.”
db_denydatawriter
What Fixed-DB Role is this:
“Cannot add, modify, or delete any data in the user tables within a DB.”
db_denydatareader
What Fixed-DB Role is this:
“Cannot read any data in the user tables within a DB.”
CREATE ROLE admin_name
GRANT SELECT, UPDATE
ON table_name
TO admin_name
Write down the syntax: create user-defined DB role with privilege.
ALTER ROLE role_ name
ADD MEMBER user_name;
Write down the syntax: assign role to a user.