Chapter 11: Users and Security


If a company has more than one employee who needs access to the Oracle database, then the security of the database is a prime concern for the DBA. The data integrity of the database and the level of security in the database are maintained, in part, by preventing unauthorized or unintentional actions in the database.

Database security can be divided into roughly two areas: data security and system security. Data security includes monitoring and assigning users permissions to the various objects in the database. System security covers the user login process, how much disk space is assigned to each user, and what kinds of actions each user can perform.

Creating User Accounts

To connect to the Oracle database, a user must have an Oracle database account, also known as a username. When you create the username, you can specify various other characteristics of the account, including a password, a profile, default tablespaces, and disk space quotas.

username

An Oracle database account identifier that, along with a password, allows a user to connect to the database.

The basic syntax to create a username is as follows:

CREATE USER user <other options>;

At a minimum, you should assign a password to the account. Passwords and the other user account options are discussed in the following sections.

Assigning Passwords

The password for the user account is typically assigned at the time the account is created, and then changed after the user logs in for the first time. Janice, the DBA, creates an account for one of the new stocking managers with an initial password of BLINKIE6:

create user jsmith identified by blinkie6; User created.

Passwords are not case sensitive; for example, BlinKIe6 or blinkIe6 would both be stored as BLINKIE6 in the database. To ensure that the password won’t be easy to guess, it’s important to use a mixture of letters, numbers, and punctuation characters in the password. The DBA can define additional rules for allowable passwords by the use of a special stored function owned by the SYS schema. For example, the DBA may require that certain sensitive accounts such as HR have a password that is longer than the password for any other accounts.

The DBA or user can use the ALTER USER command to change the password:

alter user jsmith identified by spinner40; User altered.

To change a password from an iSQL*Plus session, the user can use the Preferences link in the upper-right area of the browser. From within SQL*Plus, the user can change the password using the SQL*Plus PASSWORD command. The advantage to these last two methods is that the old and new passwords are not echoed to the screen:

SQL> password Changing password for JSMITH Old password: ********* New password: ******** Retype new password: ******** Password changed SQL>

Creating and Assigning Profiles

Each username in the database has a profile associated with it. A profile is a set of predefined resource parameters that can be used to monitor and control various database resources. The following are some examples of resources that can be controlled in a profile:

  • Concurrent connections to the database

  • Maximum failed login attempts before the account is locked

  • Elapsed time connected

  • Continuous idle time connected

  • CPU time used

  • Disk reads performed

  • How often a password needs to be changed

profile

A set of predefined resource parameters that can be used to monitor and control various database resources, such as CPU time and number of disk reads against the database.

When an account is created, a profile can be specified; otherwise, Oracle assigns a default profile. Not surprisingly, this profile is called DEFAULT. The initial values of the DEFAULT profile allow for unlimited use of all resources.

At Scott’s widget company, the users in the stocking department are notorious for leaving their sessions connected to the database and forgetting to log off when they are finished. This consumes valuable memory resources, so Janice, the DBA, decides to create a new profile in the database to make sure that users are disconnected from the database after 15 minutes of idle time:

create profile st_user limit    idle_time 15; Profile created.

In the new ST_USER profile just created, all resources are set to UNLIMITED except for the IDLE_TIME resource, which has been set to 15 minutes. The DBA modifies the recently created user to use the newly created profile:

alter user jsmith profile st_user; User altered. 

For JSMITH’s subsequent sessions, the session will be disconnected if the session remains idle for 15 minutes.

Assigning Default Tablespaces and Quotas

When a user creates some type of object—a table, an index, a sequence, or another object—that object uses space in one of the database’s tablespaces. In addition, a user may need temporary space for sorting and other operations. Each user has a default tablespace for permanent objects and a default tablespace for temporary objects, although a user may explicitly create objects in a different tablespace if the user has the proper permissions.

If a default permanent tablespace is not specified when the user account is created, the SYSTEM tablespace is used. It is generally not a good idea to leave SYSTEM as the default tablespace. Since the SYSTEM tablespace contains all of the data dictionary objects, there is a high level of contention in the SYSTEM tablespace already, so any new user objects in the SYSTEM tablespace might have a negative impact on overall system performance.

Janice, the DBA, remedies this situation with the new user account and changes the default tablespace:

alter user jsmith default tablespace users; User altered.

Janice double-checks her work by querying the DBA_USERS data dictionary view:

select username, default_tablespace,    temporary_tablespace from dba_users where username = ‘JSMITH’; USERNAME     DEFAULT_TABLESPACE   TEMPORARY_TABLESPACE ------------ -------------------- -------------------- JSMITH       USERS                TEMP 1 row selected.

Janice makes a mental note to use the GUI-based Oracle Enterprise Manager (OEM) tool next time. Its Create User facility, shown below, is not only easier to use, but it also automatically specifies the USER tablespace as the default tablespace for new users, among other defaults.

click to expand

Although disk space gets cheaper every day, you may also want to limit how much disk space each user can allocate in each tablespace. The limit on the amount of disk space in a tablespace is called a quota. Even though each username is assigned a default tablespace when the username is created, the quota defaults to zero. Therefore, you must assign a quota to the user before that user can create objects in the tablespace.

quota

A numeric limit on the amount of disk space that a user can allocate within a tablespace. The quota can also be specified as UNLIMITED.

Since the new user, JSMITH, is expected to create tables for other people in the stocking department, Janice allocates 15MB of disk space in the USERS tablespace for JSMITH:

alter user jsmith quota 15M on users; User altered.

If Janice specified UNLIMITED instead of 15M, JSMITH would not have any limits on how much space she can use in the USERS tablespace for database objects.




Oracle9i DBA JumpStart
Oracle9i DBA JumpStart
ISBN: 0782141897
EAN: 2147483647
Year: 2006
Pages: 138
Authors: Bob Bryla

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net