HomeOracle Page 2 - Profiles, Password and Resource Control for Oracle Database XE
Profiles and Resource Control - Oracle
In this fifth part of a ten-part series on securing Oracle Database XE, you'll learn about profiles as they pertain to password and resource control. This article is excerpted from chapter 31 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).
The list of resource-control profile options that can appear afterCREATE PROFILE profilename LIMITare explained in Table 31-4. Each of these parameters can either be an integer,UNLIMITED, orDEFAULT.
Table 31-4. Resource-Related Profile Parameters
Maximum number of sessions a user can simultaneously have
Maximum CPU time allowed per session, in hundredths of a second
Maximum CPU time for a statement parse, execute, or fetch operation, in hundredths of a second
Maximum total elapsed time, in minutes
Maximum continuous inactive time in a session, in minutes, while a query or other operation is not in progress
Total number of data blocks read per session, either from memory or disk
Maximum number of data blocks read for a statement parse, execute, or fetch operation
Total resource cost, in service units, computed as a composite weighted sum of CPU_PER_SESSION, CONNECT_TIME, LOGICAL_ READS_PER_SESSION, and PRIVATE_SGA
Maximum amount of memory a session can allocate in the shared pool, in bytes, kilobytes, or megabytes
As with the password-related parameters,UNLIMITEDmeans that there is no bound on how much of the given resource can be used.DEFAULTmeans that this parameter takes its values from theDEFAULTprofile.
TheCOMPOSITE_LIMITparameter allows you to control a group of resource limits when the types of resources typically used varies widely by type; it allows a user to use a lot of CPU time but not much disk I/O during one session, and vice versa, during another session, without being disconnected by the policy.
To adjust the resource cost weights, use theALTER RESOURCE COSTcommand. In this example, we change the weightings so thatCPU_PER_SESSIONfavors CPU usage over connect time by a factor of 25 to 1; in other words, a user will be disconnected more likely because of CPU usage than because of connect time:
SQL> alter resource cost 2 cpu_per_session 50 3 connect_time 2; Resource cost altered. SQL> select * from resource_cost;
In Table 31-5, we provide some examples of resource usage to see if the composite limit of 250 is exceeded.
Table 31-5. Resource Usage Scenarios
(50 * 5) + (2 * 100) = 450
(50 * 2) + (2 * 30) = 160
(50 * 1) + (2 * 150) = 350
(50 * 2) + (2 * 5) = 110
The parametersPRIVATE_SGAandLOGICAL_READS_PER_SESSIONare not used in this particular example, so unless they are specified otherwise in the profile definition, they default to whatever their value is in theDEFAULTprofile. The goal of using composite limits is to give users some leeway in the types of queries or DML they run. On some days, they may run a lot of queries that perform numerous calculations but donít access a lot of table rows; on other days, they may do a lot of full table scans but donít stay connected very long. In these situations, you donít want to limit a user by a single parameter, but instead by total resource usage weighted by the availability of each resource on the server.
Please check back next week for the next part of this series.