ORACLE learning notes-create a user and a table, and create an oracle learning note

ORACLE learning notes-create a user and a table, and create an oracle learning note

I. User-related SQL statements

/* Create a user */

create user SA identified by 2013;

Description: SA user name and 2013 Password

/* Authorize connect, resource to sa */grant connect, resource to sa;

Note: The CONNECT role is the most basic and typical right granted to end users.

ESOURCE role: -- It is granted to developers

/* Query all users */select * from all_users;

/* GRANT the table STU query permission to sa */grant select on stu to sa/* GRANT the table STU add permission to sa */grant insert on stu to sa/* update the table STU grant sa */grant update on stu to sa/* grant sa */GRANT DELETE ON STU TO SA

Ii. Table Creation

/* Create table specified tablespace */create table KC (KC_NO varchar2 (10) not null, KC_NAME varchar2 (50) not null) tablespace TEST; create table STU (STU_ID varchar2 (10) not null, STU_NAME varchar2 (50) not null, STU_AGE number (2) not null, STU_SET number (2) not null, KC_NO varchar2 (10) not null) tablespace TEST;

Note: two tables are used in this article, namely the student table STU and the course table KC. The two tables are stored in the TEST table space.

/* Create constraints, Set PRIMARY keys */alter table stu add primary key (STU_ID) alter table kc add primary key (KC_NO)/* View TABLE space */Select table_name, tablespace_name from dba_tables where tablespace_name = 'test'

/* Query all tables */select table_name, tablespace_name, temporary from user_tables

How Does oracle create a new user and grant permissions?

Create user username identified by password; -- this is the creation USER (this is the simplest creation statement without specifying the tablespace)
Grant create session to username; -- this ensures that the user can log on TO the database
Grant create table to username; -- this allows the user to create a TABLE
There are still a lot of permissions. These are some basic syntaxes.

Users created using command lines in oracle cannot create tables.

The tablespace is not defined.
Create user abc identified by abc default tablespace users temporary tablespace temp;
Grant connect, resource to abc