Types of System Privileges and Object Create Privilege

For just about every database object that you can create, you will find that there is an associated CREATE privilege. If the object is a schema object^[1], the CREATE privilege will allow a user to create, alter, and delete such objects in their own schema. For such objects you will also usually have

1. CREATE ANY,
2. ALTER ANY, and
3. DROP ANY

privileges. These allow privileged users, such as a DBA, to manage objects which they do not themselves own. For non-schema objects, in other words for objects not owned by a specific user, you usually just have separate CREATE, ALTER, and DROP privileges.
Oracle defines a very large number of system privileges that a DBA can use to fine-tune his or her control over what users are allowed to do. Once you know the functions of the various privileges, you will need to weigh the needs of the organization and the employee when deciding which privileges to grant.

What privileges should you give your users?
Answer: The generally accepted answer to that question is: "as few as possible," while still enabling them to do their work.
To end-users, who only connect when running an application, I generally give the

CREATE SESSION

privilege, whatever object privileges and roles are required by the application, and nothing more. That allows them to log in and use their application, and that's all you want them to be able to do. Developers often require the ability to create objects. I usually end up granting them the following privileges, which are still available in Oracle 19c. They are common system privileges that have been part of Oracle's database management system across various versions, including Oracle 19c:

1. CREATE SESSION: Allows a user to connect to the database.
2. CREATE TABLE: Grants the ability to create tables in the user's schema.
3. CREATE DATABASE LINK: Allows the creation of a database link, which is a schema object that enables users to access objects in a remote database.
4. CREATE SEQUENCE: Grants the privilege to create sequences, which are used to generate unique numeric values.
5. CREATE PROCEDURE: Allows the user to create stored procedures and functions in their own schema.
6. CREATE TRIGGER: Enables the user to create triggers, which are procedures that are automatically executed in response to certain events on a table or view.
7. CREATE VIEW: Grants the ability to create views, which are virtual tables based on the result of a SQL query.
8. CREATE SYNONYM: Allows the creation of synonyms, which are alternative names for objects such as tables, views, sequences, and other database objects.
9. ALTER SESSION: Grants the ability to modify session-level settings such as enabling/disabling specific features, adjusting parameters, and changing language settings for the session.

All these privileges are essential for typical database management and development tasks, and they remain part of Oracle 19c's privilege management system.
These privileges allow developers to connect, and to create objects such as tables, views, and so forth. Developers often need these capabilities in order to test code or to experiment. Note however, that this applies only to those databases used for development. It is very rare for me to grant the ANY privileges, or the specific system-wide object privileges such as CREATE TABLESPACE.
These represent tasks that are best left to the DBAs.

The following table defines and describes the use of these privileges:

Type of privilege	Function of privilege
Schema Object Management	Includes privileges like CREATE TABLE, that allow users to create, alter, and drop objects in their own schema
The ANY Privileges	Includes privileges like CREATE ANY TABLE and ALTER ANY TABLE that allow users to create and manage objects owned by other users
System Object Management	Includes privileges like CREATE USER and ALTER USER that allow you to manage objects that aren't attached to any particular schema
Database Administration	Includes such privileges as ALTER DATABASE and ALTER SYSTEM that allow you to control various aspects of database operation
Miscellaneous	Includes anything that doesn't fall neatly into any of the other categories

The following diagram outlines the privileges in each category.

Oracle system privileges into distinct categories, each serving different aspects of database management.

• Object Management: This category includes privileges related to creating and managing standard database objects like tables, views, sequences, procedures, synonyms, and triggers within a user's own schema.
• System Object Management: These privileges focus on operations related to more global or public objects like public synonyms and database links, and tasks such as managing tablespaces and rollback segments.
• ANY: Privileges in this section allow the user to perform actions on any object of that type across the database, not just those owned by the user. This includes creating, altering, and dropping any table or index.
• Database Administration: This section includes high-level operational privileges that affect the database as a whole.
• Miscellaneous: This includes specific privileges like 'UNLIMITED TABLESPACE,' allowing a user to use an unlimited amount of any tablespace.

Each category is meant to group system privileges based on their functional scope and impact level within the database system, offering a clear and organized way to understand and manage access and capabilities within Oracle databases.
The listed system privileges are still available in Oracle 19c. Below is a breakdown of these privileges and their relevance in Oracle 19c:

1. Object Management
   • CREATE TABLE: allows creating tables in the user's schema.
   • CREATE DATABASE LINK: allows creating private database links.
   • CREATE VIEW: allows creating views in the user's schema.
   • CREATE SEQUENCE: allows creating sequences in the user's schema.
   • CREATE PROCEDURE: allows creating stored procedures and functions in the user's schema.
   • CREATE SYNONYM: allows creating synonyms for schema objects.
   • CREATE TRIGGER: allows creating triggers in the user's schema.
2. System Object Management
   • CREATE PUBLIC SYNONYM: allows creating public synonyms for objects.
   • CREATE PUBLIC DATABASE LINK: allows creating public database links.
   • DROP PUBLIC DATABASE LINK: allows dropping public database links.
   • DROP PUBLIC SYNONYM: allows dropping public synonyms.
   • CREATE TABLESPACE: allows creating tablespaces.
   • ALTER TABLESPACE: allows altering tablespaces.
   • DROP TABLESPACE: allows dropping tablespaces.
   • CREATE ROLLBACK SEGMENT: though rollback segments are mostly managed automatically in Automatic Undo Management (AUM), this privilege is still available for legacy systems using Manual Undo Management.
   • DROP ROLLBACK SEGMENT: similar to the above, relevant for Manual Undo Management.
   • ALTER ROLLBACK SEGMENT: still available for systems using Manual Undo Management.
3. ANY Privileges
   • CREATE ANY TABLE: allows creating tables in any schema.
   • ALTER ANY TABLE: allows altering tables in any schema.
   • DROP ANY TABLE: allows dropping tables in any schema.
   • CREATE ANY INDEX: allows creating indexes in any schema.
   • ALTER ANY INDEX: allows altering indexes in any schema.
   • DROP ANY INDEX: allows dropping indexes in any schema.
4. Database Administration
   • ALTER DATABASE: allows altering the database (e.g., renaming database files, changing database settings).
   • ALTER SYSTEM: allows altering system-level parameters dynamically.
5. Miscellaneous
   • UNLIMITED TABLESPACE: grants the user the ability to use an unlimited amount of space in any tablespace (quota-related privilege).

Additional Notes:

1. Privileges have not changed in terms of their names or purposes in Oracle 19c, but there are enhancements to security and privilege management.
2. Oracle strongly recommends using roles to group privileges and assigning roles to users to reduce direct privilege grants.
3. If using Oracle Database Vault or Oracle Data Guard, some of these privileges might be restricted unless explicitly granted under certain conditions.

The object management privileges, the ones that you grant to users so that they can create their own objects, convey broader rights than do the corresponding ANY privileges. Grant a user CREATE TABLE, for example, and he or she will be able to create, modify, and drop tables into his or her own schema. On the other hand, the CREATE ANY TABLE privilege only allows the user to create a table, but that table may be created in any user's schema. To modify or delete another user's table, the ALTER ANY TABLE or DROP ANY TABLE privileges must be granted.

Before moving on to the next lesson, click the Quiz link below to test your understanding of system privileges.
System Privileges - Quiz

[1] schema object:In the context of an Oracle DBA, a schema object is any defined object within a schema that's used to store or reference data. These objects can be tables, indexes, views, sequences, stored procedures, or any other object that a user can create within their schema. Schema objects are the building blocks of an Oracle database, and DBAs are responsible for managing and maintaining them to ensure data integrity and optimal database performance.