°èÁ¤ ¸¸µé±â, ±ÇÇÑ Á¦¾î
°èÁ¤
¸¸µé±â, ±ÇÇÑ
Á¦¾î
ÀÛ¼ºÀÚ :
Çö¹¬ÀÌ¿Í Àμö
½Ã½ºÅÛ °èÁ¤À¸·Î
·Î±×ÀÎ
sqlplus "/as
sysdba"
CREATE USER :
°èÁ¤
»ý¼º
DROP
USER
: °èÁ¤
Á¦°Å
CONN :
°èÁ¤ ¹Ù²ã
Á¢¼Ó
ALTER
USER
: ºñ¹Ð¹øÈ£
º¯°æ
À¯Àú °èÁ¤À»
»ý¼ºÇÔ (CREATE USER ¸í·É)
/*
À¯ÀúÀÇ Å×ÀÌºí ½ºÆäÀ̽º°¡ ±âº»
½Ã½ºÅÛ¿¡ ¸¸µé¾îÁö±â ¶§¹®¿¡ ¿Ã¹Ù¸£Áö ¾ÊÀ½ */
CREATE USER jang
IDENTIFIED BY jang
À¯ÀúÀÇ °èÁ¤À»
»èÁ¦ÇÔ, ´Ü¼øÈ÷ À¯ÀúÀÇ °èÁ¤¸¸
»èÁ¦µÈ´Ù. (DROP USER ¸í·É)
DROP USER
jang;
À¯ÀúÀÇ °èÁ¤À»
»èÁ¦ÇÔ, ¸ðµç Á¤º¸¸¦
»èÁ¦ÇÔ(DBµ¥ÀÌÅ͵îµî)
DROP USER jang
CASCADE;
»ç¿ëÀÚ °èÁ¤À¸·Î
Á¢¼ÓÇÔ
connect
jangis/gkrtod12;
conn
jangis/gkrtod12
ÀÚ½ÅÀÌ ¾î¶² °èÁ¤À¸·Î
Á¢¼ÓÇß´ÂÁö º¸±â
show
user;
ºñ¹Ð¹øÈ£
¹Ù²Ù±â
alter user jangis
identified by dlstn12;
°èÁ¤À»
»ý¼ºÇÔ, Å×ÀÌºí ½ºÆäÀ̽º
¿É¼Ç
1 create user
jangis
2 identified by
dlstn12
3 default tablespace
users
4 temporary tablespace
temp
Á¢¼Ó ¹× ¿©·¯ ±ÇÇÑ
ÁÖ±â (GRANT ¸í·É)
±ÇÇÑÀº ½Ã½ºÅÛ ±ÇÇÑ°ú À¯Àú ±ÇÇÑÀÌ
ÀÖ´Ù.
½Ã½ºÅÛ
񀀥
CREATE
USER
:
»ç¿ëÀÚ¸¦ »ý¼ºÇÒ ¼ö ÀÖ´Â
񀀥
DROP
USER
:
»ç¿ëÀÚ¸¦ »èÁ¦ÇÏ´Â
񀀥
DROP ANY
TABLE
: ÀÓÀÇ Å×À̺íÀ» »èÁ¦ÇÒ ¼ö ÀÖ´Â
񀀥
QUERY
REWRITE
: ÁúÀǸ¦ ÀçÀÛ¼ºÇÒ ¼ö ÀÖ´Â
񀀥
BACKUP ANY
TABLE
: ÀÓÀÇ Å×À̺íÀ» ¹é¾÷ÇÒ ¼ö ÀÖ´Â
񀀥
À¯Àú
񀀥
CREATE
SESSION
: µ¥ÀÌÅÍ º£À̽º¿¡ Á¢¼ÓÇÒ ¼ö ÀÖ´Â ±ÇÇÑ
CREATE
TABLE
: »ç¿ëÀÚ ½ºÅ°¸¶¿¡¼ Å×À̺íÀ» »ý¼ºÇÒ ¼ö
ÀÖ´Â ±ÇÇÑ
CREATE
VIEW
: »ç¿ëÀÚ ½ºÅ°¸¶¿¡¼ ºä¸¦ »ý¼ºÇÒ ¼ö
ÀÖ´Â ±ÇÇÑ
CREATE
SEQUENCE
: »ç¿ëÀÚ ½ºÅ°¸¶¿¡¼ ½ÃÄö½º¸¦ »ý¼ºÇÒ ¼ö
ÀÖ´Â ±ÇÇÑ
CREATE
PROCEDURE
: »ç¿ëÀÚ ½ºÅ°¸¶¿¡¼ ÇÔ¼ö¸¦ »ý¼ºÇÒ ¼ö
ÀÖ´Â ±ÇÇÑ
/*
µ¥ÀÌÅÍ º£À̽º¿¡ Á¢¼ÓÇÒ ¼ö ÀÖ´Â
±ÇÇѸ¸ ÁÜ */
grant create session
to jangis
/*
À¯Àú ±ÇÇÑÀ» ¸ðµÎ °¡Áöµµ·Ï
ÁÜ
*/
grant create
session, create table, create view, create sequence, create procedure to
jangis
°´Ã¼
񀀥
/* INSU
¶ó´Â
»ç¿ëÀÚ¿¡°Ô TEST Å×ÀÌºí¿¡ SELECT
ÇÒ ¼ö ÀÖ´Â ±ÇÇÑÀ» ºÎ¿©
*/
grant select on test
to insu;
/* INSU
¶ó´Â
»ç¿ëÀÚ¿¡°Ô TEST Å×ÀÌºí¿¡ SELECT, INSERT
ÇÒ ¼ö ÀÖ´Â ±ÇÇÑÀ» ºÎ¿©
*/
grant select, insert
on test to insu;
WITH
GRANT OPTION
¿É¼Ç
°´Ã¼ ±ÇÇÑÀ» ÁÖ´Â »ç¿ëÀÚ°¡ °°Àº
±ÇÇÑÀ» ´Ù¸¥ »ç¿ëÀÚ¿¡°Ô ºÎ¿© ÇÒ ¼ö ÀÖµµ·Ï ÇÔ
/* insu
»ç¿ëÀÚ´Â °°Àº ±ÇÇÑÀ» ´Ù¸¥ »ç¿ëÀÚ¿¡°Ô
ÁÙ ¼ö ÀÖ´Ù
*/
grant select on test
to insu with grant option;
񀀥
Á¦°Å
/*
insu¿¡°Ô
testÅ×À̺íÀÇ select
±ÇÇÑÀ» Á¦°ÅÇÔ
*/
revoke select on
test from insu;
**Å×½ºÆ®¸¦ Çغ¸±â À§ÇØ
jangis°èÁ¤À¸·Î
test¶ó´Â Å×À̺íÀ» ¸¸µé°í
insu¶ó´Â °èÁ¤À» ¸¸µé±â**
/* insu
°èÁ¤
»ý¼º
*/
1 create user
insu
2 identified by
dlstn12
3 default tablespace
users
4 temporary
tablespace temp
create table
test(ename varchar(10) not null) /*Å×À̺í
»ý¼º*/
insert into
test(ename) values('ÀåÀμö')
/*testÅ×ÀÌºí¿¡ µ¥ÀÌÅÍ
Ãß°¡*/
/*
insu°¡
jangisÀÇ test
Å×À̺íÀ» ÀÐÀ» ¼ö ÀÖµµ·Ï °´Ã¼ ºÎ¿©
*/
SQL> conn
jangis/dlstn12
Connected.
SQL> grant
select, insert on test to
insu;
Grant
succeeded.
SQL> conn
insu/dlstn12
Connected.
SQL> select *
from jangis.test;
ENAME
----------
ÀåÀμö
Emp, dept
Å×À̺í ÀÚµ¿À¸·Î ¸¸µé±â
¹Ì¸® ¸¸µé¾îÁ®
ÀÖ´Â sql¹®À»
½ÇÇàÇÔ
SQL>
@/home/student/demobld.sql
»çÀü °Ë»ö
1 select table_name
from dictionary
2 where table_name
like '%PRIVILEGE%';
/* sys, xdb¸¦ Á¦¿ÜÇÑ ³»¿ëÀ» º¸±â
*/
1 SELECT * FROM
TABLE_PRIVILEGES
2 WHERE NOT OWNER LIKE
'%SYS%'
3 AND NOT OWNER LIKE
'%XDB%'
1 SELECT * FROM
TABLE_PRIVILEGES
2 WHERE OWNER LIKE
'%JANGIS%' OR GRANTOR LIKE
'%JANGIS%'