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
TableViewSequenceProcedure
ALTERV
V
DELETEVV

EXECUTE


V
INDEXV


INSERTVV

REFERENCESV


SELECTVVV
UPDATEVV



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


'STUDY > OCP' 카테고리의 다른 글

OCP license.E06.130701  (0) 2013.07.06
OCP license.E05.130628  (0) 2013.07.06
OCP license.E04.130627  (0) 2013.07.06
OCP license.E03.130626  (0) 2013.07.06
OCP license.E02.130625  (0) 2013.07.06
OCP license.E01.130624  (0) 2013.07.06

Comment



티스토리 툴바