INDEXING, CLUSTERING, SEQUENCE AND PSEUDO COLUMNS

*  What is an index

*  Why to use an index

*  Creating an index

*  When Oracle does not use index

*  Clustering

*  Sequence

*  Pseudo columns

What is an Index?

I believe, the best way of understanding an index in Oracle (or any database system) is by comparing it with the index that we find at the end of a textbook. For instance, if you want to read about indexing in an Oracle textbook, you will use index of the book to locate the topic indexing. Once the topic is found in index then you take the page number of that topic from index. Then you go to the page with that page number and start reading about indexing.

The concept of indexing in Oracle is same as the concept of book index.  First let us look at the features of an index of a textbook.

q  It is at the end of the textbox so that you need not search for the index in the first place.

q  It contains all topics of the book in the ascending (alphabetical) order of the topics.

q  After the topic the page number(s) where the topic is found in the book is listed.

q  Index does increase the size of the book by a few pages.

q   We use index only when we need it. In other words, we use index when we feel it is going to help locating the topic quickly.

All the characteristics of an index in a textbook will be found in an index of Oracle. The following are the characteristics of an index in Oracle.

q  Index is used to search for required rows quickly.

q  Index occupies extra space. Index is stored separately from table.

q  Index contains the values of key – column on which index is created – in the ascending order.

q  Just like the page number in book index, Oracle index stores ROWID – a unique value to internally identify each row of the table. For each entry in the index a key and corresponding ROWID are stored.

q  Oracle uses index only when it feels the index is going to improve performance of the query.

Note: ROWID is a unique value assigned to each row created in a table. Once ROWID is assigned to a row it doesn’t change during the lifetime of the row. Oracle access rows internally using ROWID.

An index in Oracle is internally stored as Self-balancing binary tree. A data structure that makes searching for a particular value faster.

Why To Use An INDEX

An index in Oracle is used for two purposes.

q  To speed up searching for a particular value thereby improving performance of query.

q  To enforce uniqueness

Using index to improving performance

Just like how you can quickly locate a particular topic in the book by using index at the end of the book, Oracle uses index to quickly locate the row with the given value in the indexed column. Indexed column is the one on which index is created.

For example if you want to search for a particular student by name then Oracle does the following without and with index.

q  Without index, Oracle will start looking for the given name at the first row of the table and continues until it finds the name or until end of the table is reached. This could be very time consuming process especially for tables with many rows.

q  With index, Oracle will use index to search for the given name. Since index is stored in the form of binary tree, locating the name in the index is going to be very fast. Then by using the ROWID obtained from index entry, Oracle will take the row from the table.

Tables with more number of rows will greatly benefit from indexes. Having an index and not having an index could make a lot of difference for large tables with thousands of rows.

Enforcing uniqueness with index

An index may also be used to enforce uniqueness in the column(s) given in the index. Once a UNIQUE index is created, Oracle makes sure values in the indexed column(s) are unique.

Note: A UNIQUE index is automatically created on the columns with PRIMARY KEY and UNIQUE constraints.

Creating an Index

DDL command CREATE INDEX is used to create an index. The following is the syntax of this command.

CREATE [UNIQUE] INDEX index_name

     ON table (column-1 [, column-2]...);

UNIQUE keyword is used to create a unique index. Unique index makes sure that the indexed column(s) is always unique.

To create an index on NAME column of STUDENTS table,  enter:

create index students_name_idx

 on  students (name);

If you want to create a unique index on NAME column of STUDENTS table, enter:

create unique index students_name_idx

       on students(name);

It is also possible to create an index on more than one column.  The following command creates an index on CCODE and FCODE columns of BATCHES table.

create index  batches_ccode_fcode_idx

on  batches ( ccode,fcode);

Note: When two or more columns are used in an index then give the column in the order of frequency of usage. That means the most frequently used column should be given first.

Just like an index of the book, which is not used every time you read the book, even Oracle index is not used every time by Oracle. In the next section we will see when Oracle uses index and when not.

When Oracle does not use index?

The best part of Oracle index is; it is completely automatic. That means, you never have to explicitly refer to an index.  Oracle decides whether to use an index or not depending upon the query.

Oracle can understand whether using an index will improve the performance in the given query. If Oracle thinks using an index will improve performance, it will use the index otherwise it will ignore the index.

 

Let us assume we have an index on NAME column of STUDETNS table. Then the following query will use the index as we are trying to get information about a student based on the name.

select  * from students

where  name = 'Richard Marx';

But in the following case Oracle does NOT use index, as index’s usage doesn’t improve performance of the query.

SELECT doesn’t contain  WHERE clause

If SELECT command doesn’t contain WHERE clause then Oracle doesn’t use any index. Since all rows of the table are to be retrieved, Oracle will directly access the table without referring to any index.

In the following example no index of STUDENTS table will be used.

select  * from students;

SELECT contains WHERE clause, but WHERE clause doesn’t refer to indexed column.

In this case SELECT contains WHERE clause but the WHERE clause is not referring to any columns on which an index is created. For example, STUDETNS table contains two indexes – one on ROLLNO, as it is primary key and another on NAME (created earlier in this chapter).

The following query uses WHERE clause but condition is based on DJ column for which there is no index.

select * from students

where dj > ’16-jan-2001’;

However, note that if there is any index on DJ then Oracle will use that index.

WHERE clause uses indexed columns but indexed column is modified in the WHERE clause.

In this case WHERE clause refers to indexed columns but doesn’t refer to indexed column as it is.

In the query below, NAME column is used but as it is used with SUBSTR function that modifies the column in the condition, index created on NAME column will not be used.

select * from students

where   substr(name,4,3) = ‘Joh’;

Note: Data dictionary view USER_INDEXES provides information about indexes.

Creating function-based index

Prior to Oracle8i, it is not possible to create an index with an expression as the index column. Index column must be column of the table.  If any function is used with indexed column in the query then Oracle does not use index.  For example, if we created an index on NAME column of STUDENTS table as follows:

create index students_name_idx

 on students (name);

Then the above index cannot be used for the following query as indexed column is used with UPPER function.

select * from students

 where  upper(name) = 'RICHARD MARX';

It is also not possible to create any index based on an expression.

Oracle8i allows indexes to be created on the result of the expression.  For example, the following command creates an index on the result of UPPER function.

create index students_name_idx

 on  students ( UPPER(name));

As the result if we issue the following command Oracle can use index as the expression used in the query and expression used to crate index are same.

select * from students

where upper(name) = ‘RICHARD MARX’;

Note: To create a function-based index, user must have QUERY REWRITE system privilege.

The following is another example of expression-based index.  Here index is created on duration of the batch.

create index batches_duration_idx

   on batches ( enddate  - stdate);

The following query will make use of the above index.

select * from batches

where  enddate - stdate > 30;

Online indexing

Prior to Oracle8i, Oracle used to lock the table on which index is being created throughout creation process. This makes table unavailable for data manipulation during the creation of index.  But Oracle8i introduced online indexing, where Oracle doesn’t lock the table on which index is being built.

Online indexing is provided through the keyword ONLINE.

The following command creates index on NAME column of STUDENTS table without locking the table.

create index  students_name_idx

  on students (name)  online;

Note: Online indexing increases availability of the table. This is a very important facility especially for Internet databases where availability matters a lot.

Drawbacks of indexes

Though indexes increase performance of query, they can also decrease performance of data manipulation.

Many indexes on a table can slow down INSERTS and DELETES drastically.  For example, if we take 5 indexes on a table then every insertion of a new row will update all 5 indexes of the table. That means, one insertion will actually result in six insertions. Similarly every change to an indexed column will need  a change to index. 

Having more indexes is advantageous in case of tables that are primarily used only for querying and not updated frequently.

Though the extra space occupied by indexes is also a consideration, it may not matter much since the cost of data storage has declined substantially.

Dropping an index

You can drop an index using DROP INDEX command.  It removes the named index.  Removing an index will effect the performance of existing applications but not the functionality in any way.

Using and not using an index is transparent to users. Oracle decides whether to use or not on its own. However, it is possible for users to control the usage of index to certain extent using hints, which are directive to Oracle regarding how to execute a command. But hints are too heavy in a book meant for beginners.

DROP INDEX  indexname;

The following example drops the index created on NAME of STUDENTS table.

drop index student_name_idx;

Clustering

Clustering is a method of storing tables that are often used together (in joining) in one area of the disk. As tables that are related are stored in one area of the disk, performance of joining will improve.

In order to place tables in a cluster, tables have to have a common column. For example the following steps will create a cluster and place COURSES and BATCHES tables in the cluster.

A cluster is created with a name and a key. The key is the column, which must be present in each table that is placed in the cluster. The key is also called as   cluster key.

Steps

The following are the required steps to create a cluster and place tables into it.

1.    Create a cluster with CREATE CLUSTER command.

2.    Create an index on the cluster using CREATE INDEX command. This must be done before you insert rows into clustered tables.

3.    Place the tables into cluster using CLUSTER option of CREATE TABLE command.

Creating a cluster

A cluster is created using CREATE CLUSTER command. At the time of creating a cluster the name of the cluster and data type and name of the key must be given.

The following is the syntax of CREATE CLUSTER command.

CREATE CLUSTER clustername

    (column  datatype [, column datatype] ... );

The following command creates a cluster to store BATCHES and COURSES table. As CCODE is the common column of these two tables, it is to be the cluster key.  We specify the data type and name of the cluster key. However, name may be anything and it is never used after cluster is created.

create cluster course_batches

      (ccode  varchar2(5));

Creating cluster index

After the cluster is created and before any rows can be inserted into tables in the cluster, an index on the cluster must be created.

CREATE INDEX command is used to create an index on the cluster.  Unless an index is created on the cluster no data manipulation can be done on the tables that are placed in the cluster.

create index cl_ccode_index

           on cluster course_batches;

Placing tables into a cluster

Once a cluster is created, it may be used to store tables that are related. Each table loaded into cluster, must have a column that matches with cluster key.

A table must be placed in to cluster at the time of creation.  CLUSTER option of CREATE TABLE command specifies the cluster in to which the table must be placed.  It also specifies the name of the column in the table that matches the cluster key of the cluster.

The following commands create COURSES and BATCHES tables and place them into cluster.

create table courses

(

  ccode varchar2(5),

  name  varchar2(30),

  . . .

)

cluster course_batches(ccode);

create table batches

(

   bcode   varchar2(5),

   ccode   varchar2(5),

   . . .

)

cluster course_batches(ccode);

Note: Placing a table into cluster is transparent to users. Users and application will use the table in the same manner whether the table is in the cluster or not.

Storage of clustered tables

When two tables are placed in a cluster, they are stored together on the disk making joining these tables faster.  Apart from that storing table in a cluster will also reduce the space requirement. This is because of cluster storing common column of the clustered tables only for once. In the above example, each unique CCODE is stored only for once. That means for course ORA though there are multiple batches, the value ORA is stored only for once in the cluster.

Sequence

Sequence is an object in Oracle database, which is used by multiple users to generate unique numbers.   Sequence is typically used to generate primary keys like account number, employee number etc., where uniqueness and sequence matter. 

In order to use a sequence, first it is to be created using CREATE SEQUENCE command. Then pseudo columns NEXTVAL and CURRVAL are used to retrieve unique values from sequence.

The following is the syntax of CREATE SEQUENCE command .

CREATE SEQUENCE sequencename

      [INCREMENT BY integer]

      [START WITH integer]

      [MAXVALUE integer | NOMAXVALUE]

      [MINVALUE integer | NOMINVALUE]

      [CYCLE | NOCYCLE];

The following is the description of available options.

Option	Meaning
START WITH	Specifies the values at which sequence must start. Default is 1.
MAXVALUE	Maximum value the sequence can generate. Default is 10e27-1.
MINVALUE	Minimum value the sequence can generate. Default is 1.
INCREMENT BY	Specifies by how much the value of the sequence is to be incremented. If you want numbers in the descending order give negative value. Default is 1.
CYCLE	Restarts numbers from MINVALUE after reaching MAXVALUE.

Table 1: Options of CREATE SEQUENCE command.  

The following command creates a sequence called ROLLNO to generate roll numbers for students.

create sequence rollno

 start with 100

 increment by 1;

The above sequence starts generating numbers at 100 and increments the number by 1 every time the number is taken.

Once a sequence is created, it can be used to generate unique numbers in the sequence. Once a value is taken from sequence, Oracle automatically increments the value by one (or whatever value specified using INCREMENT BY option). Oracle guarantees uniqueness. Once it gives the number then it doesn’t give that number to anyone else.

The following two pseudo columns are used to access the next and current value of the sequence.

NEXTVAL

This pseudo column will yield the next value from sequence and automatically increments the value of the sequence.

CURRVAL

This returns the value that is taken by most recent NEXTVAL. This cannot be used unless NEXTVAL is first called.

The following examples will demonstrate how to use NEXTVAL and CURRVAL pseudo columns.

select  rollno.nextval  from  dual;

  NEXTVAL

---------

      100

As the sequence starts with 100, first NEXTVAL returned 100. And it also increments the sequence by 1. See the next example below.

select  rollno.nextval  from  dual;

  NEXTVAL

---------

      101

CURRVAL pseudo column  returns the current value of the sequence, which is the value returned by most recent NEXTVAL.  In the following example CURRVAL returns 101 since that is the most recent value returned by NEXTVAL.

select rollno.currval from dual;

  CURRVAL

---------

      101

CURRVAL is used to reuse the values returned by most recent NEXTVAL.

The real usage of sequence is in inserting rows into table. The following INSERT command will use ROLLNO sequence to get next available roll number for a new student.

insert  into  students

       values (rollno.nextval ,   ...);

And if you want to insert a row into PAYMENTS table immediately with the same roll number then you can use CURRVAL as follows.

insert  into payments

      values ( rollno.currval, ...);

Though a sequence can guarantees uniqueness and sequence, its usage may not guarantee consecutiveness. See the following scenario.

insert into  students  values ( rollno.nextval, . . . );

commit;

insert into  students  values ( rollno.nextval, . . . );

rollback;

insert into  students  values ( rollno.nextval, . . . );

commit;

In the above scenario, if you take the value of ROLLNO.NEXTVAL as 102 for first INSERT then the value in the second INSERT will be 103. But as second INSERT command is rolled back that number is not actually used as roll number. Third INSERT command now takes 104 but not 103 as Oracle returns next available number, which is 104.

As you can see, 103 is not used at all in the roll number. While using sequence one must be aware of this potential gaps.

Altering a sequence

Some attributes of a sequence can be altered using ALTER SEQUENCE command.  

ALTER SEQUENCE sequencename

         [INCREMENT BY integer]

         [MAXVALUE integer | NOMAXVALUE]

         [MINVALUE integer | NOMINVALUE]

For instance, the following command changes MAXVALUE of ROLLNO sequence to 1000.

alter sequence rollno maxvalue 1000;

Dropping a Sequence

DROP SEQUENCE command is used to drop a sequence.

  DROP SEQUENCE sequencename;

The following command drops ROLLNO sequence.

drop sequence rollno;

Pseudo Columns

A pseudo column is used like a column in a table but it is not a column of any table. You can use pseudo columns just like table columns but you cannot  modify them.

The following is the list of available pseudo columns in Oracle.

Pseudo Column	Meaning
CURRVAL	Returns the current value of a sequence.
NEXTVAL	Returns the next value of a sequence.
NULL	Return a null value.
ROWID	Returns the ROWID of a row. See ROWID section below.
ROWNUM	Returns the number indicating in which order Oracle selects rows. First row selected will be ROWNUM of 1 and second row ROWNUM of 2 and so on.
SYSDATE	Returns current date and time.
USER	Returns the name of the current user.
UID	Returns the unique number assigned to the current user.

Table 2: Pseudo Columns.

The following are a few examples on usage of pseudo columns.

Display the name of the current user and user’s id.

SQL> select user, uid from dual;

USER                                 UID

------------------------------ ---------

BOOK                                  38

ROWID

Pseudo column ROWID returns the address of each row in the table. Oracle assigns a ROWID to each row.  Oracle uses ROWID internally to access rows. For instance, Oracle stores ROWID in index and uses it to access the row in the table.

You can display ROWID of rows using SELECT command as follows:

select rowid, ccode from courses;

ROWID              CCODE

------------------ -----

AAADC5AABAAAKaSAAA ora

AAADC5AABAAAKaSAAB vbnet

AAADC5AABAAAKaSAAC c

AAADC5AABAAAKaSAAD asp

AAADC5AABAAAKaSAAE java

AAADC5AABAAAKaSAAF xml

AAADC5AABAAAKaSAAG cs

Oracle provides a package called DBMS_ROWID to decode ROWID.  The following SELECT command displays only the row number from ROWID.

select  dbms_rowid.rowid_row_number(rowid) as rownumber ,ccode

from

courses

ROWNUMBER CCODE

--------- -----

        0 ora

        1 vbnet

        2 c

        3 asp

        4 java

        5 xml

        6 cs

Note: Once a row is assigned a ROWID Oracle does not change ROWID during the lifetime of the row. As the result you may see some row numbers missing in the output of above command. It means those rows were deleted.

For more information about ROWID and DBMS_ROWID package please see online documentation.

ROWNUM

This pseudo column yields the order in which Oracle has selected rows.

The following command displays row number of each row.

select rownum, ccode from courses;

   ROWNUM CCODE

--------- -----

        1 ora

        2 vbnet

        3 c

        4 asp

        5 java

        6 xml

        7 cs

ROWNUM can also be used in conditions but only two operators;  < and <=.  make sense with ROWNUM.

The following query displays first five courses.

select rownum, ccode from courses

where  rownum <= 5

   ROWNUM CCODE

--------- -----

        1 ora

        2 vbnet

        3 c

        4 asp

        5 java

However, the following is not meaningful usage of ROWNUM. It is because of the fact that Oracle assigns number to row after row is selected.

select rownum, ccode from courses

where  rownum = 5;

no rows selected

Also remember using Oracle assigns numbers to rows before rows are ordered using ORDER BY clause as demonstrated by the following query.

select rownum,ccode,duration,fee from courses

order by fee;

   ROWNUM CCODE  DURATION       FEE

--------- ----- --------- ---------

        3 c            20      3500

        6 xml          15      4000

        1 ora          25      4500

        5 java         25      4500

        4 asp          25      5000

        2 vbnet        30      5500

        7 cs           30      7000

Summary

Index and cluster are used to improve performance.  The concept of index in Oracle is similar to index found at the end of textbook.  Oracle doesn’t always use index instead Oracle uses index only when it feels, usage of index improves performance.  Cluster is another method of improving performance by storing related tables together on the disk.

Sequence is an object used to generate unique number in a sequence. Pseudo column yield values and used as columns of the table though they are not columns of any table.

SECURITY

*  Users and privileges

*  Object privileges

*  Granting object privileges

*  Using synonyms

*  Revoking object privileges

*  System privileges

*  Using roles

*  ALTER USER command

*  Data dictionary views

Users and Privileges

Every user in Oracle must have a valid username and password. In order to access Oracle database, one must logon by using username and password. 

At the time of creation of database, Oracle creates two users – SYSTEM and SYS.  These two users have privileges to perform administrative operations such as creating users, altering and dropping users, roles, profile, tablespaces (pieces that make up the database) etc.

However, it is possible to create new users using CREATE USER command as follows:

SQL> create user sateesh identified by oraclebook;

User created.

The above command creates a new user with the name SATEESH and password ORACLEBOOK. 

2.            Note: In order to create a new user you must logon either as SYSTEM or as SYS.

Though the account with the name SATEESH is created, it cannot even logon now as shown below.

SQL> connect sateesh/oraclebook;

ERROR:

ORA-01045: user SATEESH lacks CREATE SESSION privilege; logon denied

CONNECT command is used to move to another user from current user. In the above example, we were moving from SYSTEM to SATEESH.

The error indicates that user SATEESH lacks CREATE SESSION privilege. CREATE SESSION is a system privilege, which allows user to create a session with Oracle (logon).

3.            Note: A session with Oracle start from the point of login and ends when user logs out.

In order to permit user to create a session and perform other operations such as creating tables, view etc., user must be granted CONNECT and RESOURCE roles as follows.

SQL> grant connect, resource to sateesh;

Grant succeeded.

We will discuss more about roles later in this chapter. For the time being it is sufficient to know that these two roles will enable SATEESH to connect to Oracle database and also allow him to create and use object.

Privilege

A privilege is a right to access an object such as a table, view etc., or to execute a particular type of SQL command such as CREATE TABLE.

Privileges are classified into two categories depending upon what type of right they confer with the user.

q  System privileges

q  Object Privileges

4.             

5.            System privilege

A system privilege is a right to perform certain operation in the system. For example, CREATE SESSION privilege allows user to create a session with Oracle, CREATE TABLE privilege allows user to create table and so on.

Generally system privileges are granted to users through roles.  Only DBAs are concerned with system privileges.

6.             

7.            Object privilege

An object privilege is a right to perform a particular operation on an object.  An object either is a table, view, sequence, procedure, function, or package.  

The next section will discuss more about object privileges.

Object Privileges

User owns the object that he/she creates. Owner has complete access to the object. For example, if the object is a table then owner can select, insert, delete, update, alter, create an index on table and even drop the table.  Whereas other users do not have any access to the object, unless they are granted a privilege explicitly.

The following is the list of object privileges available in Oracle.

Privilege	What is permitted?
ALTER	Changing the definition of the object.
DELETE	Deleting rows from the object
EXECUTE	Execute the object.
INDEX	Creating an index on the object.
INSERT	Inserting rows into object
REFERENCES	Referencing the object in foreign key constraint.
SELECT	Selecting rows from the object.
UPDATE	Updating the data of the object.

Table 1: Object Privileges.

As you see in table 1, each object privilege specifies what can be done with an object. But not all object privileges are applicable to all   objects. For instance, ALTER privilege is not applicable to views, similarly EXECUTE privilege is applicable only to procedure and functions.  For the list of object privileges available on different types of objects, see table 2.

Object	Privileges Available
TABLE	SELECT, INSERT, DELETE, UPDATE, ALTER, INDEX, REFERENCES.
VIEW	SELECT, INSERT, UPDATE, AND DELETE.
SEQUENCE	SELECT, ALTER.
PROCEDURE, FUNCTION, PACKAGE and OBJECT TYPE	EXECUTE.

Table 2: Availability of object privileges.

8.                   

In the next section we will see how to grant privileges to other users so that they can access and perform required operation.

9.                   

Granting Object Privileges

In order to grant object privileges use GRANT Command.

10.     GRANT {object_priv|ALL} [(column [,column]...)]

11.        [,{object_priv | ALL} [(column [,column]...)]]...

12.        ON   object

13.        TO   {user | role | PUBLIC} [, {user | role | PUBLIC}] ...

14.        [WITH GRANT OPTION]

Object_priv is any of the object privileges listed in table 1.

ALL is used to grant all available object privileges on the object.

PUBLIC is used to grant the privilege to all the users of the system.

Now assume user SATEESH is the owner of COURSES table and he wants to grant SELECT privilege on COURSES table to user PRANEETH.  The following command will do just that.

grant select on courses

   to praneeth;

The following command grants all available privileges on COMPBATCHES view to PRANEETH.

grant all on compbatches

to praneeth;

It is possible to restrict the privilege to a few columns in case of UPDATE, INSERT and REFERENCES privileges.

The following command will grant UPDATE privilege to PRANEETH on DURATION column of COURSES table.

grant update(duration)

on courses

to praneeth;

WITH GRANT OPTION allows the grantee to grant the privilege to other users.

15.         Accessing other user’s objects

When a user wants to access a table of other user, the table name is to be preceded by the name of the user who owns the table.  Otherwise Oracle assumes that the current user owns the table and if table is not found under current user’s account then Oracle displays an error.

For example, if PRANEETH is trying to access COURSES table to which he has been granted SELECT privilege, from his account, the following happens.

SQL> select * from courses;

select * from courses

              *

ERROR at line 1:

ORA-00942: table or view does not exist

Oracle assumes COURSE table belongs to user PRANEETH. To specify that COURSES table is belonging to SATEESH and not PRANEETH, we have to precede the table name with username.

16.     owner.tablename

For example, to access COURSES table from user PRANEETH, give the following command.

select *

from sateesh.courses;

Using synonyms

To simplify accessing tables owned by other users, create a SYNONYM.  A synonym is an alias to a table or a view. By creating a synonym you can avoid giving the owner name while accessing tables of others.

The following CREATE SYNONYM command creates a synonym, which is an alias to COURSES table of SATEESH.

Remember synonym is to be created in the account of PRANEETH and not in the account of SATEESH.

SQL>create synonym COURSES for sateesh.courses;

Synonym created.

Once a synonym is created, you can use synonym to refer to the table. So to refer to SATEESH.COURSES, user PRANEETH may give:

SQL>select * from courses;

17.          

18.         PUBLIC Synonym

If synonym is to be available to all the users of the system, create a public synonym by including option PUBLIC in CREATE SYNONYM command.

The following sequence of commands will create a public synonym that is accessible to all the users in the system.

Grant select on courses to public;

The above command grants SELECT privilege on COURSES table to users of the database.

Then create a public synonym on SATEESH.COURSES so that any user can access the table using the synonym.

create public synonym courses

   for sateesh.courses;

19.          Note: To create a public synonym, you must have the CREATE PUBLIC SYNONYM system privilege.

Now, it is possible for anyone in the system to access SATEESH.COURSES table by using the public synonym. For example, user ANURAG can access COURSES table using public synonym as follows:

select  * from course;

20.         WITH GRANT OPTION

Using option WITH GRANT OPTION with GRANT command allows grantee to grant the privilege that he/she received to other users.

In the following example, SATEESH grants SELECT privilege on COURSE to PRANEETH with option WITH GRANT OPTION.

grant select on courses

to praneeth

with grant option;

Now user PRANEETH can grant SELECT privilege that he has received from SATEESH, to ANURAG as follows:

grant  select  on sateesh.courses

to  anurag;

Now user ANURAG can access COURSES table of SATEESH as follows.

select * from  sateesh.courses;

21.          Note: Though ANURAG has got privilege from PRANEETH; he has to give SATEESH.COURSES to access COURSES because it is owned by SATEESH.

Revoking Object Privilege

To revoke the privileges that were granted earlier, use REVOKE command.

The following is the syntax of REVOKE command.

Both GRANT and REVOKE commands are DCL commands.

22.     REVOKE {object_priv | ALL}

23.            [,{object_priv | ALL} ] ...

24.        ON [schema.]object

25.        FROM {user |role|PUBLIC} [,{user|role|PUBLIC}] ...  

26.        [CASCADE CONSTRAINTS]

Object_priv is the privilege that is to be revoked.  ALL revokes all privileges.

CASCADE CONSTRAINTS drops any referential constraint that was based on REFERENCES privilege granted earlier. For example, user A granted REFERENCES privilege to user B on CCODE column of COURSES table. And user B has referred to CCODE of COURSES in references constraint. If user A revokes REFERENCES privilege than references constraint will be deleted, if CASCADE CONSTARINTS options is used.

The following command will revoke SELECT privilege on COURSES from PRANEETH.

SQL> revoke  select  on  courses

  2  from  praneeth;

27.         Revoking is cascading

When a privilege is revoked from a user, if that user has previously granted that privilege to other users then all grantees of that privilege will also lose that privilege. For example, if A grants a privilege to B and B grants that to C, then both B and C will lose the privilege when A revokes the privilege from B.

What is a Role?

Role is a collection of privileges.  In cases where granting privileges user by user and table by table is lengthy, a role can come to your rescue.

The following are the important characteristics of roles:

q  A role is a collection of privileges. The privileges may consist of both system and object privileges.

q  A role is dynamic. Users of the role will enjoy whatever new privileges (added after the role has been granted to users) the role has been granted.  In other words changes made to privileges of the role will affect all the users of the role.

q  To create a role one must have CREATE ROLE system privilege.

q  Oracle comes with a few predefined roles such as CONNECT, RESOURCE, and DBA.

q  A single user can be granted multiple roles.

q  User will use only those roles that are enabled by default. However, user can enable/disable any roles that are granted to him.

The following sections will explain how to create and use role.

28.         Creating and using role

A role is created using CREATE ROLE command whose syntax is as follows:

29.          

30.      CREATE ROLE rolename

31.        [identified by password]

32.          

password is the password of the role. Users must use password of the role at the time of enabling the role. Password may be used for extra security.

The following are the three important steps related to roles. Let us see how to create and use a simple role called MANAGER.

33.          

Creating a role using CREATE ROLE command. The following command creates a role with the name MANAGER.

34.          

create role manager;

35.          

36.         Granting required privileges to role

GRANT command can be used to grant privileges to role. The following GRANT commands are used to grant privilege to MANAGER role.

37.          

grant select on courses to manager;

grant select, update, insert, delete on batches to manager;

grant all on students to manager;

grant select on payments;

38.         Granting role to users

A role can be granted to user using GRANT command as follows.

39.          

grant manager to praneeth;

grant manager to anurag;

Enabling a role

It is possible for a single user to have been granted more than one role. However, all roles granted to user may not be enabled by default. That means, the role is granted to user but user cannot use the privileges of the role.

At the time of creating user or even afterwards, administrator can specify which roles of the user must be enabled by default. The remaining roles are to be enabled explicitly.

If role MANAGER is granted to user but not enabled then user can enable the role as follows:

SQL > set role manager;

The privileges the user currently has, depend on the roles that are enabled. The roles that are not currently enabled are called as disabled roles and roles that are currently enabled are called as enabled roles.

For more details on SET ROLE command, please see on-line help.

40.         Dropping a role

It is possible to drop a role using DROP ROLE command. The following command drops role MANAGER that we created earlier.

drop role manager;

41.         Using password with role

It is possible to assign a password to a role. The password is assigned at the time of creating the role or after the role is created using ALTER ROLE command.

42.          

create role manager identified by efficient;

Then grant a few privileges to MANAGER role. Though DBA can only create the role, any user can grant privileges to role.

grant select on courses to manager;

Now grant role to user anurag;

grant manager to anurag;

Now if user ANURAG wants to access COURSES table through the role, first he has to enable the role as follows.

set role manager identified by efficient;

As the role is assigned a password, user must supply password at the time of enabling role.  If user doesn’t supply password while enabling role, the following error occurs.

SQL> set role manager;

set role manager

*

ERROR at line 1:

ORA-01979: missing or invalid password for role 'MANAGER'

ALTER USER Command

ALTER USER command can be used to modify the characteristics of a user. For example it can be used to modify:

q  Password

q  Default roles

To change the password of the current user, user may give:

alter user praneeth identified by tunu;

It is possible to specify the default roles of a user using DEFAULT ROLE option of ALTER USER command as follows:

alter user book default role all except manager;

43.          Note: Except changing password, user cannot change any other of his/her attributes.

Please see on-line help for remaining options in ALTER USER command and their usage.

Data Dictionary Views

Data dictionary views contain information regarding database in a simple form so that user can easily understand.  All data dictionary views are based on tables owned by user SYS.  Data dictionary tables and views are created at the time of creating database. 

The following is the list of data dictionary views that are commonly used.

Data dictionary view	What it contains?
ALL_TAB_COLUMNS	Columns of all tables and views accessible to the user.
ALL_OBJECTS	Objects accessible to user.
DICTIONARY	Description of data dictionary view.
USER_CATALOG	Tables, views, synonyms and sequences owned by user.
USER_CLUSTERS	Description of user’s own clusters.
USER_COL_PRIVS	Grants on columns for which the user is the owner, grantor or grantee.
USER_ERRORS	Current errors on stored objects owned by the user.
USER_INDEXES	Description of the user’s own indexes.
USER_OBJECTS	Objects owned by the user.
USER_SEQUENCES	Description of the user’s own sequences.
USER_SYNONYMS	The user’s private synonyms.
USER_TABLES	Description of the user’s own tables.
USER_TAB_PRIVS	Grants on tables for which the user is the owner, grantor, or grantee.
USER_TRIGGERS	Triggers owned by the user.
USER_TYPES	Object types created by user.
USER_USER	Information about the current user.
USER_VIEWS	Text of views owned by the user.

Table 3: Data dictionary views.

The following is the list of synonyms based on Data dictionary views.

Synonym	Data dictionary view
DICT	DICTIONARY
OBJ	USER_OBJECTS
CAT	USER_CATALOG
TABS	USER_TABLES
COLS	USER_TAB_COLUMNS
SEQ	USER_SEQUENCES.
SYN	USER_SYNONYM.
IND	USER_INDEXES.

Table 4:Synonyms for Data dictionary views.

To list all tables owned by current user, enter:

SQL> select * from tabs;

To list the all the objects and their types, enter:

select object_name, object_type from user_objects;

To get the query stored along with view, enter:

select view_name, text from user_views;

Summary

Security is an important feature of any multi-user database system. Oracle implements security using system privileges and object privileges. System privileges specify which commands a user can execute.  Unless otherwise specified an object (table, view etc.)  can be accessed only by the owner.  But using object privileges a user can allow other users to access his objects.

Roles are used to group privileges. When a role is granted to a user, all the privileges that are granted to role are granted to grantee of the role. It greatly simplifies the job of implementing security because with one role many privileges can be granted. 

Data dictionary views may be used to get useful information regarding objects, users, and system.