How to change the user name in Oracle

Many people think that Oracle can easily change the password, but it cannot change the username. In fact, the name can also be changed, but it is a bit difficult to change.

 

Use of username change

As for the usefulness of name change, it is very useful in such a scenario. assume that a company has an account for each employee. The user name is your English name. when Arwen of an employee is about to resign, it is about to delete Arwen. however, we know that users in Oracle are fully bound to the objects created under this user, and objects such as tables in SQL Server can be completely separated from users. in this case, when you delete the user Arwen, all the data below will be lost. if we need the data, the easiest thing to think of is to export all the data first and then import it to a user. however, this is troublesome. so it would be better to change the name.

 

How To Change User Name

The following example is used. suppose we have a new employee, weiwenhp. he is from Arwen's class. therefore, he needs the data below Arwen. change Arwen to weiwenhp. when we log on to the database, the database system will judge whether our user name and password are correct or not. Then we naturally think of where the information must be stored in the database, there must be a table storing user information. yes, so the user information is stored in the table user $.

Select * from user $

If you use this SQL statement, you will see all the usernames and passwords and other information. however, user names are encrypted. therefore, even the Administrator does not see any user's password and can only change the user's password.

I must have changed the user name and password here. In fact, this is the case.

Select User #, name from user $ where name = 'arwen'; -- first look at the Arwen information, where user # is a serial number, which is equivalent to an ID card number. Suppose it is 250.

Update user $ set name = 'weiwenhp 'where user # = 250; -- the user name is changed. Of course, you can submit the update statement again.

 

Work to be done

After you change the name, you are eager to log on and try again. as a result, the website cannot be accessed. however, you can log on with the previous account Arwen. then you are dumb. what's going on?

I know that if I change the password, it will take effect immediately. If I change the name, I won't.

We know that the important thing in the database is to ensure data consistency. The data in a certain place has been changed, and the relevant areas have to be changed. it must be because the user information read during login has not been updated. data inconsistency may occur. you may think that sometimes we have to restart the database to change some parameter information with alter to take effect. so you don't think I have to restart the database after changing the name. this is terrible.

In fact, you do not need to restart the database to pull it, so you can force update it.

Alter system checkpoint;

Alter system flush shared_pool;

If you log on again, you will find that you can log on. The previous Arwen users won't be able to log on.

However, it is not convenient to change the user name like above. It is estimated that few DBAs will do so, because there are not many scenarios for changing the user name, and it can be done even if the user name needs to be changed first and then imported.

 

Do Something Bad clear text display Password

You just found that both the user name and password are in the same table, and the password is encrypted ciphertext, so you think I can't try to make the whole plain text. in this way, you can see other people's passwords. so you want to use the same name

Update user $ SET Password = 'abc' where user #= 250;

Alter system checkpoin;

Alter system flush shared_pool;

 

Select * from user $ where name = 'arwen' -- now you find that the password is actually in plain text, ABC. So you have a bad cola.

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

As a result, you are very frustrated. You cannot log on, and the original password of Arwen is also scrapped. You can only use alter to change the password.

Alter user Arwen identified by ABC.

 

It cannot be logged on after being changed to plain text, but no error is reported during the modification. I don't know what is behind oracle. I think it is better to prompt that the password in the table user $ cannot be changed.

However, no administrator is really idle. It doesn't matter whether the password is displayed in plain text.

I am using Oracle 11gr1. I don't know what oracle versions could do in the early stage. I could change the password to plain text, but I could not log on again.