1 / 45

Module 3 Database Management

Module 3 Database Management. Section 3 Database Security. TABLESPACE AND DATA FILLE. TABLESPACE AND DATA FILLE. Tablespaces are made up of at least one datafile

stella
Download Presentation

Module 3 Database Management

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

More Related