ORACLE-02 User Management

First, create a 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;
Note: Oracle has a problem, the password must start with a letter, and if it starts with a number, it does not create a user
eg, create user xiaoming identified by Oracle;

Second, the user to change the password
Overview: If you change your password you can use it directly
sql> password user name or PASSW
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

Third, delete the 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"
Note: When deleting a user, if you want to delete the user, has created a table, then you need to delete the time with a parameter cascade, that is, the user and the table deleted together;

Iv. Permissions
Permissions are divided into system permissions and object permissions.
What is System permissions?
User-related permissions to the database, connect, resource, DBA and other system permissions, such as building a library, building tables, building indexes, building stored procedures, landing database, changing passwords and so on.
What is Object permissions?
User permissions on other user's data object operations, INSERT, DELETE, UPDATE, select, all object permissions, data objects are many, such as tables, indexes, views, triggers, stored procedures, packages and so on.
Execute select * from dba_object_size; statement to get Oracle database object.

Five, the role
Roles are divided into pre-defined roles and custom roles.

Vi. Comprehensive case of user management
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. Assign permissions to a user using the command grant, which reclaims permissions using the command revoke.
In order to clarify the user's management, here I give you a case.
Sql> Conn Xiaoming/oracle
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/oracle
is connected.
Sql> Grant connect to Xiaoming;
Authorization is successful.
Sql> Conn Xiaoming/oracle
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 Scott.emp to Xiaoming
* Hope Xiaoming users can modify Scott's EMP table
Grant update on Scott.emp to Xiaoming
* Hope Xiaoming users can go to modify/delete, query, add Scott's EMP table
Grant all on Scott.emp to Xiaoming
* Scott wants to reclaim Xiaoming's query permissions on the EMP table
Revoke select on Scott.emp from Xiaoming

vii. transfer of authority
The maintenance of permissions.
* Hopefully xiaoming users can go to Scott's EMP table/and Hope Xiaoming can pass this permission to someone else.
--If object permissions, join with GRANT option
Grant SELECT on EMP-xiaoming with GRANT option
My operation process:
Sql> Conn Scott/oracle;
is connected.
Sql> Grant Select on Scott.emp to xiaoming with GRANT option;
Authorization is successful.
Sql> Conn System/oracle;
is connected.
Sql> create user Xiaohong identified by Oracle;
The user has created.
Sql> Grant connect to Xiaohong;
Authorization is successful.
Sql> Conn Xiaoming/oracle;
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/oracle;
is connected.
sql> revoke select on EMP from Xiaoming;
Undo success.
Sql> Conn Xiaohong/oracle;
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."

Eight, with admin option differs from with GRANT option
1, with admin option for system permission authorization, with GRANT option for object authorization.

2, to give a user the system permissions with the admin option, this user can grant this system permissions to other users or roles, but to reclaim the user's system permissions, this user has been granted other users or roles of this system permissions will not be invalid due to propagation, such as grant a system permissions create Session with admin option, then a gives the Create session permission to B, but when the administrator reclaims the Create session permission of A, B still has the Create session permission, but the administrator can explicitly retract B Create session permission, that is, direct revoke create session from B.

When with GRANT option is used for object authorization, the granted user can also grant this object permission to another user or role, except that when the administrator reclaims the user object permissions granted with GRANT option, the permissions are invalidated by propagation, such as the grant Select on Table with the grant option to a,a the user grants this permission to B, but when the administrator withdraws the permissions of a, the permissions of B are invalidated, but the administrator can not directly retract the Select on TABLE permission for B.

ORACLE-02 User Management