Controlling User Access
Controlling User Access
Database
administrator
Username and password
privileges
Users
SQL GRANT REVOKE Commands
DCL commands are used to enforce database security in
a multiple user database environment.
Two types of DCL commands are
GRANT
REVOKE.
Only Database Administrator's or owner's of the
database object can provide/remove privileges on a
database object.
SQL GRANT Command
SQL GRANT is a command used to provide access or
privileges on the database objects to the users.
The Syntax for the GRANT command is
GRANT privilege_name
ON object_name
TO {user_name |PUBLIC |role_name}
[WITH GRANT OPTION];
SQL GRANT Command
privilege_name is the access right or privilege granted to
the user. Some of the access rights are ALL, EXECUTE,
and SELECT.
object_name is the name of an database object like
TABLE, VIEW, STORED PROC and SEQUENCE.
user_name is the name of the user to whom an access
right is being granted.
PUBLIC is used to grant access rights to all users.
ROLES are a set of privileges grouped together.
WITH GRANT OPTION - allows a user to grant access
rights to other users.
SQL GRANT Command
GRANT SELECT ON employee TO user A;
This command grants a SELECT permission on employee
table to user A.
Use the WITH GRANT option carefully .if you GRANT
SELECT privilege on employee table to user A using the
WITH GRANT option, then user A can GRANT SELECT
privilege on employee table to another user, such as user
B etc.
Later, if you REVOKE the SELECT privilege on employee
from user A, still user B will have SELECT privilege on
employee table.
SQL REVOKE Command
The REVOKE command removes user access rights or
privileges to the database objects.
The Syntax for the REVOKE command is:
REVOKE privilege_name
ON object_name
FROM {user_name |PUBLIC |role_name}
SQL REVOKE Command
REVOKE SELECT ON employee FROM user A;
This command will REVOKE a SELECT privilege on
employee table from user A.
When you REVOKE SELECT privilege on a table from a
user, the user will not be able to SELECT data from that
table anymore.
However, if the user has received SELECT privileges on
that table from more than one users, he/she can SELECT
from that table until everyone who granted the
permission revokes it.
You cannot REVOKE privileges if they were not initially
granted by you.
Privileges and Roles:
Privileges defines the access rights provided to a user on
a database object.
There are two types of privileges.
1) System privileges - This allows the user to
CREATE, ALTER, or DROP database objects.
2) Object privileges - This allows the user to
EXECUTE, SELECT, INSERT, UPDATE, or DELETE
data from database objects to which the privileges
apply.
Roles
Roles are a collection of privileges or access rights.
When there are many users in a database it becomes
difficult to grant or revoke privileges to users.
Therefore, if you define roles, you can grant or revoke
privileges to users, thereby automatically granting or
revoking privileges.
You can either create Roles or use the system roles pre-
defined by oracle.
Creating Roles
The Syntax to create a role is
CREATE ROLE role_name
[IDENTIFIED BY password];
For Example: To create a role called "developer" with
password as “inaaya",the code will be as follows
CREATE ROLE developer
[IDENTIFIED BY inaaya];
Creating Roles
It's easier to GRANT or REVOKE privileges to the users
through a role rather than assigning a privilege directly to
every user.
If a role is identified by a password, then, when you
GRANT or REVOKE privileges to the role, you definitely
have to identify it with the password.
Creating Roles
For example:
To grant CREATE TABLE privilege to a user A by creating
a developer role.
First, create a developer Role
CREATE ROLE developer ;
Second, grant a CREATE TABLE privilege to the ROLE
developer.
You can add more privileges
Creating Roles
GRANT CREATE TABLE TO developer;
Third, grant the role to a user.
GRANT developer TO userA;
To revoke a CREATE TABLE privilege from developer
ROLE,
REVOKE CREATE TABLE FROM developer
Drop a role
The Syntax to drop a role from the database is as
below
DROP ROLE role_name;
For example: To drop a role called developer, you can
write:
DROP ROLE developer;