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.
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
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:
External procedure libraries
Java classes, Java resources, Java sources
Materialized view logs
Stored functions, stored procedures
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:
Parameter files (PFILEs) and server parameter files (SPFILEs)
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
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
Data Manipulation Language (DML)
Following commands are used as part of DML
Data Control Language (DCL)
Following commands are used as part of DCL
Transaction Control Language (TCL
Following commands are used as part of TCL
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