Basic operations for users in Oracle

Create user

1. First login to System user SYS (SYS user has permission to create user).

2. Then write the code to create the user in the Code edit box.

Syntax: CREATE USER uer_name identified by password;

　　Example: Create user student identified by 123456; created a student user with a login password of 123456.

3.default_tablespace: The user's default tablespace for storing database objects. If this parameter is omitted, the default is stored to the system table space

temporary_tablespace: Is the default storage table space for temporary objects, including temporary tables. If this parameter is omitted, the default is stored to the system table space

Assigning Permissions to users

1. If you want to allow users to complete some tasks in the database, you need to grant permissions to the user accordingly. For example , if you want a user to connect to a database, you must grant the user the system permission to create session.

2. Authorization must be done through grant statements through some special user authorizations (for example, System). The following table lists the permissions that users may have.

System permissions	Allow user's actions
CREATE SESSION	Connecting to a database
CREATE SEQUENCE	Create a sequence to generate a series of values
CREATE synonym	Create synonyms
CREATE TABLE	Create a table in the user schema
CREATE any TABLE	Create a table in any schema
DROP TABLE	To delete a table from the user schema
DROP any TABLE	Delete a table in any schema
CREATE PROCEDURE	Create a stored procedure
EXECUTE any PROCEDURE	Execute a stored procedure in any schema
CREATE USER	Create user
DROP USER	Delete User
CREATE VIEW	Create a View

Syntax: GRANT CREATE SESSION to student; Assign the user student permissions to the connection.

　　Connect role: Allow users to connect to the database

Resource role: Allows users to create related database objects, such as tables, sequences, etc.

However, in practice we have a single statement that assigns multiple permissions, such as GRANT Connect,resource to student; Student users are assigned a connection database, create tables, create sequences (ignoring graph permissions) and other basic permissions, enough for beginners to use and operation.

3. A user assigned a permission can also assign another user the permissions it has, but only if it is assigned permissions and finally has the word with ADMIN option.

Revoke user Rights

1. Keywords: REVOKE.

Example: REVOKE CONNECT from student;

2. Note: If the student user assigns permissions to another user, the permissions of the user who student the user to assign permissions will not change when the student permission is revoked.

Modify User Password

1. The ALTER keyword is used to begin the general modification, and the keyword used as the creation time is create.

Syntax: Alter user student identified by 111111; Modify the student user's password to 111111.

2. We can also use password to visualize the change of password.

Student users under:

Syntax: CONN student/123456

PASSWORD;

Password must be placed on the next line.

SYS User:

Syntax: CONNECT student/123456

PASSWORD;

Password must be placed on the next line.

Querying user Information

1. In an Oracle database, you can query through Dba_users for information that is related to user and user characteristics. (It is the information of all users)

Syntax: SELECT username,user_id,password,default_tablespace

From Dba_users;

2. If you want to query the current user's information, you can log in to the user you want to query, and then use the select * from User_role_privs; statement to view its information.

Delete User

1. Drop keyword, for example delete student user.

Example: DROP USER student;

2. Note: when deleting a user, if the user schema contains an object (such as a table), you can delete the user by using the CASCADE keyword after the user name of the DROP USERS statement to cascade Delete. However, it is important to note that it is important to make sure that no other users have access to these objects in the user.

Basic operations for users in Oracle