Oracle user

The concept of oracle users is very important for Oracle databases. In a real environment, a server generally only installs one Oracle instance, and an Oracle user represents a user group. They log in to the database through the account of the corresponding user to create and query database objects.

Each user corresponds to the N multiple objects under the user. Therefore, in the actual project development process, different project groups use different Oracle users for development, and do not interfere with each other.

It can also be understood that an Oracle user is both a business module, and these user groups constitute a complete business system. The association between different modules can be controlled by the authority of the Oracle user. For example, obtaining data from other business modules and operating certain objects of other business modules.

Oracle user creation

Syntax: Create user

-- Create the user 
create user student--username
  identified by "123456"--password
  default tablespace USERS--tablespace
  temporary tablespace temp --tmp
  profile DEFAULT    --datafile（default data file）
  account unlock; -- Whether the account is unlocked（lock、unlock）

Through the above statement, a student user can be created, but the user cannot log in to the database yet, because it does not have permission to log in to the database. At least he needs a create session system permission to log in to the database.

User permissions

Oracle database user permissions are divided into two types: system permissions and object permissions.

System permissions: For example: create session can connect with the database, create table, create view, etc. have the permission to create database objects.
Object permissions: For example, add, delete, modify, and query data in the table, and users with database object permissions can perform corresponding operations on the objects they own.

Database role

The Oracle database role is a collection of several system permissions. Granting database roles to an Oracle user is equivalent to granting the user several database system permissions. The commonly used database roles are as follows:

CONNECT role: The connect role is the basic role of an Oracle user. The connect permission means that the user can connect to the Oracle server and establish a session.
RESOURCE role: RESOURCE role is a commonly used role in the development process. RESOURCE provides users with objects that can create their own, including: tables, views, sequences, procedures, triggers, indexes, packages, types, etc.
DBA role: The DBA role is the role of the management database administrator. It has all the permissions of the system and the permissions granted to other users. The SYSTEM user has DBA authority.

Tips:

System permissions can only be authorized by DBA users.
Object authorization is authorized by the user who has the authority of the object (not necessarily the user to which the object belongs)!
Users cannot authorize themselves!

Therefore, in the actual development process, a certain role or system authority can be assigned to a certain user according to the needs. The user’s authorization commands are as follows:

Syntax: grant

--GRANT object permission on object TO user    
grant select, insert, update, delete on JSQUSER to STUDENT;

--GRANT system permissions to user
grant select any table to STUDENT;

--GRANT role TO user
grant connect to STUDENT;--grant connect role
grant resource to STUDENT;--grant resource role

Syntax: Revoke user permissions

-- Revoke object permission on object from user
revoke select, insert, update, delete on JSQUSER from STUDENT;

-- Revoke system permission from user
revoke SELECT ANY TABLE from STUDENT;

-- Revoke role from user
revoke RESOURCE from STUDENT;

Syntax: Other operations of Oracle users

--Modify user information
alter user STUDENT
   identified by ****** - modify password
   account lock;--Modify that the user is locked or unlocked (LOCK|UNLOCK)