Question

Discuss at least three types of database privileges. Then explain how you can assign privileges or remove the privileges from database users.

Answer 1

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)
Create a table
Select rows from another user's table
Execute another user's stored procedure

You grant privileges to users so these users can accomplish tasks required for their job. You should grant a privilege only to a user who absolutely requires the privilege to accomplish necessary work. A user can receive a privilege in two different ways:

You can grant privileges to users explicitly. For example, you can explicitly grant the privilege to insert records into the employees table to the user SCOTT.
You can also grant privileges to a role (a named group of privileges), and then grant the role to one or more users. For example, you can grant the privileges to select, insert, update, and delete records from the employees table to the role named clerk, which in turn you can grant to the users scott and brian.

similarly, you can remove the privilages by revoking the privilages from the users or group.