OCP license.E04.130627

What Is a View?
You can present logical subsets or combinations of data by creating views of tables. A view is a
logical table based on a table or another view. A view contains no data of its own but is like a
window through which data from tables can be viewed or changed. The tables on which a view
is based are called base tables. The view is stored as a SELECT statement in the data dictionary.

l. Controlling User Access

 Objectives
After completing this lesson, you should be able to do the following:
• Differentiate system privileges from object privileges
• Grant privileges on tables
• View privileges in the data dictionary
• Grant roles
• Distinguish between privileges and roles Objectives

 Privileges
• Database security:
     – System security
     – Data security
• System privileges: Gaining access to the database
• Object privileges: Manipulating the content of the database objects
• Schemas: Collection of objects such as tables, views, and sequences

 System Privileges
• More than 100 privileges are available.
• The database administrator has high-level system
  privileges for tasks such as:

     – Creating new users

     – Removing users

     – Removing tables      

     – Backing up tables

Creating Users
The DBA creates users with the CREATE USER statement.

CREATE USER user IDENTIFIED BY password;

CREATE USER USER1 IDENTIFIED BY USER1; User created.

conn system/oracle
CREATE USER USER1 IDENTIFIED BY USER1;           // USER1/USER1 으로 계정 생성

conn user1/user1 (x)

conn system/oracle
grant create session to user1;                     // user1에게 'session' 권한을 부여하여 접속할 수 있도록 함
conn user1/user1
create table test (id number); (x)                
conn system/oracle
grant create table to user1;                          // user1에게 'create' 권한 부여
conn user1/user1
create table test (id number); (x)                // user1이 사용하는 공간이 부족하여 불가능
conn system/oracle
alter user user1 quota 10m on users;           // user1에게 10MB의 공간 할당

 User System Privileges
• After a user is created, the DBA can grant specific system privileges to that user.

GRANT privilege [, privilege...] TO user [, user| role, PUBLIC...];

• An application developer, for example, may have the following system privileges:
     – CREATE SESSION
     – CREATE TABLE
     – CREATE SEQUENCE
     – CREATE VIEW
     – CREATE PROCEDURE

 What Is a Role?

 Object Privileages

Object Privilege	Table	View	Sequence	Procedure
ALTER	V		V
DELETE	V	V
EXECUTE				V
INDEX	V
INSERT	V	V
REFERENCES	V
SELECT	V	V	V
UPDATE	V	V

Revoking Object Privileges
• You use the REVOKE statement to revoke privileges granted to other users.
• Privileges granted to others through the WITH GRANT OPTION clause are also revoked

REVOKE {privilege [, privilege...]|ALL} ON object FROM {user[, user...]|role|PUBLIC} [CASCADE CONSTRAINTS];

[CASCADE CONSTRAINTS] :: 다른 table로 foreign key로 쓰고 있을 때 끊어버리고 권한 회수..

user1의 lock 권한 풀고 패스워드 재설정

alter user user1 identified by user1 account unlock;

ROLE_TAB_PRIVS

USER_ROLE_PRIV

USER_TAB_PRIVS_MADE

USER_TAB_PRIVS_RECD

USER_COL_PRIVS_MADE

USER_SYS_PRIVS