[Oracle] user management

Create user hywin identified by hywin; create a user

Password username Change Password
Alter User Username identified by new password
Drop User Username [cascade]; if you have already created a table when deleting a user, you need to include the cascade parameter when deleting the user.

Grant connect to user name; Authorize a user to connect to the database. Connect is a role.

Revoke select on EMP from hywin; revoke the permission of hywin to query the EMP table.

Permission:

1. system permissions (about 140 types)
Permissions related to databases, database creation, table creation, and password modification. Create session

2 object permissions (about 25 types)
The user's permission to operate on Data Objects of other users. (Data Objects: tables, views, indexes, triggers, etc)
Select Insert update delete all

Grant select on EMP to hywin; the EMP table can be queried for hywin authorization.
Select * from Scott. EMP; Solution

Role:

1 pre-defined role
2. Custom roles

Connect has 7 permissions.
DBA administrator role
Resource can be used to create tables in any tablespace.

Desc table name; view table structure

Permission Maintenance
Object permission: suffix with grant option
Grant select on EMP to hywin with grant option;

System permission: Suffix: with admin Option
Grant select on EMP to hywin with admin option;

After multi-level authorization, some permissions granted to the intermediate user are revoked at the same time.

Password Management
Profile

Scott can log on three times at most and then lock for 2 days.
Create profile lock_account limit failed_login_attempts 3 password_lock_time 2; -- lock_account rule name

Alter user Scott profile lock_account;

Unlock a user
Alter user Scott account unlock;

Abort Password
Create profile myprofile limit password_life_time 10 password_grace_time 2; change the password once every ten days, with a grace of two days.

Alter user Scott profile myprofile;

Password history
Create profile password_history limit password_life_time 10 password_grace_time 2 password_reuse_time 10; reusable in ten days

Delete profile
Drop profile password_history [cascade]