How we are Protecting the Data within the Database.

Authorization is permission given “to user, program, or process to access an object or set of objects. The type of data access granted to a user can be read-only, or read and write. Privileges specify the type of Data Manipulation Language (DML) operations like SELECT, INSERT, UPDATE, DELETE, etc., which the user can perform upon data.

The two methods by which the access control is done are by using privileges and roles.

• Privilege

• Role

Privileges

A privilege is permission to access a named object in a prescribed manner; for example, permission to query a table. Privileges can be granted enable a particular user to connect to the database (create a session); select rows from someone else’s table; or execute someone else’s stored procedure.

Database privileges

A privilege is a right to execute a particular type of SQL statement or to access another user’s object. Some examples of privileges include:

• The right to connect to the database (create a session)

• The right. to create a table

• The right to select rows from another user’s table

• The right to execute another user’s stored procedure

Privileges are granted to users so that these can accomplish tasks required for their job. You should grant a privilege only to user who absolutely requires the privilege to accomplish necessary work. Excessive granting of unnecessary privileges can lead to compromised security.

System privileges

A system privilege is the right to perform a particular action, on a particular type of object. For example, the privileges to create tables and to delete the OWS of any table in a database are system privileges. In many commercial database management systems there are hundreds of distinct system privileges.

Object privileges

An object privilege is a privilege or right to perform a particular action on a specific table, view, sequence, procedure, function, or package. For example, the privilege to delete rows from the table DEPT is an object privilege.

Object privileges granted for a table, view, sequence, procedure, function, or package apply whether referencing the base object by name or using a synonym.

Roles

A role is a mechanism that can be used to provide authorization. A single person or a group of people can be granted a role or a group of roles. One role can be granted in turn to other roles. By defining different types of roles, administrators can manage access privileges much more easily.

Database management systems provide for easy and controlled privilege management through roles. Roles are named groups of related privileges that you grant to users or other roles. Roles are designed to ease the administration of end-user system and object privileges.

The following properties of roles allow for easier privilege management within a database:

• Reduced privilege administration – Rather them explicitly granting the same set of privileges to several uses, you can grant the privileges for a group of related users to a role. Then, only the role needs to be granted to each member of the group.

• Dynamic privilege management – If the privileges of a group must change, only the privileges of the role need to be modified.

• Selective availability of privileges – You can selectively enable or disable the roles granted to a user. This allows specific control of a user’s privileges in any given situation.

• Application-specific security – you can protect role use with a password. Applications can be created specifically to enable a role when supplied the correct password. Users cannot enable the role if they do not know the password.

Granting and Revoking Privileges and Roles

You grant or revoke privileges and roles from users or other roles using the SQL commands GRANT and REVOKE. Who can grant and revoke the privileges and roles? The answer is “it depends on the DBMS”. For example, in Oracle, a with the GRANT ANY ROLE system privilege can grant or revoke any role to or from other users or roles of the database most database management systems, the Database Administrators (DBAs) and Security Officers will have the necessary powers to grant and revoke the privileges and roles to the users.

GRANT Command

The GRANT command gives users privileges to base tables and views.

The syntax of this command is as follows:

GRANT privileges ON object to users [with GRANT Option]
 
Here, object is either a base table or a view. If ‘with GRANT Option’ is specified, it means that the recipient has authority to grant the privileges that were granted to him to another user.
Examples
Grant the SELECT authority on the EMP table to all users.
GRANT SELECT ON EMP TO PUBLIC;
Grant the SELECT, DELETE and UPDATE authority on DEPT table to user 'AJAY'.
GRANT SELECT, DELETE, UPDATE ON DEPT TO AJAY;
Grant the SELECT.' DELETE and UPDATE authority with the capability to 
grant those privileges to others users on DEPT table to user 'AJAY'.
GRANT SELECT, UPDATE ON DEPT TO AJAY WITH GRANT OPTION;
Grant ALL privileges on EMP table to user 'DEEP'.
GRANT ALL ON EMP TO DEEP;
• Give the system privileges for creating tables and views to ‘AJAY’.
GRANT CREATE TABLE, CREATE VIEW TO AJAY
Grant the UPDATE authority on the SAL column of the EMP to user 'AJAY'.
GRANT UPDATE (SAL) ON EMP TO AJAY;
REVOKE Command
REVOKE is a complementary command to GRANT that allows the withdrawal of privileges. The syntax of the REVOKE command is as follows:
REVOKE [GRANT OPTION FOR] privileges
ON object FROM users {Restrict I Cascade}
The command can be used to REVOKE either a privilege or just the GRANT option on a privilege (by using the optional GRANT OPTION FOR Clause). When a user executes a REVOKE command with the CASCADE keyword, the effect is to withdraw the named privileges or GRANT option from all users who currently hold these privileges solely through a GRANT command that was previously executed by the same user who is now executive the REVOKE command. If these users received the privileges with the GRANT option and passed it along, those recipients will also lose their privileges as a consequence of the REVOKE command unless they also received these privileges independently. RESTRICT keyword is a complementary to CASCADE keyword.
Examples
Revoke the system privileges for creating table from 'AJAY';
REVOKE CREATE TABLE FROM AJAY;
Revoke the SELECT privileges on EMP table from ‘AJAY’;
REVOKE SELECT ON EMP FROM AJAY;
Revoke the UPDATE privileges on EMP table from all users.
REVOKE UPDATE ON EMP FROM PUBLIC;
Remove ALL privileges on EMP table from user 'AJAY';
REVOKE ALL ON EMP FROM AJAY;
Remove DELETE and UPDATE authority on the SAL and 
JOB columns of the EMP table from user 'AJAY'.
REVOKE DELETE, UPDATE (SAL, JOB) ON EMP FROM AJAY;
 [/vc_column_text][/vc_column][/vc_row]
You’ll also like:
What steps are needed to change the data in a database 
What is Data Mining? and Explain Data Mining Techniques. Compare between Data Mining and Data Warehousing. 
What is Distributed Database? Characteristics of Distributed Database Management System. 
What is Data Warehouse? Benefits & Problems of Data Warehousing. 
What is Data Replication? Advantages & Disadvantages of Data Replication. 
   
Next →
← Prev