CHAPTER 9. Controlling Database Security

• Starting Security Manager

• User Containers, Multi-Column Lists, and Menu Options

• Privileges and Roles Multi-Column Lists

• Role Containers, Multi-Column Lists, and Menu Options

• Profile Containers, Multi-Column Lists, and Menu Options

Starting Security Manager

• If a connection to a database has already been made through the Console or an application, the Security window displays.

• If a valid database connection has not been made, the Connect dialog box displays. See "Connecting to an Instance" .

After the Security Manager has successfully connected to a database, the Users, Roles, and Profiles containers display in a tree list on the left side of the Security window. These containers are located in the database container which displays the name of the database that the application is connected to.

Figure 9 - 1. The Security Manager

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.

For information about:

• Application containers, see page 7 - 3.

• Multi-column lists, see page 7 - 10.

• Dialog boxes, see page 7 - 11.

• Property sheets, see page 7 - 12.

Security Menus

Context-sensitive menus may also be active when you press the right mouse button to select a specific object from the navigator or the multi-column list. This feature provides quick access to a subset of the menu options provided in the menu bars.

Security Icons

• A folder icon identifies an object type container.

• A single person icon depicts a user.

• A mask icon signifies a role or subrole.

• A scroll icon depicts a profile.

• An open door icon identifies a privilege.

• A suitcase shows an identifies an object privilege.

Users Containers

When you select:

• The Users group container, a multi-column list displays a row of summary information for each of the users in the database.

• An individual user, the detailed settings for the user are displayed in a property sheet.

• The Roles Granted, System Privileges Granted, or Object Privileges Granted container under an individual user, a multi-column list of the privileges or roles granted to the user displays. See page 9 - 13.

Users Multi-Column List

If the selected Users container is a main branch of the database container, the columns include all the fields on the General page of the Create User property sheet. For more information on these columns, see the description of the Create User property sheet .

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.

Users Menu Options

• Create to create a new user.

• Create Like to create a new user based on the selected user in the tree list.

• Remove to delete the selected user in the tree list.

• Revoke Privilege to remove a selected privilege or role.

• Add Privileges to User to add multiple privileges to one or more users.

Creating a User

The Create User property sheet consists of the following pages:

• General

• Quotas

• Privileges

• Default Roles

Create User: General Page

User	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.
Profile	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.
Authentication	The method Oracle uses to authenticate the user.
	Click External to specify that the operating system verify the user.
	Click Password to require a password for logon. Enter the password in the adjacent text entry field. Enter the password again in the Confirm text entry field for verification.
Tablespaces	The user's default and temporary tablespaces.
	Use the drop-down list to choose the default tablespace for objects the user creates.
	Use the drop-down list to choose the tablespace for the user's temporary segments.

Create User: Quotas Page

UNLIMITED TABLESPACE	Check the box to grant the UNLIMITED TABLESPACE System Privilege to the user. With this privilege, the user can allocate an unbounded amount of space in any tablespace. The Quota Details option is disabled when this option is enabled.
Quota Details	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.
None	Click the None button if you do not want the user to have any quota on the selected tablespace.
Unlimited	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.
Value	To specify a specific quota, click the Value button and enter a quota value in the adjacent text entry field. Select the K or M button to specify kilobytes or megabytes.

Create User: Privileges Page

Privilege Type:	A drop-down list containing System Privileges, Object Privileges, and Roles. Your selection in the drop-down list determines what is displayed on the rest of the page.
Current	A multi-column scrolling list of the current system privileges, object privileges, or roles assigned to the user is displayed depending on the Privilege Type selection. YES or NO displays in the second column to indicate whether the Admin or Grant Option has been granted.
	You can sort the list on either column by clicking on the column heading.
	To remove any of the current privileges or roles, select the items in the Current window and click on the Delete button. Note: The roles are not actually revoked until you click the Apply button.

Attention: If you want to change 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.

Roles	If you selected Roles, where 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 user.

Attention: The roles that you add to the user are assigned as default roles unless you change the specification on the Default Roles page.

	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.
	Click the Add button to add the selected roles to the user.

Attention: 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.

Attention: In 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 Oracle7 Server SQL Reference.

System Privileges	A scrolling list of the system privileges that you are able to grant to a user. 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 user. Click the With Admin Option box to allow the user to grant the system privileges to other users or roles.
	Click the Add button to add the selected system privileges to the user.

Attention: You must add the privileges with the Admin Option in a separate operation from the privileges you want to add without the Admin Option.

Object Privileges	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 container 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 are displayed to the right in the Privileges scrolling list. You can simultaneously select multiple entries in this 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.
	Click the With 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.

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.

Create User: Default Roles Page

Note: Only a role granted directly to the user can be specified as a default role. A role granted through another role cannot be a default role.

The Default Role page contains the following:

Default:	Scrolling list of the default roles granted to the user.
To remove a role as a default, select the roles you wish to remove as the user's default roles and click the Remove Default button.

Note: Roles assigned as non-default roles for a user need to be activated explicitly by the user after connecting to the database.

Non-Default:	Scrolling list of the non-default roles that have been granted to the user.
To add a role as a default, select the roles you wish to designate as the user's default roles and click the Add Default button.

Create Like 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 the Create User property sheet. Refer to "Creating a User" for information about the property sheet.

Altering a User

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" 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 page 9 - 11.

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.

Removing a User

The Remove User alert box indicates if the user still owns any objects. If you remove a user who owns objects, the Security Manager:

• Drops all of the objects in the user's schema

• Drops any referential integrity constraints in other schemas that refer to the dropped user's tables

• Invalidates any views or synonyms for objects in the dropped user's schema

• Invalidates any stored procedures, functions, or packages that query objects in the dropped user's schema

• Does not drop snapshots on tables or views in the dropped user's schema

• Does not drop any roles created by the user

Adding Privileges or Roles to Users

A scrolling list of users is displayed in the top half of the dialog box. Select the users in the list that you want to add privileges or roles to.

Select System Privileges, Object Privileges, or Roles from the drop-down list. The display in the bottom half of the dialog box varies according to your selection.

Roles	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.

Attention: 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.

Attention: In 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 Oracle7 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.

Attention: You must add the privileges with the Admin Option in a separate operation from the privileges you want to add without the Admin Option.

Object Privileges	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 container icon next 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 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.
	Click the With Grant Option box to allow the users to grant the object privilege to other users and roles.

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.

Click on the Apply or OK button to save any changes you have made in the dialog box. For details on the dialog box command buttons, see page 7 - 11.

Removing Privileges or Roles from a User

In the Users tree list, select the privilege or role that you want to remove from a user. If necessary, click on the '+' to the left of the container icon to display the privileges or roles that have been assigned to the user.

Select Revoke Privilege from the User menu or Revoke from the the context-sensitive menu to remove the selected privilege or role from the user.

To conveniently remove multiple privileges or roles from a single user, use the appropriate property sheet.

Privileges Multi-Column Lists

Roles Granted

Role	Name of the role.
Admin option	Whether the role was granted with the Admin option to the user or role.
Default	Whether the role has been assigned as a default role to the user or role granted to a user.

System Privileges

System Privileges	Name of the system privilege.
Admin Option	Whether the privilege was granted with the Admin option to the user or role.

Object Privileges

Object Privilege	Name of the object privilege.
Grant option	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 .

Role Containers

When you select:

• the Roles object type container, a multi-column list displays a row of summary information for each of the roles in the database.

• an individual role, the Alter property sheet displays the detailed settings for the role.

• the Roles Granted, System Privileges Granted, or Object Privileges Granted container under an individual role, a multi-column list of the privileges or roles granted to the role displays. See page 9 - 13.

Role Multi-Column Lists

If the container is named Roles and is a main branch of the database container, the columns include all the fields on the General page of the Create Role property sheet. For information on these columns, see the description of the Create Role property sheet .

If the container 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 include:

Role	Name of the role.
Admin option	Whether the role was granted with the Admin option to the user or role.
Default	Whether the role has been assigned as a default role to the user or role granted to a user.

For more information on these columns, see the description of the Create User property sheet .

Suggestion: If a multi-column list that is wider than the its 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.

Role Menu Options

• Create to create a new role

• Create Like to create a new role based on the selected role.

• Remove to delete the selected role.

• Revoke Privilege to remove a privilege or role from a role.

• Add Privileges to Roles to add privileges or roles to roles

Creating a Role

The property sheet contains the following pages.

• General

• Privileges

Create Role: General page

Role	Name of the role to be created. Enter the name of the new role.
Authentication	Method used to enable the role.
	Click None to indicate that a user granted the role may enable it without specifying a password.
	Click External to require the operating system or an external security utility to verify the role.
	Click Password to require a password in order to enable the role. Enter the password in the adjacent text entry field. Enter the password again the the Confirm text entry field to verify the new password.

Click on the Apply button to save any changes you have made to the property sheet. For details on the property sheet command buttons, see page 7 - 12.

Create Role: Privileges page

Privilege Type:	A drop-down list containing System Privileges, Object Privileges, or Roles. Your selection in the drop-down list determines what is displayed on the rest of the page.
Current	A multi-column scrolling list of the current system privileges, object privileges, or roles assigned to the role is displayed depending on the privilege type selection. YES or NO displays in the second column to indicate whether the Admin or Grant Option has been granted.
	You can sort the list on either column by clicking on the column heading.
	To remove any of the current privileges or roles, select the items in the Current window and click on the Delete button.

Attention: If you want to change the Admin option of a current privilege or role, you must add the privilege or role with Admin option specified as you want.

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.
	Select the roles that you want to add to the role.
	Click the With Admin Option box to allow the role to grant the role to other users or roles. If you grant a role with the Admin Option, the role can also alter or drop the role.
	Click the Add button to add the selected roles to the role.

Attention: You must add the roles with the Admin Option in a separate operation from the roles you want to add without the Admin Option.

System Privileges	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.
	Select the privileges that you want to add to the role. Click the With Admin Option box to allow the role to grant the system privileges to other users or roles.
	Click the Add button to add the selected system privileges to the role.

Attention: You must add the privileges with the Admin Option in a separate operation from the privileges you want to add without the Admin Option.

Object Privileges	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 container 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 Object Privileges scrolling list.
	Select the privileges you want to grant for the selected objects.
	Click the Add button to add the selected object privileges to the role.

Create Like Role

You can also perform this operation by selecting a role in the tree list and choosing the Create Like menu option from the Role menu or a context-sensitive menu. You must enter the name of the new role and enter a new password if the Password button is selected.

The Create Like property sheet is identical to the Create Role property sheet. Refer to "Creating a Role" for information about the property sheet.

Altering a Role

The Alter Role property sheet is identical to the Create Role property sheet except that the name is read-only. Refer to "Creating a Role" for information about the property sheet.

Suggestion: If you want to add privileges or roles to multiple roles, use the Add Privileges to Roles menu item. See page 9 - 18.

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.

Removing a Role

The Remove Role alert box appears. Click Yes to remove the role.

Adding Privileges or Roles to Roles

A scrolling list of roles is displayed in the top half of the dialog box. Select the roles in the list that you want to add privileges or roles to.

Select System Privileges, Object Privileges, or Roles from the drop-down list. The display in the bottom half of the dialog box varies according to your selection.

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.
	Select the roles that you want to add to the role.
	Click the With Admin Option box to allow the role to grant the role to other users or roles. If you grant a role with the Admin Option, the role can also alter or drop the role.
	Click the Apply button to add the selected roles to the role.

Attention: You must add the roles with the Admin Option in a separate operation from the roles you want to add without the Admin Option.

System Privileges	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.
	Select the privileges that you want to add to the role. Click the With Admin Option box to allow the role to grant the system privileges to other users or roles.
	Click the Add button to add the selected system privileges to the role.

Attention: You must add the privileges with the Admin Option in a separate operation from the privileges you want to add without the Admin Option.

Object Privileges	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 container 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.
	Select the privileges you want to grant for the selected object. The scrolling list includes the object privileges you can grant on an object.
	Click the Apply button to add the selected object privileges to the role.

Click on the Apply button to save any changes you have made in the dialog box. For details on the dialog box command buttons, see page 7 - 11.

Removing Privileges or Roles from a Role

Select the privilege or subrole that you want to remove from a role. If necessary, click on the '+' to the left of the container icon to display the privileges or subroles that have been assigned to the role.

Choose the Remove from Role menu option to remove the selected privilege or subrole.

To conveniently remove multiple privileges or subroles from a single role, use the appropriate property sheet.

Profile Containers

When you select:

• the Profiles object type container, a multi-column list displays a row of summary information for each of the profiles in the database.

• an individual profile, the Alter property sheet displays the detailed settings for 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.

Attention: The initialization parameter RESOURCE_LIMIT must be set to TRUE to enforce the limits set in database profiles. For more information, see the Oracle7 Server Reference.

For more information about profiles, see the Oracle7 Server Concepts, the Oracle7 Server Administrator's Guide, and the Oracle7 Server SQL Reference.

Profile Multi-Column Lists

The columns include all the fields on the pages of Create Profile property sheet. For more information on these columns, see the description of the Create Profile property sheet .

Suggestion: If a multi-column list that is wider than the its 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.

Profile Menu Options

• Create to create a new profile.

• Create Like to create a new profile that is based on the selected profile.

• Remove to delete the selected profile.

• Assign Profile to Users to assign a profile to a specific user.

Creating a Profile

• Profile name

• Details on the Session, CPU, Connect, and Idle times

• Database Services Parameters

Create Profile: Profile Name

Profile Name	Name of the new profile.

Create Profile: Details

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:

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.

Create Profile: Database Services

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:

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.

Attention: 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 Oracle7 Server SQL Reference.

Create Like Profile

You can also perform this operaiton 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. Refer to "Creating a Profile" for information about the property sheet.

Altering a Profile

The Alter Profile property sheet is identical to the Create Profile property sheet except that the name field is read-only. See "Creating a Profile" for information about the property sheet.

Attention: 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 Oracle7 Server SQL Reference.

Removing a Profile

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, the Security Manager assigns the DEFAULT profile to them.

Attention: You cannot drop the DEFAULT profile.

Assigning a Profile to Users

Select the profile that you want to assign from the drop-down list in the Assign Profiles dialog box. In the scrolling list, select the users that you want to assign the profile to. Click on the Assign button to assign the selected profile to the users.