Database Administration and Security PDF

IT2003 Database Administration and Security Database security refers to DBMS features and other related measures that comply with the organization’s security A. Rol...

IT2003 Database Administration and Security Database security refers to DBMS features and other related measures that comply with the organization’s security A. Roles of Data Administrator and Database Administrator requirements. From the DBA’s point of view, security measures should be implemented to protect the DBMS against service Data Administrator (DA) – a management-oriented role that degradation and to protect the database against loss, corruption, or concerns controlling the overall corporate data resources, both mishandling. computerized and manual. It is essential to implement security within the organization to make Database Administrator (DBA) – a person who maintains a sure that the right people have access to the right data. Without successful database environment by directing or performing all these security measures in place, you might find someone related activities to keep the data secure. destroying your valuable data, or selling your company's secrets to It is the responsibility of the Data Administrator (DA) to your competitors, or someone invading the privacy of others. determine the contents and logical boundaries of each database. The SQL security model provides a basic syntax used to specify Database Administrators (DBA) are responsible for the design, security restrictions. The DBMS will then implement the security implementation, maintenance, and security of physical structures system and enforce the required restrictions. (databases). DA first builds a logical model of the database, which is later implemented by DBA. There are four (4) common concepts of SQL security as follows: Users – This represents people or programs performing actions on Contrasting DA and DBA Activities and Tasks objects in the database. The DBMS grants users an ID for Data Administrator (DA) Database Administrator (DBA) authentication and privileges to perform specific actions on specific Builds logical design Facilitates the development and tables/rows. use of the database Objects – These are the things defined by SQL standards in the Has a managerial orientation Has a technical orientation database that users can manipulate. This includes rows, columns, Analyze and perform business Analyze data volumes and space tables, indexes, and views. data requirements requirements in DBMS Privileges – This refers to the rights of users to manipulate objects. Define policies and standards Enforces policies and These privileges start with SELECT, INSERT, DELETE, and (definition, naming, abbreviation) programming standards UPDATE, ALTER, INDEX, AND REFERENCES for database objects. DA and DBA functions to organizations may tend to overlap. Roles – is a named collection of database access privileges that However, when the organization does not include a DA position, authorize a user to connect to the database and use its system the DBA executes some of the DA’s functions. In this combined resources. role, the DBA must have a diverse mix of technical and managerial skills. B. Users DBA's managerial services include supporting end-users, defining and enforcing policies and programming standards Setting up security begins with Authentication and Authorization. for the database, providing data backup and recovery Individual users, groups, or processes granted access to the SQL services, and monitoring distribution and use of the data in the server instance either at server level or database level. Server- database. level includes logins and server roles. Database-level include users and database roles. Database Security 05 Handout 1 *Property of STI  [email protected] Page 1 of 3  IT2003 Login allows you to connect to the SQL Server service (also called When multiple users can access database objects, authorization an instance), and permissions inside the database are granted to can be controlled to these objects with privileges. Every object has the database users. an owner. Privileges control if a user can modify an object owned Logins must be mapped to a database user to connect to a by another user. Access privileges in relational databases are database. If your login is not mapped to any database user, you can assigned through SQL GRANT and REVOKE commands. still connect to SQL Server instance using SQL Server GRANT – is a command used to provide access or privileges on Management Studio (SSMS), but you are not allowed to access any the database objects to the users. objects in the database. To access any objects in the database, you REVOKE – is a command used to remove privileges from a specific must have a login that is mapped to a user in the database, and that user or role or from all users to perform actions on database objects. the user must be granted appropriate rights in the database. The user named user1 that we have created before does not have any access to myDB. Using the GRANT command, we want user1 to be able to Here is the syntax to create a new login in T-SQL: view and modify the data in the myDB database. Here is the syntax: CREATE LOGIN login_name_test WITH PASSWORD = 'Mypassword'; USE myDB S: To check the newly created login or any changes in login, open the GO Object Explorer window under the SQL server, expand the security GRANT SELECT, UPDATE ON [Names] TO user1 folder, and expand the login folder. Now, we want to remove the modifying privilege of user1. Here is the syntax: Here is the syntax for changing any credentials of the login For the login name: REVOKE UPDATE ON [Names] TO user1 ALTER LOGIN login_name_test WITH NAME = newlogin_name; If we try to execute the following statement using the login account For the password: newlogin_name that we have created before, it will show an error like this. ALTER LOGIN newlogin_name WITH PASSWORD = 'Newpassword'; Assume that we have a database named myDB and a table named Names having the following values: ID LastName FirstName 1 Escalona John Smith 2 Cequena Abby Now we will create a new user under the myDB database and the login D. Roles account that we have created before. Here is the syntax: A role is a collection of privileges that can be granted to one or more USE myDB users or other roles. Roles help you grant and manage sets of GO privileges for various categories of users, rather than grant those CREATE USER user1 FOR LOGIN newlogin_name privileges to each user individually. The main benefit of roles is efficient management. Imagine a group C. Privileges of 1,000 users suddenly needing to view or modify new data. Instead of modifying 1,000 user accounts, you can simply select a database-level role and assign it to a user. There are two types of 05 Handout 1 *Property of STI  [email protected] Page 2 of 3  IT2003 database-level roles: fixed-database roles that are predefined in Members of the db_denydatareader the database and user-defined database roles that you can create fixed database role cannot read any db_denydatareader on your own based on your preferences. data in the user tables within a database. The following table shows the fixed-database roles and their capabilities. We can assign a db_owner role to the user that we previously created Fixed-Database role name Description using this syntax: Members of the db_owner fixed USE myDB database role can perform all GO db_owner configuration and maintenance ALTER ROLE db_owner ADD MEMBER user1 activities on the database and can also drop the database in SQL Server. We can also create a user-defined database role having the privilege of Members of the db_securityadmin viewing and updating the data in table Names. Here is the syntax: fixed database role can modify role membership for custom roles only and USE myDB db_securityadmin GO manage permissions. Members of this role can potentially elevate their CREATE ROLE [Admin1] privileges. GO Members of the db_accessadmin GRANT SELECT, UPDATE ON [Names] fixed database role can add or remove TO [Admin1] db_accessadmin access to the database for Windows logins, Windows groups, and SQL GO Server logins. Members of the db_backupoperator db_backupoperator fixed database role can back up the database. REFERENCES Members of the db_ddladmin fixed Coronel, C. and Morris, S. (2018). Database systems design, database role can run any Data implementation, & management (13th ed.). Cengage Learning. db_ddladmin Definition Language (DDL) command in a database. Elmasri, R. & Navathe, S. (2016). Fundamentals of database systems (7th Members of the db_datawriter fixed db_datawriter database role can add, delete, or ed.). Pearson Higher Education. change data in all user tables. Members of the db_datareader fixed Kroenke, D. & Auer, D. Database processing: Fundamentals, design, and db_datareader database role can read all data from implementation (12th ed.). Pearson Higher Education. all user tables. Members of the db_denydatawriter Silberschatz A., Korth H.F., & Sudarshan, S. (2019). Database system fixed database role cannot add, db_denydatawriter modify, or delete any data in the user concepts (7th ed.). McGraw-Hill Education. tables within a database. 05 Handout 1 *Property of STI  [email protected] Page 3 of 3