INTRODUCTION OF WEEK 7 Assignment Discussion Graded 3

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 ITEC 450 § § 2011 Fall § 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 1

2011 Fall ITEC 450 MODULE 3 DATABASE MANAGEMENT 2 Section 3 Database Security

DATA INTEGRITY 2011 Fall Database structure integrity Index corruption, data block corruption � Invalid objects � Managing structural problems: DBMS utility � ITEC 450 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 � 3

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 2011 Fall 4

AUTHORIZATION 2011 Fall � Data ITEC 450 Authorization – controlling access to data Policy issues – what info should be viewed Integrity issues – preventing unexpected results Types of privileges 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 5

AUTHORIZATION IMPLEMENTATION 2011 Fall ITEC 450 Data Control Language (DCL) – Grant and Revoke Grant different types of privileges GRANT SELECT, INSERT on Titles to user 5; � GRANT CREATE session, CREATE table to user 1; � GRANT SELECT ANY table to user 9; � GRANT EXECUTE on Proc 1 to user 2, user 5; � Grant with GRANT option – pass the granted authority to others � GRANT SELECT on Titles to user 3 WITH GRANT OPTION; Revoke � REVOKE INSERT on Titles from user 5; 6

ROLE AND GROUP 2011 Fall Roles – a collection of granted privileges 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 ITEC 450 �A – 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 7

AUTHENTICATION 2011 Fall � ITEC 450 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 � 8

AUDITING 2011 Fall � � � Login and logoff attempts Commands issued to an object Stored procedure executions ITEC 450 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 9

ENCRYPTION Public key encryption Digital signature ITEC 450 � � 2011 Fall 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 10

ENTERPRISE SECURITY 2011 Fall ITEC 450 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 11

2011 Fall ITEC 450 MODULE 3 DATABASE MANAGEMENT 12 Section 4 User Management and Security in Oracle

USER MANAGEMENT Creating a new user ITEC 450 Privilege required: “create user” system privilege � SQL> CREATE USER mobeydick IDENTFIEDY BY whitewhale; � 2011 Fall 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 � 13

ORACLE PRIVILEGES ITEC 450 A role is a named group of collected system and object privileges. 2011 Fall 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. 14

SYSTEM PRIVILEGE 2011 Fall ITEC 450 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 15

OBJECT PRIVILEGE ITEC 450 � 2011 Fall 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 user 1; Information is stored in: dba_tab_privs 16

ORACLE NAMING CONVENTION ITEC 450 Oracle dynamic views: current instance information V$ v$session contains info of all current sessions 2011 Fall 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 17

EXAMPLES OF DATA DICTIONARY VIEWS 2011 Fall ITEC 450 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 18

MIDTERM REVIEW ITEC 450 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 2011 Fall 19