Introduction of dcl in sql -: The full name of the dcl is Data Control Language. A data control language (dcl) is a syntax similar to a computer programming language used to control access to data stored in a database. In particular, it is a component of Structured Query Language (SQL).
In other words we can say that the The Data Control Language (DCL) is a subset of the Structured Query Language (SQL) and allows database administrators to configure security access to relational databases.
Types of data control language -:
- Controlling user access
- Privileges
- Schema
Controlling User Access-: In a multiple user environment we want to maintain security of the database access and user. With controlled user access
- Control database access.
- Give the specific object in the database.
- Confirm given and received privilege with data dictionary.
- Create synonyms for database object
Data security are two types
- System Security-: System security covers access and use of database at the system level such as a user name and password disk space allocated to users and system operation allowed by the user.
- Data Security-: It covers access and use of the database object and the actions that those users can have on the object.
Privileges -: These are the right to execute particular SQL statement. The database administration is a high level user with the ability to grant user access to the database and its object. The user requires system privileges to gain access to the database and object privilege to manipulate the content of the object in database. User can also given the privileges to grant additional privileges to other user or to the role which are named groups of related privileges.
A.System Privileges- There are more than 80 system privileges are available for user and role. This privilege typically are provide by database administration . The main part of system privileges are.
1. Typically database administration -:
Create User -: It allows grantee to create users to database administration As like Create User username Identified by Password;
In the syntax User name is the name of the user to be created and password is the specified that the user must log in with the password.
Create User scott Identified by tiger;
Once a user created the database administration can grant specific system privileges to a user
Grant privilege [,privilege….] To user [,user…];
in the syntax privileges is the system privilege to be granted and the user is the the name of user.
Drop User -: In the system Privileges we can drop another user .
Drop any table -: In the system database privilege we drops a table in any schema.
Backup any table-: we can do backup any table in any schema with the export utility.
2 .Typical User Privilege-: This is the part of system privileges that are created for the user privileges these are the
- Create a Session- Connect to database
- Create table- create a table in the user schema
- Create sequence- create a sequence in the user’s schema
- Create view- Create a view in user’s schema
- Create procedure – Create a stored procedure function or privileges in the user schema.
B. Object Privileges.-:An object privilege is a privileges or right to perform a particular action on a specific table,view sequence or procedure. Each object has a particular set of guaranty privileges. These are the
ALTER,DELETE,EXECUTE,INDEX,INSERT, REFERENCE, SELECT, UPDATE
Different object privilege are available for different types of schema object.A user automatically has all object privilege for schema object oriented in the user’s schema. A user can grant any object privilege on any schema object that the user owns to any other user or role.
Grant object_priv[(columns)] on object To {user ! role ! public } [With grant option];
Schema- : Schema is a collection of objects such as a tables views and sequences. The schema is owned by a database user and has the same name as the user.