SQL User

 

- Info:

- Each time you create a User, Oracle implicitly creates his Schema with the same name.

Think of Schema as a place where everything this user creates is stored: tables, sequences, procedures, etc.

- If user name is myUser, his schema will be also called myUser.

Name of everything that he creates in this schema will have prefix "myUser.".

So if myUser creates table myTable it's full name will acctually be myUser.myTable.

- Only myUser can access myTable withouth specifing prefix "myUser.".

All other users that have access to this table must use full table name myUser.myTable or synonym for it.

 

- Create User:

CREATE USER myUser

IDENTIFIED BY myPassword

PROFILE profile1;

 

- Create user:

CREATE USER myUser IDENTIFIED BY myPassword

TEMPORARY TABLESPACE TEMP

DEFAULT TABLESPACE USERS

QUOTA UNLIMITED ON USERS;

 

- Grant actions to user:

GRANT CREATE SESSION,

CREATE TABLE,

CREATE SEQUENCE,

CREATE VIEW,

CREATE PROCEDURE

TO myUser

IDENTIFIED BY myPassword;

 

- Change Password:

ALTER USER myUser

IDENTIFIED BY newPassword;

 

- Delete User:

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 included statement.

If all statements execute successfully, Oracle commits the transaction.

If any statement results in an error, Oracle rolls back all the statements.

 

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;