Oracle creates new users based on the business

New requirements, create a user that can query the base table's data, but cannot modify it, and can also create objects by themselves

1. Create a user
The first approach is more common, and the prerequisite and temporary table spaces must exist

Format:
Create user username identified by password
Profile default
Default Tablespace table space name (typically users)
Temporary tablespace temporary table space name (varies)
account unlock;

Example:
Sql> Create user Sun identified by xxxx
2 Profile Default
3 Default tablespace users
4 Temporary tablespace tempts01
5 Account unlock;

The second way is created by default (the system loves to build which builds which)

Format:
Create user username identified by password;

Example:
Sql> Create user Sun identified by XXXX;

The user has created.

2. Authorization

After the creation of a direct login error, because there is no permission

Example:
Sql> Conn Sun/xxxx

ERROR:
Ora-01045:user SUN lacks CREATE SESSION privilege; Logon denied

Connect SYS user, authorize

Sql> Conn/as SYSDBA
is connected.

Description Connect,resource is the underlying permission
Sql> Grant Connect,resource to Sun;

Authorization is successful.

Link again, succeed, and query user has permissions
Sql> Conn Sun/xxxx
is connected.

Sql> select * from User_role_privs;

USERNAME granted_role admin_ Defaul Os_gra
------------------------------------------------------------ --------------------------------------------------- --------- ------ ------ ------
SUN CONNECT No YES No
SUN RESOURCE No YES No

3. Achieve business needs, the underlying data is unchanged, but the respective data generated in the respective tables
Sun user for new user
Scott uses the following table as the underlying table, which can be queried and cannot be modified

Business One: view base table Data
Sql> Conn Scott/xxxx
is connected.
Sql> Select table_name from User_tables;

table_name
------------------------------------------------------------
DEPT
Emp
BONUS
Salgrade
TEST

Grant Sun the query permission for the Dept table under Scott, enabling the ability to query the underlying table data
Sql> Conn/as SYSDBA
is connected.
Sql> Grant Select on Scott.dept to Sun;

Authorization is successful.

Test it.
Sql> Conn Sun/xxxx
is connected.

Sql> select * FROM dept;
SELECT * FROM Dept
*
An error occurred on line 1th:
ORA-00942: Table or view does not exist
This error is because the user name is not specified, and is found by default under its own user

Find again, success, handsome!
Sql> select * from Scott.dept;

DEPTNO dname LOC
---------- ---------------------------- --------------------------
Ten ACCOUNTING NEW YORK
DALLAS
SALES CHICAGO
OPERATIONS BOSTON

Find other tables that are unsuccessful because there is no authorization
Sql> select * from Scott.emp;
SELECT * FROM Scott.emp
*
An error occurred on line 1th:
ORA-00942: Table or view does not exist

Let's check the rights of sun users.
Sql> select * from User_tab_privs;

GRANTEE OWNER table_name grantor PRIVILEGE GRANTA Hierar

---------- -------- ------------- ---------- ----------- --------- --------
SUN Scott DEPT Scott SELECT No No

Business II: Users can create objects themselves

Authorized:
Grant create any produce to user name;
Grant create any sequence to user name;
Grant create any view to user name;
Grant create any synonym to user name;

Oracle creates new users based on the business