EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

Oracle GRANT

By Priya PedamkarPriya Pedamkar

Home » Data Science » Data Science Tutorials » Oracle Tutorial » Oracle GRANT

Oracle GRANT

Introduction to Oracle GRANT

An Oracle GRANT clause is a Data Control language statement. It is used to provide certain types of Rights to Users. The database can be shared with other users using Grant command. It is used for database security. It is a Data Control Language (DDL). The security upon databases is applied by defining and describing separate Schema Objects and Granting required Privileges upon them. In Oracle, the privileges can be granted using Grant command. Grant command is used when the database needs to be shared with other users. The other users are granted with a certain type of Rights. Grant command can be issued not only one Oracle Table objects, but also on Views, Synonyms, Indexes, Sequences, etc. In a single Grant command, multiple privileges can be issued to a user. A Grant command can be issued on an object by the owner of that object or the database owner. A Grant command can be issued on an object by the user who has ADMIN Option or has to Grant Any Privilege role.

Syntax

Below is the syntax for Oracle GRANT:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

GRANT<PrivilegeName_1>, <PrivilegeName_2>ON<ObjectName>TO<UserName / Role>;

Explanation:

PrivilegeName_1/ _2/ _N:It can be a privilege name.

ObjectName: Object name can be an Oracle object i.e. Table, Views, Synonyms, Indexes, and Sequence etc.

Username: User that will receive the privilege.

Role: It’s not a user but it’s a group of privileges that can be assigned to a User or another Role.

How does GRANT command work in Oracle?

The GRANT clause is used to prevent perform any unnecessary operation of the object by other users. Grant command allows that specific user to perform that specific or granted operation on that specific Oracle object. So that unknown user cannot access any object or data.

List of Some Commonly used Privileges:

below is the list of used privileges:

Type of Privilege Description
SELECT It allows users to perform SELECT statements on that object.
INSERT It allows users to perform INSERT records on that object.
UPDATE It allows users to perform UPDATE records on that object.
DELETE It allows users to perform DELETE records on that object.
REFERENCES It allows users to create a constraint that refers to that object.
ALTER It allows users to perform ALTER TABLE statements to change that object definition.
EXECUTE It allows users to compile or execute Function / Procedure directly.
INDEX It allows users to create an index on that object with the create index statement.
ALL It provides all-access to users.

Examples to Implement Oracle GRANT

Implementations of GRANT command with Examples:

In this section, we’ll see the implementation of Oracle GRANT Command and its behavior. For that, we will create a user to understand the Oracle GRANT command behavior.

1. Oracle CREATE privilege

Step1: The current session logged on as a SCOTT user and tried to create a new user.

Code:

CREATE USER EDUCBA IDENTIFIED BY EDUCBA_1;

Output:

Oracle GRANT1

SCOTT user

Explanation: In the above example, as a Scott user tried to create a new user but it returns an error “insufficient privileges”. Because the user (Scott) doesn’t have the privilege to create a new user for the DB.

Step2: Now connected to the current session as an SYSDBA. It’s like an admin role to the DB. Now will try to create a user in this session

Popular Course in this category
Sale
Oracle Training (14 Courses, 8+ Projects)14 Online Courses | 8 Hands-on Projects | 120+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.5 (8,901 ratings)
Course Price

View Course

Related Courses
Oracle DBA Database Management System Training (2 Courses)All in One Financial Analyst Bundle - 250+ Courses, 40+ Projects

Code:

CREATE USER EDUCBA IDENTIFIED BY EDUCBA_1;

Output:

create a user

Explanation: Now user (EDUCBA) is created successfully. So privileges are essential for the users to perform any operation on DB. But the user EDUCBA doesn’t have any privilege, even it does not have to create session privilege and because of that user is unable to log on to the DB.

Step3: To log on to the DB, the user needs to create session privilege.

Code:

GRANT CREATE SESSION TO EDUCBA;

Output:

Oracle GRANT4

Oracle GRANT5

Explanation: In the above query, SYSDBA provided a Create session privilege to EDUCBA. Now user “EDUCBA” is successfully created a session and logged into DB.

TIP: In the same way, object owners can GRANT privileges to users.

2. How to Check Received Privileges

All the privileges received by the user ‘EDUCBA’ or any other user can be checked. For that, the user needs to log on and run the below SQL query.

Code:

SELECT * FROM USER_SYS_PRIVS;

Output:

Privileges

Explanation: In the above example, the user “EDUCBA” has only created session privilege. So EDUCBA cannot access or perform any operation except create a session.

Code:

SELECT * FROM Employee;

Output:

Oracle GRANT7

Explanation: In the above example user “EDUCBA” tries to access an object ‘Employee’ and returns an error because the user does not have any privilege except create a session. To access objects or perform any operation, it requires respective privileges.

TIPS: Instead of providing privileges to users, better to create a Role, assign PRIVILEGES to that Role, and then Grant that Role to multiple Users and Roles.

Roles reduces the effort to provide privileges one at a time to users. The syntax for Role creation below:

CREATE ROLE<RoleName>IDENTIFIED BY<Password>;

Revoke is used to remove the privileges from users or Roles.

Conclusion

Oracle GRANT command is a very useful command to allow users to perform specific operations on the specific object. This is also very useful for security purposes. Without the privilege, users are not authorized to operate on the DB objects.

Recommended Articles

This is a guide to Oracle GRANT. Here we discuss an introduction to Oracle GRANT, syntax, how does it work, examples for better understanding. You can also go through our other related articles to learn more –

  1. Oracle Database Administration
  2. Oracle Self Join
  3. Oracle While Loop
  4. Oracle MD5

Oracle Training (14 Courses, 8+ Projects)

14 Online Courses

8 Hands-on Projects

120+ Hours

Verifiable Certificate of Completion

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
Oracle Tutorial
  • Advanced
    • Timestamp to Date in Oracle
    • Oracle Describe Table
    • Oracle Clauses
    • Oracle Having Clause
    • Oracle FOREIGN Key
    • PIVOT in Oracle
    • Oracle Alter Table
    • Oracle Queries
    • Oracle Views
    • Oracle Window Functions
    • Oracle String Functions
    • Oracle Date Functions
    • Oracle Analytic Functions
    • Oracle Aggregate Functions
    • Select in Oracle
    • INSERT in Oracle
    • DISTINCT in Oracle
    • Function in Oracle
    • Oracle GROUP_CONCAT
    • Oracle INSTR()
    • Oracle CONVERT
    • Oracle LENGTH()
    • Oracle EXISTS
    • Oracle REPLACE()
    • Oracle MERGE
    • Oracle LEAD()
    • Oracle EXTRACT()
    • Oracle LISTAGG()
    • Oracle SYS_CONTEXT()
    • Oracle COALESCE
    • Oracle NVL()
    • Oracle SYSDATE()
    • Oracle?Date Format
    • Oracle SYS_GUID()
    • Oracle WILDCARDS
    • Oracle Synonyms
    • Oracle Subquery
    • BETWEEN in Oracle
    • FETCH in Oracle
    • Oracle Index
    • Oracle Function-based Index
    • Oracle UNIQUE Index
    • Oracle Bitmap Index
    • Oracle Column
    • Oracle Triggers
    • Oracle Procedures
    • Sample Database for Oracle
    • Oracle LIKE Operator
    • ORDER BY in Oracle
    • Oracle ORDER BY DESC
    • GROUP BY in Oracle
    • Oracle GROUP BY HAVING
    • Oracle Aliases
    • Table in Oracle
    • Oracle Temporary Table
    • Oracle? Table Partition
    • Oracle rename table
    • Oracle CTE
    • Cursor in Oracle
    • Oracle LOCK TABLE
    • Oracle Tablespace
    • Oracle CARDINALITY
    • Oracle REGEXP
    • Oracle REGEXP_REPLACE
    • Oracle to_date
    • JSON in Oracle
    • Oracle COMMIT
    • Oracle GRANT
    • Oracle MD5
    • Oracle ROLLBACK
    • Oracle Users
    • Oracle TIMESTAMP
    • IF THEN ELSE in Oracle
    • Oracle While Loop
    • Oracle Clone Database
    • Oracle Backup Database
    • Oracle? XML
    • Oracle XMLAGG
    • Oracle XMLTABLE
    • Oracle Performance Tuning
    • Oracle B Tree Index
    • Oracle fusion
    • Oracle ebs
    • Oracle GRC
    • Oracle Cloud
    • Oracle HCM Cloud
    • Oracle Integration Cloud
    • Oracle Jinitiator
    • Oracle pathfinder
    • Oracle VirtualBox
    • Oracle Weblogic Server
    • Oracle decode
    • Oracle Exadata
    • Oracle ZFS
    • Oracle? utilities
    • JDBC Driver for Oracle
    • Oracle? DBA Versions
    • Oracle DBA Salary
  • Basic
    • Oracle Marketing Cloud
    • What is Oracle?
    • Career in Oracle
    • How to Install Oracle
    • Oracle Versions
    • What Is Oracle Database
    • Oracle Data Warehousing
    • Oracle Warehouse Builder
    • Career In Oracle Database Administrator
    • Career In Oracle DBA
    • What is Oracle RAC
    • Oracle DBA
    • Oracle? Vanderbilt
    • What is RMAN Oracle
    • Oracle Database Administration
    • Oracle Operators
    • Oracle Constraints
    • Oracle number
    • Oracle Data Types
    • Oracle UNIQUE Constraint
    • Oracle Check Constraint
  • Joins
    • Joins in Oracle
    • Inner Join in Oracle
    • Oracle Cross Join
    • Left Join in Oracle
    • OUTER Join in Oracle
    • Oracle Full Outer Join
    • Natural Join in Oracle
    • Oracle Self Join
    • Oracle hash join
    • Oracle? Update with Join
  • Oracle SET Operators
    • UNION in Oracle
    • Oracle UNION ALL
    • INTERSECT in Oracle
    • MINUS in Oracle
  • Interview Questions
    • Oracle Interview Questions
    • Oracle Apps Interview Questions
    • Oracle Apps Technical Interview Questions
    • Oracle Database Interview Questions
    • Oracle Forms Interview Questions
    • Oracle PL/SQL Interview Questions
    • Oracle RAC Interview Questions
    • Oracle SOA Interview Questions

Related Courses

Oracle Course Training

Oracle DBA Certification Course

MongoDB Certification Training

Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Special Offer - Oracle Training (14 Courses, 8+ Projects) Learn More