Oracle(15)Managing Users

Table of Contents

1. Basic knowledge

1. Users and Security Users and Security

2. Database Schema

3. Checklist for Creating Users Steps to create users

2. Basic operations

1. Create a user

2. OS Authentication Operating system authentication

3. Dropping a User

4. Getting User Information Obtaining user information

Managing Users

Goal:

  • Create new database user
  • Change and delete existing database users
  • Monitor information for existing users
  • Get user information

1. Basic knowledge

1. Users and Security Users and Security

2. Database Schema

  • A schema is a named collection of objects. Schema is a named collection of objects
  • A user is created, and corresponding schemais created. Create a user, and create a corresponding schema.
  • A user can be associated with onlyone schema. A user can be associated with only one schema.
  • Username and schemaare often used interchangeably. Username and schemaare often used interchangeably.

3. Checklist for Creating Users Steps to Create Users

  • Determine the tablespace in which the user must store objects
  • Determine quotas for each tablespace
  • Specify default tablespace and temporary tablespace
  • Create a user
  • Grant permissions and roles to users

2. Basic operations

1. Create a user

You need to clearly follow the third step in the basic knowledge in advance.

The code format is as follows:

CREATE USER username
IDENTIFIED BY user's password (an error may be reported here because the password is too simple)
DEFAULT TABLESPACE The name of the table space (here is the default table space for the specified user)
TEMPORARY TABLESPACE The name of the temporary table space (here is the temporary table space of the specified user);

Note: If you don’t want to specify the temporary table space, you don’t need to write it, but the default table space must be written.

At this time, the user has been created successfully, but it is not yet possible to log in with this user. We need to give this user permissions.

grant create session to username;

2. OS Authentication Operating System Authentication

  • The OS_AUTHENT_PREFIX initialization parameter specifies the format of the user name.
  • Default is ops$
CREATE USER aaron
IDENTIFIED EXTERNALLY
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE temp
QUOTA 15m ON data;

Full process using operating system authentication:

1. First you need to create a new user (if there are already other users)

Create command for:

useradd username

Then set the user password:

passwd username

Then press Enter to enter your password

2. Enter the database and log in as sys user

Create a database user:

CREATE USER OPS$ username (consistent with the user just created)
IDENTIFIED EXTERNALLY
default tablespace The name of the tablespace;

Give this user permissions:

grant create session to OPS$username;

3. Exit the database and switch to the user created above

su - username

Then set Oracle environment variables for this user:

Then log in to the database:

sqlplus /

Then you will find that this user can log in to the database directly without password and user name verification. This is operating system authentication.

Of course, the most commonly used method is to create users in the database (basic operation: 1). Operating system authentication is not secure.

We usually need to turn it off in actual environments

Enter in sql: show parameter auth to view

What is currently displayed is false, which means it is closed. During development, it can only be opened and used when needed. It needs to be closed in time after use.

3. Dropping a User to delete the user

  • Use the DROP command to delete users. 
    • DROP USER username;
  • If the schema contains objects, use the CASCADE clause to delete all objects in the schema. 
    • DROP USER username CASCADE;
  • The user currently connected to the Oracle server cannot be deleted

To see which users are connected we can view the dynamic view v$session

Because there are many fields, we can try DESC first to see the structure of this table, and then select what we need to query:

DESC v$session;

Then view the data in the dynamic view:

select sid, username, status from v$session;

If the user you want to delete is already linked to the server, we need to disconnect him first and then delete him.

4. Getting User Information Get user information

Information about users can be queried through the following views:

  • DBA_USERS
  • DBA_TS_QUOTAS