Oracle 11g R2 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

createtablespace 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>connsys/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

Permission for Tom user to authorize the EMP table under the Selectscott user

Connect to a database by using a Tom user

If you are interested in this, please scan the QR code below for free for more details

Oracle 11g R2 User and Schema (schema)