Oracle Database Management-user management

1. Oracle Database User Management
1. Differences between sys and system users
(1) The importance of stored data is different:
Base tables and views of all ORACLE data dictionaries in SYS are stored in SYS users. These base tables and views are crucial for Oracle operation and are maintained by the database, no user can change it manually. Sys users have the roles or permissions such as dBA, sysdba, and sysoper, create database permissions, and are the users with the highest Oracle permissions;
The system user is used to store the second-level internal data, such as the Management Information of some features or tools of oracle. You do not have the create database permission. The system user has the common DBA role permission.
(2) Different permissions:
Sys users must Log On As sysdba or as sysoper, and cannot log on to the database in normal mode;
If the system is logged on normally, it is actually a common DBA user, but if it is logged on as sysdba, it actually logs on as a sys user,
Sys users have system permissions of "sysdba" or "sysoper" and cannot use normal,
Log on to the database:
Sys User Logon: conn sys/change_on_install as sysdba
Systeme user login: conn system/Manager
(3) differences between sysdba and sysoper system Permissions
Sysdba has the highest system permissions.
Sysoper is mainly used to start and close databases. After logging on to sysoper, the user is public.
Sysdba and sysoper belong to system privilege, also known as administrative privilege. The specific permissions of sysdba and sysoper at the system management level such as enabling or disabling a database can be viewed in the following table:

2. User Management
(1) create a user:

Users can be created only when they have DBA (Database Administrator) permissions.
Eg: SQL> create user *** (User Name) identified by *** (password)
The password must start with a letter. In this case, the created new user does not have any permissions, even the database logon permissions. You must specify the corresponding permissions for the user.
Grant permissions: Grant
Revoke permission: revoke
Eg: SQL> grant connect to *** (user name)
Users with the connect permission can only log on to Oracle, create entities, and create database structures. At this time, the user does not have a tablespace and cannot create a table. Therefore, the resource role should be assigned.
Eg: SQL> grant resource to *** (user name)
For more information about permissions, follow up.
(2) Change the password
A. If you change your password
Eg: SQL> password *** (new password)
B. If other users do not need to change the password, the DBA permission is required, or the system permission of the alter user is required.
Eg: SQL> alter user *** (User Name) identified by *** (new password)
(3) Delete a user
Delete a user as a DBA. to delete a user as another user, you must have the permission to drop the user and cannot delete yourself. When deleting a user, note that if the user you want to delete has already created a table, you must include the cascade parameter when deleting the table.
Eg:
SQL> drop User Username; // The user has not created any entity
SQL> drop User Username cascade; // Add cascade to delete all the items created by the user

3. Use profile to manage user passwords
Overview: profile is a set of commands for password restrictions and resource restrictions. When a database is created, Oracle automatically creates a profile named default. If the profile option is not specified for the created user, Oracle assigns the default option to the user.
(1) account locking:Specify the maximum number of times that a user can enter a password or specify the time when the user is locked. Generally, the command is executed as a DBA.
Eg: specifies that Scott can only log on three times at most, and the lock time is 2 days.
Create profile file
SQL>Create ProfileLock_accout (profile file name)Limit failed_login_attempts3Password_lock_time2;
SQL> alter user Scott profile lock_account;
Eg:
SQL> conn system/Manager
SQL> Create profile aaa1 limit failed_login_attempts 3 pawword_lock_time 2
SQL> alter user Xiaoming profile aaa1;

(2) Unlock an account
SQL> alter user Xiaoming account unlock

(3) Termination password:To enable users to change their passwords on a regular basis, you need to use the command to terminate the passwords.
Eg: create a profile file for the previously created user Xiaoming. the user is required to change his/her login password every 10 days. The grace period is 2 days.
SQL> Create profile myprofile limit password_life_time 10 password_grace_time 2;
SQL> alter user Xiaoming profile myprofile

(4) password history:If you do not want to use a previously used password when changing the password, you can use the password history. In this way, Oracle will change the password to information and store it in the data dictionary, in this way, Oracle will compare the old and new passwords when the user changes the password. When the old and new passwords are the same, it will prompt the user to re-enter the password.
Eg:
1) create a profile
SQL> Create profile password_history limit password_life_time 10 password_grace_time 2 password_reuse_time 10
Password_reuse_time // specify the password reusable time, which can be reused in 10 days.
Delete profile
SQL> drop profile password_history [cascade]