1 사용자 생성하기
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu Oct 23 13:48:59 2014
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning option
SYS>set line 200
SYS>col tablespace_name for a10
SYS>col file_name for a50
SYS>select tablespace_name, bytes/1024/1024 MB, file_name
2 from dba_data_files;
TABLESPACE MB FILE_NAME
---------- ---------- --------------------------------------------------
EXAMPLE 345.625 /home/oracle/disk4/example01.dbf
USERS 5 /home/oracle/disk5/users01.dbf
UNDOTBS1 310 /home/oracle/disk5/undo01.dbf
SYSAUX 510 /home/oracle/disk4/sysaux01.dbf
SYSTEM 710 /home/oracle/disk4/system01.dbf
HAKSA /app/oracle/product/11g/dbs/MISSING00006
HAKSA /app/oracle/product/11g/dbs/MISSING00007
7 rows selected.
SYS>SYS>create tablespace ts_webhard
2 datafile '/app/oracle/oradata/testdb/ts_web01.dbf' size 100M;
Tablespace created.
SYS>create tablespace ts_web_idx
2 datafile '/app/oracle/oradata/testdb/ts_web_idx01.dbf' size 100M;
Tablespace created.
SYS>select tablespace_name, bytes/1024/1024 MB, file_name
2 from dba_data_files;
TABLESPACE MB FILE_NAME
---------- ---------- --------------------------------------------------
EXAMPLE 345.625 /home/oracle/disk4/example01.dbf
USERS 5 /home/oracle/disk5/users01.dbf
UNDOTBS1 310 /home/oracle/disk5/undo01.dbf
SYSAUX 510 /home/oracle/disk4/sysaux01.dbf
SYSTEM 710 /home/oracle/disk4/system01.dbf
HAKSA /app/oracle/product/11g/dbs/MISSING00006
HAKSA /app/oracle/product/11g/dbs/MISSING00007
TS_WEBHARD 100 /app/oracle/oradata/testdb/ts_web01.dbf
TS_WEB_IDX 100 /app/oracle/oradata/testdb/ts_web_idx01.dbf
9 rows selected.
SYS>create temporary tablespace temp_web
2 tempfile '/app/oracle/oradata/testdb/temp_web01.dbf' size 100M;
Tablespace created.
SYS> 1 create user webuser
2 identified by webpwd
3 default tablespace ts_webhard
4 temporary tablespace temp_web
5 quota unlimited on ts_webhard
6 quota 0m on system
SYS>grant resource, connect to webuser;
Grant succeeded.
SYS>conn webuser/webpwd;
Connected.
2 사용자 정보 확인하기
default tablespace 와 temporary tablespace 정보 확인하기
SYS>set line 200
SYS>col default_tablespace for a10
SYS>col temporary_tablespace for a10
SYS>select username, default_tablespace "Defautlt TS", temporary_tablespace "Temp TS"
2 from dba_users
3 where username='WEBUSER';
USERNAME Defautlt TS Temp TS
------------------------------ ------------------------------ ------------------------------
WEBUSER TS_WEBHARD TEMP_WEB
3 password 관련 profile 생성하기
조건
로그인 시도 5회 실패시 계정을 5일 동안 사용 못하게 할 것
계정의 암호는 10일에 한 번씩 변경하게 할 것
동일한 암호는 10일 동안 사용 못하게 할 것
SYS>create profile sample_prof limit
2 failed_login_attempts 5
3 password_lock_time 5
4 password_life_time 10
5 password_reuse_time 10;
Profile created.
4 resource 관련 profile 만들기
조건
1명당 연속적으로 cpu를 사용할 수 있는 시간을 10초로 제한할 것
하루 중 8시간만 db에 접속 가능하게 할 것
10분 동안 사용하지 않으면 강제로 접속을 끊을 것
profile을 9i 이상 버전에서 즉시 적용시키기 위해서 입력하는 명령어
SYS>alter system set resource_limit=true;
System altered.
SYS>create profile re_sample_prof limit
2 cpu_per_session 1000
3 connect_time 480
4 idle_time 10;
Profile created.
사용자에게 profile 할당하기
2,4번에서 만든 profile을 webhard에게 설정함
현재 모든 사용자가 적용받고 있는 profile 확인하기
SYS>select username "사용자명", profile "적용프로파일"
2 from dba_users
3 where username='WEBUSER';
사용자명 적용프로파일
------------------------------ ------------------------------
WEBUSER DEFAULT
해당 profile에 어떤 내용이 있는지 확인하기
SYS>select * from dba_profiles
2 where profile='SAMPLE_PROF';
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------- ------------------------------ -------- ----------
SAMPLE_PROF COMPOSITE_LIMIT KERNEL DEFAULT
SAMPLE_PROF SESSIONS_PER_USER KERNEL DEFAULT
SAMPLE_PROF CPU_PER_SESSION KERNEL DEFAULT
SAMPLE_PROF CPU_PER_CALL KERNEL DEFAULT
SAMPLE_PROF LOGICAL_READS_PER_SESSION KERNEL DEFAULT
SAMPLE_PROF LOGICAL_READS_PER_CALL KERNEL DEFAULT
SAMPLE_PROF IDLE_TIME KERNEL DEFAULT
SAMPLE_PROF CONNECT_TIME KERNEL DEFAULT
SAMPLE_PROF PRIVATE_SGA KERNEL DEFAULT
SAMPLE_PROF FAILED_LOGIN_ATTEMPTS PASSWORD 5
SAMPLE_PROF PASSWORD_LIFE_TIME PASSWORD 10
SAMPLE_PROF PASSWORD_REUSE_TIME PASSWORD 10
SAMPLE_PROF PASSWORD_REUSE_MAX PASSWORD DEFAULT
SAMPLE_PROF PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
SAMPLE_PROF PASSWORD_LOCK_TIME PASSWORD 5
SAMPLE_PROF PASSWORD_GRACE_TIME PASSWORD DEFAULT
16 rows selected.
SYS>select * from dba_profiles
2 where profile='RE_SAMPLE_PROF';
PROFILE RESOURCE_NAME RESOURCE LIMIT
---------------- ------------------------------ -------- ----------
RE_SAMPLE_PROF COMPOSITE_LIMIT KERNEL DEFAULT
RE_SAMPLE_PROF SESSIONS_PER_USER KERNEL DEFAULT
RE_SAMPLE_PROF CPU_PER_SESSION KERNEL 1000
RE_SAMPLE_PROF CPU_PER_CALL KERNEL DEFAULT
RE_SAMPLE_PROF LOGICAL_READS_PER_SESSION KERNEL DEFAULT
RE_SAMPLE_PROF LOGICAL_READS_PER_CALL KERNEL DEFAULT
RE_SAMPLE_PROF IDLE_TIME KERNEL 10
RE_SAMPLE_PROF CONNECT_TIME KERNEL 480
RE_SAMPLE_PROF PRIVATE_SGA KERNEL DEFAULT
RE_SAMPLE_PROF FAILED_LOGIN_ATTEMPTS PASSWORD DEFAULT
RE_SAMPLE_PROF PASSWORD_LIFE_TIME PASSWORD DEFAULT
RE_SAMPLE_PROF PASSWORD_REUSE_TIME PASSWORD DEFAULT
RE_SAMPLE_PROF PASSWORD_REUSE_MAX PASSWORD DEFAULT
RE_SAMPLE_PROF PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
RE_SAMPLE_PROF PASSWORD_LOCK_TIME PASSWORD DEFAULT
RE_SAMPLE_PROF PASSWORD_GRACE_TIME PASSWORD DEFAULT
16 rows selected.
사용자에게 profile 적용시키고 확인하기
SYS>alter user webuser profile sample_prof;
User altered.
SYS>select username, profile
2 from dba_users
3 where username='WEBUSER';
USERNAME PROFILE
------------------------------ ----------------
WEBUSER SAMPLE_PROF
SYS>alter user webuser profile re_sample_prof;
User altered.
SYS>select username, profile
2 from dba_users
3 where username='WEBUSER';
USERNAME PROFILE
------------------------------ ----------------
WEBUSER RE_SAMPLE_PROF
여러 개의 프로파일을 적용시킬 수 없으므로,
프로파일을 만들 때 원하는 파라미터를 모두 넣고 한꺼번에 만든 후 적용을 시켜야 한다.
사용 안하는 profile 삭제하기
SYS>drop profile re_sample_prof;
drop profile re_sample_prof
*
ERROR at line 1:
ORA-02382: profile RE_SAMPLE_PROF has users assigned, cannot drop without CASCADE
* 현재 사용자에게 할당되어 있는 profile 은 기본적으로 삭제가 안됨
cascade 옵션을 사용하면 할당되어 있더라도 삭제 가능
삭제가 되면 해당 프로파일을 사용하던 사용자는 default profile을 사용하게 됨
SYS>drop profile re_sample_prof cascade;
Profile dropped.
SYS>select username, profile
2 from dba_users
3 where username='WEBUSER';
USERNAME PROFILE
------------------------------ ----------------
WEBUSER DEFAULT
권한 관리하기
-system 관련 주요 권한
- sysoper / sysdba 권한
- system 관련 권한 할당하기 / 해제하기
scott 사용자에게 create table, create session 권한을 할당
SYS>grant create table, create session to scott;
Grant succeeded.
scott 사용자에게 create table 권한 해제
SYS>revoke create table from scott;
Revoke succeeded.
- 사용자가 가지고 있는 권한 조회
SYS>select * from dba_sys_privs
2 where grantee='SCOTT';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
SCOTT UNLIMITED TABLESPACE NO
SCOTT CREATE SESSION NO
-object 관련 권한(privilege)
주로 dml과 연관 많음 : object 를 select, insert, update, delete 등을 할 수 있는 권한을 말함
-object 권한 할당 / 해제하기
scott 사용자에게 webuser가 만든 webuser 테이블을 select 할 수 있도록 하기
WEBUSER>create table webtest
2 ( no number,
3 name varchar2(10));
Table created.
WEBUSER>insert into webtest values(1, 'sss');
1 row created.
WEBUSER>select * from webtest;
NO NAME
---------- ----------
1 sss
SCOTT>select * from webuser.webtest;
select * from webuser.webtest
*
ERROR at line 1:
ORA-00942: table or view does not exist
SYS>grant select on webuser.webtest to scott;
Grant succeeded.
SYS>conn scott;
Enter password:
Connected.
SCOTT>select * from webuser.webtest;
NO NAME
---------- ----------
1 sss
scott 사용자에게 webuser가 만든 webtest 테이블을 update 할 수 있도록 하기
또 scott 사용가가 이 권한을 다른 사람에게 줄 수 있는 권한도 주기
SCOTT>update webuser.webtest
2 set no=101
3 where name='sss';
update webuser.webtest
*
ERROR at line 1:
ORA-01031: insufficient privileges
SCOTT>conn / as sysdba
Connected.
SYS>grant update on webuser.webtest to scott with grant option;
Grant succeeded.
SYS>conn scott/tiger
SCOTT>update webuser.webtest
2 set no=101
3 where name='sss';
1 row updated.
SCOTT>select * from webuser.webtest;
NO NAME
---------- ----------
101 sss
scott 사용자가 가지고 있는 webuser의 webtest 테이블을 select 하는 권한을 해제
SCOTT>conn / as sysdba
Connected.
SYS>revoke select on webuser.webtest from scott;
Revoke succeeded.
SYS>conn scott/tiger
Connected.
SCOTT>select * from webuser.webtest;
select * from webuser.webtest
*
ERROR at line 1:
ORA-01031: insufficient privileges
scott 계정이 hr 계정에게 업데이트 권한만 부여함
SCOTT>grant update on webuser.webtest to hr with grant option;
Grant succeeded.
hr 계정이 언락 되어 있다면 아래와 같은 작업은 넘어가도 됨
SCOTT>conn / as sysdba
Connected.
SYS>alter user hr identified by hr account unlock;
User altered.
SYS>conn hr/hr
Connected.
webuser의 webtest 테이블을 조회할 수 있는 권한을 hr은 가지고 있지 않으므로 권한 에러 뜸
HR>select * from webuser.webtest;
select * from webuser.webtest
*
ERROR at line 1:
ORA-01031: insufficient privileges
업데이트 권한을 scott에게서 부여 받았기 때문에 업데이트는 정상적으로 됨
HR>update webuser.webtest
2 set no=3
3 where name='sss';
1 row updated.
HR>conn scott/tiger
Connected.
SCOTT>select * from webuser.webtest;
select * from webuser.webtest
*
ERROR at line 1:
ORA-01031: insufficient privileges
with grant option의 옵션은 DBA가 scott 계정의 권한을 해지하면 scott가 권한을 주었던 hr의 계정도 해지된다.
WEBUSER>conn / as sysdba
Connected.
SYS>revoke update on webuser.webtest from scott;
Revoke succeeded.
SYS>conn scott/tiger
Connected.
SCOTT>update webuser.webtest
2 set no=5
3 where name='sss';
update webuser.webtest
*
ERROR at line 1:
ORA-00942: table or view does not exist
SCOTT>conn hr/hr
Connected.
HR>update webuser.webtest
2 set no=5
3 where name='sss';
update webuser.webtest
*
ERROR at line 1:
ORA-00942: table or view does not exist
role 관리하기
role: 권한의 그룹
role 생성하기
SYS>create role trole;
Role created.
role에 create session, create table 권한 할당하기
SYS>grant create session, create table to trole;
Grant succeeded.
scott 사용자에게 trole 할당하기
SYS>grant trole to scott;
Grant succeeded.
어떤 사용자가 어떤 role를 사용하는지 확인하기
SYS>SELECT * FROM dba_sys_privs
2 WHERE grantee='SCOTT';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
SCOTT UNLIMITED TABLESPACE NO
SCOTT CREATE SESSION NO
어떤 role에 어떤 권한이 있는지 확인하기
SYS>select * from dba_sys_privs
2 where grantee='CONNECT';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
CONNECT CREATE SESSION NO
SYS>select * from dba_sys_privs
2 where grantee='RESOURCE';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
RESOURCE CREATE TRIGGER NO
RESOURCE CREATE SEQUENCE NO
RESOURCE CREATE TYPE NO
RESOURCE CREATE PROCEDURE NO
RESOURCE CREATE CLUSTER NO
RESOURCE CREATE OPERATOR NO
RESOURCE CREATE INDEXTYPE NO
RESOURCE CREATE TABLE NO
8 rows selected.
