Oracle Schema and Users

Oracle Schema and Users

ORACLE USER v/s ORACLE SCHEMA

 

Database User

To access the database a user must specify a valid user account and successfully authenticate as required by that account.
The user accounts will have the following:

·         Unique Username : Usernames cannot

ü  exceed 30 characters

ü  contain special characters

ü  must start with a letter

·         Authentication mode: Common authentication mode is password, but oracle 10g supports several other authentication modes like certificate, and token authentication.

            Authentication techniques:

ü  Password: Create user with an associated password that must be supplied when the user attempts to establish a connection.

ü  External: also referred as authentication by operating system, users can connect to oracle more conveniently, without specifying a username or password.

ü  Global: Strong authentication through oracle advanced security option. This allows users to be identified through the use of certificates, token devices and the oracle internet directory.

·         Default Tablespace: Location where user will create objects if user doesn’t specify some other tablespace. (Note: User must be granted with privilege of creating objects and a quota of tablespace explicitly).

·         Temporary Tablespace: Place where user can create temporary objects such as sorts and temporary tables.

 i) Creating a User:

        CREATE USER user_dba 

        IDENTIFIED BY user_dba        -- Assign password

        DEFAULT TABLESACE  praveen     -- Assign space for table and 

        TEMPORARY TABLESPACE temp;    -- Assign sort space

ii) Assign the required privileges:

GRANT CONNECT, RESOURCE TO user_dba;

GRANT DBA TO user_dba; -- Make user a DB Administrator

 iii) Give the user some space quota on its tablespaces:

ALTER USER user_dba quota unlimited on praveen;

Locking and Unlocking User Accounts:
Oracle user accounts can be locked, unlocked, forced to choose new passwords, etc. For example, all accounts except SYS and SYSTEM will be locked after creating an Oracle9iDB database using the DB Configuration Assistant (dbca). DBA's must unlock these accounts to make them available to users.
Look at these examples:

ALTER USER scott ACCOUNT LOCK    -- lock a user account

ALTER USER scott ACCOUNT UNLOCK; -- unlocks a locked users account

ALTER USER scott PASSWORD EXPIRE; -- Force user to choose new password

Changing user’s passwords:

ALTER USER <username> IDENTIFIED BY <new password>;

SQL> password

Changing password for SCOTT

Old password:

New password:

Retype new password:

SQL> passw scott

Changing password for scott

New password:

Retype new password:

Password changed

Note: Oracle usernames and passwords are not case sensitive in database versions below Oracle 11g.

Dropping a user:

DROP USER user_dba CASCADE;           -- Remove user

Profiles and Users :
Profiles impose a named set of  Resource limits on database usage and instance resources. Profiles also place limitations on user’s password (length, expiration time,..). Every User is assigned to a profile and may belong to only one profile at any time.

CREATE PROFILE my_profile LIMIT

       PASSWORD_LIFE_TIME 30;

ALTER USER user_dba PROFILE my_profile;

Note: Profiles cannot impose resource limitation on user unless RESOURCE_LIMIT initialization parameter is set to TRUE . With RESOURCE_LIMIT at its default value of FALSE, Profile limitations are ignored.
Administrative Privileges :

Administrative privileges that is required for an administrator to perform basic

database operations, such as creating the database and instance startup and shutdown,

are granted through two special system privileges, SYSDBA and SYSOPER. You must

have one of these privileges granted to you, depending upon the level of authorization

you require.

Note: The SYSDBA and SYSOPER system privileges allow access to a

database instance even when the database is not open. Control of

these privileges are totally outside of the database itself.

The SYSDBA and SYSOPER privileges can also be thought of as types of connections

that enables you to perform certain database operations for which privileges cannot be

granted in any other fashion. For example, you if you have the SYSDBA privilege, you

can connect to the database by specifying CONNECT AS SYSDBA.

Schemas and Common Schema Objects :

A schema is a collection of database objects owned by a particular user. A schema has the same name as the user that owns the schema. Schema objects are the logical structures that directly refer to the database’s data.

                 Schema objects include structures like:

ü  Tables

ü  Views

ü  Indexes

ü  Synonyms

Note : There is no relationship between a tablespace and a schema. Objects in the same schema can be in different tablespaces and a tablespace can hold objects from different schemas.

Illustrates the relationship among schema objects, tablespaces, and datafiles.

When we create the database, a number of schemas are created. Two of particular importance is:

SYS Schema: All of the base tables and views that constitute the data dictionary are created in SYS Schema. Data dictionary is created in SYSTEM Tablespace when the database is created and is updated by oracle database server when a DDL statement is executed.

Note: Objects in SYS Schema should never be modified by any user or database administrator, and no one should create any tables in schema of user SYS.

SYSTEM Schema: Contains additional tables and views that sore administrative information and internal tables and views used by various oracle options and tools. Users shouldn’t create any additional objects in the SYSTEM schema.

            During a complete installation of an oracle database, the sample schemas are installed automatically with the database. The sample schema serves the purpose of providing a common platform. For example:  HR (Human Resource)

Difference between Schema and user:

* A schema is collection of database objects, including logical structures such as tables, views, sequences, stored procedures, synonyms, indexes, clusters, and database links.
* A user owns a schema.
* A user and a schema have the same name.
* The CREATE USER command creates a user. It also automatically creates a schema for that user.
* The CREATE SCHEMA command does not create a "schema" as it implies, it just allows you to create multiple tables and views and perform multiple grants in your own schema in a single transaction.
* For all intents and purposes you can consider a user to be a schema and a schema to be a user.