Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to understand default role in oracle". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to understand default role in oracle.

ORACLE can authenticate with a password when creating a role. When a role is granted to a user, if the DEFAULT is granted, then the user automatically has the role when logging in, and there is no need to authenticate the password for that role. If it is not DEFAULT, then SET ROLE ROLE_NAME IDENTIFIED ROLE_PASSWORD can be used for password authentication before the user can have the corresponding role.

There is a default role in the oracle authority system, which is difficult to understand. Here is an example to illustrate the effect.

We can assign some roles to a user, such as role R1, default role, and others can be set to default role, so that when the user logs in, he or she automatically has the permissions contained in default role, and the permissions of other roles are obtained through the set role role.

Let's give an example:

(1) the sys user logs in as sysdba and creates 4 roles:

Create role r1

Create role r2 identified by r2

Create role r3 identified by r3

Create role r4 identified by r4

(2) the permissions assigned to these four roles by sys users:

Grant create session to r1

Grant select on hr.test to R2; (here hr.test is a new table I created with two columns ID and name)

Grant update (name) on hr.test to R3

Grant insert on hr.test to r3

Grant delete on hr.test to r4

(3) sys user creates a user U3

Create user u3 identified by u3

(4) assign the role r1menr2re3rer4 to user U3

Grant r1,r2,r3,r4 to u3

Before modifying the default role of user U3, there is no problem with default role with U3 user login, query, addition, deletion and modification of hr.test.

(5) now the sys user modifies the default role of user U3 to only use R1 as the default role of U3:

Alter user U3 default role R1;-- the original setting will be overridden at this time. The default role of U3 is R1 and only has login permissions.

(6) user U3 log off, and then log on, found after entering

Queries, additions, deletions and changes to hr.test cannot be carried out.

(7) users turn on role permissions by themselves

Set role r2 identified by r2

At this point, select * from hr.test is executed and no problem is found. I can't add, delete or change it.

Open the corresponding role:

Set role r3 identified by r3

There is no problem with modifying and inserting records at this time, but select * from hr.test does find that it is not working. It is proved that the role that the user belongs to at this time is only the default role R1 and the role R3 that has just been opened, and R2 is overwritten by set role R3 identified by R3;.

What should I do if I have permission for r _ 2 ~ r _ 3 ~ 4 at the same time?

Set role r2 identified by r2,r3 identified by r3,r4 identified by r4 . At this time, you can query hr.test at the same time, add, delete and change it.

However, the effect of set role is temporary, but the current session is valid, and other session is invalid. When you log in after the end of the current session, you only have the permission of default role.

At this point, I believe you have a deeper understanding of "how to understand default role in oracle". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.