Creation and deletion of Oracle users

1. Create a user :

Sql> create user User01 identified by User01;

User created.

But login prompt 01045 error, missing create session permission.

ERROR:
Ora-01045:user USER01 lacks CREATE SESSION privilege; Logon denied
Enter User-name:

At this point you need to give connect permission to the user (for the reason (see (2)), after logging in to create a table hint 01031 error:

Sql> CREATE table T (x number);
CREATE table T (x number)
*
ERROR at line 1:
Ora-01031:insufficient Privileges

Once again give resource permission to the user (see (2)), you can create the table.

2. You need to grant connect and resource permissions to the user :

Sql> Grant Connect, resource to User01;

Grant succeeded.

Let's say why Connect and resource permissions are required,

the permissions on Connect and resource can be found in previous blog posts: http://blog.itpub.net/7192724/viewspace-1184490/

RESOURCE: A user with RESOURCE permission can only create entities and cannot create a database structure.

Connect: A user with connect permission can only log on to Oracle, not create an entity, and cannot create a database structure.

Sql> Select Grantee,privilege from Dba_sys_privs where grantee= ' RESOURCE ' order by privilege;

GRANTEE PRIVILEGE

------------------------------ ----------------------------------------
RESOURCE CREATE CLUSTER
RESOURCE CREATE Indextype
RESOURCE CREATE OPERATOR
RESOURCE CREATE PROCEDURE
RESOURCE CREATE SEQUENCE
RESOURCE CREATE TABLE
RESOURCE CREATE TRIGGER
RESOURCE CREATE TYPE
8 rows selected.

sql> Select Grantee,privilege from Dba_sys_privs where grantee= ' CONNECT ' order by privilege;

GRANTEE PRIVILEGE
------------------------------ ----------------------------------------
CONNECT CREATE SESSION

Therefore, the user with Connect has the Sqlplus to create the session, and the creation of the table depends on the resource permission.

3. Delete the user :

sql> drop user User01;
Drop User User01
*
ERROR at line 1:
Ora-01922:cascade must is specified to drop ' USER01 '

The error indicates that there are other objects created under the user, and if the user is deleted, there are two methods:

1. Delete the object under the user:

Log in User01 after execution:

sql> drop table tbl_seg;

Table dropped.

Log in to Sys again to execute:

sql> drop user User01;

User dropped.

2. Use Cascade to cascade Delete:

drop user User01 cascade;