Privilege and role concepts in Oracle databases

(1) privileges:Privilege is the power to execute a special type of SQL statement or to access another user's object. There are two types of privileges: SYSTEM privileges and object privileges.

System privilege: it is the right to execute a special action or to execute a special action on the object type. Oracle has over 60 different system privileges, each of which allows users to perform a special database operation or a class of database operations.

System privileges can be authorized to users or roles. Generally, SYSTEM privileges are provided to all management personnel and application developers. End users do not need these functions. system privileges authorized to a user and granted to other users or roles. otherwise, you can revoke SYSTEM privileges from authorized users or roles.

Object privileges: the right to execute special actions on a specified table, view, sequence, process, function, or package. Different types of objects have different types of object privileges. Some schema objects, such as clustering, indexing, triggers, and database chains, do not have related object privileges, which are controlled by system privileges.

For objects in a mode that contains a user name, the user automatically has all object privileges for these objects, that is, the mode owner has all object privileges for objects in the mode. The owner of these objects can grant any object privileges on these objects to other users. If the authorized user includes the grant option authorization, the authorized user can grant the permissions to other users.

(2) role:Authorized to users and roles. Assumel uses roles to manage privileges more easily. It has the following advantages:

◆ Reduce privileged management. Do not explicitly grant the same privileged group to several users. You only need to grant this privileged group to the role and then authorize the role to each user.

◆ Dynamic Privilege Management: if a group of privileges needs to be changed, you only need to modify the role's privileges. The security domains of all users authorized to this role will automatically reflect the modifications made to the role.

◆ Select the availability of privileges. The role authorized to the user can selectively enable (available) or make (unavailable) the user ).

◆ Application Readability: when a user executes an application with a user name, the database application can query the dictionary and automatically selects role enabling or not.

◆ Dedicated application security: The role can be protected by a password, and the application can provide the correct password permission to use the role to achieve dedicated application security. The role cannot be enabled because the user does not know the password.

Generally, role creation serves two purposes: database application management privileges and user group management privileges. The corresponding roles are called Application roles and user roles.

The application role is all the privileges required to run a database application. An application role can be assigned to another role or a specified user. An application can have several different roles. Each role with different privileged groups can access different data when using an application.

A user role is created for a group of database users who require public privileges. User Privilege Management is controlled by the application role or privilege authorized to the user role, and then the user role is authorized to the corresponding user.

The database role provides the following functions:

◆ A role can be granted system or object privileges.

◆ A role can be authorized to other roles, but cannot be cyclically authorized.

◆ Any role can be authorized to any database user.

◆ Each role authorized to a user can be enabled or disabled. A user's security domain only contains the privileges of all roles currently enabled to this user.

◆ An indirectly authorized role (a role authorized to another role) can explicitly enable or disable a user.

In a database, each role name must be unique. The role name is different from the user name. The role is not included in any mode. Therefore, deleting a user who creates a role does not affect the role.

Oracle predefines the following roles to provide compatibility with previous versions: connent, resource, DBA, EXP-FULL-DATABASE, and imp-full-database.