· SQL – User
time you create a User, Oracle implicitly creates his Schema with the same
of Schema as a place where everything this user creates is stored: tables,
sequences, procedures, etc.
user name is myUser, his schema will be also called myUser.
of everything that he creates in this schema will have prefix
myUser creates table myTable it's full name will acctually be myUser.myTable.
myUser can access myTable withouth specifing prefix "myUser.".
other users that have access to this table must use full table name
myUser.myTable or synonym for it.
CREATE USER myUser
IDENTIFIED BY myPassword
CREATE USER myUser IDENTIFIED BY myPassword
TEMPORARY TABLESPACE TEMP
DEFAULT TABLESPACE USERS
QUOTA UNLIMITED ON USERS;
Grant actions to user:
GRANT CREATE SESSION,
IDENTIFIED BY myPassword;
ALTER USER myUser
IDENTIFIED BY newPassword;
DROP USER myUser;
- Populate Schema:
- Once user/schema is created you might want to create inital
objects in it which can be done using CREATE SCHEMA.
- CREATE SCHEMA command will NOT create the schema.
Oracle executes CREATE SCHEMA statement by executing each
If all statements execute successfully, Oracle commits the
If any statement results in an error, Oracle rolls back all
CREATE SCHEMA AUTHORIZATION myUser
CREATE TABLE PEOPLE (NAME VARCHAR2(10) PRIMARY KEY, AGE NUMBER)
CREATE VIEW PEOPLE_VEW AS SELECT NAME, AGE FROM PEOPLE WHERE NAME LIKE '%vo%'
GRANT SELECT ON PEOPLE_VEW TO myUser2;