User authorization and cancel permissions in Oracle database

User authorization and cancel permissions in Oracle

User authorization (authorization) in Oracle databases is divided into two categories: system privileges (System Privileges) and object privileges.

Here is an introduction to these two types of permissions and how to use them:

System Privileges

System permissions allow users to perform specific system-level operations, such as creating tables, views, or starting transactions. Examples of system permissions include:

CREATE SESSION: Allow users to log in to the database.
CREATE TABLE: Allows users to create tables.
ALTER SYSTEM: Allows users to change system-level settings.
DBA: A special role with almost all system permissions.

Grant system permissions：

GRANT &lt;Permissions&gt; TO &lt;username&gt;;

For example, granting usersCREATE SESSIONandUNLIMITED TABLESPACEPermissions:

GRANT CREATE SESSION, UNLIMITED TABLESPACE TO username;

Object Privileges

Object permissions are permissions for specific database objects (such as tables, views, procedures, etc.). Examples of object permissions include:

SELECT: Allows users to retrieve data from one or more tables.
INSERT: Allows users to add data to one or more tables.
UPDATE: Allows the user to modify data in one or more tables.
DELETE: Allows users to delete data from one or more tables.

Grant object permissions：

GRANT &lt;Permissions&gt; ON &lt;Object&gt; TO &lt;username&gt;;

For example, grant a user tablemy_tableofSELECTandINSERTPermissions:

GRANT SELECT, INSERT ON my_table TO username;

Role Privileges

A role is a collection of permissions that can be granted to users to simplify permission management.

Create a role：

CREATE ROLE role_name;

Grant permissions to roles：

GRANT &lt;Permissions&gt; TO role_name;

Grant roles to users：

GRANT role_name TO username;

View permissions

View the permissions that the user has:

SELECT * FROM USER_SYS_PRIVS; -- View system permissions
SELECT * FROM USER_TAB_PRIVS;  -- View object permissions

Revoke permissions

Revoke the permissions granted previously:

REVOKE &lt;Permissions&gt; FROM &lt;username&gt;;

Things to note

The granting and revocation of permissions shall be performed by a database administrator with sufficient permissions.
When granting permissions, the principle of minimum permissions should be considered, that is, only the permissions necessary to complete their work should be granted to users.
Permission management is an important part of database security and should be handled with caution.

Summarize

The above is personal experience. I hope you can give you a reference and I hope you can support me more.