Oracle 11g R2 User Management

If you want to see the presence of a user in an Oracle system 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 with Oracle authentication, and we can configure the application to require that each individual who needs access has a different database account. You can also configure the application itself to connect to the database as a public user and to process the application-level permissions internally. Regardless of which method you choose, you need to create one or more users within the database that allow operational data.

User and Schema (schema)

To create the user's syntax:

Create user username identified by password default Tablespace table space quota size on table space;

Note: While user names are written in lowercase when they are created, they are capitalized when stored in a data dictionary, such as create user Tom. If you want to use lowercase, the user name needs to be enclosed in double quotation marks to create "Tom"

The user created does not have any permissions and does not have permission 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 table space.

To illustrate:

View the status of an HR user

Modify the password for the HR user to Oracle

Manual settings expire;

Unlock Users

Examples of Oracle GRANT and REVOKE permissions

Create a table space before creating a user xxx

Create a table space xxx

Create tablespace xxx datafile '/u01/xxx.dbf ' size100m autoextend on next 10m MaxSize Unlimited;

Create user Tom

Create user Tom identified by Oracle;

Query created by user Tom

Although it was created in lowercase Tom, it is capitalized in the data dictionary, so you also need to write uppercase when querying

Delete Tom User

Drop user Tom;

Create user "Tom" identified by Oracle;

Delete Tom User

The correct way to delete this should be:

Drop user "Tom";

Create user tom, the default tablespace is "XXX" and does not specify quotas, so Tom users cannot create objects on the XXX table space

Results of using the Tom user to connect to the database

Sql>conn Sys/oracle as Sysdba

Drop user Tom; Delete Tom User

When you create a Tom user, you specify that the default tablespace is the Xxx,tom user's disk quota is 10m

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


We have just tested the creation of the Tom user even the right to create a session is not

Granting permissions to Users

Grant session permissions to Tom users

Sql>conn Sys/oracle as Sysdba

Sql> Grant create session to Tom;

Authorization is successful.

Test the connection.

Test if Tom user can create a table

Grant Tom user permission to create a table

Create AA Table

To test whether Tom users can insert records into a table, insert a record.

Revoke permissions

View the permissions that Tom users currently have

Revoke a Tom user's CREATE TABLE permission

Test Tom users to see the EMP table under the Scott user

Connect to database using SYS user

Permissions for Tom users to authorize the EMP table under Select Scott Users

Connect to a database by using a Tom user

If you have any questions about this article, please add the following discussion

Oracle 11g R2 User Management