DCL stands for Data Control Language. DCL statements can give or take away privileges to database objects or privileges to perform certain actions. At a minimum, most users are granted the right to connect to the database. Many users may not need to create tables, so they are not granted that privilege.
DCL (Data Control Language) Includes statements such as GRANT and REVOKE to provide or deny users or roles system or object privileges.
Privileges can also be granted to a role. A role is a way to bundle together multiple privileges into a single entity. This makes it easier to grant a group of privileges to one or more users in one easy step, rather than needing to enumerate each of those privileges every time you want to grant them to a new user (or to another role). The converse is also true: It's easier to revoke a role from a user than to remove the individual privileges that make up the role.
The GRANT Statement
The GRANT statement is almost self-explanatory. GRANT will give a privilege (either object or system) to a user, a role, or to all users. The basic syntax for granting both system and object privileges is as follows:
Role A group of related privileges that is referenced by a single name. Privileges can be assigned to a role, and a role can be assigned to a database user or to another role. Roles ease the maintenance issues with managing privileges for a large number of users who can be grouped into a relatively small number of categories based on job function.
Granting object privileges with the WITH GRANT OPTION clause allows the user or users granted that role the ability to pass those rights onto yet another user or role.
Suppose that Scott has acquired additional responsibilities and now must help to maintain the tables in the order-entry system, specifically the ORDER_ITEMS table owned by the user OE. The DBA grants the rights on this table to user SCOTT using the following command:Grant succeeded.
Scott can now add, delete, update, and view rows in the OE.ORDER_ITEMS table. He cannot, however, grant these privileges to other users or roles, since the WITH GRANT OPTION clause was not used by the DBA.
The REVOKE Statement
As you would expect, the REVOKE statement is the opposite of the GRANT statement. Either system privileges or object privileges can be revoked with the following basic syntax:
When the DBA granted the rights to SCOTT to work with the ORDER_ITEMS table, he noticed that the user OE had the DBA role assigned! This was obviously an oversight, so he corrected the situation immediately by using the REVOKE statement to remove the DBA role from OE:
The user OE retains all other object and system privileges granted by the DBA and other users.
Oracle DBA Related Interview Questions
|Oracle 10g Interview Questions||Oracle 9i Interview Questions|
|Oracle 8i Interview Questions||Oracle 11g Interview Questions|
|Oracle apps Interview Questions||Oracle Apps ERP Interview Questions|
|Oracle 7.3 Interview Questions||Oracle ADF Interview Questions|
|Oracle Application Framework Interview Questions||Oracle Apps Functional Interview Questions|
|Oracle Apps DBA Interview Questions||Oracle Workflow Interview Questions|
Oracle Dba Tutorial
Relational Database Concepts
Sql*plus And Isql*plus Basics
Oracle Database Functions
Restricting, Sorting, And Grouping Data
Using Multiple Tables
Advanced Sql Queries
Installing Oracle And Creating A Database
Creating And Maintaining Database Objects
Users And Security
Making Things Run Fast (enough)
Saving Your Stuff (backups)
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.