1 / 19

Introduction of Week 7

Introduction of Week 7. Assignment Discussion Graded: 3-1-2 (Creation of Database ) ( All submitted!) Naming standard, Logical to physical design OEM required Listener configuration, Delete group 3

Download Presentation

Introduction of Week 7

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Introduction of Week 7 • Assignment Discussion • Graded: 3-1-2 (Creation of Database) (All submitted!) • Naming standard, Logical to physical design • OEM required Listener configuration, Delete group 3 • Lessons learned (10 points): error and resolution, documentation as future instruction manual, reflection of your learning • Turn in the 3-1-3 only in the future, not 3-1-1 and 3-1-2 • Turned in: 5-1 (Connectivity Paper) • Due this week: 6-1 (High Availability Paper) • Review of previous week and module • Database Change Management • Availability Requirement and Architecture • Database access validation: were you able to use OEM and SQL Plus? • Overview of this week • Database Security • User Management and Security in Oracle • Midterm Review ITEC 450

  2. Module 3 Database Management ITEC 450 Section 3 Database Security

  3. Data Integrity • Database structure integrity • Index corruption, data block corruption • Invalid objects • Managing structural problems: DBMS utility • Semantic data integrity • Entity integrity – primary key should be used • Check constraint , rules in SQL Server – validate data • Trigger – a piece of code that is executed automatically when a data modification happens • Referential integrity – establish the relationship between primary and foreign key columns ITEC 450

  4. Aspects of Database Security • Authorization – controlling access to data • Authentication – restricting access to legitimate users • Auditing – ensuring accountability and inspection • Encryption – safeguarding key data in the database • Enterprise security – managing the security of the entire organizational information structure beyond database itself ITEC 450

  5. Authorization Authorization – controlling access to data • Policy issues – what info should be viewed • Integrity issues – preventing unexpected results • Types of privileges • Data access – read, write, or reference to a table, a view • Database object – create or modify database objects • System – perform certain types of system-wide activities • Procedure – execute specific functions and stored procedures ITEC 450

  6. Authorization Implementation Data Control Language (DCL) – Grant and Revoke • Grant different types of privileges • GRANT SELECT, INSERT on Titles to user5; • GRANT CREATE session, CREATE table to user1; • GRANT SELECT ANY table to user9; • GRANT EXECUTE on Proc1 to user2, user5; • Grant with GRANT option – pass the granted authority to others • GRANT SELECT on Titles to user3 WITH GRANT OPTION; • Revoke • REVOKE INSERT on Titles from user5; ITEC 450

  7. Role and Group • Roles – a collection of granted privileges • A role can be created using the CREATE ROLE. • The permissions are granted to the role • The role is assigned to users using GRANT. These users will get all the privileges from the role. • Groups – DBMS built-in roles • System administrator: SYS in Oracle, SA in SQL Server • Database administrator: all privileges over the database • Operations control: perform certain operations such as backup and recovery ITEC 450

  8. Authentication Authentication – restricting access to legitimate users • DBMS user ID and password • Password guidance and enforcement • Operating system login ID and password • LDAP authentication • A global login account is established for enterprise access • In order to access a database, the global login account has to be associated with the database. • It is a single sign-on or with same login and password ITEC 450

  9. Auditing Auditing – ensuring accountability and inspection • When auditing is enabled, the DBMS will produce an audit trail of database operations. • Auditing level – database, user, object • Auditing issues – performance degradation, storage usage • Examples of audit facilities • Login and logoff attempts • Commands issued to an object • Stored procedure executions ITEC 450

  10. Encryption Encryption – protect sensitive data in the database • It is a security technique that encodes legible data into a scrambled format. • Encryption consists of applying an encryption algorithm to data using some pre-specified encryption key. The resulting data has to be decrypted using a decryption key to recover the original data. • Examples of encryption • Public key encryption • Digital signature ITEC 450

  11. Enterprise Security Enterprise security – managing the security of the entire organizational information structure beyond database itself • System-related issues: Operating system, database physical files, network access and control • Legal and ethical issues ITEC 450

  12. Module 3 Database Management ITEC 450 Section 4 User Management and Security in Oracle

  13. User Management • Creating a new user • Privilege required: “create user” system privilege • SQL> CREATE USER mobeydick IDENTFIEDY BY whitewhale; • Altering a user – modifying password, assign resource, etc. • Privilege required: “alter user” system privilege • SQL> ALTER USER Jim IDENTIFIED BY red; • SQL> ALTER USER Jim DEFAULT TABLESPACE users; • Resource • Default tablespace, temporary tablespace • Profile: limit on the amount of resources a user can use ITEC 450

  14. Oracle Privileges In oracle, there are two types of privileges: system and object. • System privilege - allowing a user to perform a particular action within the database, or on any schema objects • Object privilege - gives user ability to perform some operations on a specific object such as table, view, procedure, function. A roleis a named group of collected system and object privileges. ITEC 450

  15. System Privilege System privilege - allowing a user to perform a particular action within the database, or on any schema objects • Privilege required: the system privilege with ADMIN OPTION, or GRANT ANY PRIVILEGE system privilege • Examples: • CREATE SESSION: connect to a database • CREATE ANY INDEX, SELECT ANY TABLE • Information is stored in: dba_sys_privs ITEC 450

  16. Object Privilege Object privilege - gives user ability to perform some operations on a specific object such as table, view, procedure, function • Privilege required: the owner of the object, object privileges with the GRANT option, GRANT ANY OBJECT system privilege • Examples: • GRANT SELECT, INSERT ON Titles to user1; • Information is stored in: dba_tab_privs ITEC 450

  17. Oracle Naming Convention In Oracle data dictionary, most object names begin with one of three prefixes: • USER_ information about objects owned by the user performing the query • ALL_ information from USER plus other objects on which privileges have been granted to PUBLIC or to the user • DBA_ all database objects, regardless of owner Oracle dynamic views: current instance information • V$ v$session contains info of all current sessions ITEC 450

  18. Examples of Data Dictionary Views • DBA_USERS – information about all user accounts • DBA_ROLES – all the roles in the database • USER_TAB_PRIVS – table privileges for which you are the grantee, the grantor, or the object owner • USER_SYS_PRIVS – system privileges that have been granted to the user • USER_ROLE_PRIVS – roles that have been assigned to the user • SESSION_PRIVS – system privileges currently enabled for the login user • SESSION_ROLES – roles currently enabled for the user ITEC 450

  19. Midterm Review • DBA roles and responsibilities • DBMS architecture and selection drivers • Oracle architecture • database vs. instance • logical and physical structures • high-level understand background processes and memory structure • DBMS installation basics, change consideration • Database design: conceptual, logical, and physical design • Database connectivity layers, ODBC/JDBC architecture • Oracle Net, Listener, purposes of “listener.ora” and “tnsnames.ora” • Database change: types, impacts, and management • Database high-availability problems and solutions • Database security: aspects, authorization, authentication • Oracle privileges: system and object, roles ITEC 450

More Related