Setting user privileges and quotas in Oracle 10g

Learning objective

After completing this topic, you should be able to recognize how to grant privileges and assign quotas to users.

1. Granting system privileges

As an administrator, you may need to provide users with specific privileges, or rights, to change objects other than their own or to execute certain SQL statements.

Oracle's privileges are fine-grained, allowing you to control precisely how users access objects in the database.

Privileges fall into two categories:

• system privileges
• object privileges

system privileges

A system privilege provides the user with the right to perform a specific database operation or type of database operation - for example, creating tablespaces. Only an administrator or someone who specifically grants the permission to administer the privilege can grant a system privilege.

object privileges

Object privileges provide the user with the right to perform a specific action on an object, such as a view, table, procedure, function or sequence. A user can normally only access objects they own. However, an object privilege can be granted by an administrator or the owner of the object, to allow the user to change another user's objects.

You grant privileges to users through their user accounts.

To open an account, you click Users in the Administration tabbed page of the Database Control interface.

In the Users page, you select the user's account, Andrew Chung in this case, in the Select column of the Results table.

You then click Edit at the top of the Results table.

The General tabbed page of the Edit User page contains the properties of the user's account.

There are also several links to other tabbed pages in the user account.

To grant a system privilege to the user, you click the System Privileges link.

The System Privileges tabbed page lists the user's current system privileges.

Each system privilege has an Admin Option checkbox.

If you want the user of this account to either administer the privilege or grant the privilege to others, you select the Admin Option checkbox.

You now want to grant a new system privilege to Andrew Chung.

You click Modify.

The Modify System Privileges page contains two list boxes: Available System Privileges and Selected System Privileges, with buttons between them that allow you to edit the list of selected privileges.

The Available System Privileges list box allows you to select privileges that apply within the user's own schema.

For example, ADMINISTER RESOURCE MANAGER only allows a user to make changes to the Resource Manager in their own schema.

However, granting a privilege with the ANY clause, such as ALTER ANY CLUSTER, provides the user with rights to make changes in any schema.

You should consider your security needs carefully before granting system privileges.

Certain system privileges are usually only granted to administrators:

• RESTRICTED SESSION
• SYSDBA and SYSOPER
• DROP ANY OBJECT
• CREATE, MANAGE, DROP, and ALTER TABLESPACE

RESTRICTED SESSION

With the RESTRICTED SESSION privilege, the user is allowed to log in although the database has been opened in restricted mode.

SYSDBA and SYSOPER

The SYSDBA and SYSOPER privileges provide the user with the rights to start up and shut down the database and perform a recovery, as well as other administrative database functions.

DROP ANY OBJECT

With the DROP ANY OBJECT privilege, the user can delete any specified object they don't own.

CREATE, MANAGE, DROP, and ALTER TABLESPACE

The CREATE, MANAGE, DROP, and ALTER TABLESPACE privileges provide the user with the rights to change tablespaces. However, nonadministrators shouldn't be allowed to control tablespaces.

Other system privileges which should only be available to administrators include

• CREATE ANY DIRECTORY
• EXEMPT ACCESS POLICY
• GRANT ANY OBJECT PRIVILEGE
• ALTER DATABASE and ALTER SYSTEM

CREATE ANY DIRECTORY

The CREATE ANY DIRECTORY system privilege lets you call up external code from inside PL/SQL. For security reasons, the operating-system directory object that contains code must be mapped to a virtual Oracle directory object. However, the CREATE ANY DIRECTORY privilege allows a user to call up what may be an insecure code object.

EXEMPT ACCESS POLICY

The EXEMPT ACCESS POLICY privilege can be used to let a user bypass security placed on views and tables.

GRANT ANY OBJECT PRIVILEGE

GRANT ANY OBJECT PRIVILEGE provides the user with the right to grant object permissions on objects they do not own.

ALTER DATABASE and ALTER SYSTEM

The ALTER DATABASE and ALTER SYSTEM privileges provide users with rights to change the database and instance. However, non-administrative users should not be given these rights.

You grant a particular system privilege by selecting the privilege in the Available System Privileges list box, ALTER ANY CLUSTER in this case, and clicking Move.

The privilege now appears in the Selected Privileges list.

You can use Move All to select all the privileges. Similarly to remove one or all of the privileges from the Selected Privileges section, you click the Remove or Remove All buttons.

You click OK when you have made your selection.

The system privilege ALTER ANY CLUSTER now appears in the System Privileges table.

Question

Match system privileges with the appropriate descriptions.

Options:

1. CREATE ANY DIRECTORY
2. EXEMPT ACCESS POLICY
3. RESTRICTED SESSION
4. SYSDBA

Targets:

1. Allows a user to log into database set in restricted mode
2. Allows a user to start up the database
3. Allows a user to call up external code from inside PL/SQL
4. Allows a user to bypass security

Answer

CREATE ANY DIRECTORY allows a user to call up external code from inside PL/SQL and EXEMPT ACCESS POLICY allows a user to bypass security. RESTRICTED SESSION allows a user to log into a database set in restricted mode, and SYSDBA allows a user to start up the database.

The CREATE ANY DIRECTORY privilege allows a user to call up what may be an insecure code object.

The EXEMPT ACCESS POLICY allows the user to bypass security set on objects such as tables and views.

With the RESTRICTED SESSION privilege, the user is allowed to log in even when the database has been opened in restricted mode.

The SYSDBA privilege also allows the user to shut down, perform a recovery, and complete other administrative database functions.

Question

You have been asked by the HR department to allow Andrea Horner access to all workspaces.

This task requires you to grant the system privilege ACCESS ANY WORKSPACE to Andrea Horner. Which of these options allows you to complete this task?

Options:

1. You select Modify in the Edit User: Andrea Horner page. Then you select ACCESS ANY WORKSPACE in the Available System Privileges list box. Finally you click Move.
2. You select Create in the Edit User: Andrea Horner page. Then you select ACCESS ANY WORKSPACE in the Available System Privileges list box. Finally you click Move.

Answer

You select Modify in the Edit User: Andrea Horner page. Then you select ACCESS ANY WORKSPACE in the Available System Privileges list box. Finally you click Move.

2. Granting object privileges

Object privileges allow a user to modify objects such as tables or views, each of which have their own set of privileges.

For example, you can grant a user the rights to alter, delete, index, insert, reference, select or update another user's table.

However, you can only grant rights to delete, insert, select, and update a view belonging to another user.

Other commonly used object types are queues and programs.

With a queue, you can grant the privilege to the user to enqueue or dequeue a queue owned by another user.

With a program object type, you can allow a user to alter and execute another user's program.

Question

With which of these object types are the privileges to delete and update another user's object provided?

Options:

1. Program
2. Queue
3. Table
4. View

Answer

With tables and views, the user can be granted the privilege to delete, insert, select, and update another user's object.

Option 1 is incorrect. With a program object type, you can only grant a user the right to alter and execute another user's program.

Option 2 is incorrect. With a queue object type, you can only grant a user the right to enqueue or dequeue a queue that is owned by another user.

Option 3 is correct. With a table object type, you can grant a user the privilege to alter, delete, index, insert, reference, select or update another user's table.

Option 4 is correct. With a view object type, you can grant a user the privilege to delete, insert, select, and update a view belonging to another user.

To grant object privileges you first open the Users page.

You then select the user to whom you want to grant the privilege, Andrea Horner in this case, and click Edit.

In the Edit User: Andrea Horner page you click Object Privileges.

The Object Privileges tabbed page contains a table that lists the object privileges already granted to the user.

It also has a Select Object Type drop-down list box.

You now want to begin granting Andrea Horner the rights to modify a table belonging to the HR department.

You click the Select Object Type drop-down list box, select Table, and click Add.

The Add Table Object Privileges page contains three list boxes: Select Table Objects, Available Privileges, and Selected Privileges.

First you must select the table object. You can do this by entering the username and object name, if you know it, using the syntax username.objectname.

Alternatively, you click the flashlight icon beside the Select Table Objects list box.

An additional Search Table Objects page opens.

You now select the schema where the object you want to allow rights to modify resides.

To do this you click the Schema drop-down list box and select the table, HR in this case.

You enter the name of the table object from the selected schema in the Search Table Name field - Employees, in this example - and click Go.

When the search is successful, the object name appears in a table.

You then click the Select checkbox beside the object name and click the Select button.

The username and object name now appear in the Select Table Object section, using the username.objectname syntax.

You now select the privilege you want to grant to the user in the Available Privileges box, ALTER in this case, and click Move.

When the privilege appears in the Selected Privileges section you click OK.

The table is now listed in the Object Privileges tabbed page in the Edit User page.

Question

You have been asked to provide Andrew Chung with the rights to alter programs in the IT schema. The Edit User: Andrew Chung page is currently open.

This task requires you to open the Add Programs Object Privileges page. Which of these options allows you to complete this task?

Options:

1. You click the Select Object Type drop-down list box in the Edit User: Andrew Chung page and select Programs. You then click Add.
2. You click the Select Object Privileges drop-down list box in the Edit User: Andrew Chung page and select Programs. You then click Add.

Answer

You click the Select Object Type drop-down list box in the Edit User: Andrew Chung page and select Programs. You then click Add.

3. Assigning quota options

A user is not automatically allocated space in a given tablespace.

As an administrator, you have to grant the user a certain amount of space in a particular tablespace.

This provision of space is called a quota.

There are three ways you can grant a quota to a user:

• unlimited
• value
• UNLIMITED TABLESPACE system privilege

unlimited

The unlimited option lets the user take all the space they need from what is available in the tablespace.

value

The value option sets a limit on how much space, in kilobytes or megabytes, a user can take up in the tablespace. The value may be more or less than the space currently available in the tablespace.

UNLIMITED TABLESPACE system privilege

The UNLIMITED TABLESPACE system privilege provides the user with an unlimited quota on all tablespaces, including SYSAUX and SYSTEM. It overrides the tablespace quota granted on an individual basis. You should be cautious about granting this privilege.

Question

Match each quota option to the appropriate definition.

Options:

1. Unlimited
2. UNLIMITED TABLESPACE system privilege
3. Value

Targets:

1. Lets the user take all the space they need from what is available
2. Sets a limit on how much space a user can take up
3. Provides the user with an unlimited quota on all tablespaces

Answer

The unlimited option lets the user take all the space they need from what is available. The value option sets a limit on how much space a user can take up, while the UNLIMITED TABLESPACE system privilege provides the user with an unlimited quota on all tablespaces.

The unlimited option lets the user control how much space they use. They are only limited by the amount of available space in the tablespace.

This option overrides the tablespace quota granted on an individual basis. You should be cautious about granting this privilege.

The value entered bears no relation to the space available in the tablespace. It may be more or less than what is currently available.

When users are on the SYSAUX or SYSTEM tablespace, you do not give them a quota.

It is usually only the user's SYSTEM and SYS that create objects in the SYSAUX or SYSTEM tablespace.

Similarly, you do not need to give a quota to users on their undo tablespaces or their given temporary tablespaces.

You click Quotas in the Edit User page to set the quota for the user.

You now want to allow the user to have a set amount of space in the INVENTORY tablespace.

You click the Quota drop-down list box for the INVENTORY tablespace, and select Value.

The Quota setting has changed to Value.

You now enter the limit in the Value field, 150 in this case. By default, the Unit option is set at MBytes.

This changes the space allocated to the user Andrew Chung in the INVENTORY tablespace to 150 MB.

To change the unit setting to kilobytes, you select the Unit drop-down list box for the relevant tablespace and select KBytes.

You click Apply to set the quota for the user.

An update message appears at the top of the Edit User page telling you that the user ANDREW CHUNG has been modified successfully.

Question

You are setting the quotas for Andrea Horner to allocate space to her in the various tablespaces.

This task requires you to provide Andrea Horner with unlimited space in the STOCK tablespace. Which of these options allows you to complete this task?

Options:

1. You click the Quota drop-down list box for the STOCK tablespace in the Quota page. You then select Value from the drop-down list and click Apply.
2. You click the Quota drop-down list box for the STOCK tablespace in the Quota page. You then select Unlimited from the drop-down list and click Apply.

Answer

You click the Quota drop-down list box for the STOCK tablespace in the Quota page. You then select Unlimited from the drop-down list and click Apply.

Summary

With Oracle Database 10g, you can provide users with certain fine-grained privileges to make changes to parts of the system, including objects other than their own, or to provide them with space on a given tablespace. There are two main privilege categories: system and object. System privileges allow the user to perform database operations, such as creating tablespaces.

Object privileges allow a user to modify an object that is not in their schema. The privileges over a database object granted to a user vary according to the function of the object. For example, with a table you can grant the user privileges to alter, delete, index, insert, reference, select or update another user's table. However, with a program you can only grant privileges to alter and execute another user's program.

A user is not given space in any given tablespace by default. You have to grant the user a quota of space for a specific tablespace. There are three options for providing users with a space quota: Unlimited, Value, and the UNLIMITED TABLESPACE system privilege.