How to Grant All Privileges to a User in Oracle

Last updated on Dec 20 2021
Sankalp Joshi

Table of Contents

How to Grant All Privileges to a User in Oracle

Granting all privileges to a replacement user

First, create a replacement user called super with a password by using the subsequent CREATE USER statement:

CREATE USER super IDENTIFIED BY abcd1234;

The super user created. Note that you simply should use a secure password rather than abcd124.

Second, use the GRANT ALL PRIVILEGES statement to grant all privileges to the super user:

GRANT ALL PRIVILEGES TO super;

Third, log in to the Oracle Database because the super user:

Enter user-name: super@pdborcl

Enter password:

And query the super user’s privileges:

SELECT * FROM session_privs
ORDER BY privilege;

Here is that the output in Oracle 12c:

PRIVILEGE

—————————————-

ADMINISTER ANY SQL TUNING SET
ADMINISTER DATABASE TRIGGER
ADMINISTER RESOURCE MANAGER
ADMINISTER SQL MANAGEMENT OBJECT
ADMINISTER SQL TUNING SET
ADVISOR
ALTER ANY ANALYTIC VIEW
ALTER ANY ASSEMBLY
ALTER ANY ATTRIBUTE DIMENSION
ALTER ANY CLUSTER
ALTER ANY CUBE

PRIVILEGE

—————————————-

ALTER ANY CUBE BUILD PROCESS
ALTER ANY CUBE DIMENSION
ALTER ANY DIMENSION
ALTER ANY EDITION
ALTER ANY EVALUATION CONTEXT
ALTER ANY HIERARCHY
ALTER ANY INDEX
ALTER ANY INDEXTYPE
ALTER ANY LIBRARY
ALTER ANY MATERIALIZED VIEW
ALTER ANY MEASURE FOLDER

PRIVILEGE

—————————————-

ALTER ANY MINING MODEL
ALTER ANY OPERATOR
ALTER ANY OUTLINE
ALTER ANY PROCEDURE
ALTER ANY ROLE
ALTER ANY RULE
ALTER ANY RULE SET
ALTER ANY SEQUENCE
ALTER ANY SQL PROFILE
ALTER ANY SQL TRANSLATION PROFILE
ALTER ANY TABLE

PRIVILEGE

—————————————-

ALTER ANY TRIGGER
ALTER ANY TYPE
ALTER DATABASE
ALTER LOCKDOWN PROFILE
ALTER PROFILE
ALTER RESOURCE COST
ALTER ROLLBACK SEGMENT
ALTER SESSION
ALTER SYSTEM
ALTER TABLESPACE
ALTER USER

PRIVILEGE

—————————————-

ANALYZE ANY
AUDIT ANY
AUDIT SYSTEM
BACKUP ANY TABLE
BECOME USER
CHANGE NOTIFICATION
COMMENT ANY MINING MODEL
COMMENT ANY TABLE
CREATE ANALYTIC VIEW
CREATE ANY ANALYTIC VIEW
CREATE ANY ASSEMBLY

PRIVILEGE

—————————————-

CREATE ANY ATTRIBUTE DIMENSION
CREATE ANY CLUSTER
CREATE ANY CONTEXT
CREATE ANY CREDENTIAL
CREATE ANY CUBE
CREATE ANY CUBE BUILD PROCESS
CREATE ANY CUBE DIMENSION
CREATE ANY DIMENSION
CREATE ANY DIRECTORY
CREATE ANY EDITION
CREATE ANY EVALUATION CONTEXT

PRIVILEGE

—————————————-

CREATE ANY HIERARCHY
CREATE ANY INDEX
CREATE ANY INDEXTYPE
CREATE ANY JOB
CREATE ANY LIBRARY
CREATE ANY MATERIALIZED VIEW
CREATE ANY MEASURE FOLDER
CREATE ANY MINING MODEL
CREATE ANY OPERATOR
CREATE ANY OUTLINE
CREATE ANY PROCEDURE

PRIVILEGE

—————————————-

CREATE ANY RULE
CREATE ANY RULE SET
CREATE ANY SEQUENCE
CREATE ANY SQL PROFILE
CREATE ANY SQL TRANSLATION PROFILE
CREATE ANY SYNONYM
CREATE ANY TABLE
CREATE ANY TRIGGER
CREATE ANY TYPE
CREATE ANY VIEW
CREATE ASSEMBLY

PRIVILEGE

—————————————-

CREATE ATTRIBUTE DIMENSION
CREATE CLUSTER
CREATE CREDENTIAL
CREATE CUBE
CREATE CUBE BUILD PROCESS
CREATE CUBE DIMENSION
CREATE DATABASE LINK
CREATE DIMENSION
CREATE EVALUATION CONTEXT
CREATE EXTERNAL JOB
CREATE HIERARCHY

PRIVILEGE

—————————————-

CREATE INDEXTYPE
CREATE JOB
CREATE LIBRARY
CREATE LOCKDOWN PROFILE
CREATE MATERIALIZED VIEW
CREATE MEASURE FOLDER
CREATE MINING MODEL
CREATE OPERATOR
CREATE PLUGGABLE DATABASE
CREATE PROCEDURE
CREATE PROFILE

PRIVILEGE

—————————————-

CREATE PUBLIC DATABASE LINK
CREATE PUBLIC SYNONYM
CREATE ROLE
CREATE ROLLBACK SEGMENT
CREATE RULE
CREATE RULE SET
CREATE SEQUENCE
CREATE SESSION
CREATE SQL TRANSLATION PROFILE
CREATE SYNONYM
CREATE TABLE

PRIVILEGE

—————————————-

CREATE TABLESPACE
CREATE TRIGGER
CREATE TYPE
CREATE USER
CREATE VIEW
DEBUG ANY PROCEDURE
DEBUG CONNECT ANY
DEBUG CONNECT SESSION
DELETE ANY CUBE DIMENSION
DELETE ANY MEASURE FOLDER
DELETE ANY TABLE

PRIVILEGE

—————————————-

DEQUEUE ANY QUEUE
DROP ANY ANALYTIC VIEW
DROP ANY ASSEMBLY
DROP ANY ATTRIBUTE DIMENSION
DROP ANY CLUSTER
DROP ANY CONTEXT
DROP ANY CUBE
DROP ANY CUBE BUILD PROCESS
DROP ANY CUBE DIMENSION
DROP ANY DIMENSION
DROP ANY DIRECTORY

PRIVILEGE

—————————————-

DROP ANY EDITION
DROP ANY EVALUATION CONTEXT
DROP ANY HIERARCHY
DROP ANY INDEX
DROP ANY INDEXTYPE
DROP ANY LIBRARY
DROP ANY MATERIALIZED VIEW
DROP ANY MEASURE FOLDER
DROP ANY MINING MODEL
DROP ANY OPERATOR
DROP ANY OUTLINE

PRIVILEGE

—————————————-

DROP ANY PROCEDURE
DROP ANY ROLE
DROP ANY RULE
DROP ANY RULE SET
DROP ANY SEQUENCE
DROP ANY SQL PROFILE
DROP ANY SQL TRANSLATION PROFILE
DROP ANY SYNONYM
DROP ANY TABLE
DROP ANY TRIGGER
DROP ANY TYPE

PRIVILEGE

—————————————-

DROP ANY VIEW
DROP LOCKDOWN PROFILE
DROP PROFILE
DROP PUBLIC DATABASE LINK
DROP PUBLIC SYNONYM
DROP ROLLBACK SEGMENT
DROP TABLESPACE
DROP USER
EM EXPRESS CONNECT
ENQUEUE ANY QUEUE
EXECUTE ANY ASSEMBLY

PRIVILEGE

—————————————-

EXECUTE ANY CLASS
EXECUTE ANY EVALUATION CONTEXT
EXECUTE ANY INDEXTYPE
EXECUTE ANY LIBRARY
EXECUTE ANY OPERATOR
EXECUTE ANY PROCEDURE
EXECUTE ANY PROGRAM
EXECUTE ANY RULE
EXECUTE ANY RULE SET
EXECUTE ANY TYPE
EXECUTE ASSEMBLY

PRIVILEGE

—————————————-

EXEMPT DDL REDACTION POLICY
EXEMPT DML REDACTION POLICY
EXPORT FULL DATABASE
FLASHBACK ANY TABLE
FLASHBACK ARCHIVE ADMINISTER
FORCE ANY TRANSACTION
FORCE TRANSACTION
GLOBAL QUERY REWRITE
GRANT ANY OBJECT PRIVILEGE
GRANT ANY PRIVILEGE
GRANT ANY ROLE

PRIVILEGE

—————————————-

IMPORT FULL DATABASE
INSERT ANY CUBE DIMENSION
INSERT ANY MEASURE FOLDER
INSERT ANY TABLE
LOCK ANY TABLE
LOGMINING
MANAGE ANY FILE GROUP
MANAGE ANY QUEUE
MANAGE FILE GROUP
MANAGE SCHEDULER
MANAGE TABLESPACE

PRIVILEGE

—————————————-

MERGE ANY VIEW
ON COMMIT REFRESH
QUERY REWRITE
READ ANY FILE GROUP
READ ANY TABLE
REDEFINE ANY TABLE
RESTRICTED SESSION
RESUMABLE
SELECT ANY CUBE
SELECT ANY CUBE BUILD PROCESS
SELECT ANY CUBE DIMENSION

PRIVILEGE

—————————————-

SELECT ANY MEASURE FOLDER
SELECT ANY MINING MODEL
SELECT ANY SEQUENCE
SELECT ANY TABLE
SELECT ANY TRANSACTION
SET CONTAINER
UNDER ANY TABLE
UNDER ANY TYPE
UNDER ANY VIEW
UNLIMITED TABLESPACE
UPDATE ANY CUBE

 

PRIVILEGE

—————————————-

UPDATE ANY CUBE BUILD PROCESS
UPDATE ANY CUBE DIMENSION
UPDATE ANY TABLE
USE ANY JOB RESOURCE
USE ANY SQL TRANSLATION PROFILE

Granting all privileges to an existing user

To grant all privileges to an existing user, you only got to use the GRANT ALL PRIVILEGES statement. for instance , the subsequent statement grants all privileges to the user alice:

GRANT ALL PRIVILEGES to alice;

So, this brings us to the end of blog. This Tecklearn ‘How to Grant All Privileges to a User in Oracle’ blog helps you with commonly asked questions if you are looking out for a job in Oracle DB. If you wish to learn Oracle DB and build a career in Database domain, then check out our interactive, Oracle 12c DBA Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

https://www.tecklearn.com/course/oracle-12c-dba-course/

Oracle 12c DBA Course Training

About the Course

Oracle Database Administration training will help you master the Oracle DBA. As part of the training, you will master the Oracle Database architecture, the database processes, memory structures, schema objects, security, data recovery and backup through hands-on projects and case studies. Oracle DBA Training helps students gain the skills and knowledge required to install, configure, and administer Oracle databases. Besides, this training also develops the full skill set required to become a successful Oracle Database Administrator.

Why Should you take Oracle DBA Training?

  • The Average salary of an Oracle DBA is $108,837 per annum – GlassDoor.com
  • According to Gartner, Oracle is the market leader in the Relational Database with 48.35% market share globally.
  • IBM, TCS, Tech Mahindra, Oracle, Wipro & other MNCs worldwide use Pl-SQL for their database deployments.

What you will Learn in this Course?

Introduction to Oracle Database

  • What is the Oracle Database
  • Pre-requisites for oracle database
  • Oracle database 12c installation
  • Multi-tenant architecture

Exploring the Oracle 12c Database Architecture

  • Explain the Memory Structures
  • Describe the Process Structures
  • Overview of Storage Structures

Preparing the Database Environment

  • Identify the tools for Administering an Oracle Database
  • Plan an Oracle Database Installation
  • Install the Oracle Software by using Oracle Universal Installer (OUI)
  • Create a Database by using the Database Configuration Assistant (DBCA)

Managing the Oracle Instance

  • Setting database initialization parameters
  • Describe the stages of database startup and shutdown

Database Storage Structures and Schema Objects

  • Managing Database Storage Structures
  • Overview of table space and data files
  • Overview of Primary Components
  • Identify the importance of checkpoints, redo log files, and archived log files
  • Configure ARHIVELOG mode

Database Startup & User Requests and Oracle Network Environment

  • Describe the stages of database startup and shutdown
  • Using alert log and trace files
  • Configure and Manage the Oracle Network
  • pFile and spFile
  • Tablespace Management
  • Storage and Relationship Structure

Oracle Backup & Recovery

  • Redo log File Management
  • Performing Database Recovery
  • Overview of Data Recovery Advisor
  • Use Data Recovery Advisor to Perform recovery (Control file, Redo log file and Data file)

Deep Dive into Oracle Recovery Manager (RMAN)

  • Using the RMAN Recovery Catalog
  • Identify situations that require RMAN recovery catalog
  • Create and configure a recovery catalog
  • Synchronize the recovery catalog
  • Create and Use RMAN stored scripts
  • Back up the recovery catalog
  • Create and use a virtual private catalog

Configuring Backup Specifications

  • Using RMAN to Create Backups
  • Create image file backups
  • Create a whole database backup
  • Enable fast incremental backup
  • Using RMAN to Perform Recovery
  • Perform complete recovery from a critical or noncritical data file loss using RMAN
  • Perform incomplete recovery using RMAN

Data Movement and Oracle Data Pump

  • Moving Data
  • Describe and use methods to use move data (Directory objects, SQL* Loader, External tables)
  • Explain the general architecture of Oracle Data Pump

Data Dictionary & Dynamic Performance Tables

  • Oracle Data Dictionary contains information related to database privileges
  • Data dictionary views
  • Media failure
  • Flashback CDB

Database Tuning

  • What is database tuning
  • How to tune the database
  • Tuning the data dictionary

Got a question for us? Please mention it in the comments section and we will get back to you.

0 responses on "How to Grant All Privileges to a User in Oracle"

Leave a Message

Your email address will not be published. Required fields are marked *