Create tablespaces, create tables, modify tables, authorize, and authorize ORACLE in oracle

Create tablespaces, create tables, modify tables, authorize, and authorize ORACLE in oracle
Create a tablespace


/* Divided into four steps */
/* Step 2: create a temporary tablespace */
Create temporary tablespace user_temp
Tempfile 'd: \ oracle \ oradata \ Oracle9i \ user_temp.dbf'
Size 50 m
Autoextend on
Next 50 m maxsize 20480 m // if there is no upper limit to the maximum value, use unlimited
Extent management local; // extent indicates that the range management mode is set locally. The other is the dictionary mode. extent can be understood as the table extension mode.
 
/* Step 2: create a data table space */
Create tablespace user_data
Logging // indicates creating logs nologging indicates not creating logs
Datafile 'd: \ oracle \ oradata \ Oracle9i \ user_data.dbf'
Size 50 m
Autoextend on
Next 50 m maxsize 20480 m
Extent management local;
 
/* Step 2: create a user and specify the tablespace */
Create user username identified by password
Default tablespace user_data
Temporary tablespace user_temp;
 
/* Step 4: grant permissions to users */
Grant connect, resource, dba to username;
Grant create table to username






1. grant XXX to user; grant is the role of authorization. Here XXX can be a role or a permission, such as grant role to user ;, or grant insert on table to user ;.


The standard SQL statement in MOS:


Create a role:


Create role <role name> [identified by <password>/USING <package>/EXTERNALLY/GLOBALLY];


Grant role permissions:


Grant <object/system privilege> to <role name>;


Revoke permissions from a role:


Revoke <privilege> from <role name>;


Assign a role to another role or user:


Grant <role> to <username or role>;


2. connect and resource are the built-in roles of the two systems, which are in parallel with dba.


According to some posts, permissions can be divided into two categories:


System permission: The system specifies the user's permission to use the database. (System permissions are for users ).


Object permission: the access permission of a certain user to tables or views of other users. (For tables or views ).


Next, let's look at the system permissions,


DBA: it has all the privileges and is the highest system permission. only DBA can create a database structure.


RESOURCE: users with Resource permissions can only create entities, but cannot create database structures.


CONNECT: users with the Connect permission can only log on to Oracle, create entities, and create database structures.


For normal users: grant connect and resource permissions.


For DBA management users: grant the connect, resource, and dba permissions.


In addition, system permissions can only be granted by DBA users: sys and system (only these two users are allowed at the beginning ). Normal users can have the same user permissions as system through authorization,


However, the permissions of system users can never be the same as those of sys users.


In addition, for Cascade authorization with admin option,


1) if you use the with admin option to grant system permissions to a user, the user's


System permissions are not cascaded to cancel the same permissions of these users.


2) The system permissions are continuously connected, that is, A grants B permissions, and B grants C permissions. If A revokes B permissions, C permissions are not affected. system permissions can be revoked across users, that is, A can


Directly revoke the permissions of user C.



Create a table
Create a table in oracle:
Create table users (
Id number (9) not null primary key,
Username varchar2 (40) not null,
Age int check (age> 18 and age <50)
)


Create table orders (
Oid varchar2 (10) not null,
Userid number (9 ),
Primary key (oid ),
Foreign key (userid) references users (id)
)


Drop table orders


Alter table orders
Rename to orderss


Add column: alter table orders
Add price number (9)


Alter table orders
Modify price number (10)


Delete column: alter table orders
Drop column price


Alter table orders
Rename column price to pri


Add foreign key: alter table orders
Add constraint fkuid foreign key (userid) references users (id)


Delete constraint: alter table orders
Drop constraint fkuid

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.