Data Control and Transaction Control Command in Oracle

CREATING USERS :- Users are account name that are allowed to log on to the ORACLE database. In order to create users DBA role must be granted. Apart from the DBA role, there are other roles like CONNECT and RESOURCE which must be given to users so that they can login to ORACLE.

SYNTAX :

CREATE USER username IDENTIFIED BY password;

Example:-

CREATE USER dolly IDENTIFIED BY dolly;

Here login name is dolly and password is dolly.

TRANSACTION  COMMAND

Ø  A series of one or more SQL statements that are logically related or a series of operations performed on oracle table data is termed as a transaction.

Ø  First changes requested done. To make these changes permanent a commit statement has to be given at the SQL prompt. A rollback statement given at the SQL prompt can be used to undo a part of or the entire transaction.

Ø  A transaction begin with first executable SQL statement after a commit, rollback or connection made to oracle engine.

Closing Transaction:-

A transaction can be closed by using either a commit or a rollback statement.

ROLLBACK, COMMIT AND SAVEPOINT

Rollback, Commit and Savepoint are considered as Transaction that Oracle treats as a single unit. A set of changes made to a table using UPDATE, DELETE or INSERT are temporary.

COMMIT:- To make the changes permanent, COMMIT must be issued.

ROLLBACK:-Part or all of a transaction can be undone with the ROLLABACK statement.

A transaction begins with the execution of the first SQL in the transaction and ends with either the Rollback statement or the commit statement.

Example: DELETE FROM STUDENT WHERE CITY=’RAJKOT’;

                        COMMIT;

If a COMMIT is not issued, it is possible to undo the delete operation by issuing a ROLLBACK.

Example: DELETE FROM STUDENT WHERE CITY=’RAJKOT’;

     ROLLBACK;

In order to ROLLBACK to a certain point a SAVEPOINT may be issued.

Example:        SAVEJPOINT D1;

DELETE FROM STUDENT WHERE CITY=’RAJKOT’;

                        SAVEPPOINT D21;

                        DELETE FROM STUDENT WHERE CITY=’JAMNAGAR’;

                        ROLLBACK TO D2;

                        COMMIT;

                        Will undo the second delete operation.

Grant, Revoke (DCL - Data Control Language)

Anything as important as business data has to be well protected. SQL provides protection of data be allocating rights and privileges to authorized users. Their login identification and passwords identify authorized users. Users can be allocated rights and privileges on various database objects. They can have rights like:

Rights of selecting data (SELECT)

Rights of adding data (INSERT)

Rights of updating data (UPDATE)

Rights of deleting data (DELETE)

Grant

Object privileges are permissions on schema objects, such as tables, views etc. the privileges enables a user to perform a particular action on specific object.

Syntax:-

GRANT <object privilege>/ [ALL]

ON <object name>

TO <user/Role>

Example:-

Grant select on scott.emp to demo;

Revoke

Object privileges can be taken back using REVOKE command.

Syntax:-

REVOKE <object privilege>/ [ALL]

ON <object name>

FROM<user/Role>

Example:-

Revoke select on scott.emp from demo;

Role

A role is a set of privileges that can be granted to users or to other roles. To create a role you must have create role system privilege.

Syntax:-

Create role <rolename> identified by <password>;

Example:-

Create role st_role identified by user;

 Using role:-

Oracle uses roles to help in managing grants given to multiple users. A new role is initially empty. You can add privileges to a role with the grant option.

Example:-

Create a role student by using the SQL syntax.

Ø  Create role student;

If u want to give privileges to role use GRANT command .

Ø  Grant select on emp to student;

Ø  Grant update,delete on details to student.

Then give the role to some user by using grant.

Ø  GRANT STUDENT TO DEMO;

When you create a role oracle grants you the role with administrator option which allows you to

• Grant the role to another user or role.
• Revoke the role from another user or role.
• Alter the role.

Drop the role