05. Database Administration and Security

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

1/59

encourage image

There's no tags or description

Looks like no tags are added yet.

60 Terms

1
New cards

Data Administrator

What does DA stands for?

2
New cards

Database Administrator (DBA)

What does DBA stands for?

3
New cards

DA

What is the management-oriented role that concerns controlling the overall corporate data resources, both computerized and manual?

4
New cards

DBA

What is the person who maintains a successful DB environment by directing or performing all related activities to keep the data secure?

5
New cards

DA

Who has the responsibility to determine the contents and logical boundaries of each database?

6
New cards

DBA

Who are responsible for the design, implementation, maintenance, and security of physical structures (DB)?

7
New cards

DA then DBA

‘Who’ first builds a logical model of the DB then ‘who’ later implements it?

8
New cards

DA

Whose activities and tasks are these:

  1. Builds logical design

  2. Has a managerial orientation

  3. Analyze and perform business data reqs

  4. Define policies and standards


9
New cards

DBA

Whose activities and tasks are these:

  1. Facilitates the dev-ent and DB use

  2. Has a tech-al orientation

  3. Analyze data volumes and space reqs in DBMS

  4. Enforces policies and prog-ing standards


10
New cards

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.

11
New cards
12
New cards

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?

13
New cards

DB Security

What refers to DBMS features and other related measures that comply with the org’s security reqs?

14
New cards

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?

15
New cards

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.

16
New cards

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.

17
New cards
  1. Privileges

  2. Users

  3. Roles

  4. Objects


What are the four common concepts of SQL security?

18
New cards

Privileges

What refers to the rights of users to manipulate objects?

19
New cards

Privileges

What do starts with SELECT, INSERT, DELETE and UPDATE, ALTER, INDEX, AND REFERENCES for DB objects?

20
New cards

Users

What represents people or programs performing actions on objects in the DB?

21
New cards

Users

Who does DBMS grants an ID for authentication and privileges to perform specific actions on specific tables/rows?

22
New cards

Roles

What is the named collection of DB access privileges that authorize a user to connect to the DB and use its system resources?

23
New cards

Objects

What are the things defined by SQL standards in the DB that users can manipulate?

24
New cards

Objects

What includes rows, columns, tables, indexes, and views?

25
New cards

Authentication and Authorization

Setting security begins with […].

26
New cards

Server-level

What level includes logins and server roles?

27
New cards

DB-level

What level include users and DB roles?

28
New cards

Login

What allows you to connect to the SQL Server service (also called

an instance),?

29
New cards

Users

Permissions inside the DB are granted to the DB […].

30
New cards

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.

31
New cards

CREATE LOGIN login_name_test

WITH PASSWORD = ‘Mypassword’;

Write down the syntax: create a new login in T-SQL.

32
New cards

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.

33
New cards

ALTER LOGIN login_name_test

WITH NAME = newlogin_name;

Write down the syntax: change credentials for login name in T-SQL.

34
New cards

ALTER LOGIN newlogin_name

WITH PASSWORD = ‘Newpassword’;

Write down the syntax: change credentials for password in T-SQL.

35
New cards

CREATE USER user_name

FOR LOGIN login_name;

Write down the syntax: create a new user.

36
New cards

Privileges

When multiple users can access database objects, authorization can be controlled to these objects with […].

37
New cards

Privileges

[…] control if a user can modify an object owned by another user.

38
New cards

GRANT and REVOKE

Access privileges in relational DBs are assigned through SQL […] and […] commands.

39
New cards

GRANT

What is the command used to provide access or privileges on the DB objects to the users?

40
New cards

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?

41
New cards

GRANT SELECT, UPDATE, DELETE

ON table_name

TO user_name

Write down the syntax: using GRANT command to user.

42
New cards

REVOKE SELECT, UPDATE, DELETE

ON table_name

TO user_name

Write down the syntax: using REVOKE command to user.

43
New cards

Roles

What is the collection of privileges that can be granted to one or more users or other roles?

44
New cards

Roles

What helps you grant and manage sets of privileges for various categories of users, rather than grant those privileges to each user individually?

45
New cards

Roles

The main benefit of […] is efficient management.

46
New cards
  1. Fixed-DB Roles

  2. User-Defined DB Roles


What are the two DB-level roles?

47
New cards

Fixed-DB Roles

What DB-level role is predefined in the DB?

48
New cards

User-Defined DB Roles

What is the DB-level role that you can create on your own based on your preferences?

49
New cards
  1. owner

  2. securityadmin

  3. accessadmin

  4. backupoperator

  5. ddladmin

  6. datawriter

  7. datareader

  8. denydatawriter

  9. denydatareader


List down examples of fixed-DB role name: [db_…]

50
New cards

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.”

51
New cards

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.”

52
New cards

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.”

53
New cards

db_backupoperator

What Fixed-DB Role is this:

“Can back up the DB.”

54
New cards

db_ddladmin

What Fixed-DB Role is this:

“Can run any Data Definition Language (DDL) command in a DB.”

55
New cards

db_datawriter

What Fixed-DB Role is this:

“Can add, delete, or change data in all user tables.”

56
New cards

db_datareader

What Fixed-DB Role is this:

“Can read all data from all user tables.”

57
New cards

db_denydatawriter

What Fixed-DB Role is this:

“Cannot add, modify, or delete any data in the user tables within a DB.”

58
New cards

db_denydatareader

What Fixed-DB Role is this:

“Cannot read any data in the user tables within a DB.”

59
New cards

CREATE ROLE admin_name

GRANT SELECT, UPDATE

ON table_name

TO admin_name

Write down the syntax: create user-defined DB role with privilege.

60
New cards

ALTER ROLE role_ name

ADD MEMBER user_name;

Write down the syntax: assign role to a user.