Oracle creates table spaces, creates users, grants, grants access to authorized objects, and Views permissions ____oracle



After installing Oracle software, you can use
User name Sys
Password: password when creating an Oracle instance
Server type: DBA
ORCL the default naming when installing software
You can go to the server

Create a new user name by using the following methods
You can log on using Ccflow-ccflow-orcl-normal this series configuration

When you import data, the DBA can only be imported to the DBA,
The action required at this point is: to authorize DBA Authority for the Ccflow user
Grant CONNECT,RESOURCE,DBA to Ccflow

Database Import Commands
In cmd, with imp ccflow (username)/ccflow (password) @orcl file=c:/data/data.dmp (for the existing DMP file storage path, you can drag and drop to the CMD environment, the path does not exist in Chinese characters can) log=c:\ Gengbao.log (custom generated log files for import) full=y (full import) ignore=y (ignore conflicts)

Database Export command
In cmd, use the exp ccflow (username)/ccflow (password) @orcl file=c:/data/data.dmp (the storage path for the file) Log=c:\gengbao.log (custom generated log files for export)

1. Create a temporary table space
The Oracle temporary table space is used primarily for querying and storing buffer data. The main reason for temporary table space consumption is the need for intermediate knots of queries

Fruit to be sorted.
Primary role of temporary tablespace:
index Create or rebuild
Order BY or GROUP by
Distinct operation
Union or intersect or minus
Sort-merge joins

CREATE temporary tablespace "Test_temp"
Tempfile ' D:/oracle/product/10.2.0/oradata/gis/test_data.ora '
SIZE 20M
Autoextend on
NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT Local;

Cases
An Oracle folder was created in Disk E

CREATE temporary tablespace "Test_temp"
Tempfile ' E:/oracle/test_data.ora '
SIZE 20M
Autoextend on
NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT Local;

2. Create a user table space
CREATE tablespace "Test_data"
LOGGING
DataFile ' D:/oracle/product/10.2.0/oradata/gis/test_data.ora '
SIZE 20M
Autoextend on
NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT Local;

Cases
Create a new Ora file in e-disk

CREATE tablespace "Test_data"
LOGGING
DataFile ' E:/oracle/test_data2.ora '
SIZE 20M
Autoextend on
NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT Local;

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

CREATE USER Usertest identified by userpwd
DEFAULT tablespace Test_data
Temporary tablespace test_temp;

Cases
User name is Ccflow password is ccflowpwd
CREATE USER Ccflow identified by ccflowpwd
DEFAULT tablespace Test_data
Temporary tablespace test_temp;

4. Give user authorization
GRANT
Create session, create any TABLE, create no 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, inserts any table, the UPDATE any table, deletes any table
to username;
"Example--Start"
User name is Ccflow
GRANT
Create session, create any TABLE, create no 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, inserts any table, the UPDATE any table, deletes any table
to Ccflow;

"Case-End"

First authorize users to testuser two basic role permissions

Connect role:--is the typical right to give end users, the most basic
Create session--establishing sessions

Resource role:--is granted to developers
Create CLUSTER--Create a cluster
Create PROCEDURE--build process
Create SEQUENCE--Create a sequence
CREATE table--building tables
Create TRIGGER--creating triggers
Create type--build types
Create OPERATOR--Creating an operator
Create Indextype--Creating an index type
CREATE table--Creating tables

The SQL statement that authorizes the role to the user:
GRANT role to username;

Note: The permissions of both roles may need to be granted to testuser under the SYS user
Grant SELECT on Csm_mid.pob_rtu to TestUser;
Grant Connect, resource to testuser;

If you now assign the SELECT permission of the User User1 table Table1 to TestUser
Connect to Oracle in User1, execute the following SQL statement
Grant SELECT on Table1 to TestUser;

Now we're using the TestUser username to query the Table1 table data.
SELECT * User1.table1 from TestUser;
Note: User1 must be added before table table1

If we want to query the table table1, do not add User1 before, you can do this:
First grant TestUser permission to create a view
Grant CREATE view to TestUser;

Then in the TestUser the user first creates a view user1.table1 the base table V_user1_table1
CREATE VIEW V_user1_table1
As
SELECT *
From User1.table1;
We can use the following query:
SELECT * from V_user1_table1;

5. View User Rights

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;

To 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;

To view the roles owned by a user or role
SELECT * from Dba_role_privs;
SELECT * from User_role_privs;

Encountered no privileges on tablespace ' tablespace '
Alter user UserQuota 10m[unlimited] on tablespace;

Reference resources: http://download.csdn.net/detail/hugaozhuang/7338529