1 / 55

Lecture 5 ORACLE Security Features

Lecture 5 ORACLE Security Features . User Terms . A database user account is a means to organize the ownership of and access to database objects. A password is an authentication by the Oracle database.

adli
Télécharger la présentation

Lecture 5 ORACLE Security Features

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. Lecture 5 • ORACLE • Security Features

  2. User Terms • A database user account is a means to organize the ownership of and access to database objects. • A password is an authentication by the Oracle database. • A privilege is a right to execute a particular type of SQL statement or to access another user’s object. • A role is a named group of related privileges that are granted to users or to other roles. • Profiles impose a named set of resource limits on database usage and instance resources. • Quota is a space allowance in a given

  3. Database User Accounts • Each database user account has: • A unique username • An authentication method • A default tablespace • A temporary tablespace • A user profile • A consumer group • A lock status

  4. Predefined Accounts: SYS and SYSTEM • The SYS account: • Is granted the DBA role • Has all privileges with ADMIN OPTION • Is required for startup, shutdown, and some maintenance commands • Owns the data dictionary • Owns the Automatic Workload Repository (AWR) • The SYSTEM account is granted the DBA role. • These accounts are not used for routine operations.

  5. Creating a User Select Administration > Schema > Users & Privileges > Users, and then click the Create button.

  6. Creating New Users • Authentication with user name • Decide quotas for each Tablespece • Default Tablespace and Temporary Tablespace • Grant privileges and roles to the user • Use Profile to place limits on database resources available to users • Use a standard password initially • Use the Expire keyword to force user to reset their password • Restrict quotas to few users – use Quota Unlimited with caution • Educate users

  7. Authenticating Users • Password • External • Global

  8. Administrator Authentication • Operating System Security • DBAs must have the OS privileges to create and delete files. • Typical database users should not have the OS privileges to create or delete database files. • Administrator Security • SYSBA and SYSOPER connections are authorized via password file or OS. • Password file authentication records the DBA user by name. • OS authentication does not record the specific user. • OS authentication takes precedence over password file authentication for SYSDBA and SYSOPER.

  9. Oracle Security Mechanism • Database user and schema : each user has a security domain that determines the privileges and roles, the tablespace quota, and the system resource limits for the user. • Privilege : a right to execute a particular type of SQL statement. • Roles : named groups of related privileges that are granted to users or other roles. • Storage settings and quotas : Oracle provides means for directing and limiting the use of disk space allocated to the database on a per user basis. • Resource limits : Each user is assigned a profile that specifies limitations on several system resources available to the user • Auditing : aid in the investigation of suspicious database use. Dr. Ouyang, CS 543, Fall 2000

  10. Different levels of security in Oracle There are Different levels of security in Oracle Account security for validation of users Access security for database objects System-level security for managing global privileges.

  11. Tools for DBA to monitor Users • DBA_USERS • USERNAME • USER_ID • CREATED • ACCOUNT_STATUS • LOCK_DATE • EXPIRY_DATE • DEFAULT_TABLESPACE • TEMPORARY_TABLESPACE • DBA_TS_QUOTAS • USERNAME • TABLESPACE_NAME • BYTES • MAX_BYTES • BLOCKS • MAX_BLOCKS

  12. Privileges for Oracle Users Three types of privileges: System: Enable users to perform particular action in the database Object: Enable users to access and manipulate a specific object Role : Enable users to access and manipulate a specific role.

  13. Privileges • There are two types of user privileges: • System: Enables users to perform particular actions in the database • Object: Enables users to access and manipulate a specific object HR_DBA Object privilege: Update employees. System privilege: Create session.

  14. System Privileges

  15. 1- System Privileges • There are about 200 System Privileges • CONNECT to the database (create a session)to let the user connect • CREATE TABLESPACE , CREATE DATABASE , CREATE TABLE • ANY keyword in the privileges signifies that users have the privilege in every schema. • CREATE ANY TABLE • DROP ANY TABLE • UPDATE ANY TABLE • CREATE ANY INDEX

  16. System Privileges Cont.. • GRANT command adds a privilege to user or a group of users • grant CREATE SESSION, CREATE TABLE to Emam1; • grant CREATE SESSION to AliHWITH ADMIN option; • REVOKE command deletes the privileges from users/group of users • revoke CREATE TABLE from Emam1; • revoke CREATE SESSION from AliH;

  17. Tools to monitor System Privileges • Database : DBA_SYS_PRIVS • - GRANTEE • - PRIVILEGE • - ADMIN OPTION • Session : SESSION_PRIVS • - PRIVILEGE

  18. 2- Object Privileges • Object Privileges can be granted/revoked on different objects like Tables, View, Sequence, Procedure etc. and are : • ALTER • INSERT • DELETE • SELECT • EXECUTE • REFERENCES • INDEX • UPDATE

  19. Object Privileges • GRANT command adds a privilege to user or a group of users • grant EXECUTE on EMPLOYEE to Emam1; • grant UPDATE (first_name, salary) on EMPLOYEE to Emam1 with grant option; • REVOKE command deletes the privileges from users/group of users • revoke delete on EMPLOYEE from Emam1; • revoke all on EMPLOYEE from AliH;

  20. Object Privileges • To grant object privileges, perform these tasks: • 1. Choose the object type. • 2. Select objects. • 3. Select privileges.

  21. DBA Jeff Emi DBA Jeff Emi Revoking System Privilegeswith ADMIN OPTION User GRANT Privilege Object REVOKE REVOKE CREATE TABLE FROM jeff;

  22. Bob Jeff Emi Bob Jeff Emi Revoking Object Privilegeswith GRANT OPTION GRANT REVOKE

  23. Tools to monitor Object Privileges • DBA_TAB_PRIVS • GRANTEE • OWNER • TABLE_NAME • GRANTOR • PRIVILEGE • GRANTABLE • DBA_COL_PRIVS • GRANTEE • OWNER • TABLE_NAME • COLOUMN_NAME • GRANTOR • PRIVILEGE • GRANTABLE

  24. Password Functionality • Account Locking : You can manually/ automatically lock a user account out of the database for any amount of time. • Password Complexity Verifications : Passwords conform to certain characteristics, such as have the password be no more than eight characters, or that a password must contain at least two numbers • Password History : check a newly specified password to ensure that it is not reused for the specified amount of time or for the specified number of password changes. • Password Lifetime and Expiration : This allows the database administrator to specify a passwords' lifetime, after which time the password expires automatically and must be changed before a login to database can be completed.

  25. Password-Related Options that can be Defined for Profiles • FAILED_LOGIN_ATTEMPTS : locks account after the user enters an incorrect password for FAILED_LOGIN_ATTEMPTS times, cumulative. • PASSWORD_GRACE_TIME : Number (or fraction) of days that a user can login with an expired password until a password change occurs. • PASSWORD_LIFE_TIME : Number (or fraction) of days before a password expires. • PASSWORD_LOCK_TIME : Number (or fraction) of days that the user is locked out of the database after entering invalid passwords. • PASSWORD_REUSE_MAX : Number of times a user must change the password before it can be reused. For example, setting this to 3 requires a user must change the password to three other passwords before a password can be reused. • PASSWORD_REUSE_TIME : Number (or fraction) of days before a password can be reused. • PASSWORD_VERIFY_FUNCTION : : Checks the password to see whether an intruder can easily break the password.

  26. Default Profile Password Parameter Values • When a user account is first created, it is assigned to the DEFAULT profile. The above seven password management options all have default values. • If you do not wish to have these values, you may either change it for the DEFAULT profile or create your own customized profiles

  27. Maintaining Restrictions via Profiles • Restrictions on system and object resources are maintained via profiles • Some of them include: SESSION_PER_USER, CPU_PER_SESSION, CPU_PER_CALL, CONNECT_TIME, IDLE_TIME, LOGICAL_READS_PER_SESSION, LOGICAL_READS_PER_CALL, COMPOSITE_LIMIT, IDLE_TIME, and PRIVATE_SGA. • To restrict user accounts to 15 minutes per connection, you would create a profile with the CONNECT_TIME set, then assign the user to the new profile.

  28. Creating Profiles • When a user account is created, they are assigned to the DEFAULT profile (unless otherwise specified). To create a new profile, use the CREATE PROFILE command and list all restrictions along with their values. • CREATE PROFILE Student LIMIT • FAILED_LOGIN_ATTEMPTS 3 • PASSWORD_GRACE_TIME 7 • PASSWORD_LIFE_TIME 90 • PASSWORD_LOCK_TIME 1 • PASSWORD_REUSE_MAX 5 • PASSWORD_REUSE_TIME unlimited • PASSWORD_VERIFY_FUNCTION student_password ; • CREATE PROFILE profile_name LIMIT SESSIONS_PER_USER 4;

  29. Assign a Profile to a User • Once a profile has been created, you can associate users to the profile • ALTER USER username PROFILE profile_name; • It is through this method that different password options can be set for user accounts.

  30. Creating a Sample Password-Related Profile • If you want to assign non-default password management options to user accounts, you can create a new profile. The following SQL statement below will lock a user account out of the database for one hour after failing to successfully login three times. Also, the password will automatically expire every thirty days. CREATE PROFILE NORMAL_USERS LIMIT PASSWORD_LIFE_TIME 30 FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 1/24;

  31. The Default verify_function Provided by Oracle • This function must be created in SYS schema, and you must connect sys/<password> as sysdba before running the script. This snippet contains the start of the function and checks if the password is the same as the username. The verify_function is shown in five parts: 1. The beginning of the verify_function, check if the password is the same as the username2. Check to make sure that the password has at least 4 characters3. Check to make sure that the password is not “too simple” by comparing against 8 simple words4. Check to make sure that the password contains at least one letter, one digit, and one punctuation mark5. Check if the password differs from the previous password by at least 3 letters

  32. Data Dictionary Views Used for Password Management There are a few data dictionary views that can be queried to determine the status of password management in your database: • USER_PASSWORD_LIMITS,ALL_PASSWORD_LIMITS, DBA_PASSWORD_LIMITS • USER_USERS, ALL_USERS, DBA_USERS • USER_PROFILES, ALL_PROFILES, DBA_PROFILES

  33. ROLES • Oracle provides for easy and controlled privilege management through roles. • Roles are named groups of related privileges that you grant to users or other roles. • Roles are designed to ease the administration of end-user system and schema object privileges. • Database administrators often create roles for a database application. • The DBA grants a secure application role all privileges necessary to run the application. • The DBA can create a role with a password to prevent unauthorized use of the privileges granted to the role.

  34. Common Uses for Roles • Common Uses for Roles • Role will serve one of two purposes: • To manage the privileges for a database application • To manage the privileges for a user group • Application Roles : All privileges necessary to run a given database application. T • User Roles : A group of database users with common privilege requirements.

  35. Benefits of Roles • Easier privilege management • Dynamic privilege management • Selective availability of privileges

  36. Assigning Privileges to Roles and Roles to Users Users Jenny David Rachel Roles HR_CLERK HR_MGR Delete employees. Select employees. Update employees. Privileges Insert employees.

  37. Predefined Roles The following roles are defined automatically for Oracle databases: • CONNECT • RESOURCE • DBA • EXP_FULL_DATABASE • IMP_FULL_DATABASE

  38. Predefined Roles

  39. Creating a Role • Select Administration > Schema > Users & Privileges > Roles.

  40. Secure Roles • Roles may be nondefault. SET ROLE vacationdba; • Roles may be protected through authentication. • Roles may also be secured programmatically. CREATE ROLE secure_application_role IDENTIFIED USING <security_procedure_name>;

  41. Assigning Roles to Users

  42. Tools to monitor ROLES • SESSION_ROLES view shows all roles that are currently enabled DBA_SYS_PRIVS • - Name • - Typs • Session : SESSION_PRIVS • - PRIVILEGE

  43. Authorization Database Security : The protection of the database against intentional or unintentional threats using computer-based or non-computer-based controls. The granting of a right or privilege, which enables a subject to have legitimate access to a system or a system’s object. The term “object” represents a database table, view, application, procedure, or any other object that can be created within the system. Ownership of objects gives the owner all appropriate privileges on the objects owned. The DBMS needs to keep track of all privileges granted to users, and by whom, in order to maintain the correct set of privileges. Authentication refers to a mechanism that determines whether a user is who he or she claims to be.

  44. PROFILE • Use the CREATE PROFILE to set of limits on database resources. If you assign the profile to a user, then that user cannot exceed these limits. • Prerequisites to create a ptrofile • You must have the CREATE PROFILE system privilege. • To specify resource limits for a user, you must: • Enable resource limits dynamically with the ALTER SYSTEM statement or with the initialization parameter RESOURCE_LIMIT. • This parameter does not apply to password resources. Password resources are always enabled. • Assign the profile to the user using the CREATE USER or ALTER USER statement

  45. CREATE PROFILR SYNTAX

  46. RESOURCES PARAMETERS

  47. RESOURCES PARAMETERS • SESSIONS_PER_USER : Specify the number of concurrent sessions . • CPU_PER_SESSION : Specify the CPU time limit for a session. • CPU_PER_CALL : Specify the CPU time limit for a call. • CONNECT_TIME : Specify the total elapsed time limit for a session. • IDLE_TIME :Specify the permitted periods of continuous inactive time during a session. • LOGICAL_READS_PER_SESSION : Specify the permitted number of data blocks read in a session including blocks read from memory and disk. • LOGICAL_READS_PER_CALL :Specify the permitted number of data blocks read for a call to process a SQL statement. • PRIVATE_SGA : Specify the amount of private space a session can allocate in the shared pool of the system global area (SGA).

  48. PASSWORD PARAMETERS • PASSWORD PARAMETERS : Parameters that set lengths of time are interpreted in number of days(minutes (n/1440) or seconds (n/86400)). • FAILED_LOGIN_ATTEMPTS:  Specify the number of failed attempts to log in to the user account before the account is locked. • PASSWORD_LIFE_TIME  : Specify the number of days the same password can be used for authentication. • PASSWORD_REUSE_TIME : Specifies the number of days before which a password cannot be reused. • PASSWORD_REUSE_MAX : Specifies the number of password changes required before the current password can be reused. • PASSWORD_LOCK_TIME  :Specify the number of days an account will be locked after the specified number of consecutive failed login attempts. • PASSWORD_GRACE_TIME  : Specify the number of days after the grace period begins during which a warning is issued and login is allowed.

  49. Unlocking a User Account andResetting the Password Select the user, and click Unlock User.

More Related