Pro Oracle Database 11g Administration (27 page)

Limiting Database Resource Usage

As mentioned earlier, the password-profile settings take effect as soon as you assign the profile to a user.

Unlike password settings, kernel resource profile restrictions don’t take effect until you set the RESOURCE_LIMIT initialization parameter to TRUE for your database. For example: SQL> alter system set resource_limit=true scope=both;

To view the current setting of the RESOURCE_LIMIT parameter:

SQL> select name, value from v$parameter where name='resource_limit'; When you create a user, if you don’t specify a profile, then the DEFAULT profile is assigned to the user.

You can modify the DEFAULT profile with the ALTER PROFILE statement. The next example modifies the DEFAULT profile to limit CPU_PER_SESSION to 240000 (in hundredths of seconds): SQL> alter profile default limit cpu_per_session 240000;

This limits any user with the DEFAULT profile to 2400 seconds of CPU use. You can set various limits in a profile. Table 6–2 describes the database resource settings you can limit via a profile.

126

CHAPTER 6 ■ USERS AND BASIC SECURITY

Table 6–2.
Database Resource Profile Settings

Profile Resource

Meaning

COMPOSITE_LIMIT

Limit based on a weighted-sum algorithm for these resources:

CPU_PER_SESSION, CONNECT_TIME, LOGICAL_READS_PER_SESSION, and

PRIVATE_SGA

CONNECT_TIME

Connect time in minutes

CPU_PER_CALL

CPU time limit per call in hundredths of seconds

CPU_PER_SESSION

CPU time limit per session in hundredths of seconds

IDLE_TIME

Idle time in minutes

LOGICAL_READS_PER_CALL

Blocks read per call

LOGICAL_READS_PER_SESSION

Blocks read per session

PRIVATE_SGA

Amount of space consumed in the shared pool

SESSIONS_PER_USER

Number of concurrent sessions

You can also create custom profiles and assign them to users via the CREATE PROFILE statement. You can then assign that profile to any existing database users. The following SQL statement creates a profile that limits resources, such as the amount of CPU an individual session can consume: create profile user_profile_limit

limit

sessions_per_user 20

cpu_per_session 240000

logical_reads_per_session 1000000

connect_time 480

idle_time 120;

After you create a profile, you can assign it to a user. In the next example, user HEERA is assigned USER_PROFILE_LIMIT:

SQL> alter user heera profile user_profile_limit;

■
Note
Oracle recommends that you use Database Resource Manager to manage database resource limits.

However, I find database profiles (implemented via SQL) to be an effective and easy mechanism for limiting resources.

As part of the CREATE USER statement, you can specify a profile other than DEFAULT: SQL> create user heera identified by foo profile user_profile_limit; 127

CHAPTER 6 ■ USERS AND BASIC SECURITY

When should you use database profiles? You should always take advantage of the password-security settings of the DEFAULT profile. You can easily modify the default settings of this profile as required by your business rules.

A profile’s kernel resource limits are useful when you have power users who need to connect directly to the database and run queries. For example, you can use the kernel resource settings to limit the amount of CPU time a user consumes, which is handy when a user writes a bad query that inadvertently consumes excessive database resources.

Managing Privileges

A database user must be granted privileges before the user can perform any task in the database. In Oracle, you assign privileges either by granting a specific privilege to a user or by granting the privilege to a role and then granting the role that contains the privilege to a user. There are two types of privileges: system privileges and object privileges. The following sections discuss these privileges in detail.

Assigning Database System Privileges

Database system privileges allow you to do tasks such as connecting to the database and creating and modifying objects. There are hundreds of different system privileges. You can view system privileges by querying the DBA_SYS_PRIVS view:

SQL> select distinct privilege from dba_sys_privs;

You can grant privileges to other users or roles. To be able to grant privileges, a user needs the GRANT

ANY PRIVILEGE privilege or must have been granted a system privilege with ADMIN OPTION.

Use the GRANT statement to assign a system privileges to a user. For example, minimally a user needs CREATE SESSION to be able to connect to the database. You grant this system privilege as shown: SQL> grant create session to inv_mgmt;

Usually, a user needs to do more than just connect to the database. For example, a user may need to create tables and other types of database objects. This example grants a user the CREATE TABLE and CREATE DATABASE LINK system privileges:

SQL> grant create table, create database link to inv_mgmt;

If you need to take away privileges, use the REVOKE statement:

SQL> revoke create table from inv_mgmt;

Oracle has a feature that allows you to grant a system privilege and also give that user the ability to administer a privilege. You do this with the WITH ADMIN OPTION clause: SQL> grant create table to inv_mgmt with admin option;

I rarely use WITH ADMIN OPTION when granting privileges. Usually, a user with the DBA role is used to grant privileges, and that privilege isn’t generally meted out to non-DBA users in the database. This is because it would be hard to keep track of who assigned what system privileges, for what reason, and when. In a production environment, this would be untenable.

You can also grant system privileges to the PUBLIC user group (I don’t recommend doing this). For example, you could grant CREATE SESSION to all users that ever need to connect to the database, as follows:

SQL> grant create session to public;

128

CHAPTER 6 ■ USERS AND BASIC SECURITY

Now every user that is created can automatically connect to the database. Granting system privileges to the PUBLIC user group is almost always a bad idea. As a DBA, one of your main priorities is to ensure that the data in the database is safe and secure. Granting privileges to the PUBLIC role is a sure way of not being able to manage who is authorized to perform specific actions within the database.

Assigning Database Object Privileges

Database object privileges allow you to access and manipulate other users’ objects. The types of database objects on which you can grant privileges include tables, views, materialized views, sequences, packages, functions, procedures, user-defined types, and directories. To be able to grant object privileges, one of the following must be true:

• You own the object.

• You’ve been granted the object privilege with GRANT OPTION.

• You have the GRANT ANY OBJECT PRIVILEGE system privilege.

This example grants object privileges (as the object owner) to the INV_MGMT_APP user: SQL> grant insert, update, delete, select on registrations to inv_mgmt_app; The GRANT ALL statement is equivalent to granting INSERT, UPDATE, DELETE, and SELECT to an object.

The next statement is equivalent to the prior statement:

SQL> grant all on registrations to inv_mgmt_app;

You can also grant INSERT and UPDATE privileges to tables at the column level. The next example grants INSERT privileges to specific columns in the INVENTORY table:

SQL> grant insert (inv_id, inv_name, inv_desc) on inventory to inv_mgmt_app; If you want a user that is being granted object privileges to be able to subsequently grant those same object privileges to other users, then use the WITH GRANT OPTION clause: SQL> grant insert on registrations to grouping_app with grant option; Now the GROUPING_APP user can grant insert privileges on the REGISTRATIONS table to other users.

I rarely use the WITH GRANT OPTION when granting object privileges. This option allows other users to propagate object privileges to other users. This makes it hard to keep track of who assigned what object privileges, for what reason, when, and so on. In a production environment, this would be untenable.

When you’re managing a production environment, when problems arise, you need to know what changed and when and for what reason.

You can also grant object privileges to the PUBLIC user group (I don’t recommend doing this). For example, you could grant select privileges on a table to PUBLIC:

SQL> grant select on registrations to public;

Now every user can select from the REGISTRATIONS table. Granting object privileges to the PUBLIC

role is almost always a bad idea. As a DBA, one of your main priorities is to ensure that the data in the database is safe and secure. Granting object privileges to the PUBLIC role is a sure way of not being able to manage who can access what data in the database.

If you need to take away object privileges, then use the REVOKE statement. This example revokes DML privileges from the INV_MGMT_APP user:

SQL> revoke insert, update, delete, select on registrations from inv_mgmt_app; 129

CHAPTER 6 ■ USERS AND BASIC SECURITY

Grouping and Assigning Privileges

A
role
is a database object that allows you to logically group system and/or object privileges together so you can assign those privileges in one operation to a user. Roles help you manage aspects of database security in that they provide a central object that has privileges assigned to it. You can subsequently assign the role to multiple users or other roles.

To create a role, connect to the database as a user that has the CREATE ROLE system privilege. Next, create a role and assign to it the system or object privileges that you want to group together. This example uses the CREATE ROLE statement to create the JR_DBA role:

SQL> create role jr_dba;

The next several lines of SQL grant system privileges to the newly created role: SQL> grant select any table to jr_dba;

SQL> grant create any table to jr_dba;

SQL> grant create any view to jr_dba;

SQL> grant create synonym to jr_dba;

SQL> grant create database link to jr_dba;

Next, grant the role to any schema you want to possess those privileges: SQL> grant jr_dba to lellison;

SQL> grant jr_dba to mhurd;

The users LELLISON and MHURD can now perform tasks such as creating synonyms, views, and so on.

To view users assigned to roles, query the DBA_ROLE_PRIVS view:

SQL> select grantee, granted_role from dba_role_privs order by 1;

To view roles granted to your currently connected user, query from the USER_ROLE_PRIVS view: SQL> select * from user_role_privs;

To revoke a privilege from a role, use the REVOKE command:

SQL> revoke create database link from jr_dba;

Similarly, use the REVOKE command to remove a role from a user:

SQL> revoke jr_dba from lellison;

■
Note
Unlike other database objects, roles don’t have owners. A role is defined by the privileges assigned to it.

130

CHAPTER 7 ■ TABLES AND CONSTRAINTS

Table 7–1
. Oracle Table Type Descriptions

Table Type

Description

Typical Use

Heap-organized

The default table type and the

Table type to use unless you have a specific

most commonly used.

reason to use a different type.

Temporary

Session private data, stored for

Program needs a temporary table structure

the duration of a session or

to store and sort data. Table isn’t required

transaction. Space is allocated

after program ends.

in temporary segments.

Index-organized (IOT)

Data stored in a B-tree index

Table is queried mainly on primary key

structure sorted by primary

columns. Provides fast random access.

key.

Partitioned