One of Oracle database permissions-Concepts

Oracle system permissions and role permissions are limited to the user's permissions, which also protects the access security of the database to some extent. Let's take a look at the following.

Oracle System permissions

Oracle system permissions require the ability of the grantor to perform system-level activities, such as connecting to a database, changing user sessions, setting up tables or establishing users, and so on. You can get full system permissions on the data dictionary view System_privilege_map.

Object permissions

A user can delegate directly to other users on the object.

Note that when a user UserA has a view viewa that queries another user UserB the TableA of a table, such as UserA users need to grant Viewa the query permission UserC, You must also grant USERC users the query permission for the TableA table under the UserB user.

Both object permissions and Oracle system permissions are granted to users or roles through the grant statement. Note that the statement should be with the GRANT OPTION clause when granting object permissions, but the statement is with ADMIN option when you attempt to grant system permissions, so use the statement with the grant The option system will report an error: Only the ADMIN option can be specified. Pay special attention to this syntax and error message.

Roles and Role permissions

Role permissions grant a role to a user's permissions. Any permission can be granted to a role. At the same time, a role can be granted to another role, but cannot be granted to itself, nor can it be cycled.

The role permissions are granted or revoked through the set role statement during the session.

When you use a role in a PL/SQL block, you need to determine whether it is an anonymous block, a definition, or a caller's permission.

1) All roles are invalidated when named PL/SQL blocks (stored procedures, functions, triggers) are executed according to the permissions of the definition. Query Session_roles view does not return results during execution.

2) when the named PL/SQL blocks (stored procedures, functions, triggers) are executed in accordance with the caller's permissions, and when an anonymous PL/SQL block is executed, the role can be used for permission checking, or the corresponding role (the SET Role statement) can be set in the block using dynamic SQL.

Granting and revoking permissions

To grant permissions to a user or role using the GRANT statement, the syntax for the GRANT statement is as follows:

GRANT ROLE (or system privilege) to User (Role,public) with ADMIN option (optional)

Object permissions are granted with Grant OPTION

Data dictionary tables that involve permissions are mainly:

1, System_privilege_map

A list of all system permissions in the system.

2, Dba_roles

List of all roles in the system.

3. Database-level Permissions List

Dba_sys_privs

Which users or roles have what system permissions.

Dba_role_privs

Which users or roles have which roles.

Dba_tab_privs

Which users or roles have specific permissions on which objects, including tables, views, procedures, functions, packages, types, and so on.

Dba_col_privs

Which users or roles have what permissions for which columns of the table, these permissions mainly include update, REFERENCES.

4. User-level Permissions list

User_sys_privs

The system permissions that the current user has.

User_role_privs

The role permissions that the current user has.

User_tab_privs

As the owner of the object, the grantor of the object's permissions, the grantor of the object's permissions, the relevant object permissions that the current user is involved in.

User_tab_privs_made

As the owner of the object, the current user is involved in related object permissions.

User_tab_privs_recd

As the object permission, the current user is involved in related object permissions.

User_col_privs

As the owner of the Column object, the grant of the Column object permission, the grant of the Column object permission, the related Column object permissions that the current user is involved in.

User_col_privs_made

As the owner of the Column object, the current user is involved in the related Column object permissions.

User_col_privs_recd

As the delegate of the Column object permission, the current user is involved in the related Column object permissions.

5. All-level permissions list

All_tab_privs

1) As the owner of an object, the grantor of the object's permission, the granted object permission, the relevant object permissions that the current user is involved in.

2) object permissions owned by the current user's role (including the public role).

All_tab_privs_made

As the owner of the object, the grantor of the object's permissions, the relevant object permissions that the current user is involved in.

All_tab_privs_recd

1) As the object permission, the current user is involved in the relevant object permissions.

2) object permissions owned by the current user's role (including the public role).

All_col_privs

1) As the owner of the Column object, the grant of the Column object permission, the grant of the Column object permission, the related Column object permissions that the current user is involved in.

2) The Column object permissions that are owned by the current user's role (including the public role).

All_col_privs_made

As the owner of the Column object, the delegate of the Column object permission, the related Column object permissions that the current user is involved in.

All_col_privs_recd

1) as the delegate of the Column object permission, the relevant Column object permissions are related to the current user.

2) The Column object permissions that are owned by the current user's role (including the public role).

6. Role-level permissions list

Role_role_privs

Which roles are granted to the current user under which roles.

Role_sys_privs

Which roles are granted which system permissions under the current user.

Role_tab_privs

Under the current user, which roles are granted which object permissions.

7. Session-Level Permissions list

Session_privs

The system permissions that the current user has.

Session_roles

The roles owned by the current user.

One of Oracle database permissions-Concepts