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, 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?
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:
The following privileges 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.

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