3 Database Users

How do I…

3.1 Create a user account?

3.2 Grant and determine user disk quotas?

3.3 Summarize basic user account information?

3.4 Determine who is logged on?

3.5 Kill a user’s session?

3.6 Determine user defaults?

3.7 Determine user system privileges?

3.8 Determine user object privileges?

3.9 Create, modify, and associate user profiles?

What good is an application if nobody can use it? The management of user accounts and roles is fundamental to the development and maintenance of multiuser database applications. Each person using an Oracle database must have a user account. Users should not share accounts, and each user account should have a password to protect the database from unauthorized access. A privilege is the permission to perform a task on the database or to access another user’s database object. A system privilege enables the user to perform an action, such as create a session or create a table. An object privilege enables the user to access a specific object such as a table, view, or stored procedure. Within an organization, people perform different functions. A role is a group of related privileges that can be granted to user accounts as a single privilege. The structure of roles within Oracle enables database security to be modeled around the organization. A role can be defined at a high level, such as a person using the system, or at a more detailed level, such as an accountant, payroll clerk, or supervisor. Within a production system, user accounts and roles are usually managed by the database administrator.

3.1 Create a User Account

Users are the reason applications are developed. Creating user accounts is a fundamental task in delivering a new application. This How-To takes you through the steps of creating a new Oracle user account.

3.2 Grant and Determine User Disk Quotas

Disk space is a limited resource. In environments where users or developers can create their own data objects, it is important to restrict the amount of disk space a user account’s objects can occupy. This How-To explores the method used to restrict a user account’s disk space.

3.3 Summarize Basic User Account Information

Many times, it is important to learn the various attributes of a user account: the date the account was created, the date and time that the password will expire, the date that the account was locked, the default profile, the default and temporary tablespaces, and other attributes. This How-To guides you through the steps to find information on a particular user account.

3.4 Determine Who Is Logged On

One of the most frequent tasks of an Oracle database administrator is learning which users are connected to the database at any given time. This How-To shows you how to determine who is logged on.

3.5 Kill a User’s Session

Is it possible to kill a user’s session with SQL*Plus? Although the Session Manager is perfectly capable of performing this task, you might not always want to leave SQL*Plus to kill a session. This How-To demonstrates the power of SQL*Plus by using it to query the active sessions on the database and terminate a user’s session.

3.6 Determine User Defaults

When a user connects to the database, the session acquires specific default privileges and system limitations. Also, both permanent and temporary objects the user creates are placed in default tablespaces. This How-To describes the technique for determining these user defaults.

3.7 Determine User System Privileges

As a part of maintaining user accounts, it is necessary to determine which system privileges have been granted to them. User accounts can be granted privileges directly or through roles. This How-To presents methods for identifying the system privileges and roles granted to a user account.

3.8 Determine User Object Privileges

Each user account can be set up to have different SELECT, INSERT, UPDATE, DELETE, and ALTER privileges to any or all objects in the database. For security reasons, it is important to know which objects can be viewed or modified by which users. This How-To shows you the method for determining user object privileges.

3.9 Create, Modify, and Associate User Profiles

User account defaults and profiles define the default location of database objects and the quantity of usable database resources. This How-To covers the management of user defaults and the creation and assignment of user profiles.