Oracle permissions and Roles

Allow users to access objects belonging to other users or executeProgramThe Oracle system provides three permissions:

Object-level

System system level

Role role-level

These permissions can be granted to users and special users as public or roles. If you grant a permission to a special user "public" (the user public is predefined by Oracle, each user has the permissions of this user), which means that the permission is granted to all users of the database. For management permissions, a role is a tool that can be granted to one role, and a role can also be granted to another role or user. Users can inherit permissions through roles. Besides management permissions, role services have no other purpose. Permissions can be granted or revoked in the same way.

Create and use roles

As mentioned above, a role is designed to make permission management easier. Create a role using the create role statement. Its syntax is as follows:

Create role role_name identified by password
Create role role_name identified externally
Create role role_name identified globally
By default, the created role does not have a password or other identifiers. If you use the identified by clause to create a role, the role does not automatically respond and must be activated with set role.

Set role role_name identified by password

Externally and globally roles are verified by the operating system and Oracle service server. Users usually need the permission to modify the data in the form used in the application, but only when the application is running and not using the ad hoc tool, this type of context-sensitive security can be achieved through the role of password. When a user links a database within an application,CodeThe set role command is executed and passes security verification. Therefore, you do not need to know the role password or enter the set role command.

Object permission

Object permission is the right to execute special actions on objects such as tables, views, sequences, processes, functions, and packages. There are nine different types of permissions that can be granted to users or roles. See the following table:

Permission	Alter	Delete	Execute	Index	Insert	Read	Reference	Select	Update
Directory	No	No	No	No	No	Yes	No	No	No
Function	No	No	Yes	No	No	No	No	No	No
Procedure	No	No	Yes	No	No	No	No	No	No
Package	No	No	Yes	No	No	No	No	No	No
DB object	No	No	Yes	No	No	No	No	No	No
Libary	No	No	Yes	No	No	No	No	No	No
Operation	No	No	Yes	No	No	No	No	No	No
Sequence	Yes	No	No	No	No	No	No	No	No
Table	Yes	Yes	No	Yes	Yes	No	Yes	Yes	Yes
Type	No	No	Yes	No	No	No	No	No	No
View	No	Yes	No	No	Yes	No	No	Yes	Yes

An object has more than one permission. special permissions can be granted or revoked. For example, table's all permissions include:

Select, insert, update, and delete, as well as index, alter, and reference.

The alter permission is used as an example to describe how to view the table.

Alter permission

The alter table and lock table operations are allowed. The alter table operation can be performed as follows:

. Change table name

. Add or delete Columns

. Change the data type or size of a column.

. Convert a table into a partitioned table

The alter permission on sequence allows you to execute the alter sequence statement and assign the minimum value, increment, and buffer size to the sequence again.

System Permissions

System permissions must be granted to system-level activities, such as database connection, user session modification, table creation, and user creation. You can obtain complete system permissions on system_privilege_map. Both the object permission and system permission are granted to the user or role through the grant statement. Note that the statement should be the with grant option clause when granting object permission, but the statement is with admin option when granting system permission, so when you try to grant system permission, when you use the with grant option statement, the system reports an error: Only admin option can be specified. Pay special attention to this syntax and error message during the exam.

Roles and permissions

Role permissions are used to grant permissions of a user to a role. Any permission can be granted to a role. You must use the with_admin_option clause to grant the system permission to the grantee. You can use the set role statement to grant or revoke the role permission during a session. However, role permissions cannot rely on permissions stored in SQL. If a function, program, package, trigger, or method uses an object owned by another plan, you must directly authorize the owner of the object because the permission will not change between sessions.

Grant and revoke permissions

Grant permissions to a user or role to use the grant statement. The syntax of the grant statement is as follows:

Grant role (or system privilege) to user (role, public) with admin option (optional)

Object permission is granted with grant option,

Permission and Data Dictionary

A data dictionary is a place where Oracle stores information about the database structure. data is stored elsewhere. A data dictionary consists of tables and views. The easiest way to take a data dictionary test during the test is to view the permissions that have been granted. For example, dba_tab_priv contains the object permission granted to another user by the user and whether the permission is granted with the with Grant otpion substring. Note that dba_tab_priv not only includes the relationship between table permissions, but also permissions on functions, packages, queues, and so on. The following table lists the data dictionary views of all permissions and roles:

Table: Permission data dictionary View

View	Function
All_col_privs	Indicates column authorization. The user and public are granted to the column.
All_col_privs_made	Indicates column authorization. The user is the owner and the authorized user.
All_col_recd	Indicates column authorization. The user and public are granted to the column.
All_tab_privs	Indicates the authorization on the object. The user is public or authorized or the user is the owner.
All_tab_privs_made	Indicates the permission on the object. The user is the owner or an authorized user.
All_tab_privs_recd	Indicates the permission on the object. The user is public or authorized.
Dba_col_privs	All permissions on Database Columns
Dba_role_privs	Show roles that have been granted to users or other roles
Dba_sys_privs	System permissions granted to users or roles
Dba_tab_privs	All permissions on database objects
Role_role_privs	Show roles granted to users
Role_sys_privs	Displays the system permissions granted to users through roles
Role_tab_privs	Displays the object permissions granted to users through roles
Session_privs	Displays all current system permissions that a user can use
User_col_privs	Displays the permissions on the column. The user is the owner, the grantee, or the grantee.
User_col_privs_made	Displays the permissions granted to the column. The user is the owner or the principal.
User_col_privs_recd	Displays the permissions granted to the column. The user is the owner or authorized.
User_role_privs	Show all roles granted to users
User_sys_privs	Displays all system permissions granted to users
User_tab_privs	Show all object permissions granted to the user
User_tab_privs_made	Displays the object permissions granted to other users. The user is the owner.
User_tab_privs_recd	Displays the object permissions granted to other users. The user is an authorized user.

 

--------------------------------------------------------------------------

 

Role-related views.

I. Overview

The views related to permissions and roles include the following:

Dba_sys_privs: query the system permissions of a user

User_sys_privs: system permissions of the current user

Session_privs: All permissions of the current user

Role_sys_privs: system permissions of a role

Note: You need to log on to the view as a sys user to query this view. Otherwise, null is returned.

Role_role_privs: role assigned to the current role

Session_roles: Role activated by the current user

User_role_privs: role assigned to the current user

In addition, there is a view of Table Access Permissions:

Table_privileges

All_tab_privs

Role_tab_privs: permissions granted to the related tables of a role

...

Ii. Examples

1. query the permissions of the current user

Select * From session_privs;

2. query the system permissions granted to a user.

You can use multiple methods.

Select * From user_sys_privs;

Or: Select * From dba_sys_privs where grantee = 'xxx'

(The current user must have the DBA role)

3. query the roles assigned to the current user:

1. Select * From session_roles order by role

Note: This query returns all roles granted to the current user, including

Role. For example, if a DBA role is granted to a user, the DBA role has been granted to the role (for example

Exp_full_database and imp_full_database will also be queried.

2. Select * From user_role_privs

4. query the system permissions granted to a role

Select privilege from role_sys_privs where role = & role

Enter role = 'connect'

output:
privilege
------------------
alter session
Create cluster
create database link
Create sequence
Create session
Create synonym
Create Table
Create view

 

5. query the roles assigned to the current role

Select granted_role from role_role_privs where role = & role

Enter role = 'dba'

Output:

Granted_role
----------------------
Delete_catalog_role
Execute_catalog_role
Exp_full_database
Imp_full_database
Plustrace
Select_catalog_role

Note: The Role plustrace is used to execute SQL auto trace. This role can be generated by executing $ ORACLE_HOME/sqlplus/admin/plustrce. SQL.