Oracle User Management

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  by password;

(Oracle has a problem, the password must start with a letter, if it starts with a number, 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>alteruser 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.

Like what

Drop user username [Cascade]

When deleting a user, note:

If the user you want to delete has already created a table, then you need to take a parameter cascade when you delete it.

A comprehensive case of user management

Overview: A new user is created without any permissions, or even permissions to log on to the database, and you need to specify the appropriate permissions for it. Assign permissions to a user using the command grant, which reclaims permissions using the command revoke.

Case:

Sql>Conn Xiaoming/M12; Error:ora-01045:UserXiaoming lacksCREATESESSION privilege; logon denied warning: You are no longer connected to ORACLE. SQL>ShowUser;USERto "" SQL>Conn System/p; connected. SQL> GrantConnect toXiaoming; authorized success. SQL>Conn Xiaoming/M12; connected. SQL>Note:GrantConnect toXiaoming;

Here, to be precise, connect is not a privilege, but a role. Look at the picture:

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  to Xiaoming

* Hope Xiaoming users can modify Scott's EMP table

Grant Update  on  to Xiaoming

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

Grant  All  on  to Xiaoming

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

Revoke Select  on  from Xiaoming

Maintenance of permissions

* Hope Xiaoming users can go to the Scott's EMP table/also hope that xiaoming can continue to give this permission to
People.

-- if object permissions, join with GRANT OPTION Grant Select  on  to  with Grant option

My operation process:

Sql>Conn Scott/Tiger; connected. SQL> Grant Select  onScott.emp toXiaoming with Grant option, Authorization is successful. SQL>Conn System/p; connected. SQL> Create UserXiaohong identified bym123; The user has created. SQL> GrantConnect toXiaohong; authorized success. SQL>Conn Xiaoming/M12; connected. SQL> Grant Select  onScott.emp toXiaohong; authorized success. 

If this is a system privilege
When system gives Xiaoming permissions:

Grant  to  with 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; connected. SQL> Revoke Select  onEmp fromxiaoming a successful revocation; SQL>Conn Xiaohong/m123; connected. SQL> Select *  fromscott.emp;Select *  fromScott.emp First1line error: ORA-00942: Table or view does not exist

The results showed that little red was connected.

Oracle User Management