Oracle user management overview

Differences between user, user account, and schema

A user isa person who connects to a user account by establishing a session against theinstance and log on with the user account name

A schemais a set of objects owned by a user account.

User Account attributes

1. User Name (required)

2. authentication method (required)

3. default tablespace

4. Table space quota

5. Default temporary tablespace

6. User Configuration File

7. User Account Status

The username must start with a letter and cannot exceed 30 characters. It can contain letters, numbers, $, and underscores (_), and cannot contain reserved words. The letters are case-sensitive, however, it is stored in uppercase. These rules can be broken by double quotation marks;

SQL> create user "john % #" identified by pa55w0rd;

Once a user name is created, it cannot be modified. Unless you delete the user and recreate it, but this is quite dangerous, because when you delete the user, all its objects will be deleted;

Set the default tablespace:

Set the default tablespace of the entire DATABASE: alter database default tablespace tablespacename; if not specified, the default tablespace is SYSTEM;

-- View the user's default tablespace and temporary space

Selectusername, default_tablespace, temporary_tablespace from dba_users whereusername = 'sj ';

A quota is the capacity that can be occupied by objects owned by a user in a tablespace;

-- Change the quota of a user in a tablespace

Alter user SJ quota500M on sj;

-- Change the quota of a user in the tablespace to unrestricted

Alter user SJ quotaunlimited on sj;

-- View the user's quota in the tablespace

Selecttablespace_name, bytes, max_bytes from dba_ts_quotas where username = 'sj ';

-- View the default tablespace and default temporary tablespace of the entire database

Selectproperty_name, property_value from database_properties

Where property_namelike '% TABLESPACE ';

Temporary tablespace:

When a session performs an operation, the memory capacity required exceeds the PGA capacity of the session. In this case, the session will use temporary tablespace;

Temporary tablespace operations include:

1. Sort rows

2. Table join

3. Create an index

4. Use a temporary table

The system automatically manages the space of a temporary tablespace.

You do not need to allocate a quota on the temporary tablespace because all objects in the temporary tablespace belong to SYS, And the SYS user has an unlimited quota on all tablespaces;

-- Change the user's temporary tablespace

Alter user username TEMPORARYTABLESPACE tablespacename;

When a large number of users use the same user account, temporary tablespace bottlenecks may occur. In this case, they can allocate their own temporary tablespace for each account.

 

Configuration File: Profile

The main role of the configuration file is to control the format of the password settings and restrict the usage of resources by users;

 

User Account Status:

1. OPEN

2. LOCKED

3. EXPIRED

4. EXPIRED & LOCKED

5. EXPIRED (GRACE)

6. LOCKED (TIMED)

7. EXPIRED & LOCKED (TIMED)

8. EXPIRED (GRACE) & LOCKED

9. EXPIRED (GRACE) & LOCKED (TIMED)

 

Alter user username account lock;

Alter user username ACCOUNTUNLOCK;

Alter user username PASSWORDEXPIRE;

 

Authentication Method

1. operating system authentication: sysdba/sysoper

2. Password File authentication: sysdba/sysoper

3. Password Authentication

4. external Authentication

5. global authentication of LDAP directory server

-- To view which users have sysdba/sysoper permissions, you can view the V $ PWFILE_USER view;

Select * fromv $ pwfile_users;

There is also a special permission SYSASM, which is used in the ASM instance;

SYS users are not allowed to connect using password authentication. They can only use password files, operating systems, and LDAP authentication methods;

When establishing a connection, the password must be encrypted using the AES encryption algorithm when transmitted over the network. encryption is not provided for other sessions. If encryption is required, the AdvancedSecurity Option is required;

The SYSTEM user can change the password of another user:

Alter user usename IDENTIFIED BYpassword;

External authentication:

If theAdvanced Security Option has been licensed, then the external service can beKerberos server, a RADIUS server, or inthe Windows native authentication

Without the Advanced security Option, the only form of external authenticationthat can be used is operating system authentication;

Thetechnique is to create an Oracle user account with the same name as theoperating system user account but prefixed with a string specified by theinstance parameter OS _AUTHENT_PREFIX

Selectvalue from v $ parameter where name = 'OS _ authent_prefix ';

Creating external operating system authentication in a linux or unix environment is simple. Assume that a system user on the system is cedar,

In Oracle, the following two steps are required:

Createuser ops $ cedar identified externally;

Grantcreate session to ops $ cedar;

In the windos environment, assume that the domain where the user name is cedarfan is DBA, the following statement is required in Oracle:

Createuser "OPS $ DBA \ cedar fan" identified externally;

GlobalAuthentication

Anemerging standard for identity management makes user of LDAP servers

Manage user accounts:

-- Create a user account

Create user sjidentified by sj

Default tablespace sjtemporary tablespace temp

Quota 500 M onsj, quota unlimited on users

Profile test_frofile

Password expire

Account unlock;

-- Delete a user

Drop user sj -- this command can be successfully executed only when the user does not have any objects.

Drop user sjcascade; -- this command can delete objects before deleting users.