SQL DCLシ壹Θ繝シ繧カ繝シ縲√Ο繝シ繝ォ縲∵ィゥ髯

繝ヲ繝シ繧カ繝シ縺ィ縺ッシ

Oracle繝繝シ繧ソ繝吶シ繧ケ縺ク繝ュ繧ー繧、繝ウ縺吶k繧「繧ォ繧ヲ繝ウ繝
繝サ 繝ヲ繝シ繧カ蜷
繝サ 繝代せ繝ッ繝シ繝
繝サ 陦ィ鬆伜沺
繝サ 陦ィ鬆伜沺繧オ繧、繧コ
繝サ 繝励Ο繝輔ぃ繧、繝ォ
繧定ィュ螳壹〒縺阪k縲

菴懈

CREATE USER (繝ヲ繝シ繧カ繝シ蜷) IDENTIFIED BY (繝代せ繝ッ繝シ繝)
繧ェ繝悶ず繧ァ繧ッ繝井ス懈仙郁。ィ鬆伜沺
[ DEFAULT TABLESPACE (繝繝輔か繝ォ繝郁。ィ鬆伜沺蜷) ]
窶サ逵∫払縺励◆蝣エ蜷医ヾYSTEM陦ィ鬆伜沺縺ォ菴懈舌&繧後k

菴ソ逕ィ縺吶k荳譎り。ィ鬆伜沺
[ TEMPORARY TABLESPACE (荳譎り。ィ鬆伜沺蜷) ]
窶サ逵∫払縺励◆蝣エ蜷医ヾYSTEM陦ィ鬆伜沺縺御スソ逕ィ縺輔l繧

陦ィ鬆伜沺蜑イ蠖灘宛髯
[ QUOTA (繧オ繧、繧コ) ON (陦ィ鬆伜沺蜷) ]
窶サ逵∫払縺励◆蝣エ蜷医∝牡繧雁ス薙※繧オ繧、繧コシ0シ昴Θ繝シ繧カ縺ッ繧ェ繝悶ず繧ァ繧ッ繝医r菴懈舌〒縺阪↑縺

[ PROFILE (繝励Ο繝輔ぃ繧、繝ォ蜷) ]
窶サ逵∫払縺励◆蝣エ蜷医.EFAULT 繝励Ο繝輔ぃ繧、繝ォ縺悟牡繧雁ス薙※繧峨l繧
;

繝繝輔か繝ォ繝医〒菴懈舌&繧後k繝ヲ繝シ繧カ繝シ

Oracle DBCA菴ソ逕ィ譁ケ豕/繝繝輔か繝ォ繝医〒菴懈舌&繧後k繝ヲ繝シ繧カ繝シ荳隕ァ縲榊盾辣ァ

蜑企勁

DROP USER (繝ヲ繝シ繧カ繝シ蜷)

繝ュ繧ー繧、繝ウ荳ュ縺ョ繝ヲ繝シ繧カ繝シ縺ッ蜑企勁縺ァ縺阪↑縺
繝ヲ繝シ繧カ繝シ縺梧園譛峨@縺ヲ縺繧(シ昴せ繧ュ繝シ繝槫縺ョ)繧ェ繝悶ず繧ァ繧ッ繝医b蜑企勁縺輔l繧

讓ゥ髯

繧ェ繝悶ず繧ァ繧ッ繝域ィゥ髯

莉悶せ繧ュ繝シ繝槭ョ繧ェ繝悶ず繧ァ繧ッ繝医↓蟇セ縺吶k謫堺ス懈ィゥ髯
莉悶せ繧ュ繝シ繝槭ョ謇譛芽縺後∝挨繧ケ繧ュ繝シ繝槭Θ繝シ繧カ繝シ縺ォ蟇セ縺励※莉倅ク

繧ェ繝悶ず繧ァ繧ッ繝域ィゥ髯仙錐 陦ィ 繝薙Η繝シ 鬆蠎 繝励Ο繧キ繝シ繧ク繝」 蜀螳ケ
SELECT
INSERT
UPDATE
DELETE
ALTER
INDEX
REFERENCES 蜿らァ謨エ蜷域ァ蛻カ邏繧剃ス懈
EXECUTE PL/SQL繝励Ο繧ー繝ゥ繝繧貞ョ溯。
READ
WRITE
繝繧」繝ャ繧ッ繝医Μ繧ェ繝悶ず繧ァ繧ッ繝

GRANT (讓ゥ髯仙錐) ON (繧ェ繝悶ず繧ァ繧ッ繝亥錐) TO 繝ヲ繝シ繧カ繝シ蜷/繝ュ繝シ繝ォ蜷

SQL> Grant Select, Insert, Update, Delete On tbl_test To user_test;
窶サ繝ヲ繝シ繧カ繝シシ嗽ser_test縺ォ蟇セ縺励※縲√ユ繝シ繝悶Νシ嗾bl_test縺ォ髢「縺吶kSELECTス槭ョ讓ゥ髯舌r莉倅ク

SQL> Grant Execute On func_test To user_test;
窶サ繝ヲ繝シ繧カ繝シシ嗽ser_test縺ォ蟇セ縺励※縲髢「謨ーシ喃unc_test縺ォ髢「縺吶k螳溯。梧ィゥ髯舌r莉倅ク

繧ェ繝悶ず繧ァ繧ッ繝(陦ィ繧繝薙Η繝シ)菴懈(CREATE)譎ゅッ縺昴ョ繧ェ繝悶ず繧ァ繧ッ繝医ョ讓ゥ髯舌′蜈ィ縺ヲ莉倅ク弱&繧後k

繧キ繧ケ繝繝讓ゥ髯

DBMS縺ォ蟇セ縺吶k謫堺ス懈ィゥ髯
DBA縺後Θ繝シ繧カ繝シ縺ォ讓ゥ髯舌r莉倅ク弱☆繧

繧キ繧ケ繝繝讓ゥ髯仙錐 CREATE ALTER DROP 隱ャ譏
ANY INDEX 莉サ諢上ョ繧ケ繧ュ繝シ繝槫縺ョ邏「蠑
ANY PROCEDURE 莉サ諢上ョ繧ケ繧ュ繝シ繝槫縺ョ繝励Ο繧キ繝シ繧ク繝」
ANY SEQUENCE 莉サ諢上ョ繧ケ繧ュ繝シ繝槫縺ョ鬆蠎
ANY SYNONYM 莉サ諢上ョ繧ケ繧ュ繝シ繝槫縺ョ蛻・蜷
ANY TABLE 莉サ諢上ョ繧ケ繧ュ繝シ繝槭ョ陦ィ
ANY TRIGGER 莉サ諢上ョ繧ケ繧ュ繝シ繝槫縺ョ繝医Μ繧ャ繝シ
ANY VIEW 莉サ諢上ョ繧ケ繧ュ繝シ繝槭ョ繝薙Η繝シ
CLUSTER 莉サ諢上ョ繧ケ繧ュ繝シ繝槫縺ョ繧ッ繝ゥ繧ケ繧ソ
DATABASE LINK 繝励Λ繧、繝吶シ繝医ョ繝シ繧ソ繝吶シ繧ケ繝ェ繝ウ繧ッ
PROCEDURE EXECUTE 閾ェ繧ケ繧ュ繝シ繝槫縺ョ繝励Ο繧キ繝シ繧ク繝」
PROFILE 繝励Ο繝輔ぃ繧、繝ォ
PUBLIC SYNONYM 繝代ヶ繝ェ繝繧ッ繧キ繝弱ル繝
ROLE 繝ュ繝シ繝ォ
SEQUENCE SELECT 閾ェ繧ケ繧ュ繝シ繝槫縺ョ鬆蠎
SYNONYM 閾ェ繧ケ繧ュ繝シ繝槫縺ョ蛻・蜷
PUBLIC SYNONYM 繝代ヶ繝ェ繝繧ッ繧キ繝弱ル繝
TABLE SELECT
INSERT
UPDATE
DELETE
LOCK
COMMENT
閾ェ繧ケ繧ュ繝シ繝槭ョ陦ィ
SELECTス曠ELETE 縺ッVIEW縺ォ繧よ怏蜉ケ
VIEW 閾ェ繧ケ繧ュ繝シ繝槭ョ繝薙Η繝シ
TABLESPACE 陦ィ鬆伜沺
TRIGGER 閾ェ繧ケ繧ュ繝シ繝槭ョ繝医Μ繧ャ繝シ
USER 繝ヲ繝シ繧カ
SESSION 繧サ繝繧キ繝ァ繝ウ縺ョ繝代Λ繝。繝シ繧ソ
ANALYZE ANY 陦ィ繝サ邏「蠑輔r蛻譫舌☆繧区ィゥ髯
AUDIT ANY 繧ケ繧ュ繝シ繝槭が繝悶ず繧ァ繧ッ繝医r逶」譟サ縺吶k讓ゥ髯
邂。逅繝ヲ繝シ繧カ繝シ
SYSDBA SYSOPER シ 繧ェ繝悶ず繧ァ繧ッ繝医ョ菴懈舌↑縺ゥ菴輔〒繧ょコ譚・繧区ィゥ髯
SYSOPER DB縺ョ襍キ蜍輔サ蛛懈ュ「縲ゅョ繝シ繧ソ繝繧」繧ッ繧キ繝ァ繝翫Μ縺ク縺ッ繧「繧ッ繧サ繧ケ荳榊庄

繝サINSERT ANY/SELECT ANY/UPDATE ANY/DELETE ANY
繝サCREATE/CREATE ANY/DROP
繝サROCK
繝サSYSDBA/SYSOPER 繝繝シ繧ソ繝吶シ繧ケ縺ョ襍キ蜍/蛛懈ュ「

GRANT (讓ゥ髯仙錐) TO 繝ヲ繝シ繧カ繝シ蜷/繝ュ繝シ繝ォ蜷

SQL> Grant Select Any Table To user_test;
窶サ繝ヲ繝シ繧カ繝シシ嗽ser_test縺ォ蟇セ縺励※縲∝ィ繝繝シ繝悶Ν縺ォ髢「縺吶kSELECT讓ゥ髯舌r莉倅ク

SQL> Grant Execute Any Procedure To user_test;
窶サ繝ヲ繝シ繧カ繝シシ嗽ser_test縺ォ蟇セ縺励※縲∝ィ髢「謨ー縺ョ螳溯。梧ィゥ髯舌r莉倅ク

讓ゥ髯仙炎髯、

REVOKE 讓ゥ髯 FROM 逶ク謇;

SQL> Revoke Select, Insert, Update, Delete On tbl_test To user_test;
窶サ繝ヲ繝シ繧カ繝シシ嗽ser_test縺ォ蟇セ縺励※縲√ユ繝シ繝悶Νシ嗾bl_test縺ォ髢「縺吶kSELECTス槭ョ讓ゥ髯舌r蜑企勁

繝輔Λ繝繧キ繝・繝舌ャ繧ッ陦ィ螳溯。梧ィゥ髯

繝サFLASHBACK TABLE繧キ繧ケ繝繝讓ゥ髯
縲or
繝サFLASHBACK ANY TABLE繧キ繧ケ繝繝讓ゥ髯
繝サ蟇セ辣ァ縺ョ陦ィ縺ク縺ョSELECT縲、LTER縲!NSERT縲.ELETE繧ェ繝悶ず繧ァ繧ッ繝域ィゥ髯

蠖ケ蜑イ

讓ゥ髯舌r繧ー繝ォ繝シ繝怜喧縺励◆繧ゅョ
繝ュ繝シ繝ォ縺ォ蜑イ繧雁ス薙※縺滓ィゥ髯舌ッPL/SQL螳溯。梧凾縺ォ縺ッ驕ゥ逕ィ縺輔l縺ェ縺
ROLE
繝ヲ繝シ繧カ繝シ縺ォ蟇セ縺励※讓ゥ髯舌ョ髮蜷医r莉倅ク
OS縺ョ繧「繧ォ繧ヲ繝ウ繝医げ繝ォ繝シ繝
繝ヲ繝シ繧カ繝シ縺ョ髮蜷医↓蟇セ縺励※讓ゥ髯舌r莉倅ク

螳夂セゥ貂医∩繝ュ繝シ繝ォ

繝ュ繝シ繝ォ蜷 隱ャ譏 讓ゥ髯
CONNECT 荳闊ャ繝ヲ繝シ繧カ逕ィ
DB謗・邯壹∬。ィ繧繝薙Η繝シ縺ョ菴懈千ュ
繝サCREATE SESSION
RESOURCE 髢狗匱閠逕ィ
繝励Ο繧キ繝シ繧ク繝」繧繝医Μ繧ャ縺ョ菴懈千ュ
CONNECT繝ュ繝シ繝ォ繧ょ挨騾泌ソ隕
繝サCREATE CLUSTER
繝サCRETAE INDEX TYPE
繝サCREATE SEQUENCE
繝サCREATE PROCEDURE
繝サCREATE TRIGGER
繝サCREATE TABLE
DBA 邂。逅閠逕ィ
ADMIN OPTION莉倥″縺ョ蜈ィ縺ヲ縺ョ繧キ繧ケ繝繝讓ゥ髯
EXP_FULL_DATABASE 蜈ィ繝繝シ繧ソ繝吶シ繧ケ繧ィ繧ッ繧ケ繝昴シ繝亥ョ溯。梧ィゥ髯
IMP_FULL_DATABASE 蜈ィ繝繝シ繧ソ繝吶シ繧ケ繧、繝ウ繝昴シ繝亥ョ溯。梧ィゥ髯
RECOVERY_CATALOG_OWNER 繝ェ繧ォ繝舌Μ繧ォ繧ソ繝ュ繧ー縺ョ繝。繝ウ繝繝翫Φ繧ケ縺後〒縺阪k讓ゥ髯
CONNECT縲ヽESOURCE繝ュ繝シ繝ォ繧ょ挨騾泌ソ隕
MGMT_USER EM蛻ゥ逕ィ
螳夂セゥ

Create Role role_test;
窶サ繝ュ繝シ繝ォシ嗷ole_test菴懈(讓ゥ髯千┌縺)
Grant Select, Insert, Update, Delete On tbl_test To role_test;
窶サ繝ュ繝シ繝ォシ嗷ole_test縺ォ蟇セ縺励※縲√ユ繝シ繝悶Νシ嗾bl_test縺ォ髢「縺吶kSELECTス槭ョ讓ゥ髯舌r莉倅ク
Grant role_test To user_test;
窶サ繝ヲ繝シ繧カ繝シシ嗽ser_test縺ォ蟇セ縺励※縲√Ο繝シ繝ォシ嗷ole_test縺ョ讓ゥ髯舌r莉倅ク
Grant (讓ゥ髯) To (繝ヲ繝シ繧カ繝シ/繝ュ繝シ繝ォ) WITH GRANT OPTION
繧ェ繝悶ず繧ァ繧ッ繝域ィゥ髯舌ョ莉倅ク
莉倅ク弱&繧後◆讓ゥ髯舌r莉倅ク弱〒縺阪k讓ゥ髯舌ョ莉倅ク
Grant (讓ゥ髯) To (繝ヲ繝シ繧カ繝シ/繝ュ繝シ繝ォ) WITH ADMIN OPTION
繧キ繧ケ繝繝讓ゥ髯舌ョ莉倅ク
莉倅ク弱&繧後◆讓ゥ髯舌r莉倅ク弱〒縺阪k讓ゥ髯舌ョ莉倅ク

Follow me!