Management of Oracle Users

Management of Oracle Users

Create user

Overview: In Oracle, to create a new user using the Create USER statement, you typically have DBA (database administrator) permissions to use.

Create user username identified by password; (Oracle has a problem, the password must start with a letter, if it starts with a letter, it will not create the user)

Change Password for user

Overview: If you change your password you can use it directly

Password User name

If you change the password for someone else, you need to have DBA authority, or have alter user's system permissions

Sql> alter user username identified by new password

Delete User

Overview: Generally as a DBA to delete a user, if you use other users to remove users will need to have drop user permissions.

For example, DROP user username "cascade"

When deleting a user, note:

If you want to delete the user, has created a table, then you need to delete the time with a parameter cascade;

User Management Comprehensive case

Overview: A new user is created without any permissions, or even the permissions of the database that is logged in, and you need to specify the appropriate permissions for it. Assigning permissions to a user makes

Use command grant to reclaim permissions using the command revoke.

In order to clarify the user's management, here I give you a case.

Sql> Conn Xiaoming/m12;

ERROR:

Ora-01045:user Xiaoming lacks CREATE SESSION privilege; Logon denied

Warning: You are no longer connected to ORACLE.

Sql> Show user;

USER is ""

Sql> Conn system/p;

is connected.

Sql> Grant connect to Xiaoming;

Authorization is successful.

Sql> Conn Xiaoming/m12;

is connected.

Sql>

Note: Grant connect to xiaoming; here, to be precise, connect is not a privilege, but a role.

Now say the object permissions, now do something like this:

* Hope Xiaoming users can go to query the EMP table

* Hope Xiaoming users can check Scott's EMP table

Grant SELECT on EMP to Xiaoming

* Hope Xiaoming users can modify Scott's EMP table

Grant update on EMP to Xiaoming

* Hope Xiaoming users can go to modify/delete, query, add Scott's EMP table

Grant all on the EMP to Xiaoming

* Scott wants to reclaim Xiaoming's query permissions on the EMP table

Revoke select on EMP from Xiaoming

The maintenance of permissions.

* Hopefully xiaoming users can check Scott's EMP table/also hope that xiaoming can continue to give this permission to others.

--If object permissions, join with GRANT option

Grant SELECT on EMP-xiaoming with GRANT option

My operation process:

Sql> Conn Scott/tiger;

is connected.

Sql> Grant Select on Scott.emp to xiaoming with GRANT option;

Authorization is successful.

Sql> Conn system/p;

is connected.

Sql> create user Xiaohong identified by m123;

The user has created.

Sql> Grant connect to Xiaohong;

Authorization is successful.

Sql> Conn Xiaoming/m12;

is connected.

Sql> Grant Select on Scott.emp to Xiaohong;

Authorization is successful.

--If this is a system privilege.

When system gives Xiaoming permissions:

Grant connect to xiaoming with admin option

Question: What happens to Xiaohong if Scott reclaims Xiaoming's query permissions on the EMP table?

Answer: Be recycled.

Here is how I do the procedure:

Sql> Conn Scott/tiger;

is connected.

sql> revoke select on EMP from Xiaoming;

Undo success.

Sql> Conn xiaohong/m123;

is connected.

Sql> select * from Scott.emp;

SELECT * FROM Scott.emp

An error occurred on line 1th:

ORA-00942: Table or view does not exist

The result shows: "Little Red has been connected."

Manage user passwords with profile

Overview: Profile is a set of commands that are password-constrained, resource-constrained, and when a database is established, Oracle automatically creates a profile called default. When you create a user without

With the profile option specified, Oracle assigns default to the user.

1. Account Lockout

Overview: Specify the number of times a password can be entered when the account (user) logs on, or you can specify the time that the user is locked out (days) to execute the command as a DBA.

Example: Specify Scott this user can only try to log in at most 3 times, lockout time is 2 days, let us see how to implement.

Create profile file

Sql> Create profile Lock_account limit failed_login_attempts 3 password_lock_time 2;

sql> alter user Scott profile Lock_account;

2. Unlock the account (user)

sql> alter user tea account unlock;

3. Terminating the password

In order for the user to periodically change the password can be done using the command to terminate the password, the same command also requires the role of the DBA to operate.

Example: Create a profile for the user tea created earlier, requiring the user to modify their login password every 10 days for a grace period of 2 days. See how it's done.

Sql> Create profile Myprofile limit password_life_time Password_grace_time 2;

sql> alter user tea profile myprofile;

Password history

Overview: If you want users to be able to change passwords without using previously used passwords, you can use the password history so that Oracle will store the password modification information in

Data dictionary, so that when the user modifies the password, Oracle compares the old and new password and prompts the user to reenter the password when the old and new password is found.

Example:

1) Establish profile

Sql>create profile Password_history Limit password_life_time password_grace_time 2 password_reuse_time 10

Password_reuse_time//Specifies that the password can be reused after 10 days

2) assigned to a user

Delete profile

Overview: When you don't need a profile file, you can delete it.

Sql> Drop profile Password_history "Casade"

Note: After the file is deleted, all the users who use this file to bind are also released.

If you add Casade, you'll remove the cascade of related stuff.

Management of Oracle Users