Oracle database create user, specify default (create) tablespace, and grant permissions (dba, ETL are required)

preface

In the actual development process, creating users in the database, specifying tablespaces and assigning permissions to users are the basic operations. The script commands are introduced below.

Tip: the following is the main content of this article. The following cases can be used for reference

1, How to create a tablespace

1. Create a default tablespace

Keyword Description:
Take the name after tablespace casually;
TB_ BMS01. The dbf name is optional, and the suffix must be dbf, the directory must already exist. If it does not exist, it needs to be created manually before executing the statement;
size 30000M: represents the initial allocation size;
autoextend on next 1000M: represents how much space is allocated at one time when the space is insufficient.
**datafile: * * the path of the table space storage file, which generally refers to the storage path on the server

CREATE SMALLFILE TABLESPACE COM_ZHX
DATAFILE 
  '/DBSoft/oracle/oradata/ZHX/COM_ZHX_.dbf' SIZE 30000M REUSE AUTOEXTEND ON NEXT 1000M MAXSIZE unlimited
LOGGING 
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO; 

2. Create a temporary tablespace

Keyword Description:
Take the name after tablespace casually;
TB_ BMS01. The dbf name is optional, and the suffix must be dbf, the directory must already exist. If it does not exist, it needs to be created manually before executing the statement;
size 2000M: represents the initial allocation size;
autoextend on next 500M: represents how much space is allocated at one time when the space is insufficient.
**datafile: * * the path of the table space storage file, which generally refers to the storage path on the server

CREATE TEMPORARY TABLESPACE COM_ZHX_TMP TEMPFILE '/DBSoft/oracle/oradata/ZHX/COM_ZHX_tmp.dbf' SIZE 2000M autoextend on next 500M maxsize 5000M;

2, Create a user and specify a tablespace:

1. Create user specified default tablespace

Keyword Description:
identified by is the user name in front and the password in the back;
If the user name is not in the correct format, try adding * *: c## * * in front. For example: c##oraclezhx this is caused by inconsistent database versions.

create user orclzhx identified by orclzhx default tablespace COM_ZHX;

2. Create the default temporary tablespace specified by the user

Keyword Description:
identified by is the user name in front and the password in the back;
If the user name is not in the correct format, try adding * *: c## * * in front. For example: c##oraclezhx this is caused by inconsistent database versions.

create user orclzhx identified by orclzhx default tablespace COM_ZHX_TMP;

3. You can also specify the default and temporary tablespaces

Keyword Description:
identified by is the user name in front and the password in the back;
If the user name is not in the correct format, try adding * *: c## * * in front. For example: c##oraclezhx this is caused by inconsistent database versions.

create user orclzhx 
  identified by orclzhx 
  default tablespace COM_ZHX
  temporary tablespace COM_ZHX_TMP;

4. Delete default and temporary tablespaces

Delete default tablespace

drop tablespace COM_ZHX including contents and datafiles cascade constraints;

Delete temporary tablespace

drop tablespace COM_ZHX_TMP including contents and datafiles cascade constraints;

3, Assign permissions to the specified user

1. Authorization to connect

grant connect to orclzhx;

Permission can be revoked

revoke connect from orclzhx 

2. Authority to authorize resources

grant resource to orclzhx;

3. Authorize the permission to create the view

grant create view to orclzhx;

4. Authorize the permission to create the view

grant debug any procedure, debug connect session to orclzhx;

5. Authorization of tablespace

grant unlimited tablespace to orclzhx;

Revoke permissions on a tablespace

revoke unlimited tablespace from orclzhx;

6. Grant to designated users orclzhx

grant create session to orclzhx;

Revoke permissions

revoke create session from orclzhx;

7. Authorize orclzhx create table

grant create session to public;

8. Grant to all users

grant create table to orclzhx;

Revoke permissions

revoke create table from abc;

9. View the system permissions of the current user

select * from user_sys_privs;

10. Object permission (the table given to your own user so that other users have corresponding permissions)

GRANT SELECT ON MYTAB TO Authorized user;
GRANT ALL ON MYTAB TO Authorized user;
REVOKE SELECT ON MYTAB FROM Authorized user;
REVOKE ALL ON MYTAB FROM Authorized user;
GRANT SELECT ON MYTAB TO PUBLIC;

Object permissions can be controlled to columns:

grant update(Listing) on mytab to Authorized user;

Note: only INSERT and UPDATE can be controlled to the column,
SELECT and DELETE cannot be controlled to columns

11. View the object permissions of the current user

select * from user_tab_privs;

12. View which column permissions the current user has

select * from user_col_privs;

4, Transfer of permissions

1. Transfer of system permission:

GRANT ALTER ANY TABLE TO user name WITH ADMIN OPTION;
GRANT ALTER ANY TABLE TO Pass user name

2. Transfer of object permissions

GRANT SELECT ON A TO user name WITH GRANT OPTION;
SELECT * FROM USER_TAB_PRIVS;
GRANT SELECT ON A TO Pass user name;

summary

The above is the basic syntax of database creation user specified table space. There are script commands and time to supplement.