Database Users and Schemas

Each database has a list of usernames. To access a database, a user must use a database application and attempt a connection with a valid username of the database. Each username has an associated password to prevent unauthorized use.

Security Domain #

Each user has a security domain, a set of properties that determine such things as

• The actions (privileges and roles) available to the user.

• The schema quotas (available disk space) for the user.

• The system resource limits (for example, CPU processing time) for the user

Schema Objects #

A schema is a collection of logical structures of data, or schema objects. A schema is owned by a database user and has the same name as that user. Each user owns a single schema. Schema objects can be created and manipulated with SQL and include the following types of objects:

• Clusters

• Constraints

• Database links

• Database triggers

• Dimensions

• External procedure libraries

• Index-organized tables

• Indextypes

• Java classes, Java resources, Java sources

• Materialized views

• Materialized view logs

• Object tables

• Object types

• Object views

• Operators

• Packages

• Sequences

• Stored functions, stored procedures

• Synonyms

• Tables

• Views

Non Schema Objects #

Other types of objects are also stored in the database and can be created and manipulated with SQL but are not contained in a schema:

• Contexts

• Directories

• Parameter files (PFILEs) and server parameter files (SPFILEs)

• Profiles

• Roles

• Rollbacksegments

• Schemas

• Users

The system schema contains several objects (tables and indexes). The data schema also contains several objects (tables, indexes, and clusters). For example, In oracle, the system schema is stored in the DBFILE1 datafile. It is physically stored on one of the disks of Disk Drive 1. The data schema is stored in the DBFILE2 and DBFILE3 datafiles. The datafiles are stored on two of the disks of Disk Drive 1, including the disk that the system schema is stored on.

SQL Supports the Following Categories of Commands #

Data Definition Language (DDL) #

Following commands are used as part of DDL

• create
• alter
• drop
• truncate
• rename
• commands

Data Manipulation Language (DML) #

Following commands are used as part of DML

• insert
• select
• delete
• update

Data Control Language (DCL) #

Following commands are used as part of DCL

• grant
• revoke

Transaction Control Language (TCL #

Following commands are used as part of TCL

• commit
• savepoint
• rollback

A SQL statement can be thought of as a very simple, but powerful, computer program or instruction. SQL statements are divided into the following categories:

Data Definition Language (DDL) Statements #

Data definition language statements define, maintain, and drop schema objects when they are no longer needed. DDL statements also include statements that permit a user to grant other users the privileges, or rights, to access the database and specific objects within the database.

Data Manipulation Language (DML) Statements #

Data manipulation language statements manipulate the database’s data. For example, querying, inserting, updating, and deleting rows of a table are all DML operations. Locking a table or view and examining the execution plan of an SQL statement are also DML operations.

Data Control Language (DCL) Statements #

Data Control Language provides users with privilege commands. The owner of database objects, say Tables, has the sole authority over them. The owner can allow other database users access to the objects as per his/her discretion. Granting privileges(insert, select..) to others allows them to perform operations within their(privileges) Preview. Privileges granted can also be withdrawn by the owner any time.

Transaction Control Statements (TCL) #

Transaction control statements manage the changes made by DML statements. They enable the user or application developer to group changes into logical transactions. Examples include COMMIT, ROLLBACK, and SAVEPOINT.