How Oracle Changes user names

http://blog.csdn.net/weiwenhp/article/details/8094575

Directory (?) [-]

1. Use of user name instead
2. How to change the user name
3. The work to be done
4. I want to do something bad. Clear password

Many people think that Oracle can easily change the password, but cannot change the user name. In fact, the name can be changed. It's just a little tricky to change.

Use of user name instead

The use of renaming is very useful in such a situation. If a company gives each employee an account, the username is your English name. When an employee Arwen to leave, It is necessary to delete the user Arwen. But we know that users in Oracle are fully bound to the objects created under this user, unlike SQL Objects such as tables in the server can be completely separated from the user. So you delete the user Arwen the data below is all gone. If we need that data, the easiest thing to think about is to get all the data out, And then import it to a user. But it's more troublesome. So it would be better if we could change the name.

How to change the user name

Then use the example above. If the new employee comes again, weiwenhp. He is in the Arwen class. So he needs the user Arwen the following data. The user Arwen is changed to user weiwenhp. When we landed the database, The database system will determine our user name and password is correct, then we naturally think that this information must be stored in the database where, there must be a table to save the user information. Yes, so the user information is saved in the table user$.

SELECT * FROM user$

With this SQL check you will see all usernames and passwords, and some other information. However, the user name is encrypted. So even the administrator can not see any user's password, can only change the user's password.

That's for sure, username and password here, I'm just going to change my name here. In fact, renaming is really doing that.

Select user#, name from user$ where name = ' ARWEN '; --First look at the Arwen information, where user# is a serial number, the equivalent of a social security number, if this is 250

Update user$ Set name = ' WEIWENHP ' where user# = 250; -So the user name is changed. Of course, the UPDATE statement will remember to commit the next AH.

The work to be done

When the above to change the name after you are happy to go to the landing to try, look at the change of the user name after the real can log on. The results of a login can not be found. And the previous account Arwen. You're dumbfounded.

I know that if you change the password, it will be effective immediately to use AH, changed the name of how can not be.

We know that the important thing in the database is to ensure data consistency, where the data changes, The relevant places have to be changed. It must be because the user information that was read at the time of landing has not been updated. There is a case of data inconsistency. You might think that sometimes we change some parameter information with alter to restart the database to take effect. So you don't think I'm going to have to restart the database. It's horrible. .

In fact, you do not have to restart the database pull, you force the update under this.

Alter system checkpoint;

alter system flush Shared_pool;

Once you land, you can log in. And the previous Arwen users will not be able to log.

But it's not easy to switch to a username like this, and it's estimated that very few DBAs are going to do it. Because there are really not many scenes to change the username. And even if you want to change the pilot out of the import can do.

I want to do something bad. Clear password

Just above you found that the user name and password are in a table, and the password are encrypted ciphertext, so you think I can find a way to the whole into clear text. So you can see someone else's password, convenient to do what bad ah. So you want to change the name of the same thing.

Update user$ Set password = ' abc ' WHERE user# = 250;

alter system checkpoin;

alter system flush Shared_pool;

SELECT * from user$ where name = ' Arwen '-at this point you find that the password actually becomes clear, ABC. So you have a Coke.

So you can't wait to log in and see if you can really use it.

The result makes you very depressed, unexpectedly can not log on. And the user Arwen the original password is also scrapped. So we can only obediently use alter to change.

Alter user Arwen identified by ABC; --it will be able to log in.

After changing to clear text can not be logged, but changed the time without error. I don't know what the Oracle is all about. I think it is reasonable to change the password in the table user$ should be prompted to say that you can not change the better.

Ah, but anyway, no one is really going to have the pain of the egg to dry the password clear display of the boring thing. So it doesn't matter.

I'm using Oracle 11GR1. I don't know what Oracle's version in the early days could actually change the password to clear text and log in.

How Oracle Changes user names