Introduction of Week 7

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