Privileges and Roles in SQL

1. What is a privilege?

Suppose we have created a table EMPLOYEES in the database. Now who can view or access these tables? No one other than who has created it! We need to explicitly tell the database, who can view this table and what kind of access like read, write, delete should be given to the other users.

Similarly, some of the users who were accessing this table have left the organization. But still they have access to this table, which is not acceptable. Hence we need to remove their access on such tables and database.

2. What is the SQL Grant statement, and how is it used to assign users privileges/permissions?

This provides the privileges to the users on the database objects. The privileges could be select, delete, update and insert on the tables and views. On the procedures, functions and packages it gives select and execute privileges. We can either give all the privileges or any one or more privileges to the objects. The syntax of GRANT is as below:

GRANT privilege_name 
ON object_name 
TO {user_name |PUBLIC |role_name} 
[WITH GRANT OPTION | WITH ADMIN OPTION];
  •  Privilege_name is the level of access given to the users. Some of the access rights are ALL, DELETE, UPDATE, INSERT, EXECUTE and SELECT.
  • Object_name is the name of a database object like TABLE, VIEW, PROCEDURE, FUNCTION, PACKAGE and SEQUENCE.
  • User_name is the name of the user to whom an access is being granted.
  • PUBLIC is used to grant access rights to all users.
  • ROLES are a set of privileges grouped together.

 

  • WITH GRANT OPTION / WITH ADMIN OPTION – allows a user to grant access rights to other users. i.e.; usually grants are given by the user who has created the database objects. But with this option, the users who have got the access rights can also provide the grants and access other tables/views.
GRANT SELECT, INSERT ON EMPLOYEES TO Bryan;

Here SELECT and INSERT grants are given to Bryan on EMPLOYEES table.

GRANT EXECUTE on sp_Example to Ryan;

This grant gives the execution rights to Ryan on the stored procedure sp_Example.

3. What is the SQL REVOKE statement? How does it work?

It removes the privileges given on the database objects. We can remove all the privileges or remove one or more privileges from the objects.

REVOKE privilege_name
ON object_name
FROM {user_name |PUBLIC |role_name}

REVOKE INSERT ON EMPLOYEES FROM Ryan; -- Removes the INSERT grant from Ryan on EMPLOYEES Table

Here we can notice that we have not removed all the grants on EMPLOYEES table. That means revoke can either remove all the grants on the table or remove only partial grants. Similarly roles and group list also can be partially removed.

4. What does the CREATE USER Statement do in SQL? What is the syntax and other details?

It is used to create database users – the users who can access and manipulate database data are created using CREATE USER statement. Nowadays, when we create the database itself it provides UI to create users. But after creating the database, if we need to create any new users, then we can issue this statement.

CREATE USER user_name
[IDENTIFIED BY password]
[other options];

It creates an user with user_name and password in the current database.

CREATE USER HR_USER
[IDENTIFIED BY hr_pwd];

User with HR_USER is created. The user with HR_USER name can login with hr_pwd password. In order to create the user, the user or the database should have the privilege, else it will throw an error “SQL Error: ORA-01031: insufficient privileges”.

5. What is the difference between system and object privileges?

System Privilege : A system privilege is the privilege where users are given privileges to create another user or table or view or even sessions.  This is used for database and its server privileges.

  • CREATE USER – this will give current user the privilege to create another user in the current database. Unless there is this permission, any user cannot create any other users.
  • CREATE TABLE – this will allow database user to create tables in the database. Else, the user will not be able to create any tables.
  • CREATE SESSION – this privilege will allow the database user to connect to the database.

Object Privilege : this is the privilege given to the users by which they can perform certain transaction on database objects. For example, for a user to insert, delete, update or select records from any table or view, that user should already have privilege on that table or view. This is done by using GRANT statement in SQL. It is used to provide privileges on database objects like tables, views, stored procedure, functions and packages.

Translate »