Data Control Language (DCL)

 Data Control Language (DCL)

GRANT:

·       Granting and revoking privileges add an additional security to the Oracle database system.

·       In multiple – user environment, we need to maintain security of the database access and use. Using Oracle Server database security.

Creating user

·       The oracle system has two users  SYSTEM with password Manager and another user SYS.

·       SYSTEM has all privileges, logon to SYSTEM and create a user using CREATE USER command.

 Syntax: 

CREATE USER username IDENTIFIED BY password;

 Example:

   CREATE USER xxx IDENTIFIED BY ……;

Logging on to one user from another user By using CONNECT command,

Syntax:

CONNECT username/password;

   Example:

CONNECT xxx/……;

Types of Privileges:

·       A permission granted to a user to do some action is called a privilege. A user can not do any action if he does not have the privilege to do that action.

·       There are two types of privileges. They are

1.    System privilege

2.    Object privilege

System privilege:

The Permission granted to execute various data definition, commands like CREATE TABLE, CREATE SEQUENCE, CREATE SESSION are called System privileges.

Some of the System privileges are

CREATE SESSION	Log on to database
CREATE TABLE	To create table
CREATE ANY TABLE	allow creating table in any schema (user)
CREATE SEQUENCE	To Create a sequence
CREATE ANY SEQUENCE	To Create a sequence in any schema
CREATE PROCEDURE	To create procedure
CREATE TRIGGER	To create trigger
CREATE ANY TRIGGER	To create trigger in any schema
CREATE VIEW	To create view
CREATE ANY VIEW	To create view in any schema
ALTER ANY TABLE	To alter any table
ALTER ANY SEQUENCE	To alter any sequence in any schema
ALTER ANY VIEW	To alter an view
ALTER ANY PROCEDURE	To alter any procedure in any schema
DROP ANY TABLE	To drop any table
DROP ANY TRIGGER	To drop any trigger
DROP ANY SEQUENCE	To drop any sequence in any schema

Example:

 Create user xxx identified by yyy;

Changing the password,

 Alter user xxx identified by yyyzz;

Granting System privilege

GRANT command is used to give system privileges to an Oracle USER.

Syntax:  

GRANT  system privilege to user;

Example:    

GRANT create session to xxx;  

Connect xxx/yyy;

Object Privileges:

An Object privilege enables a user to execute some command on database objects like table, view, sequence etc.

Granting Object Privileges

·       Different object privileges are available for different types of schema objects. A user automatically has all object privileges for schema objects contained in the user’s schema.

·       A user can grant any object privilege on any schema object that the user owns to any other user. If the grant includes the GRANT OPTION, the grantee can further grant the object privilege to other users.

Otherwise, the grantee can use the privilege but cannot grant it to other users.

Syntax

GRANT object_priv [(columns)] ON object TO {user|PUBLIC} [WITH GRANT OPTION]

Explanation:

object_priv  -  is an object privilege to be granted

columns         - specifies the Column from a table or view on which   privileges are granted

ON Object   - is the object on which the privilege are granted

TO                - identifies to whom the privilege is granted

PUBLIC     - grants object privileges to all users

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

Example:

GRANT select ON emp TO xxx;

Revoking the Permissions:

·       Permissions granted to a user can also be taken back by the grantor. This is done by the REVOKE command.

Revoking System privileges

The REVOKE statement is used to remove the system privileges granted to the user.

Example:

Revoke create table to xxx;

Revoking Object Privileges

The REVOKE statement is used to remove privileges granted to other users.

Syntax

REVOKE objectprivilege [object privilege] … ON objectname FROM username;

Example

REVOKE SELECT, INSERT ON emp  from xxx;

Drop user

 If you want to drop a particular user by using drop user command.

 Syntax:

drop user username;

Example:

drop user xxx;

Note: it will be executed from the SYSTEM user.

 

Share: