·  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;