CREATE table spaces in Oracle, create tables, alter tables, authorize

CREATE TABLE Space


/* divided into four steps */
/* Step 1th: Create a temporary tablespace */
Create temporary tablespace user_temp
Tempfile ' D:\oracle\oradata\Oracle9i\user_temp.dbf '
Size 50m
Autoextend on
Next 50m maxsize 20480m//Assuming maximum value is not capped, use unlimited
Extent management Local; The extent indicates that interval settings are managed in a local way and the second way is dictionary dictionary mode. Extent can be understood as a way to extend the table

/* Step 2nd: Create a data table space */
Create Tablespace User_data
Logging//= Create log nologging means log not created
DataFile ' D:\oracle\oradata\Oracle9i\user_data.dbf '
Size 50m
Autoextend on
Next 50m maxsize 20480m
Extent management Local;

/* 3rd step: Create user and specify Tablespace */
Create user username identified by password
Default Tablespace user_data
Temporary tablespace user_temp;

/* Step 4th: Grant permissions to users */
Grant CONNECT,RESOURCE,DBA to username;
Grant CREATE TABLE to username






1, first. Grant XXX to User;,grant is the role of authorization, where XXX can be a role. can also be permissions. For example, grant role to user, or grant insert on table to user;.

Standard SQL statements given in MOS:


To create a role:


Create role <role name> [identified by <password>/using <package>/externally/globally];


To assign a role permission:


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


To reclaim permissions from a role:


Revoke <privilege> from <role name>;


Assign a role to a role or user:


Grant <role> to <username or role>;


2. Second, connect and resource are the built-in roles of the two systems, and the DBA is tied to the relationship.


For some posts, permissions can be divided into two categories:


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

Entity permissions: A permission user's access to other users ' tables or views. (For a table or view).


Next look at the system permissions,


DBA: Has all privileges and is the highest system privilege. Only DBA talent is sufficient to create the database structure.

RESOURCE: A user with RESOURCE permissions can only create entities. The database structure cannot be created.

Connect: A user with connect permission can only log in to Oracle. The entity cannot be created. The database structure cannot be created.


For normal users: Grant Connect, resource permissions.


For DBA administration users: Grant Connect. Resource, DBA authority.


And the system permissions can only be granted by the DBA User: sys, system (which can only be two users at the beginning). An ordinary user is authorized to have the same user rights as the system,


However, you can never reach the same permissions as the SYS user. The permissions of the system user can also be recycled.


In addition, for the issue of cascading authorizations with the ADMIN option,


1) Assuming that a user is granted system privileges using the WITH ADMIN option, the user is canceled for all users who are granted the same permission by the user.


System permissions do not cascade the same permissions that are removed by these users.


2) system permissions are not cascaded. That is, a grants B permission. b grants C permissions, assuming that a recovers B's permissions, C's permissions are not affected, and system permissions can be reclaimed across users. That a can


Directly retract the permissions of the C user.



Create a table
Create tables in Oracle:
CREATE TABLE Users (
ID Number (9) NOT null primary key,
Username VARCHAR2 (+) NOT NULL,
Age int Check (AGE&GT;18 and age<50)
)


CREATE TABLE Orders (
OID VARCHAR2 (TEN) is not NULL,
UserID Number (9),
Primary KEY (OID),
FOREIGN KEY (userid) references users (ID)
)


Delete tables: drop table Orders


Change table name: ALTER TABLE orders
Rename to Orderss


Join column: ALTER TABLE orders
Add price number (9)


Change the data type of the column: ALTER TABLE orders
Modify price Number (10)


Delete column: ALTER TABLE orders
Drop Column Price


Change column name: 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

CREATE table spaces in Oracle, create tables, alter tables, authorize