Shulou(Shulou.com)05/31 Report--

This article is about how Oracle 11g R2 manages users. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

If you want to see the presence of the oracle system, users can view the dba_users view.

If you use sqlplus queries, you need to format them in advance.

SQL > col username for A20

SQL > col account_status for A20

SQL > col default_tablespace fora20

SQL > set linesize 120

SQL > set pagesize 120

SQL > select username,account_status,default_tablespace from dba_users

USERNAME ACCOUNT_STATUS DEFAULT_TABLESPACE

MGMT_VIEW OPEN SYSTEM

SYS OPEN SYSTEM

SYSTEM OPEN SYSTEM

DBSNMP OPEN SYSAUX

SYSMAN OPEN SYSAUX

SCOTT OPEN USERS

OUTLN EXPIRED & LOCKED SYSTEM

FLOWS_FILES EXPIRED & LOCKED SYSAUX

MDSYS EXPIRED & LOCKED SYSAUX

ORDSYS EXPIRED & LOCKED SYSAUX

EXFSYS EXPIRED & LOCKED SYSAUX

Oracle user Management

To access the database, anyone must be a valid database user who can be authenticated by oracle. We can configure the application to require that each individual who needs access have a different database account, or we can configure the application itself to connect to the database as a public user and handle application-level permissions internally. No matter which method you choose, you need to create one or more users who are allowed to manipulate the data within the database.

User and mode (schema)

Syntax for creating a user:

Create user username identified by password default tablespace tablespace quota size on tablespace

Note: although the user name is lowercase when creating a user, it is uppercase when it is stored in the data dictionary, such as create user tom. If you have to use lowercase, the user name needs to be enclosed in double quotation marks create user "tom"

The created user does not have any permissions, not even the right to log in to the database.

If a user's quota for a tablespace is not set, it cannot be written even if the user has write access to the tablespace.

Examples are as follows:

View the status of HR users

Change the password of the HR user to oracle

Manually set expiration

Unlock the user

Give an example to illustrate the permission granted and revoked by oracle.

Before creating a user, create a tablespace xxx

Create a tablespace xxx

Create tablespace xxx datafile'/ u01bind xxx.dbf' size100m autoextend on next 10m maxsize unlimited

Create user tom

Create user tom identified by oracle

User tom created by query

Although the tom is created in lowercase, what is stored in the data dictionary is uppercase, so the query also needs to be uppercase.

Delete tom user

Drop user tom

Create user "tom" identified by oracle

Delete tom user

The correct way to delete should be:

Drop user "tom"

Create user tom. The default tablespace is "XXX" and no quota is specified, so tom users cannot create objects on the xxx tablespace.

Results of using tom users to connect to the database

SQL > conn sys/oracle as sysdba

Drop user tom; deletes tom users

When creating tom users, you specify that the default tablespace is xxx,tom users and the disk quota is 10m.

You can use the dba_ts_quotas view to view disk quotas for each user

The tom user we just tested did not even have permission to create a session.

Grant permissions to users

Grant session permissions to tom users

SQL > conn sys/oracle as sysdba

SQL > grant create session to tom

Authorization successful.

Test the connection.

Test whether tom users can create tables

Grant tom users the right to create tables

Create an aa table

Test whether tom users can insert records for the table, and you can insert records.

Revoke the authority

View the permissions currently owned by the tom user

Revoke the create table rights of the tom user

Test tom users can view the emp table under SCOTT users

Use sys users to connect to the database

Authorize the permissions of the emp table under the select scott user for the tom user

Use tom users to connect to the database

Thank you for reading! This is the end of the article on "how to manage users in Oracle 11g R2". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.