[go: up one dir, main page]
Scribd
Upload
39 views10 pages

Dba Vac

The document outlines the SQL commands for controlling user access in a database, specifically focusing on the GRANT and REVOKE commands. It details the syntax and usage of the GRANT command to provide privileges to users and the REVOKE command to remove those privileges. Additionally, it includes examples of user creation, granting privileges, and dropping users with the CASCADE option.

Uploaded by

guptanaise2604
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
39 views10 pages

Dba Vac

The document outlines the SQL commands for controlling user access in a database, specifically focusing on the GRANT and REVOKE commands. It details the syntax and usage of the GRANT command to provide privileges to users and the REVOKE command to remove those privileges. Additionally, it includes examples of user creation, granting privileges, and dropping users with the CASCADE option.

Uploaded by

guptanaise2604
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
You are on page 1/ 10

DBA VAC

Controlling User Access: Grant and Revoke


Controlling User Access: Grant
command

• 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];
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.
• 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.
Controlling User Access: connect system/tiger

• connect system/tiger;
• Create user
create user riya identified by riya123 default tablespace users
quota unlimited on users;

• connect system/tiger;
Grant command

Granting session to user


• grant create session to riya;
• connect system/tiger;
• grant create table to riya;

• user creating table


• connect riya/riya123;

• create table student( roll_no number(3), name varchar2(20));


• insert into student values(01,'shweta');
• connect system/tiger;
Grant

• create another user


• create user rj identified by rj123 default tablespace users quota unlimited
on users;
• grant create session to rj;
• connect riya/riya123;
• Granting select privilege to another user
• grant select on student to rj;
• connect rj/rj123;
• select * from riya.student;
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}
REVOKE command
• connect riya/riya123;

• revoke select on student from rj;


Drop User

• Syntax
• Drop user username;
• Drop user rj;
• Drop user riya cascade;
• Note: CASCADE to drop all objects in the user's schema before
dropping the user. You must specify this clause to drop a user whose
schema contains any objects.
Exercise Questions

Consider Employee and Department table insert 5 record records of your own.
1. Develop a query to grant all privileges of employees table into departments table.
2. Develop a query to grant some privileges of employees table into departments table.
3. Develop a query to revoke all privileges of employees table from departments table.
4. Develop a query to revoke some privileges of employees table from departments table.

You might also like