Module 3 Database Management

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

2. TABLESPACE AND DATA FILLE ITEC 450

3. TABLESPACE AND DATA FILLE • Tablespaces are made up of at least one datafile • You cannot create a table space without creating its initial datafile; you cannot create a datafile without an associated tablespace • Big file tablespaces must be locally managed • Undo tablespaces are specialized to store undo (rollback) data ITEC 450

4. THE DATAFILE CLAUSE • When creating a tablespace with a user-managed file, specify a datafile name in the command • Each datafile must have a specified SIZE • Exceptions: OMFs and named files that already exist • Omitting AUTOEXTEND and SIZE parameters results in AUTOEXTEND set to ON • Example: ITEC 450

5. The EXTENT MANAGEMENT and SEGMENT SPACE MANAGEMENT Clauses • These clauses tell Oracle how to track the usage of blocks within each extent • Locally vs. dictionary-managed  performance: • Locally managed tablespaces store the map of where extents are in datafiles in a rapid access bitmap, in the header of the datafile • Dictionary-managed tablespaces store this information in metadata, in the SYSTEM tablespace ITEC 450

6. The EXTENT MANAGEMENT and SEGMENT SPACE MANAGEMENT Clauses • Deallocated extents return to free space list in data dictionary as a contiguous chunk of data blocks • For these to be usable, the next object that needs an extent must be that exact size (or smaller) extent • Otherwise, deallocated data blocks are passed over, and data blocks at the end of datafile are used • Datafile grows faster than necessary • If there are contiguous deallocated extents, they aren’t seen as a chunk of space until coalesced • Coalescence: combining of multiple adjacent free extents into a single contiguous free extent • Occurs periodically through the SMON background process ITEC 450

7. The EXTENT MANAGEMENT and SEGMENT SPACE MANAGEMENT Clauses ITEC 450

8. Creating a Dictionary-Managed Tablespace • Dictionary-managed tablespaces cause slower performance of DML commands • Updates involve multiple tables behind the scenes • Locally managed tablespaces reduce/eliminate the problem of unused free space gaps ITEC 450

9. Creating a Dictionary-Managed Tablespace ITEC 450

10. Segment Types and Their Uses ITEC 450

11. Temporary Tablespace • Temporary segments need a temporary tablespace • Oracle recommends creating locally managed, temporary tablespaces • You can also create multiple temporary tablespaces • Require a tablespace group • Group can be set as the default for the entire DB • Assign the user a default temporary tablespace: ITEC 450

12. Alter tablespace command • Tasks you can handle with ALTER TABLESPACE: • Change DEFAULT STORAGE settings for any future objects created in (dictionary-managed) tablespace • Change the MINIMUM EXTENT size • Change LOGGING to NOLOGGING and vice versa • Change PERMANENT to TEMPORARY (vice versa) • Change READ ONLY to READ WRITE (vice versa) • Coalesce contiguous storage space • Add a new datafile or temporary file • Rename a datafile or temporary file • Begin and end an open backup ITEC 450

13. Alter tablespace command • To change an existing datafile’s storage, you must use the ALTER DATABASE command instead: • To modify tempfile: ALTER DATABASE TEMPFILE ITEC 450

14. Alter tablespace command • The status of a tablespace defines its availability to end-users and also defines how it is handled during backup and recovery • ONLINE • OFFLINE • NORMAL • TEMPORARY • IMMEDIATE • It is possible to take individual datafiles offline ITEC 450

15. READ-ONLY TABLESPACE ITEC 450

16. DROPPING & RENAMING TABLESPACES ITEC 450

17. TABLESPACE VIEWS ITEC 450

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

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

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

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

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

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

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

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

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

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

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

29. CREATE USER ITEC 450

30. ALTER USER ITEC 450

31. DROP USER • Removing users requires the DROP USER system privilege, which the SYSTEM user has. DROP USER <user> CASCADE; • Use CASCADE if user owns tables or DB objects • If a user has created other users, those users are not dropped when the creating user is dropped • The new users do not belong to the original user’s schema • If a user has created tables you want to keep, do not drop the user • Instead, change the user account to LOCK status ITEC 450

32. PROFILES • Specify a profile when you create/alter a DB user • Profile: collection of settings that limits the use of system resources and the database • A profile can be assigned to any number of users • A user can be assigned only one profile at a time • A newly assigned profile overrides the old one • User’s current session isn’t affected by profile change • DEFAULT profile has no resource or DB use limits • As a system grows, resources may become stretched • Profiles can be used for managing passwords too ITEC 450

33. CREATE PROFILE CREATE PROFILE <profile> LIMIT <password_setting> ... <resource_setting> <limit> ...; • Password settings: • FAILED_LOGIN_ATTEMPTS, PASSWORD_LIFE_TIME, PASSWORD_REUSE_TIME, PASSWORD_REUSE_MAX, PASSWORD_LOCK_TIME, FAILED_LOGIN_ATTEMPTS, PASSWORD_GRACE_TIME, PASSWORD_VERIFY_FUNCTION • You can limit nine resources: • SESSSIONS_PER_USER, CPU_PER_SESSION, CPU_PER_CALL, CONNECT_TIME, IDLE_TIME, LOGICAL_READS_PER_SESSION, LOGICAL_READS_PER_CALL, PRIVATE_SGA, COMPOSITE_LIMIT ITEC 450

34. PASSWORD MANAGEMENT • There are three different areas to examine when working with passwords: • Changing a password and making it expire • Enforcing password time limits, history, and other settings • Enforcing password complexity • Uses a combination of a function and a profile • Predefined SQL script to verify the complexity of a password • Adjust the PASSWORD_VERIFY_FUNCTION setting in a profile and assign that profile to a user ITEC 450

35. ALTER PROFILE • ALTER PROFILE, with resource clauses listed: ALTER PROFILE <profile> LIMIT <password_setting> ... SESSIONS_PER_USER <concurrent sessions> CPU_PER_SESSION <hundredths of seconds> CPU_PER_CALL <hundredths of seconds> CONNECT_TIME <minutes> IDLE_TIME <minutes> LOGICAL_READS_PER_SESSION <data blocks> LOGICAL_READS_PER_CALL <data blocks> PRIVATE_SGA <bytes> COMPOSITE_LIMIT <service units> • Example: ALTER SYSTEM SET RESOURCE_LIMIT=TRUE; ALTER PROFILE PROGRAMMER LIMIT IDLE_TIME 15 CPU_PER_CALL 100; ALTER RESOURCE COST CPU_PER_SESSION 1000 PRIVATE_SGA 1; ITEC 450

36. DROP PROFLE • The syntax of DROP PROFILE is similar to the syntax for dropping a user in that it includes a CASCADE parameter: DROP PROFILE <profile> CASCADE; • You must add CASCADE if any users have been assigned the profile being dropped • Oracle automatically resets these users to the DEFAULT profile • For example, if three users have been assigned to the ACCT_MGR profile, drop the profile like this: DROP PROFILE ACCT_MGR CASCADE; ITEC 450

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

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

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

40. OBJECT PRIVILEGES ITEC 450

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

42. roles ITEC 450

43. Data dictionary views ITEC 450

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

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