 |
|
Oracle Concepts -
Export/Import Roles
Oracle Tips by Burleson Consulting |
Export/Import Roles
Oracle provides a script entitled catexp.sql for roles.
This script creates the EXP_FULL_DATABASE and IMP_FULL_DATABASE roles.
You can grant these to a user who will be executing the export and
import utilities.
The EXP_FULL_DATABASE role has the SELECT ANY
TABLE and BACKUP ANY TABLE system privileges. In addition, this role
has INSERT, DELETE, and UPDATE privileges on the SYS.INCVID,
SYS.INCFIL, and SYS.INCEXP tables.
The IMP_FULL_DATABASE role has the BECOME USER
system privilege.
Using PROFILES
You can set up limits on the system resources
used by setting up profiles with defined limits on resources.
Profiles are very useful in large, complex organizations with many
users. It allows you to regulate the amount of resources used by
each database user by creating and assigning profiles to users. Using
Oracle8 password attributes where added into profiles as well.
Creation of PROFILES
Profiles are a named set of resource limits.
By default, when you create a user, they are given the default
profile. The default profile provides unlimited use of all
resources.
The syntax to create a profile follows:
>---CREATE
PROFILE profile LIMIT resource_parameters|password_parameters--;->
Resource_parameters (you can specify multiple paramters per command):
[SESSIONS_PER_USER n|UNLIMITED|DEFAULT]
[CPU_PER_SESSION n|UNLIMITED|DEFAULT]
[CPU_PER_CALL n|UNLIMITED|DEFAULT]
[CONNECT_TIME
n|UNLIMITED|DEFAULT]
[IDLE_TIME
n|UNLIMITED|DEFAULT]
[LOGICAL_READS_PER_SESSION n|UNLIMITED|DEFAULT]
[LOGICAL_READS_PER_CALL n|UNLIMITED|DEFAULT]
[COMPOSITE_LIMIT
n|UNLIMITED|DEFAULT]
[PRIVATE_SGA
n [K|M]|UNLIMITED|DEFAULT]
Password_parameters (Oracle8 and above):
[FAILED_LOGIN_ATTEMPTS expr|UNLIMITED|DEFAULT]
[PASSWORD_LIFE_TIME expr|UNLIMITED|DEFAULT]
[PASSWORD_REUSE_TIME expr|UNLIMITED|DEFAULT]
[PASSWORD_REUSE_MAX expr|UNLIMITED|DEFAULT]
[PASSWORD_LOCK_TIME expr|UNLIMITED|DEFAULT]
[PASSWORD_GRACE_TIME expr|UNLIMITED|DEFAULT]
[PASSWORD_VERIFY_FUNCTION function_name|NULL|DEFAULT]
Restrictions on password parameters:
* Expr must resolve to either an integer value
or an integer number of days
* If PASSWORD_REUSE_TIME is set to an integer
value, PASSWORD_REUSE_MAX must be set to UNLIMITED.
* If PASSWORD_REUSE_MAX is set to an integer
value, PASSWORD_REUSE_TIME must be set to UNLIMITED.
* If both PASSWORD_REUSE_TIME and
PASSWORD_REUSE_MAX are set to UNLIMITED, then Oracle uses neither of
these password resources.
* If PASSWORD_REUSE_MAX is set to DEFAULT and
PASSWORD_REUSE_TIME is set to UNLIMITED, then Oracle uses the
PASSWORD_REUSE_MAX value defined in the DEFAULT profile.
* If PASSWORD_REUSE_TIME is set to DEFAULT and
PASSWORD_REUSE_MAX is set to UNLIMITED, then Oracle uses the
PASSWORD_REUSE_TIME value defined in the DEFAULT profile.
* If both PASSWORD_REUSE_TIME and
PASSWORD_REUSE_MAX are set to DEFAULT, then Oracle uses whichever
value is defined in the DEFAULT profile.
For example:
CREATE
PROFILE enduser LIMIT
CPU_PER_SESSION
60000
LOGICAL_READS_PER_SESSION 1000
CONNECT_TIME
30
PRIVATE_SGA
102400
CPU_PER_CALL
UNLIMITED
COMPOSITE LIMIT
60000000
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LIFE_TIME
90
PASSWORD_REUSE_TIME
180
PASSWORD_LOCK_TIME
3
PASSWORD_GRACE_TIME 3
Verify_function_one ;
You can assign a profile to a user when you
create the user or by altering the user. The syntax to alter the
profile for a user is:
ALTER USER
PROFILE profile;
For example:
ALTER USER
scott
PROFILE appuser;
You must have the CREATE PROFILE system
privilege to create a profile. To alter a profile you must be
the creator of the profile or have the ALTER PROFILE system privilege.
To assign a profile to a user, you must have the CREATE USER or ALTER
USER system privilege.
Profiles and Resource Limits
The default cost assigned to a resource is
unlimited. By setting resource limits, you can prevent users
from performing operations that will tie up the system and prevent
other users from performing operations. You can use resource
limits for security to ensure that users log off the system and do not
leave the session connected for long periods of time. You can also
assign a composite cost to each profile . The system resource limits
can be enforced at the session level, the call level or both.
The session level is from the time the user
logs into the database until the user exits. The call level is
for each SQL command issued. Session level limits are enforced for
each connection. When a session level limit is exceeded, only
the last SQL command issued is rolled back and no further work can be
performed until a commit, rollback or exit is performed. Table 7.15
lists the system resources which can be regulated at the session
level.
One thing to note if you use parallel query
option (PQO) is that the resources are applied to each new session,
not accumulated over all of the sessions that a parallel operation
uses.
TABLE 7.15: RESOURCES REGULATED AT THE
SESSION LEVEL
SYSTEM RESOURCE |
DEFINITION |
CPU_PER_SESSION |
total CPU time in hundreds of seconds |
SESSIONS_PER_USER |
number of concurrent sessions for a user |
CONNECT_TIME |
allowed connection time in minutes |
IDLE_TIME |
inactive time on the server in minutes |
LOGICAL_READS_PER_SESSION |
number of data blocks read including both
physical and logical reads from memory and disk |
PRIVATE_SGA |
bytes of SGA used in a database with the
multithreaded server (in K or M) |
You can combine the CPU_PER_SESSION,
LOGICAL_READS_PER_SESSION, CONNECT_TIME, and PRIVATE_SGA to create a
COMPOSITE LIMIT.
Call-level limits are enforced during the
execution of each SQL statement. When a call-level limit is
exceeded, the last SQL command issued is rolled back. All the
previous statements issued are still valid and the user can continue
to execute other SQL statements. The following system resources
can be regulated at the call level:
* CPU_PER_CALL for the CPU time for the SQL
statement
* LOGICAL_READS_PER_CALL for the number of data blocks read for the
SQL statement
The assignment of a cost to a resource can be
performed with the ALTER RESOURCE COST command. Resource limits that
you set explicitly for a user take precedence over the resource costs
in an assigned profile. The command line syntax for this command
is:
>-ALTER
RESOURCE COST ---------------------------------------------------;-->
|-[CPU_PER_SESSION n|UNLIMITED|DEFAULT]-----------|
|-[CONNECT_TIME n|UNLIMITED|DEFAULT]--------------|
|-[LOGICAL_READS_PER_SESSION n|UNLIMITED|DEFAULT]-|
|-[PRIVATE_SGA n [K|M]|UNLIMITED|DEFAULT]--------|
For example,
ALTER
RESOURCE COST CONNECT_TIME 100;
Use of resource limits is set in the database
initialization parameter RESOURCE_LIMIT=TRUE. By default this
parameter is set to false. This parameter can be changed
interactively with an ALTER SYSTEM command.
The DBA_PROFILES view provides information on
all the profiles and the resource limits for each profile. The
RESOURCE_COST view shows the unit cost associated with each resource.
Each user can find information on his resources and limits in the
USER_RESOURCE_LIMITS view.
Table 7.16 gives a description of these data
dictionary views.
Table 7.16: Data dictionary views for
resources.
DBA_PROFILES
Column |
Definition |
Profile |
the name given to the profile |
Resource_name |
the name of the resource assigned to the
profile |
Limit |
the limit placed on the profile |
RESOURCE_COST
Column |
Definition |
Resource_name |
name of the resource |
Unit_cost |
cost assigned |
USER_RESOURCE_LIMITS
Column |
Definition |
Resource_name |
the name of the resource |
Limit |
the limit placed on the user |
This is an excerpt from
the eBook "Oracle
DBA made Simple".
 |
For complete Oracle utilities tips and tricks, see the book "Advanced
Oracle Utilities" by Dr. Bert Scalzo. You can buy it direct
from the publisher for 30%-off and get instant access to the code depot of
Oracle tuning scripts.
|
|