Oracle creates tablespaces, creates users, authorizes and grants access to objects, and views permission sets.

Reproduced in: http://www.cnblogs.com/huanghai223/archive/2011/07/07/2100104.html

Author: Diamond tears

Oracle creates tablespaces, creates users, authorizes and grants access to objects, and views permission sets.

Create tablespace tab_ckbdata datafile '/oradata/tab_ckbdata001.bdf 'size 8192 M,'/oradata/tab_ckbdata002.bdf 'size 8192 M,'/oradata/tab_ckbdata003.bdf 'size 8192 m, '/oradata/tab_ckbdata004.bdf' size 8192 M, '/oradata/tab_ckbdata005.bdf'
Size 8192 M, '/oradata/tab_ckbdata006.bdf' size 8192 M, '/oradata/tab_ckbdata007.bdf' size 8192 m;

Create tablespace index_ckbdata datafile '/oradata/index_ckbdata001.bdf 'size 8192 M,'/oradata/index_ckbdata002.bdf 'size 8192 m;

Create user hnckb identified by Oracle
Default tablespace tab_ckbdata;

Alter user hnckb quota unlimited on tab_ckbdata;
Alter user hnckb quota unlimited on index_ckbdata;

Grant debug any procedure, debug connect session to hnckb;
Grant select on sys. dba_pending_transactions to hnckb;
Grant create view, create job, create synonym to hnckb;
Grant CONNECT, RESOURCE to hnckb;
1. Standard roles
CONNECT
RESOURCE

2. System Permissions
CREATE VIEW
CREATE DATABASE LINK
CREATE JOB
CREATE SYNONYM
UNLIMITED TABLESPACE
Change quota
Command: alter user name QUOTA 0 ON tablespace name
Alter user name QUOTA (numeric value) K | M | unlimited on tablespace name;
Usage:
A. control user data growth
B. When a user has certain data, the Administrator does not want him to add new data.
C. When the user quota is set to zero, the user cannot create new data, but the original data is still accessible.

3. Object Permissions
DBA_PENDING_TRANSACTIONS (SELECT)-XA transaction support

Grant select on sys. dba_pending_transactions to user;
4. It is recommended to open
Debug connect session-Debug stored procedures
For example:
Grant debug any procedure, debug connect session to HR;

Oracle creates tablespaces, creates users, authorizes and grants access and viewing permissions to objects.
1. Create a temporary tablespace
Oracle temporary tablespace is mainly used to query and store some buffer data. The main reason for the consumption of temporary tablespace is the need to end the query

Result.
The main functions of temporary tablespace:
Index create or rebuild
Order by or group
Distinct operation
Union, intersect, or minus
Sort-merge joins

Create temporary tablespace "test_temp"
Tempfile 'd: \ oracle \ product \ 10.2.0 \ oradata \ GIS \ test_data.ora'
Size 20 m
Autoextend on
Next 32 m maxsize 2048 m
Extent management local;

2. Create a user tablespace
Create tablespace "test_data"
Logging
Datafile 'd: \ oracle \ product \ 10.2.0 \ oradata \ GIS \ test_data.ora'
Size 20 m
Autoextend on
Next 32 m maxsize 2048 m
Extent management local;

3. Create a user and set its tablespace
Username: usertest
Password: userpwd

Create user usertest identified by userpwd
Default tablespace test_data
Temporary tablespace test_temp;

4. Authorize the user
Grant
Create session, create any table, create any view, create any index, create any procedure,
Alter any table, alter any procedure,
Drop any table, drop any view, drop any index, drop any procedure,
Select any table, insert any table, update any table, delete any table
To username;

First, authorize two basic role permissions: testuser.

CONNECT role:-the most basic and typical rights granted to end users
Create session-CREATE a SESSION

RESOURCE role:-is granted to developers
Create cluster-CREATE CLUSTER
Create procedure-creation process
Create sequence-CREATE a SEQUENCE
Create table-CREATE a TABLE
Create trigger-CREATE a TRIGGER
Create type-CREATE TYPE
Create operator-CREATE an OPERATOR
Create indextype-CREATE index type
Create table-CREATE a TABLE

SQL statement authorized by the role to the user:
GRANT role TO username;

Note: the permissions of these two roles may need to be authorized to testuser under sys.
Grant select on csm_mid.pob_rtu to testuser;
Grant connect, resource to testuser;

Assume that the select permission for table 1 of user1 is granted to testuser.
Connect to oracle with user1 and execute the following SQL statement
Grant select on table1 to testuser;

Now we use the testuser username to connect and query the data in table 1.
Select * user1.table1 from testuser;
Note: user1 must be added before table 1.

If you want to query Table 1 without adding user1, you can do this:
First, grant testuser the permission to create a view.
Grant create view to testuser;

Then, create a view v_user1_table1 on the base table user1.table1 as the testuser user.
Create view v_user1_table1
As
Select *
From user1.table1;
We can use the following query:
Select * From v_user1_table1;

 
5. view User Permissions

View All Users
Select * From dba_users;
Select * From all_users;
Select * From user_users;

View User System Permissions
Select * From dba_sys_privs;
Select * From user_sys_privs;

View user objects or role Permissions
Select * From dba_tab_privs;
Select * From all_tab_privs;
Select * From user_tab_privs;

View All roles
Select * From dba_roles;

View roles owned by a user or role
Select * From dba_role_privs;
Select * From user_role_privs;

No privileges on tablespace 'tablespace'
Alter user userquota 10 m [unlimited] On tablespace;

Thanks again! Diamond tears!