6.1 Profiles As we mentioned earlier, Oracle provides two very different types of profile: product profiles and system resource profiles. The two have no relationship to each other, but both establish limits or restrictions of some kind. Product profiles limit user access to certain Oracle commands or products, while system resource profiles limit a particular user 's use of certain system resources (e.g., sessions or CPU time). When people talk about "profiles," they are usually referring to system resource profiles. 6.1.1 Product Profiles With product profiles, you can block access to Oracle's SQL, SQL*Plus, and PL*SQL products. Any of the following can be blocked: -
One single command within any one of the product sets -
A group of commands within one or more of the product sets -
All of the commands within one or more of the product sets 6.1.1.1 PRODUCT_PROFILE and USER_PROFILE tables The PRODUCT_PROFILE and USER_PROFILE tables provide the mechanism used to block commands. You create these tables by running the PUPBLD.SQL script while connected as user system . The script is used to establish tool resource limits on the Oracle tools. Product functions blocked in this table are not table-specific, they are system-wide, so you should carefully consider the impact of using this feature. These PRODUCT_PROFILE and USER_PROFILE tables are not referenced directly by the users. The PUPBLD.SQL install script creates two views PRODUCT_PRIVS on the PRODUCT_PROFILE table, and USER_PRIVS on the USER_PROFILE table. This latter view limits the records retrieved to those pertaining only to the user executing the query. The PUPBLD.SQL script must be run by user system . If it has not been run, the tables will not exist. So when you log in with SQL*Plus, you will see a message before the "SQL>" prompt is displayed advising you that the script has not been created and telling you to run PUPBLD.SQL to build the table. The message appears because Oracle has attempted to verify, by looking in the PRODUCT_PROFILE table, that you are permitted to have access to the database via SQL*Plus. Since the table does not exist for Oracle to verify your access, an error message is generated but you will still be allowed to log in. The PUPBLD.SQL can be found in one of several different directories, depending on your version of Oracle and your operating system. Look in the $ORACLE_HOME/dbs directory first. If the script is not there, check the $ORACLE_HOME/plus80/demo directory or, for databases prior to Oracle8, check the $ORACLE_HOME/sqlplus/demo directory. 6.1.1.2 Disabling SQL privileges One of the characteristics of the SQL command processor is that it allows you to access the operating system by issuing either the HOST command or a symbolic equivalent. Depending on the operating system, the symbolic equivalent is usually $ or !. The point is that the user will be able to get to the operating system prompt. Now this is not generally a bad thing, but if there is no need for users to get to the operating system, the security requirements on the system administrator may be lessened if this access is disabled. For example, it may be that a particular application has files that must be accessible, but you don't want users to actually get to those files through the operating system. There are also users who love to save output files on disk; give these users access to the operating system and they will save everything forever! By making the appropriate entry in the PRODUCT_PROFILE table to block usage of the HOST command (which includes the symbolic equivalents), you can prohibit access to the operating system. This won't eliminate users' ability to save files, but it will reduce it. The SQL and SQL*Plus privileges and commands you can disable in the PRODUCT_PROFILE table are the following: - COPY
- EDIT
- EXECUTE
- EXIT
- GET
- HOST (or your operating system's alias for HOST, such as $ on OpenVMS and
- ! on UNIX)
- QUIT
- PASSWORD
- RUN
- SAVE
- SET (see the tip below)
- SPOOL
- START
 | If you disable the SQL*Plus SET command, the SQL SET ROLE and SET TRANSACTION commands will also be disabled. If you disable the SQL*Plus START command, the SQL*Plus @ and @@ commands will also be disabled. | | The following SQL commands can be disabled: - ALTER
- ANALYZE
- AUDIT
- CONNECT
- CREATE
- DELETE
- DROP
- GRANT
- INSERT
- LOCK
- NOAUDIT
- RENAME
- REVOKE
- SELECT
- SET ROLE
- SET TRANSACTION
- TRUNCATE
- UPDATE
The following PL/SQL commands can be disabled: - BEGIN
- DECLARE
- EXECUTE
| | If you disable the BEGIN and DECLARE commands through the PRODUCT_TABLE, the SQL*Plus EXECUTE command will not automatically be disabled. You must disable the EXECUTE command explicitly. | | 6.1.1.3 Using PRODUCT_PROFILE to enforce security As we discussed in Chapter 3, roles are used in a database to enhance security. In theory, you create a role, give it privileges on tables, and set the role when running an application. The user, when logging on to the database, will have minimal privileges directly through SQL*Plus. However, a user could use the SET ROLE command from SQL*Plus to gain access to privileges normally enabled only when the application is used. The user could then issue SQL statements from SQL*Plus to change database tables in a way detrimental to the security of the system. You could disable the SET ROLE privilege for specific users to prevent this. But remember, this is a system-wide effect. As indicated in the command list, the PRODUCT_PROFILE table can be used to disable the SET ROLE command. For each user whose access you want to block, an entry is made to the table listing that user's name in the USERID column, the word ROLES in the ATTRIBUTE column, and the specific role name to which you do not want to permit access in the CHAR_VALUE column. You can disable SQL*Plus access to a role for the entire user base by entering PUBLIC or "%" (the wild card symbol) in the USERID column. However, you should only use these values for roles granted to public . Disabling a role for a user who has not been granted that role will have no effect on the other roles the user holds. The columns within the PRODUCT_PROFILE table that are actually used by SQL and can be populated are: - PRODUCT
-
The product name, such as SQL*Plus or SQL*ReportWriter. Be sure to observe the case of the product name (upper/lower). - USERID
-
The name of the user to disable. If the wildcard (%) character or the value PUBLIC is used, then all users are blocked. - ATTRIBUTE
-
The product attribute to be disabled. If the product is SQL*Plus, any of the attributes from the previous list could be specified (e.g., ALTER, HOST, or GRANT). Only one attribute may be specified for each entry, so if these three were to be disabled, three records would be required. - CHAR_VALUE
-
To disable an attribute for a product, enter DISABLED. This must be in uppercase. Wildcards (%) are not allowed. The remaining columns, SCOPE, NUMERIC_VALUE, DATE_VALUE, and LONG_VALUE, are ignored by SQL, and may be omitted when adding rows to the table. To re-enable a privilege that has been disabled for a user, simply remove the associated entry from the PRODUCT_PROFILE table. 6.1.2 System Resource Profiles The second type of Oracle profile, the system resource profile, is a set of restrictions or limits that can be placed on a database resource for example, the amount of time a process can be idle before the process will be disconnected. Here is a sample command you might use to create a system resource profile for a particular user: CREATE PROFILE my_profile LIMIT SESSIONS_PER_USER 3 CPU_PER_CALL 3000 CONNECT_TIME 60 FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 1/12 In this example, we are creating a profile called "my_profile". The users to whom you assign this profile will have certain restrictions. They will only be allowed to establish a maximum of three sessions at one time. They can only use 3000 hundredths of a second per database transaction, and they can remain connected to the database for no longer than 60 minutes at a time. If they attempt to log on to their database account and fail more than three times in a row, they will be locked out of their account. They will remain locked out of their account for two hours (1/12 of a day). Once you have created the profile, you can assign it to a user when you create the user's account. Alternatively, in the case of an already-existing account, you can modify the user's account (via the ALTER command) and assign the profile. Each user can be assigned to a specific profile. Once a profile has been assigned to an account, that account cannot exceed the specified limits. A profile is named when it is created and may be used to control the usage of a variety of system resources. Table 6.1 shows the resource controls of particular interest from a security standpoint. Table 6.1. Security-Related Profile Paramters | Parameter | Characteristics | | CPU_PER_SESSION and CPU_PER_CALL | The volume of CPU time used, both for a session and for individual calls such as parse, execute, and fetch. Both types of CPU limits are expressed in hundredths of a second. | | CONNECT_TIME | The amount of total elapsed connection time an account may use, in minutes. | | FAILED_LOGIN_ATTEMPTS | The number of failed login attempts before an account is locked. | | IDLE_TIME | The amount of allowable idle time before a process is disconnected. This value is expressed in minutes. Long-running queries and other operations are not subject to this limit since they are not actually viewed as idle. | | PASSWORD_GRACE_TIME | The number of days after the grace period has begun during which a warning will be given that the password is going to expire. | | PASSWORD_LIFE_TIME | The number of days the same password may be used before connections will be rejected. | | PASSWORD_LOCK_TIME | The number of days an account will be locked after the specified number of failed login attempts. | | PASSWORD_REUSE_MAX | An integer value for the number of password changes before a password can be reused. If you set PASSWORD_REUSE_MAX to a value, PASSWORD_REUSE_TIME must be set to "UNLIMITED." | | PASSWORD_REUSE_TIME | The number of days before a password can be reused. | | PASSWORD_VERIFY_FUNCTION | The complexity of a password; you must specify the name of a script to be called. | | SESSIONS_PER_USER | The maximum number of allowable concurrent sessions on a per-user basis. | These password parameters are discussed in more detail later in this chapter. There are several other possible resource control parameters that can be set for a user profile. Although these do not have as important an impact on security, they are listed in Table 6.2 for completeness. Table 6.2. Remaining Profile Parameters | Parameter | Characteristics | | COMPOSITE_LIMIT | Limits the total resources cost for a session in "service units" calculated by Oracle as a weighted sum of CPU_PER_SESSION + CONNECT_TIME + LOGICAL_READS_PER_SESSION + PRIVATE_SGA. | | LOGICAL_READS_PER_CALL | An integer value (in blocks) of the number of data blocks read for a call to process a SQL statement (parse, execute, or fetch). | | LOGICAL_READS_PER_SESSION | An integer value (in blocks) of the number of data blocks that can be read from memory and disk in a session. | | PRIVATE_SGA | If you are using the multi-threaded server, this is used to limit the amount of private space a session can allocate in the shared pool in integer bytes. (You can express the value in "K" or "M" for kilobytes or megabytes.) | | UNLIMITED | Allows a user unlimited resources on a database. | | DEFAULT | Assigns the limits set in the DEFAULT profile to the specified user. | | | To create a profile, you must have the CREATE PROFILE system privilege granted to your account. | | Where a parameter is specified in "number of days," you may use a fraction of a day expressed in the form x/y. For example, to specify one hour , you would use the form 1/24. Likewise, a minute would be expressed as 1/1440, and a second as 1/86400 of a day. In the case of CPU session restrictions, if a user exceeds the limit set for CONNECT_TIME or IDLE_TIME, Oracle rolls back the current transaction and ends the session. However, the user does not realize the session has been ended because no notification is given until she issues a call. When the next call is issued from the affected session, Oracle will return an error message. For other resource limits which are exceeded, Oracle aborts the operation, rolls back the current statement, and immediately returns an error message. At the point that an error message is received, the user has the choice of committing or rolling back the current transaction. Under these circumstances, after the user has issued either a COMMIT or a ROLLBACK statement, she must end the session. If a single call has resource limits placed on it and the user attempts to exceed those limits, Oracle aborts the operation, rolls back the current statement, and returns an error message. In this case, the current transaction is left intact. In order to enable the ability to set resource limits for a user, you must set the parameter RESOURCE_LIMIT equal to TRUE. You can do this either: -
By editing the INIT.ORA parameter file for the specific database in which you want to activate resource limits; you must set the parameter RESOURCE_LIMIT = TRUE. -
By issuing the following dynamic SQL statement: ALTER SYSTEM SET RESOURCE_LIMIT = TRUE; If you modify the INIT.ORA file, in order to have the change take effect you must shut down and restart the database. If you dynamically ALTER SYSTEM to set the RESOURCE_LIMIT to "TRUE," the change occurs immediately. 6.1.2.1 The DEFAULT profile Oracle supplies a default profile, which is automatically imposed on each user who does not have a customized profile defined. You can display the default profile by examining the DBA_PROFILES view; Chapter 4 introduces this view and describes its columns. For an Oracle version 7.3 database, DBA_PROFILES looks like this: SQL> SET PAGES 999 SQL> COLUMN profile format a9 SQL> COLUMN limit format a10 SQL> SELECT * 2 FROM dba_profiles; PROFILE RESOURCE_NAME LIMIT --------- -------------------------------- ---------- DEFAULT COMPOSITE_LIMIT UNLIMITED DEFAULT SESSIONS_PER_USER UNLIMITED DEFAULT CPU_PER_SESSION UNLIMITED DEFAULT CPU_PER_CALL UNLIMITED DEFAULT LOGICAL_READS_PER_SESSION UNLIMITED DEFAULT LOGICAL_READS_PER_CALL UNLIMITED DEFAULT IDLE_TIME UNLIMITED DEFAULT CONNECT_TIME UNLIMITED DEFAULT PRIVATE_SGA UNLIMITED 9 rows selected. | | Note the absence of any password parameters in the default profile for version 7.3. Password parameters are not available until Oracle8. | | The default profile for an Oracle version 8.0 database looks like this (Oracle8 additional parameters are emphasized in bold ): SQL> COLUMN profile format a9 SQL> COLUMN limit format a9 SQL> SELECT * 2 FROM dba_profiles; 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 UNLIMITED DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED DEFAULT IDLE_TIME KERNEL UNLIMITED DEFAULT CONNECT_TIME KERNEL UNLIMITED DEFAULT PRIVATE_SGA KERNEL UNLIMITED DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD UNLIMITED DEFAULT PASSWORD_LOCK_TIME PASSWORD UNLIMITED DEFAULT PASSWORD_GRACE_TIME PASSWORD UNLIMITED 16 rows selected. 6.1.2.2 Imposing limits on a user To impose resource limits on a specific user, you must create a profile in which you define the resource restrictions and then assign the profile to the user. For any parameters not defined in the customized profile, the values present in the DEFAULT profile will be used. If you want to be complete and explicitly define all of the parameters within your customized profile, you can specify the DEFAULT keyword to request the DEFAULT profile values. Consider the following example of creating a user profile: svrmgr> CREATE PROFILE std_user LIMIT SESSIONS_PER_USER 3 CPU_PER_SESSION UNLIMITED IDLE_TIME 30 FAILED_LOGIN_ATTEMPTS 5 LOGICAL_READS_PER_SESSION DEFAULT PASSWORD_LOCK_TIME 1/24 PASSWORD_LIFE_TIME 90 PASSWORD_REUSE_TIME 60 PASSWORD_VERIFY_FUNCTION my_password_funct PASSWORD_GRACE_TIME 15; In this example, any user who is assigned "std_user" may not have more than three concurrent sessions. The CPU time is not limited, but each session cannot be idle for more than 30 minutes without being disconnected. The LOGICAL_READS_PER_SESSION will default to the value defined in the DEFAULT profile. After five unsuccessful attempts at logging in, the account will be locked. After one hour (1/24 day), the account will be unlocked automatically if it has been locked by failed attempts. The password will expire after 90 days, and there will be a 15-day grace period starting from the time at which the account is accessed after the 89th day. The same password cannot be used again within 60 days. The password complexity is established through the DBA-created function "my_password_funct," which we'll discuss later in this chapter. In the following example, we see how the profile created in the last example is assigned to the user mary : SVRMGR> CREATE USER mary IDENTIFIED BY abc75!d DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp PASSWORD expire PROFILE std_user In this example, the user mary is created with a default password in keeping with the requirements to make the password at least four characters long with at least one numeric and one punctuation character. The password is set to pre-expire, and the password profile created in the previous example will be in effect for the user mary . In the next section, we'll examine in much more detail the new password parameters used to control password management in an Oracle8 database. | 
