Home  >  Blog  >   Oracle DBA

Managing User Privileges and Roles - Oracle DBA

Rating: 4
  
 
6026

 

Privileges:
A privilege is a permission to execute an action or to access another user’s objects.
The following are a few examples of privileges:

  • The right to access the database
  • The right to select data from another user’s tables
  • The right to execute another user’s stored procedures
  • The right to create new users

Types of PRIVILEGES:

  • System privileges
  • Object privileges.

System privileges enable the user to perform an action on a type of object, where as object privileges give the user permission to perform the action on a specific object.

System privileges:

There are over 100 distinct system privileges. Each system privilege allows a user to perform a particular database operation or class of database operations.

System privileges can be very powerful, and should be granted only when necessary to roles and trusted users of the database.

MindMajix Youtube Channel

Want To Get DBA Training From Experts? Enroll Now For Free Demo On Oracle DBA Training.

System privilege table:

System privilegeOperations permitted

Analyze

 

Analyze any

Audit any schema object in the database.

Audit system

Enable and disable statement and privilege audit options.

Cluster

 

Create cluster

Create a cluster in own schema.

Create any cluster

Create a cluster in any schema. Behaves similarly to create any table.

Alter any cluster

Alter any cluster in the database.

Drop any cluster

Drop any cluster in the database.

Database

 

Alter database

Alter the database; add files to the operating system via oracle, regardless of operating system privileges.

Database link

 

Create database link

Create private database links in own schema.

Index

 

Create any index

Create an index in any schema on any table.

Alter any index

Alter any index in the database.

Drop any index

Drop any index in the database.

Library

 

Create library

Create callout libraries in own schema.

Create any library

Create callout libraries in any schema

Drop library

Drop callout libraries in own schema.

Drop any library

Drop callout libraries in any schema. 

Privilege

 

Grant any privilege

Grant any system privilege (not object privileges).

Procedure

 

Create procedure

Create stored procedures, functions, and packages in own schema.
Create any procedureCreate stored procedures, functions, and packages in any schema. (requires that user also have alter any table, backup any table, drop any table, select any table, insert any table, update any table, delete any table, or grant any table privilege.)

Alter any procedure

Compile any stored procedure, function, or package in any schema.

Drop any procedure

Drop any stored procedure, function, or package in any schema.

Execute any procedure

Execute any procedure or function (stand-alone or packaged), or reference any public package variable in any schema.

Profile

 

Create profile

Create profiles.

Alter profile

Alter any profile in the database.

Drop profile

Drop any profile in the database.

Alter resourcecost

Set costs for resources used in all user sessions

Public database link

 

Create public database link

Create public database links.

Drop public database link

Drop public database links.

Public synonym

 

Create public synonym

Create public synonyms.

Drop public synonym

Drop public synonyms.

Role

 

Create role

Create roles.

Alter any role

Alter any role in the database.

Drop any role

Drop any role in the database.

Grant any role

Grant any role in the database.

Rollback segment

 

Create rollback segment

Create rollback segments.

Alter rollback segment

Alter rollback segments.

Drop rollback segment

Drop rollback segments.

Session

 

Create session

Connect to the database.

Alter session

Issue alter session statements.

Restricted session

Connect when the database has been started using startup restrict. (the osoper and osdba roles contain this privilege.)

Sequence

 

Create sequence

Create a sequence in own schema.

Create any sequence

Create any sequence in any schema.

Alter any sequence

Alter any sequence in any schema.

Drop any sequence

Drop any sequence in any schema.

Select anysequence

Reference any sequence in any schema

Snapshot

 

Create snapshot

Create snapshots in own schema. (user must also have the create table privilege.)

Create snapshot

Create snapshots in any schema. (user must also have the create any table privilege.)

Alter snapshot

Alter any snapshot in any schema.

Drop any snapshot

Drop any snapshot in any schema.

Synonym

 

Create synonym

Create a synonym in own schema.

Create synonym

Create any synonym in any schema.

Drop any synonym

Drop any synonym in any schema.

System

 

Alter system

Issue alter system statements.

Table

 
Create tableCreate tables in own schema. Also allows grantee to create indexes (including those for integrity constraints) on table in own schema. (the grantee must have a quota for the tablespace or the unlimited tablespace privilege.)
Create any tableCreate tables in any schema. (if grantee has create any table privilege and creates a table in another user’s schema, the owner must have space quota on that tablespace. The table owner need not have the create [any] table privilege.)

Alter any table

Alter any table in any schema and compile any view in any schema.

Backup any table

Perform an incremental export using the export utility of tables in any schema.

Drop any table

Drop or truncate any table in any schema.

Lock any table

Lock any table or view in any schema.

Comment any table

Comment on any table, view, or column in schema.

Select any table

Query any table, view, or snapshot in any schema.

Insert any table

Insert rows into any table or view in any schema.

Update any table

Update rows in any table or view in any schema.

Delete any table

Delete rows from any table or view in any schema.

Tablespace

 

Create tablespace

Create tablespaces; add files to the operating system via oracle, regardless of the user’s operating system privileges.

Alter tablespace

Alter tablespaces; add files to the operating system via oracle, regardless of the user’s operating system privileges.

Manage tablespace

Take any tablespace offline, bring any tablespace online, and begin and end backups of any tablespace

Drop tablespace

Drop tablespaces
Unlimited tablespaceUse an unlimited amount of any tablespace. This privilege overrides any specific quotas assigned. If revoked, the grantee’s schema objects remain but further tablespace allocation is denied unless allowed by specific tablespace quotas. This system privilege can be granted only to users and not to roles. In general, specific tablespace quotas are assigned instead of granting this system privilege.

Transaction

 

Force transaction

Force the commit or rollback of own in-doubt distributed transaction in the local database.

Force any transaction

Force the commit or rollback of any in-doubt distributed transaction in the local database.

Trigger

 

Create trigger

Create a trigger in own schema.

Create any trigger

Create any trigger in any schema associated with any table in any schema.

Alter any trigger

Enable, disable, or compile any trigger in any schema

Drop any trigger

Drop any trigger in any schema.

User

 
Create any userCreate users; assign quotas on any tablespace, set default and temporary tablespaces, and assign a profile as part of a create user statement.

Become any user

Become another user. (required by any user performing a full database import.)
Alter userAlter other users: change any user’s password or authentication method, assign tablespace quotas, set default and temporary tablespaces, assign profiles and default roles, in an alter user statement. (not required to alter own password.)

Drop user

Drop another user.

View

 

Create view

Create a view in own schema.
Create any viewCreate a view in any schema. To create a view in another user’s schema, you must have create any view privileges, and the owner must have the required privileges on the objects referenced in the view.

Drop any view

Drop any view in any schema.

Checkout Oracle DBA Interview Questions

Accessing objects in the sys schema:

Users with explicit object privileges or those who connect with administrative privileges (sysdba) can access objects in the sys schema. Another means of allowing access to objects in the sys schema is by granting users any of the following roles:

  • Select_catalog_role

This role can be granted to users to allow select privileges on data dictionary views.

  • Execute_catalog_role

                         This role can be granted to users to allow execute privileges for packages and procedures in the data dictionary.

  • Delete_catalog_role

                          This role can be granted to users to allow them to delete records from the system audit table (aud$).

Additionally, the following system privilege can be granted to users who require access to tables created in the sys schema:

  • Select any dictionary

This system privilege allows query access to any object in the sys schema, including tables created in that schema. It must be granted individually to each user requiring the privilege. It is not included in grant all privileges, but it can be granted through a role.

$ Accessing frequently used dictionay objects:

Users with explicit object privileges and the sysdba can access dictionary objects. If, however, you need access to dictionary objects and do not have explicit object privileges, you can be granted the following roles:

  • Select_catalog_role

                             Enables users to select all exported catalog views and tables granted to this role. Grant this role to users who must access all exported views and tables in the data dictionary.

  • Execute_catalog_role

                                  Provides execute privilege on exported packages in the dictionary.

  • Delete_catalog_role

                                  Enables users to delete records from the aud$ table.

These roles enable database administrators to access certain objects in the dictionary while maintaining dictionary security.

Object privileges:

Each type of object has different privileges associated with it.

Object privilegeSql statements permitted
AlterAlter object (table or sequence)
DeleteDelete from object (table or view)
ExecuteExecute object (procedure or function).references to public package variables
IndexCreate index on object (tables only)
InsertInsert into object (table or view)
ReferencesCreate or alter table statement defining a foreign key integrity constraint on object (tables only)
SelectSelect…from object (table, view, or snapshot). Sql statements using a sequence
UpdateUpdate object (table or view)

 

Explore Oracle DBA Sample Resumes! Download & Edit, Get Noticed by Top Employers!Download Now!

Object privilege shortcut:

The all and all privileges shortcuts grant or revoke all available object privileges for a object. This shortcut is not a privilege, rather, it is a way of granting or revoking all object privileges with one word in grant and revoke statements. Note that if all object privileges are granted using the all shortcut, individual privileges can still be revoked.

Likewise, all individually granted privileges can be revoked using the all shortcut. However, if you revoke all, and revoking causes integrity constraints to be deleted (because they depend on a references privilege that you are revoking), you must include the cascade constraints option in the revoke statement.

Join our newsletter
inbox

Stay updated with our newsletter, packed with Tutorials, Interview Questions, How-to's, Tips & Tricks, Latest Trends & Updates, and more ➤ Straight to your inbox!

Course Schedule
NameDates
Oracle DBA TrainingMar 30 to Apr 14View Details
Oracle DBA TrainingApr 02 to Apr 17View Details
Oracle DBA TrainingApr 06 to Apr 21View Details
Oracle DBA TrainingApr 09 to Apr 24View Details
Last updated: 04 Apr 2023
About Author

 

Technical Content Writer

read more
Recommended Courses

1 / 15