oracle-User Management

Oracle can assign multiple users to a database, users create their own tables, and if they do not want to be assigned to other users, other users cannot see their own created tables.

User management:

Creating users: Create user Chunxiao identified by 123456
Change Password: password Chunxaio/
123123
Delete users: Drop user Chunxiao

User Role Management

Predefined roles refer to the roles provided by Oracle, each of which is used to perform specific administrative tasks, and we describe the commonly used predefined role connect,resource,dba.

1. Connect role

The Connect role has most of the permissions that a general application developer needs, and when a user is established, in most cases the Connect role has the following system privileges as long as it is sufficient to grant the connect and resource roles to the user:

Alter session

Create cluster

Create DATABASE link

Create session

CREATE view

Create sequence

2. Resource role

The resource role has additional permissions that the application developer needs, such as setting up stored procedures, triggers, and so on. It is important to note that the resource role implies unlimited tablespace system permissions.

The resource role contains the following system permissions:

Create cluster

Create Indextype

CREATE table

Create sequence

Create type

CREATE PROCEDURE

Create Trigger

3. DBA role

The DBA role has all of the system permissions, and with the admin option option, the default DBA user is SYS and the system they can grant to other users any systems permissions. Note, however, that the DBA role does not have the privileges of SYSDBA and sysoper (starting and shutting down the database).

The newly created user does not have permission to login and needs to attach the connect role to him
Grant Connect to Chunxiao
The newly created user needs to have the permissions to build the table and need to attach the resouce role to him
Grant resource to Chunxiao

User Assignment table to other users

If you want Chunxiao users to have access to Scott's empt table, you need to log in to the Scott user first.
Use the command grant select on the empt to Chunxiao;
This means giving the Chunxiao user permission to query.

At this time Chunxiao user login Query empt table SQL statement need this: SELECT * from Scott.empt;
If you want to chunxiao users of empt to increase, delete, check, change various operations. The SQL statement is as follows:
Grant all on empt to Chunxiao
Revoke permissions
Revoke select on empt to Chunxaio

Account Management

Account lockout
Specifies the maximum number of times a password can be entered when an account is logged in, and the time that is set by the specified user, which is typically used as a DBA to execute the command
For example, let Chunxiao only try to log in at most 3 times, pin time is 2 days

Create profile AAA limit failed_login_attempts 3 Password_lock_time 2;
Alter user Chunxaio profile AAA; Assign this AAA to dawn;

Account Unlocked
Alter user Chunxiao account unlock;

terminating password
Create a profile for the user Chunxiao created earlier, requiring users to change their login password every 10 days with a grace period of 2 days.
Create profile Myprofile limit password_life_time 2 password_grace_time;
Alter user Chunxiao profile Myprofile;

Delete profile
When you don't need a profile, you can delete the file
Drop profile xxx;