Release 1.6
A63731-01
Library |
Product |
Contents |
Index |
| Oracle
Enterprise Manager Administrator's Guide
Release 1.6 A63731-01 |
|
This chapter describes how to use Security Manager to control database security. With Security Manager, you can manage users, roles, and profiles. This chapter assumes that you have read Chapter 7, "Overview of the Database Tools" and are familiar with the interface elements of the database tools. The topics in this chapter are:
To start Security Manager, click the Security icon in the Launch Palette or choose Security Manager from the Console Tools menu.
You can change the database connection with the Change Database
Connection option in the File menu. For more information, see Application
Menus on page 7-10.
After Security Manager has successfully connected to a database,
the Users, Roles, and Profiles folders display in a tree list on the left
side of the Security window. These folders are located in the database
folder which displays the name of the database that the application is
connected to.
The display on the right side of the window is determined
by the object selected on the left side of the screen. The right side may
contain a multi-column list, property sheet, or other information. An example
of a Security Manager window is shown in Figure
9-1, "Security Manager".
Refer to the following sections:
Security Manager includes the standard menus, File, View,
Log, and Help, plus the User, Profile, and Role menus. The options for
each of these menus are described in this chapter. For information on the
standard menus, see Application Menus
on page 7-10.
Context-sensitive menus may also be active when you press
the right mouse button to select a specific object from the tree list or
the multi-column list. This feature provides quick access to a subset of
the menu options provided in the menu bars.
The User menu contains the following menu options:
Creates a new user.
Creates a new user based on the selected user in the tree list.
Deletes the selected user from the tree list.
Removes a selected privilege or role.
Displays database objects that rely on a selected user and any objects that the selected user relies on.
Adds multiple privileges to one or more users.
Unlock: Unlocks the user's account and enables access
to the account.
Lock: Locks the user's account and disables access
to the account.
Expire: Expires the user password.
The Role menu contains the following menu options:
Creates a new role.
Creates a new role based on the selected role.
Deletes the selected role.
Removes a privilege or role from a role.
Displays database objects that rely on a selected role and any objects that the selected role relies on.
Adds privileges or roles to roles.
Note: These menu options are enabled depending on
the object selected.
The Profile menu contains the following menu options:
Creates a new profile.
Creates a new profile that is based on the selected profile.
Deletes the selected profile.
Displays database objects that rely on a selected profile and any objects that the selected profile relies on.
Assigns a profile to a specific user.
Note: These menu options are enabled depending on
the object selected in the tree list.
The objects in the tree list are identified by various icons. In the listing:
Roles, Object Privileges, and System Privileges icons appear
with a key overlay if these objects have been granted using the Admin option/Grant
option.
The User object type folder contains information about the
users in the database arranged alphabetically in a tree structure. An individual
user can be expanded to show the roles, system privileges, and object privileges
granted to the user.
When you select:
For more information about users, see the Oracle Server
Concepts, the Oracle Server Administrator's Guide, and the Oracle
Server SQL Reference.
A Users multi-column list displays when a User folder is
selected in the tree list. The list contains a row of summary information
for each of the users in the Users folder.
If you select an individual User icon, and that icon is also
on the main branch of the Database folder, the columns of the multi-column
list summarize all information from the General page of the Create User
property sheet. For more information on these columns, see the description
of the Create User property sheet in Creating
a User on page 9-8.
Suggestion: If a multi-column list is wider than the
its window display area, you can increase the viewing area by resizing
the application window or dragging the splitter between the left and right
sections of the window.
To create a new user:
The Create User property sheet consists of the following pages when in Advanced mode:
The General page allows you to specify a user's name (when creating a new user), their default profile, authentication method, and the default tablespace. The Create User property sheet contains the following:
The name of the user to be created. Enter the name of the new user. The username can only contain characters from your database character set and can be at most 30 bytes long.
The profile assigned to the user. Use the drop-down list to choose the profile you want to assign to the user. The DEFAULT profile is assigned if you do not make a selection.
The method Oracle uses to authenticate the user.
Global: Specifies that the user be identified globally
amongst multiple databases. The global authorization option is only available
with Oracle 8 databases.
External: Specifies that the operating system verify
the user.
Password: Specifies that a password be required for
login. Enter the password in the adjacent text entry field. Enter the password
again in the Confirm text entry field for verification.
Expire Now: Forces the user's password to expire immediately. If you create a new user with this option selected, the user's password must be changed during the first attempted login. This feature is available for Oracle8 databases only.
The user's default and temporary tablespaces.
Default: Use the drop-down list to choose the default
tablespace for user-created objects.
Temporary: Use the drop-down list to choose the tablespace for the user's temporary segments.
Active status of the user's account.
Lock: Locks the user's account and prevents further
access.
Unlock: Unlocks the user's account and enables access
to the account.
On the Privileges page of the Create User property sheet, you can specify the system privileges and roles assigned to the user. The Privileges page contains the following:
A drop-down list containing System Privileges and Roles. Your selection in the drop-down list determines what is displayed on the rest of the page.
List of available roles or system privileges available for
assignment to a user.
Roles: If you selected Roles as the privilege
type, the roles that you are allowed grant to a user display in a scrolling
list. These are roles you have created and roles you have been granted
with the Admin Option.
Roles that you add to the user are assigned as default roles
unless you change the specification by clicking on the role's entry in
the Default column.
You must add the roles with the Admin Option in a separate
operation from the roles you want to add without the Admin Option.
When you grant the DBA and RESOURCE roles to a user or role
with Oracle7 release 7.2.2 or later, the user or role is also granted the
UNLIMITED TABLESPACE system privilege. When you revoke either role from
a user or role, the UNLIMITED TABLESPACE system privilege is also revoked.
The UNLIMITED TABLESPACE can also be revoked independent of the DBA and
RESOURCE roles.
System Privileges: If you select System Privileges
as the privilege type, system privileges that you are able to grant to
a user display in a scrolling list. These are the system privileges the
you have been granted with the Admin Option. If you have the GRANT ANY
PRIVILEGE system privilege, all privileges are listed. Select the privileges
that you want to add to the user.
If you want to grant the Admin or Grant option of a current privilege or role, you must add the privilege or role with Admin or Grant option specified as you want.
Up Arrow adds roles or system privileges that are selected
in the Available list to the Granted spreadsheet.
Down Arrow removes roles or system privileges that are selected in the Granted spreadsheet.
An editable spreadsheet displaying roles or system privileges
assigned to a user. New additions (selected but not yet applied) are identified
by a hand with a plus sign. When creating a user, the spreadsheet consists
of three columns:
System Privilege or Role: Name of the role or system
privilege.
Admin Option: When checked, allows the user to grant
the system privileges to other users or roles. By default, Admin Option
is disabled. You enable the Admin Option by clicking on the spreadsheet
entry. In this case, the "X" becomes a check.
Default: (Users property sheet only): When checked,
establishes the role as a default for the user upon system logon.
When creating a Role, this spreadsheet consists of two columns:
System Privilege or Role and Admin Option.
The Object Privileges page, available in Advance UI mode, allows you to grant or revoke privileges for a specific user on schema objects. This page contains the following:
A tree listing of schemas in the database and objects in
the schemas displays in the Object window. Click on the '+' to the left
of a folder icon to display the object types contained in the schema, then
click on the '+' to the left of the object type to display the actual objects.
Select the object from the tree list that you want to grant
privileges for. After the object is selected, the available privileges
for the object are displayed to the right in the Available Privileges scrolling
list.
You can grant an object privilege that you have been granted
with the Grant Option. If you are the owner of the object, you can grant
all privileges on the object. Select the privileges you want to grant for
the selected object. The scrolling list includes the privileges you can
grant on this object.
Grant Option box to allow the user to grant the object privilege
to other users and roles.
Click the Add button to add the selected object privileges to the user.
Displays privileges available for the schema object selected in the tree list.
Down Arrow adds privileges that are selected in the Available
Privileges list to the Granted Object Privileges spreadsheet.
Up Arrow removes privileges that are selected in the Granted Object Privileges spreadsheet.
An editable spreadsheet displaying object privileges to be
made available to a user. New additions (selected but not yet applied)
are identified by a hand with a plus sign.
When creating a new user, the spreadsheet consists of two
columns indicating the name of the object privilege and whether or not
the Grant Option is specified for that privilege.
When enabled, the Grant Option allows the user to grant the
specific object privilege to other users and roles. By default, this option
is disabled. To enable the grant option, click on the specific spreadsheet
entry. The "X" is replaced with a check.
When creating a role, the spreadsheet consists of a single
Object Privilege column.
On the Quotas page of the Create User property sheet, you can specify the tablespaces in which the user can allocate space and the maximum amount of space the user can allocate within each tablespace. This page is available in Advanced mode. The Quotas page contains the following items:
Scrolling list of the tablespaces in the database and the
maximum amount of space the user has been allowed in each tablespace. The
list can be sorted on the Tablespace or Quota Size column.
To specify a quota size for a tablespace, select the tablespace in the scrolling list and specify a quota size by clicking on the None, Unlimited, or Value button.
Click None if you do not want the user to have any quota on the selected tablespace.
To specify an unlimited quota for the tablespace, click the Unlimited button. With an unlimited quota, the user can allocate an unbounded amount of space in the tablespace.
To specify a specific quota, click Value and enter a quota
value in the adjacent text entry field. Select the K or M button to specify
Kilobytes or Megabytes.
If the user has been granted the UNLIMITED TABLESPACE System
Privilege, the Quota Details option is disabled.
To create a new user with the same attributes an existing user:
You can also perform this operation by selecting a user from
the tree list and then choosing the Create Like menu option. You must enter
the name of the new user and enter a new password if the Password button
is selected.
The format and content of the Create Like property sheet
is identical to the Create User property sheet. Refer to Creating
a User on page 9-8 for information about the property sheet.
To change the characteristics of a user:
You can also display the Quick Edit property sheet by selecting
a user from the multi-column list and choosing Quick Edit from the context
sensitive menu. The Quick Edit property sheet is identical to the details
property sheet.
The details/Quick Edit property sheet is identical in format
and content to the Create User property sheet except that the name field
is read-only. See Creating a User on
page 9-8 for information about the property sheet.
Suggestion: If you want to add privileges or roles
to multiple users, use the Add Privileges and Roles to Users menu item.
See Adding Privileges or Roles to Users
on page 9-15.
Attention: If you alter an object, such as a user
named DAVE or a role named CLERK, in any location of the tree list, all
instances of the object in the tree are changed.
If you no longer need a particular user in your database,
you can remove the user by selecting the user to be dropped from the Users
folder in the tree list and choosing Remove from the User menu. The Remove
User alert box appears.
The Remove User alert box indicates if the user still owns any objects. If you remove a user who owns objects, Security Manager:
To add multiple roles and grant multiple system or object privileges to users:
If you selected Roles, the roles that you can grant to a
user display in a scrolling list. These are roles you have created and
roles you have been granted with the Admin Option. If you have the GRANT
ANY ROLE system privilege, all roles are listed.
Select the roles that you want to add to the selected users.
Attention: The roles that you add to the users are
assigned as default roles unless you change the specification on the Default
Roles page of the Alter property sheet of each user.
Click the With Admin Option box to allow the user to grant
the role to other users or roles. If you grant a role with the Admin Option,
the user can also alter or drop the role.
You must add the roles with the Admin Option in a separate
operation from the roles you want to add without the Admin Option.
Note: When you grant the DBA and RESOURCE roles to
a user or role with Oracle7 release 7.2.2 or later, the user or role is
also granted the UNLIMITED TABLESPACE system privilege. When you revoke
either role from a user or role, the UNLIMITED TABLESPACE system privilege
is also revoked. The UNLIMITED TABLESPACE can also be revoked independent
of the DBA and RESOURCE roles.
From the SQL Worksheet, use the GRANT command to grant privileges
on a column in a table or view. For information about the GRANT command,
see the Oracle Server SQL Reference.
System Privileges: A scrolling list of the
system privileges that you are able to grant to users. These are the system
privileges you have been granted with the Admin Option. If you have the
GRANT ANY PRIVILEGE system privilege, all privileges are listed.
Select the privileges that you want to add to the selected
users. Click the With Admin Option box to allow the user to grant the system
privileges to other users or roles.
You must add the privileges with the Admin Option in a separate operation from the privileges you want to add without the Admin Option.
A tree listing of schemas in the database and objects in
the schemas displays in the Object window. Click on the '+' to the left
of a folder icon next to display the object types contained in the schema
and then click on the '+' to the left of the object type folder to display
available objects. Select the objects that you want to grant privileges
for.
After the object is selected, the available privileges for
the object are displayed to the right in the Privileges scrolling list.
You can grant an object privilege that you have been granted
with the Grant Option. If you are the owner of the object, you can grant
all privileges on the object. Select the privileges you want to grant for
the selected objects.
Attention: You must add the privileges with the Grant
Option in a separate operation from the privileges you want to add without
the Grant Option.
To remove the roles or privileges that are currently assigned to a user:
The Privileges multi-column scrolling list displays when
a Roles Granted, System Privileges Granted or Object Privileges Granted
object type folder is selected in the tree list. The list contains information
about privileges assigned to the user or role.
The columns in the Roles Granted list include:
Name of the role.
Whether the role was granted with the Admin option to the user or role.
Indicates the role as a default for the user upon system
logon
The columns in the System Privileges Granted list include:
Name of the system privilege.
Whether the privilege was granted with the Admin option to
the user or role.
The columns in the Object Privileges Granted list include:
Name of the object privilege.
Whether the privilege was granted with the Grant option to
the user.
For more information on these columns, see the description
of the Create User property sheet in the section, Creating
a User on page 9-8.
The Roles object type folder contains information about the
roles defined in your database arranged alphabetically in a tree structure.
An individual role can be expanded to show the system privileges, object
privileges, and roles granted to the role.
When you select:
Roles are named groups of privileges granted to users or
other roles. For information about managing roles, see the Oracle Server
Concepts, the Oracle Server Administrator's Guide, and the Oracle
Server SQL Reference.
A Roles multi-column list displays when a Roles or Roles
Granted folder is selected in the tree list. The multi-column scrolling
list contains a row of summary information for each of the roles in the
roles folder.
If the folder is named Roles Granted and is contained in a user or role, the list only contains information about roles assigned to the user or role. The columns of the Roles multi-column list include:
Name of the role.
Type of authentication used: none, external, password, or
global.
For more information on these columns, see the description
of the Create User property sheet in the section, Creating
a User on page 9-8 .
If a multi-column list that is wider than the window display
area, you can increase the viewing area by resizing the application window
or dragging the splitter between left and right section of the window.
To create a new role:
The Create Role property sheet contains the following pages.
The General page allows you to enter the following information:
Name of the role to be created. Enter the name of the new role.
Method used to enable the role.
None specifies that a user granted the role may enable
it without specifying a password.
Global: Specifies that a user granted the role may
enable it globally amongst multiple databases. The global authorization
option is only available with Oracle 8 databases.
External: Specifies that the operating system or an
external security utility to verify the role.
Password: Specifies that a password is required to
enable the role. Enter the password in the Enter Password entry field.
Enter the password again in the Confirm Password entry field to verify
the new password.
If you want to create a new role with the similar parameters as an existing role:
You can also perform this operation by selecting a role in
the tree list and choosing the Create Like menu option from the context-sensitive
menu. You must enter the name of the new role and enter a new password
if the Password button is selected.
This property sheet is identical to the Create Role property
sheet. Refer to Creating a User on page
9-8 for information about Create Role property sheet.
To alter the property sheet information for an existing role:
You can also modify a role by selecting a role from the Role
multi-column list with the right mouse button to call up the context-sensitive
menu. Select Quick Edit to bring up the Quick Edit property sheet appears.
The Role property sheet is identical to the Create Role property
sheet except that the name is read-only. Refer to Creating
a User on page 9-8 for information about the property sheet.
If you want to add privileges or roles to multiple roles,
use the Add Privileges to Roles menu item. See Adding
Privileges or Roles to Roles on page 9-22.
If you alter an object, such as a user named DAVE or a role
named CLERK, in any location of the tree list, all instances of the object
in the tree list are changed.
To remove a role that is no longer needed:
You can also remove a role by selecting the role to be dropped
from the Roles object type folder in the tree list and choosing Remove
from the context-sensitive menu.
To assign subroles and grant individual privileges to multiple roles, or add roles and grant privileges to multiple roles:
If you selected Roles, the roles that you can grant to a role display in a scrolling list. These are roles you have created and roles you have been granted with the Admin Option. If you have the GRANT ANY ROLE system privilege, all roles are listed.
You must add the roles with the Admin Option in a separate operation from the roles you want to add without the Admin Option.
A scrolling list of the system privileges that you are able to grant to a role. These are the system privileges you have been granted with the Admin Option. If you have the GRANT ANY PRIVILEGE system privilege, all privileges are listed.
You must add the privileges with the Admin Option in a separate operation from the privileges you want to add without the Admin Option.
A tree listing of schemas in the database and objects within
the schemas displays in the Object window. Click on the '+' to the left
of a folder icon to display the objects contained in the schema, then select
the objects that you want to grant privileges for.
After the object is selected, the available privileges for the object is displayed to the right in the Privileges scrolling list.
To remove privileges or subroles that are assigned to a role in the Roles folder:
To conveniently remove multiple privileges or subroles from
a single role, use the appropriate property sheet.
The Profiles folder contains information about the profiles
defined for the database arranged alphabetically in a tree structure. An
individual profile can be expanded to show the users that have been assigned
the profile.
When you select:
A profile is a set of limits on database resources. When
you assign a profile to a user, that user cannot exceed the limits set
in the profile.
Oracle automatically creates a default profile named DEFAULT.
The DEFAULT profile initially defines unlimited resources. You can alter
the DEFAULT profile to change any of its resource limits.
Any user who is not explicitly assigned a profile is subject
to the limits defined in the DEFAULT profile. Also, if the profile that
is explicitly assigned to a user omits a limit for a resource or specifies
the value DEFAULT for a limit, then the user is subject to the limit on
that resource as defined in the DEFAULT profile.
The initialization parameter RESOURCE_LIMIT must be set to
TRUE to enforce the limits set in database profiles. For more information,
see the Oracle Server Reference.
For more information about profiles, see the Oracle Server
Concepts, the Oracle Server Administrator's Guide, and the Oracle
Server SQL Reference.
The profiles multi-column list displays when the Profiles
folder is selected in the tree list. The scrolling list contains a row
of summary information for each of the profiles in the Profiles folder.
The columns include all the fields on the pages of Create
Profile property sheet. For more information on these columns, see Creating
a Profile on page 9-25.
Choose Create from the Profile menu.The Create Profile property
sheet appears. This property sheet contains the General and Password pages.
This field allows you to enter the name of a new profile.
These fields determine the amount of time allocated to the
CPU per Session, CPU per Call, Connect Time, and Idle Time for this profile.
The fields are:
CPU/Session: Total amount of CPU time allowed in a
session. The limit is expressed in seconds.
CPU/Call: Maximum amount of CPU time allowed for a
call (a parse, execute, or fetch). The limit is expressed in seconds.
Connect Time: Maximum elapsed time allowed for a session.
The limit is expressed in minutes.
Idle Time: Maximum idle time allowed in a session.
Idle time is a continuous period of inactive time during a session. Long-running
queries and other operations are not subject to this limit. The limit is
expressed in minutes.
You can enter a value in a field or choose from the drop-down list adjacent to the field. Click on the down-arrow to display the list. The drop-down list provides the following choices:
These fields determine the database services allocated to
this profile. The fields are:
Concurrent Sessions: Maximum number of concurrent
sessions allowed for a user.
Reads/Session: Total number of data block reads allowed
in a session. The limit includes blocks read from memory and disk.
Reads/Call: Maximum number of data block reads allowed
for a call (a parse, execute, or fetch) to process a SQL statement.
Private SGA: Maximum amount of private space a session
can allocate in the shared pool of the System Global Area (SGA). The Private
SGA limit applies only if you are using the multi-threaded server architecture.
The limit is expressed in kilo bytes (KBytes).
Composite Limit: Total resource cost for a session.
The resource cost for a session is the weighted sum of the CPU time used
in the session, the connect time, the number of reads made in the session,
and the amount of private SGA space allocated.
You can enter a value in a field or choose from the drop-down
list adjacent to the field. Click on the down-arrow to display the list.
The drop-down list provides the following choices:
In the SQL Worksheet, you can use the SQL command ALTER RESOURCE COST to specify the weights for the resources in the Composite Limit. For information about the ALTER RESOURCE COST command, see the Oracle Server SQL Reference.
Default: Use the limit specified for this resource
in the DEFAULT profile.
Unlimited: The user's access to this resource is unlimited.
Values: Select one of the existing values. The default
values vary by field and are common values for the field. If you have entered
a value in the field, that value appears in the drop-down list.
The Password page allows you to set account password parameters. This page consists of the following:
Expire in: Limits the number of days after which a
password expires. Select a value from the drop-down list, or enter a specific
value.
Lock: Limits the number of days during which a password can be changed following the first successful login after password expiration.
Keep: Specifies the number of times a password must
be changed before it can be reused. Keep for is disabled if a value is
specified in this field.
Keep for: Limits the number of days before a password can be reused after it expires. Keep is disabled if a value is specified in this field.
Allows a PL/SQL routine to be used for password verification
when users who are assigned this profile log into a database. This PL/SQL
routine must be locally available for execution on the database to which
this profile applies.
Oracle provides a default script (utlpwdmg.sql), however,
you can also create your own routine, or use third-party software as an
alternative. The password verification routine must be owned by SYS.
NULL (no password verification) is set by default.
Lock after: Limits the number of failed logon attempts
allowed before a user is locked out from the account.
Lock for: Specifies the number of days the account
is locked after failing the specified number of logon attempts. If UNLIMITED
is specified, only the database administrator can unlock the account.
Note: If Default is selected for any password options, values
defined in the Default profile are used.
To create a new profile that has identical parameter settings to an existing profile:
You can also perform this operation by selecting a profile
from the tree or multi-column list and choosing the Create Like menu option.
You must enter the name of the new profile.
The Create Like property sheet is identical to the Create
Profile property sheet. See Creating a Profile
on page 9-25 for information about the property sheet.
To alter the resource limits for an existing profile:
You can also use the Quick Edit property sheet to modify
a profile by selecting a profile from the multi-column list using the right
mouse button and choosing Quick Edit from the context-sensitive menu.
The Quick Edit property sheet is identical to the Create
Profile property sheet except that the name field is read-only. See Creating
a Profile on page 9-25 for information about the property sheet.
In the SQL Worksheet, you can use the SQL command ALTER RESOURCE
COST to specify the weights for the resources in the Composite Limit. For
information about the ALTER RESOURCE COST command, see the Oracle Server
SQL Reference.
To display dependents for a particular profile:
Dependencies are organized by user. Expanding individual
users in the Dependencies Viewer displays the schema objects, roles, and
privileges associated with that user.
To remove a profile that is no longer needed:
The Remove Profile alert box indicates if the profile you wish to drop is assigned to any users. If you drop a profile that is assigned to users, Security Manager assigns the DEFAULT profile to them.
Note: You cannot drop the DEFAULT profile.
To assign a profile to multiple users in the database:
