Oracle DB Profile ____oracle

1. Profiles and Users   Only one profile can be assigned to the user at a time. Profile: • Controlling resource consumption • Managing account status and password expiration   Note: The profile can enforce resource limits only if Resource_limit is set to true. The         profile is a set of named resource restrictions used to restrict database usage and instance resources. The profile also allows you to manage account status and limit the user's password (length, expiration time, and so on). Each user is assigned a profile, and the user belongs to only one profile at a specified time. If the user is logged on when the user profile is changed, the changes will take effect only the next time the user logs on. The default profile is used as the basis for all other profiles. As shown in the figure above, you can implicitly specify the limitations of the profile, as in "Cpu/session (cpu/session)", you can remove the restriction, such as "Cpu/call (cpu/Call)", or you can refer to any setting in the default profile, such as "Connect Time (connection times). You cannot force a user's resource limit through a profile unless the Resource_limit initialization parameter is set to True. If Resource_limit uses the default value false, the profile resource limit is ignored. Always enforce profile password settings.   Administrators use profiles to control the following system resources:   CPU: CPU resources can be limited by session or call. Restricting "Cpu/session (cpu/session)" to 1,000 means that if any session using this profile consumes more than 10 seconds of CPU time (CPU time limit in 1% seconds), the session receives an error message and is logged out: ORA-02392: Exceeded session limit on CPU usage, your are being logged off each invocation restriction is the same, but it does not restrict the user's entire conversation, but prevents any command from consuming too much CPU. If the Cpu/call (cpu/call) is restricted and the user exceeds the limit, the command is aborted. The user will receive an error message such as the ora-02393:exceeded call limit on CPU usage     network/Memory: Each database session consumes both system memory resources and network resources (if the session is from a non-local user of the server) 。 You can specify the following parameters:-Connection time: Indicates that the user can be insured before automatic logoffThe number of minutes to hold the connection. -Idle time: Indicates the number of minutes that a user's session can remain idle before automatically logging off. Only the idle time of the server process is calculated. No application activity is considered in idle time. Idle_time restrictions are not affected by long-running queries and other operations. -Parallel Session: Indicates how many parallel sessions can be created using a database user account. -Dedicated SGA: limits the amount of space taken up by operations such as sorting, merging bitmaps, and so on in the system global Zone (SGA). This limit is valid only if the session is using a shared server. • Disk I/O: Limits the amount of data that users can read at each session level or at each call level.     read/session and read/call can limit the total number of memory and disk reads. Doing so ensures that statements that perform a large number of I/O operations do not overuse memory and disk. Combination restrictions can also be provided through the profile. The combination limit is based on a weighted combination of cpu/session, read/session, connection time, and dedicated SGA. To create a profile, click the Server tab, and then under the security (Secure) heading, click Profiles (Profile). On the Profiles (profile) page, click the Create button.       NOTE: The resource manager can override many profile settings.2. Implement password security function Oracle password management is implemented by using user profiles. Profiles can provide many standard security features. Note: Do not use profiles that cause sys, Sysman, and Dbsnmp password failures and the corresponding accounts to be locked.     View the configuration of the default profile. Sys@test0924> SELECT * from Dba_profiles where profile= ' DEFAULT '; Profile resource_name RESOURCE LIMIT----------------------------------------------------------------------------- ---------------------------------default Composite_limit KERNEL Unlimited default sessions_per_user KERNEL Unlimited Default cpu_per_session KERNEL Unlimited default cpu_per_call KERNEL unlimited DEFAULT logical_reads_per_session KERNEL U nlimited default Logical_reads_per_call KERNEL Unlimited default idle_time KERNEL Unlimited default connect_time KERNEL UN LIMITED default PRIVATE_SGA KERNEL Unlimited default failed_login_attempts PASSWORD ten DEFAULT password_life_time passwor D 180 DEFAULT password_reuse_time PASSWORD Unlimited default Password_reuse_max PASSWORD unlimited DEFAULT password_verif Y_function PASSWORD NULL default password_lock_time PASSWORD 1 DEFAULT password_grace_tiME PASSWORD 7 rows selected. Account lockout:If a user attempts to log on to the system within a specified number of times, the system automatically locks the account failed_login_attempts the duration of the setting: Specifies the number of failed attempts to log on before the account is locked password_lock_ Time: Specifies the number of days to lock an account after a specified number of failed logon attemptsPassword expiration and expiration:The use of the account order has a lifetime, the password expires after this lifetime, and must be changed. password_life_time: Determines the password lifetime (days), after which the password is invalidated password_grace_time: Specifies the grace period (days) for the first successful logon to the password that must be changed, and then the password expires. Note: Locking the SYS, Sysman, and DBSNMP accounts and invalidating their passwords can prevent the enterprise Manager from running properly. The application must catch the password failure warning message and handle the password change, otherwise the user will be locked out without notifying the cause when the grace period expires.     password history: By checking the new password, you can ensure that passwords are not reused within a specified amount of time or for a specified number of password changes. Check by using one of the following: password_reuse_time: Specifies that users cannot reuse passwords within a specified number of days Password_reuse_max: Specify the number of password changes that need to be reached before the current password can be reused Note: The value of the profile parameter is set in the default profile or inherited from the profile file. If two password history parameters have value Unlimited, Oracle DB ignores both. Users can reuse any password at any time, but this is not an effective security practice. If all two parameters are set, the password is allowed to be reused, but both conditions must be met: The user must change the password for a specified number of times, and the specified number of days has elapsed since the last time the old password was used. For example, suppose the Password_reuse_max setting in the user Alfred profile is set to 10,password_reuse_time set to 30, then the user Alfred only has reset the password 10 times, and The password cannot be reused until it has been 30 days since the last time the password was used. If a parameter is set to a number and another parameter is specified as unlimited, the user cannot reuse the password.     Password Complexity validation: Checks the password for complexity to verify that the password conforms to a specific rule. This check must ensure that the password is complex enough to prevent intruders from attempting to break into the system by guessing the password. The password_verify_function parameter specifies a pl/sql function that performs password complexity checking before assigning a password. The password validation function must be owned by the SYS user and must return a Boolean value (True or false). The Utlpwdmg.sql script, located in the following directory, provides model password validation functions: UNIX and Linux Platform: $ORACLE _home/rdbms/admin Windows platform:%oracle_home%\rdbms\admin 3. Create Password Profile   To create a password profile, click the Server tab, and then under the Security heading, click Profiles (Profile). On the Profiles (profile) page, click the Create button. Click the Password tab to set the password limit.     You can select the common values for each setting from the list of values (click the flashlight icon to browse), or you can enter custom values. All periods are represented by "days", but can also be expressed as fractions. There are 1,440 minutes in a day, so 5/1,440 is 5 minutes.   Use enterprise Manager to edit an existing password profile.   Use SQL to create profile as follows: sys@test0924> Create profile Grace_5 limit 2 failed_login_attempts 3 3 password_lock_time Unli Mited 4 password_reuse_time 5 password_life_time 6 Password_grace_time 5; Profile created. You can also use ALTER profile to modify: sys@test0924> alter profile grace_5 limit 2 failed_login_attempts 2 3 password_life_time 4 PAS Sword_grace_time 7 5/profile altered.   If the Utlpwdmg.sql script is already running, the verify_function and VERIFY_FUNCTION_11G functions will be available. If you created your own complexity function, you can enter the name of the function. The function name does not appear in the select list. If the function generates a run-time error, the user cannot change the password. Delete Password profile   in Enterprise Manager, you cannot delete profiles used by users. However, if you delete a profile with the CASCADE option (for example, in Sql*plus), the default profile is automatically assigned to all users who have the profile.  4, provides the password Authentication function: verify_function_11g The Oracle server provides two password complexity validation functions named Verify_function and verify_function_11g respectively.   These functions are created using the <oracle_home>/rdbms/admin/utlpwdmg.sql script. Verify_function is provided for users who prefer the password functions provided by previous versions. The password complexity validation function must be created in the SYS scheme.   This function can be used as a template for custom password validation. In addition to creating verify_function, the following alter Profile command is used in the UTLPWDMG script to change the default profile: Alter profiles default LIMIT Password_life_time 180 Password_grace_time 7 Password_reuse_time Unlimited Password_reuse_max Unlimited FAILED_LOGIN_ATTEMPTS Lock_time 1 password_verify_function verify_function_11g; Note that when you create a user, the default profile is assigned to the user unless you specify another profile.5. Allocate the quota to the user A limit is a space that allows you to specify a table space. By default, no table space user has a quota. Use the following three options to provide a table space limit for the user. • Unrestricted: Allows users to maximize the use of free space in the tablespace. • Value: The space (in kilobytes or megabytes) that the user can use. This does not guarantee that the space will be reserved for the user. Because this value may be greater than or less than the current free space in the table space. Unlimited tablespace system permissions: Overrides all individual table space quotas, providing unlimited quotas for all tablespaces (including system and Sysaux).   You must be cautious when granting this permission.   Note that this permission is included when granting the resource role. You must not provide a limit to the user for system or Sysaux tablespace. In general, only the SYS and system users can create objects in the system or Sysaux table space. You do not need to have a quota for the allocated temporary table space or any restore table space. • When a user creates or extends a segment, the Oracle instance checks the limit. • For activities that are assigned to a user scenario, only those activities that use space in the tablespace are accounting for the limit. Activities that do not use space in the allocated table space, such as creating a view or using temporary table space, do not affect the quota. • The limit is added when you delete a user-owned object by using the Purge clause, or when you automatically clear the object owned by the user in the Recycle Bin.6. Apply the principle of least privilege • Protect data dictionary: O7_dictionary_accessibility=false• Revoke unnecessary public permissions. • Use Access control lists (ACLs) to control network access. • Restrict the directories that users can access. • Restrict users with administrative privileges.• Restricting remote database authentication: Remote_os_authent=false Applying the least privilege principle means that users must be granted only the permissions required to perform tasks efficiently. This reduces the chance that users will modify or view (unintentionally or maliciously) the data they have no right to modify or view.Protect data dictionary:By default,the o7_dictionary_accessibility parameter is set to False。 If there is no good reason, be sure not to change this setting,This prevents users with any table system permissions from accessing the base table of the data dictionary. This parameter setting also ensures that the SYS user can log on only as SYSDBA. Revoke unnecessary public permissions: There are multiple packages that are useful for applications that require these packages, but require the proper configuration to safely use them. Public is granted Execute permissions on the following packages: Utl_smtp, utl_tcp, Utl_http, and Utl_file. In Oracle Database 11g, network access is controlled by access control lists (ACLs), which can be configured to allow certain users to access specific network services. By default, access to the network is denied. To allow access to the network, ACLs must be created. Access to files through Utl_file can be controlled at two levels: permissions to use files and directories at the operating system level, and directory objects in the database to allow access to specific file system directories. You can grant users read or write access to a directory object. You should carefully control the execution permissions on other pl/sql packages.Some of the more powerful packages may be misused, including: UTL_SMTP: Allows the database to be used as a Simple Mail Transfer Protocol (SMTP) mail server to send arbitrary e-mail messages. Use ACLs to control which users have access to which computers. utl_tcp: Allows the database server to establish an outgoing network connection with any network service that is receiving or waiting. Therefore, arbitrary data is sent between the database server and any waiting network services. Use ACLs to control access rights. utl_http: Allows the database server to request and retrieve data through HTTP. If you grant this package to a user, you may be allowed to send the data to a malicious Web site in HTML format. You can use ACLs to restrict access permissions. utl_file: If improperly configured, it may allow access to any text file on the host operating system. When properly configured, this package restricts users to access only to a specific directory location.   Restricting access to the operating system directory: Through directory objects in the database, DBAs can map directories to the operating system path, thereby granting permissions to individual users of these directories. Restrict users with administrative rights: Do not provide unnecessary permissions to database users. You must not grant a DBA role to a non-administrator. To enforce the least privilege, restrict the permissions granted to systems and objects • The SYS permissions attached to the database, such as SYSDBA and Sysoper Other DBA types, such as drop any TABLETo restrict remote database authentication:By default,the Remote_os_authent parameter is set to False. Be sure not to change this setting unless you are sure that all clients can properly authenticate the user. After the introduction of secure external password storage (provided in Oracle Database 10g Release 2), there are compelling reasons to allow remote operating system validation. During the remote authentication process: • Database users authenticate by external means • Remote system authentication User • User logon database, do not have to accept additional validation Note: If you revoke some permissions, be sure to test the application thoroughly. 7. Protect the Authorized accountAuthorized accounts are protected by: • Password-case-sensitive password files • Enable strict authentication method settings for the Administrator role the database administrator verifies that users with SYSDBA, Sysoper, or sysasm permissions must be authenticated at all times. When you connect locally, verify that the user is a member of the authorized operating system group through the local operating system. If it is a remote connection, the password file is used to authenticate the authorized user. If the password file is configured, the file is checked first. In Oracle Database 11g, these passwords are case-sensitive. Oracle Database 11g provides a number of other ways to enable remote administrators to authenticate more securely and centrally manage these authorized users. Password files are case-sensitive when you use the database Configuration Assistant to create databases. If you are upgrading from an earlier database version, make sure that the password file used for remote connections is case-sensitive: orapwd FILE=ORAPWORCL entries=5 ignorecase=n It is difficult to worry about a password file being compromised or to maintain many password files. You can implement strict validation.   If you want to use a rigorous authentication method, you need the Advanced security option. Related views: Dba_users Dba_profiles