Oracle Rights Management

System permissions

System permissions require that the grantor have the ability to perform system-level activities, such as connecting to a database, changing user sessions, establishing tables or establishing users, and so on. You can get full system permissions on the data dictionary view System_privilege_map. Both object permissions and system permissions are granted to the user or role 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 grammar and error message in the exam.

Roles and Role permissions

Role permissions grant a role to a user's permissions. Any permission can be granted to a role. Granting system permissions to a given person must use the with_admin_option clause to grant or revoke role permissions through the SET ROLE statement during the session. However, role permissions cannot depend on the permissions stored in SQL. If a function, program, package, trigger, or method uses an object owned by another plan, it must be granted directly to the owner of the object, because the permissions do not change between sessions.

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,

Permissions and Data dictionary

A data dictionary is where Oracle stores information about the structure of the database, where the data itself resides elsewhere, and the data dictionary consists of tables and views. The easiest thing to do about data dictionaries in exams is to see what kind of permissions have been granted. For example, Dba_tab_priv contains the object permissions that the user grants to another user and whether the information is provided with the WITH Grant Otpion substring at the time of Grant. Note that dba_tab_priv contains more than just the permissions on the table, he also includes the relationships of permissions on functions, packages, queues, and so on. The following table lists the data dictionary views for all permissions and roles:

Table: Data dictionary view of permissions

View	Role
All_col_privs	Represents authorization on a column, user and public are grantees
All_col_privs_made	Represents the authorization on a column, which is the owner and the grantee
All_col_recd	Represents authorization on a column, user and public are grantees
All_tab_privs	Represents the authorization on the object, the user is public or the grantee or the user is a master
All_tab_privs_made	Represents a permission on an object, a user is a master or a grant
All_tab_privs_recd	Represents a permission on an object that the user is public or granted
Dba_col_privs	All authorizations on a database column
Dba_role_privs	Show roles that have been granted to users or other roles
Dba_sys_privs	System permissions granted to a user or role
Dba_tab_privs	All permissions on a database object
Role_role_privs	Show roles that have been granted to users
Role_sys_privs	Show system permissions granted to users by role
Role_tab_privs	Show object permissions granted to users by role
Session_privs	Show all system permissions that users can now take advantage of
User_col_privs	Displays the permissions on the column, which is the owner, the grantor, or the person being granted
User_col_privs_made	Show permissions granted on a column, user is owner or grant
User_col_privs_recd	Displays the permissions granted on the column, the user is the owner or the person being granted
User_role_privs	Show all roles that have been granted to a user
User_sys_privs	Show all system permissions that have been granted to a user
User_tab_privs	Display all object permissions that have been granted to a user
User_tab_privs_made	Displays object permissions that have been granted to other users, which is the owner of the user
User_tab_privs_recd	Show object permissions that have been granted to other users, who are the granted

Role-related views.

I. Overview

The views associated with permissions, roles are probably the following:

Dba_sys_privs: Querying system permissions owned by a user

User_sys_privs: System permissions owned by the current user

Session_privs: All permissions owned by the current user

Role_sys_privs: System permissions owned by a role

Note: To query this view with SYS user login, otherwise return NULL.

Role_role_privs: Role assigned to the current role

Session_roles: The role that the current user is activating

User_role_privs: The role that the current user is granted

There is also a view of access rights for the table:

Table_privileges

All_tab_privs

Role_tab_privs: Permissions on related tables that a role is given

...

Two. Examples

1. Querying permissions owned by the current user

Select * from Session_privs;

2. Query the system permissions that a user has been given.

There can be many ways

Select * from User_sys_privs;

Or: SELECT * from Dba_sys_privs where grantee= ' XXX '

(requires current user to have DBA role)

3. Query the role that the current user is granted:

1. Select * from Session_roles ORDER by ROLE

Description: This query returns all roles that the current user has been granted, including nested authorization roles. For example, a DBA role is granted to a user, and the roles that the DBA role has been granted (for example, Exp_full_database and imp_full_database) are also queried

2. Select * from User_role_privs

4. Querying the system permissions assigned to a role

Select Privilege from Role_sys_privs where role=&role

Input role= ' CONNECT '

Output:

PRIVILEGE

——————–

ALTER SESSION

CREATE CLUSTER

CREATE DATABASE LINK

CREATE SEQUENCE

CREATE SESSION

CREATE synonym

CREATE TABLE

CREATE VIEW

5. Query the role that the current role is granted

Select granted_role from Role_role_privs where role=&role

Input role= ' DBA '

Output:

Granted_role

———————-

Delete_catalog_role

Execute_catalog_role

Exp_full_database

Imp_full_database

Related Posts:

• No Related Posts found!

Oracle Rights Management