[Oracle] 다른 스키마에 테이블 생성하기

• 주제
  • 다른 스키마에 테이블을 생성하는 방법
• 버전
  • Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production
• 실행 플로우
  • 유저 TOM 생성 후 유저의 접속 권한과 테이블 생성 권한 부여
  • 테이블 스페이스 TOMJERRY 생성 후  TOM에게 테이블 스페이스 TOMJERRY 에 대해 일부 할당
  • 테스트 테이블(칼럼, 프라이머리키, 커멘트 등) 생성
  • 유저 JERRY 생성 후 유저의 접속 권한과 테이블 생성 권한, 테이블 스페이스 TOMJERRY 에 대해 무제한 할당
  • 다른 유저 TOM 하위에 테이블을 생성하기 위해 권한 부여
  • TOM 스키마 하위에 테스트 테이블(칼럼, 프라이머리키, 커멘트 등) 생성
  • TOM 으로서 테이블 삭제
  • JERRY 로서 TOM 의 테이블 삭제를 위한 권한 부여
  • JERRY 로서 TOM 의 테이블 삭제
• 실행

create user tom identified by "1234";

-- logon privilege
grant create session to tom;

-- create table privilege
grant create table to tom;
select default_tablespace, username from user_users where username='TOM';

-- create table on tablespace
select tablespace_name, file_name, bytes / 1024/ 1024 MB from dba_data_files;
create tablespace tomjerry datafile 'tom_jerry.dbf' size 10m;
select tablespace_name, file_name, bytes / 1024/ 1024 MB from dba_data_files;
alter user tom default tablespace tomjerry quota 5m on tomjerry;

-- logon as tom
conn tom/1234;

-- create table
create table test(id number);
create table test2(id number not null enable, primary key (id));
describe test;
describe test2;
select owner, table_name from all_tables where owner='TOM';

comment on column test.id is 'id for test table';

-- log on as sysdba
conn / as sysdba;

-- create jerry and grant privileges as same as tom
create user jerry identified by "1234";
grant create session to jerry;
grant create table to jerry;
grant user jerry default tablespace quota unlimited on tomjerry;

-- grant privileges to jerry to create test3, test4 table under schema tom
grant create any table to jerry;
grant create any index to jerry;
grant comment any table to jerry;

-- conn as jerry and create tables under tom schema
create table test3(id number);
create table test4(id number not null enable, primary key (id));
describe test3;
describe test4;
select owner, table_name from all_tables where owner='TOM';
select owner, table_name from all_tables where owner='JERRY';

-- drop table test3, test4 as tom
conn tom/1234;
drop table test3;
drop table test4;

-- grant drop privilege to jerry to drop tables
conn / as sysdba;
grant drop any table to jerry;

-- drop tables as jerry;
drop table tom.test;
drop table tom.test2;
select ind.index_name from sys.dba_indexes ind where ind.table_owner='TOM';

 

• 참고
  • ORA-01045 : user TOM lacks CREATE SESSION privilege; logon denied
    => 접속 세션을 생성할 수 없을 때 발생
  • ORA-01031 : insufficient privileges
    => sql 문을 실행할 권한이 없을 때 발생
  • ORA-01950 : no privileges on tablespace '테이블스페이스이름'
    => 테이블스페이스에 할당된 권한(쿼터)이 없을 때 발생
• 아쉬운 점
  • grant ~ any ~ 문에서 any 가 최선일까?