Oracle/PLSQL: Change a user's password in Oracle

Oracle/PLSQL: Change a user's password in Oracle

---

Question:  How do I change the password for a user in Oracle?

Answer:  To change a user's password in Oracle, you need to execute the alter user command.

The syntax for changing a password is:

alter user user_name identified by new_password;

user_name is the user whose password you wish to change.

new_password is the new password to assign.

For example:

If you wanted to reset the password for a user named smithj, and you wanted to set the new password to autumn, you would run the following command:

alter user smithj identified by autumn;

Oracle/PLSQL: Synonyms

---

A synonym is an alternative name for objects such as tables, views, sequences, stored procedures, and other database objects.

Creating or replacing a synonym

The syntax for creating a synonym is:

create [or replace]  [public]  synonym [schema .] synonym_name
for [schema .] object_name [@ dblink];

The or replace phrase allows you to recreate the synonym (if it already exists) without having to issue a DROP synonym command.

The public phrase means that the synonym is a public synonym and is accessible to all users. Remember though that the user must first have the appropriate privileges to the object to use the synonym.

The schema phrase is the appropriate schema. If this phrase is omitted, Oracle assumes that you are referring to your own schema.

The object_name phrase is the name of the object for which you are creating the synonym. It can be one of the following:

table	package
view	materialized view
sequence	java class schema object
stored procedure	user-defined object
function	synonym

For example:

create public synonym suppliers
for app.suppliers;

This first example demonstrates how to create a synonym called suppliers. Now, users of other schemas can reference the table called suppliers without having to prefix the table name with the schema named app. For example:

select * from suppliers;

If this synonym already existed and you wanted to redefine it, you could always use the or replace phrase as follows:

create or replace public synonym suppliers
for app.suppliers;

Dropping a synonym

It is also possible to drop a synonym. The syntax for dropping a synonym is:

drop [public] synonym [schema .] synonym_name [force];

The public phrase allows you to drop a public synonym. If you have specified public, then you don't specify a schema.

The force phrase will force Oracle to drop the synonym even if it has dependencies. It is probably not a good idea to use the force phrase as it can cause invalidation of Oracle objects.

For example:

drop public synonym suppliers;

This drop statement would drop the synonym called suppliers that we defined earlier