Äú¿ÉÒÔ¾èÖú£¬Ö§³ÖÎÒÃǵĹ«ÒæÊÂÒµ¡£

1Ôª 10Ôª 50Ôª





ÈÏÖ¤Â룺  ÑéÖ¤Âë,¿´²»Çå³þ?Çëµã»÷Ë¢ÐÂÑéÖ¤Âë ±ØÌî



  ÇóÖª ÎÄÕ ÎÄ¿â Lib ÊÓÆµ iPerson ¿Î³Ì ÈÏÖ¤ ×Éѯ ¹¤¾ß ½²×ù Model Center   Code  
»áÔ±   
   
 
     
   
 ¶©ÔÄ
  ¾èÖú
OracleÊý¾Ý¿â£¨È¨ÏÞ¡¢Óû§¡¢½ÇÉ«¡¢»ù±¾ÖªÊ¶½éÉÜ£©
 
  8552  次浏览      28
 2018-7-6 
 
±à¼­ÍƼö:
±¾ÎÄÀ´×ÔÓÚcsdn£¬½éÉÜÁËÊý¾Ý¿â³£¼û·ÖÀ࣬¹ØÏµÐÍÊý¾Ý¿â¸ÅÄOracle Êý¾Ý¿â»ù±¾ÖªÊ¶½éÉÜ£¬OracleÊý¾Ý¿âȨÏÞ¡£

¹ØÓÚÓû§Óëģʽ:

Óû§£º¶ÔÊý¾Ý¿âµÄ·ÃÎÊ£¬ÐèÒªÒÔÊʵ±Óû§Éí·Ýͨ¹ýÑéÖ¤£¬²¢¾ßÓÐÏà¹ØÈ¨ÏÞÀ´Íê³ÉһϵÁж¯×÷.SYSÓû§£¬È±Ê¡Ê¼ÖÕ´´½¨£¬ÇÒδ±»Ëø¶¨£¬ÓµÓÐÊý¾Ý×ֵ估Æä¹ØÁªµÄËùÓжÔÏó.SYSTEMÓû§£¬È±Ê¡Ê¼ÖÕ´´½¨£¬ÇÒδ±»Ëø¶¨£¬¿ÉÒÔ·ÃÎÊÊý¾Ý¿âÄÚµÄËùÓжÔÏó

ģʽ(schema)£ºÊÇij¸öÓû§ÓµÓÐËùÓжÔÏóµÄ¼¯ºÏ¡£¾ßÓд´½¨¶ÔÏóȨÏÞ²¢´´½¨Á˶ÔÏóµÄÓû§³ÆÎªÓµÓÐij¸öģʽ

×¢Ò⣺´´½¨Êý¾Ý¿â¶ÔÏó(ÊÓͼ,±íµÈ)µÄÈÎÒ»Óû§¶¼ÓµÓÐÒ»¸öÒÔ¸ÃÓû§Ãû³Æ¿ªÍ·µÄģʽ£¬ÇÒ±»ÊÓΪģʽÓû§

Ò»¡¢Êý¾Ý¿â»ù±¾ÖªÊ¶

СÐÍÊý¾Ý¿â£ºÃ»Óа²È«ÐÔ
Access
ÖÐÐÍÊý¾Ý¿â£ºÓÐÒ»¶¨µÄ°²È«ÐÔ
SQL Server
MySQL
´óÐÍÊý¾Ý¿â£º°²È«ÐÔ×î¸ß
Oracle£ºÐÔÄܸߣ¬Êг¡ÐèÇóÁ¿´ó
DB2
SYSBASE

£¨¹ØÏµÐÍ£©Êý¾Ý¿âÏà¹Ø¸ÅÄî

Êý¾Ý¿â Database DB ´æ´¢Êý¾ÝµÄ²Ö¿â
Êý¾Ý¿â¹ÜÀíϵͳ Database Management System DBMS
ÓÃÓÚ¹ÜÀíÊý¾Ý¿âµÄÈí¼þ
¸ºÔðÊý¾ÝµÄ¼ìË÷¡¢´æ´¢¡¢°²È«¡¢Ò»ÖÂÐÔ¿ØÖƵÈ
¶ÔÒÑÓеÄÊý¾Ý½øÐд¦Àí£¬µÃµ½ÐµÄÓÐÓõÄÊý¾Ý
¹ØÏµÐÍÊý¾Ý¿â
E.F.Codde ¹ØÓÚ¹ØÏµÄ£Ð͵ÄÂÛÎÄ
½¨Á¢ÔÚ¹ØÏµÄ£ÐÍ»ù´¡ÉϵÄÊý¾Ý¿â
ÓµÓÐÒ»×éÓÐÃèÊöµÄ±í¸ñ£¬ÕâЩ±í¸ñµÄ×÷ÓÃÊÇ×°ÔØÊý¾Ý
±í¸ñ
ÐУºÒ»Ìõ¼Ç¼£¬Ò»¸öʵÌ壬¾ßÌå´æÔÚµÄÊÂÎJava ÖеÄÒ»¸ö¶ÔÏó£©
ÁУº×ֶΡ¢ÊôÐÔ£¬ÓÃÓÚÃèÊöÊÂÎïµÄijһ¸ö·½Ã棨JavaÖжÔÏóµÄÊôÐÔ£©
ʵÌ弯£ºËùÓÐʵÌåµÄ¼¯ºÏ
¹ØÏµ£ºÊµÌå¼äµÄ¹ØÏµ

SQL£¨Structured Query Language£©

½á¹¹»¯²éѯÓïÑÔ
ÊÇÒ»ÖÖÓïÑÔ
ÊÇÒ»Öֹ淶 ANSI SQL-82 SQL-86 SQL-92 SQL-99
ËùÓÐÖ÷Á÷Êý¾Ý¿â¶¼×ñÊØ
¹ØÏµ£ºÓз½Ïò
1£º1 Ò»¶ÔÒ»
¶ÔÓÚʵÌ弯AÖеÄÿ¸öʵÌ壬ÔÚʵÌ弯BÖÐ×î¶àÖ»ÓÐÒ»¸öʵÌåÓëÖ®¶ÔÓ¦;·´Ö®£¬ÒàÈ»¡£
1£ºn Ò»¶Ô¶à£ºÍâ¼ü×ÜÉèÔÚ¶à·½
¶ÔÓÚʵÌ弯AÖеÄÿ¸öʵÌ壬ÔÚʵÌ弯BÖпÉÒÔÓжà¸öʵÌåÓëÖ®¶ÔÓ¦¡£
n£ºn ¶à¶Ô¶à£º·Ö½â³ÉÁ½¸öÒ»¶Ô¶à£¬Ìí¼ÓÒ»¸öÖмä±í
¶ÔÓÚʵÌ弯AÖеÄÿ¸öʵÌ壬ÔÚʵÌ弯BÖпÉÒÔÓжà¸öʵÌåÓëÖ®¶ÔÓ¦;·´Ö®£¬ÒàÈ»¡£
Êý¾Ý¿âÉè¼Æ£ºÂß¼­Ä£ÐÍ
E-Rͼ£¨Entity-Relationͼ£©

Èý´ó·¶Ê½£¨¹æ·¶£©£º

1.Ò»ÕÅ±í±ØÐëÒªÓÐÒ»¸öÖ÷¼ü£¬×îºÃÑ¡ÔñÓëÒµÎñÎ޹صÄÂß¼­Ö÷¼ü;
Âß¼­Ö÷¼ü£¬ÒµÎñÖ÷¼ü
2.Íâ¼üÒýÓÃÖ÷¼ü
Âß¼­Íâ¼ü
3.²»Òª³öÏÖÈßÓà×Ö¶Î
ʵ¼Ê¿ª·¢ÖУ¬ÓÐʱºò²¢²»×ñÊØ£¬Ò»¶¨ÒªÈßÓ࣬ÒÔ±£Ö¤Êý¾ÝµÄÕýÈ·ÐÔ£¬Ìá¸ß²éѯЧÂÊ£¬¿Õ¼ä»»Ê±¼ä;

¹ØÓÚOracleÊý¾Ý¿â¼òµ¥½éÉÜ

1970 Oracle5
×îРOracle12c
9i 10g С Ö§³Ö×îеÄSQL¹æ·¶SQL-99
°²×°ÒªÇó£º
Äڴ棺100M+ Õý³£ÔËÐÐËùÐè×îСÄÚ´æ £¨3G+ µçÄÔ²»¿¨£©
Ó²ÅÌ£º1.5+G

OracleÊý¾Ý¿âÏà¹Ø¸ÅÄî

Êý¾Ý¿â£º´æ´¢Êý¾ÝµÄ²Ö¿â£¬Ö÷ÒªÖ¸´æ·ÅÊý¾ÝµÄÎļþ£¬°üÀ¨Êý¾ÝÎļþ¡¢¿ØÖÆÎļþ¡¢Êý¾Ý¿âÈÕÖ¾Îļþ
Êý¾ÝÃû£ºÓÃÓÚΨһµÄ±êʶÊý¾Ý¿â
ʵÀý£ºÒ»ÏµÁÐΪµ±Ç°Êý¾Ý¿âÌṩ·þÎñµÄÄÚ´æºÍºǫ́½ø³Ì£¬ÄÚ´æÓë·þÎñ½ø³ÌµÄ¼¯ºÏ³ÆÎªÒ»¸öʵÀý
ʵÀýÃû/SID/·þÎñÃû£ºÓÃÓÚΨһµÄ±êʶһ¸öʵÀý
ʵÀýÃûÓëÊý¾Ý¿âÃû¿ÉÒÔ²»Ò»ÖÂ

Oracle·þÎñ

OracleService+SID£¬Êý¾Ý¿âÆô¶¯µÄ»ù´¡·þÎñ
OracleOraDb10g_home1TNSListner£¬Îª¿Í»§¶ËÌṩ¼àÌý·þÎñ£¬ÌṩÁ¬½Ó·þÎñ
OracleOraDb10g_home1iSQL*Plus£¬Èÿͻ§¶Ë¿ÉÒÔͨ¹ýä¯ÀÀÆ÷À´¶ÔÊý¾Ý¿â½øÐвÙ×÷

Êý¾Ý¿âµÄÆô¶¯ºÍ¹Ø±Õ

µÇ¼Óû§±ØÐëÊǹÜÀíÔ±Óû§
Æô¶¯£ºstartup open
¹Ø±Õ£ºshutdown immediate
Oracle¿Í»§¶Ë¹¤¾ß
SQL*Plus
ÈýÖÖÆô¶¯·½Ê½
µÚÒ»ÖÖ¡¢¿ªÊ¼ ¡ú ³ÌÐò ¡ú Oracle ¡ú Ó¦Óÿª·¢¹¤¾ß ¡ú SQL*Plus
µÚ¶þÖÖ¡¢ÃüÁîÐд°¿Ú ¡ú sqlplus
µÚÈýÖÖ¡¢ä¯ÀÀÆ÷ ¡ú http://ip:5561/isqlplus
µÚËÄÖÖ¡¢±¾·½·¨Ç°ÌáÊÇOracleÎļþ·ÅÔÚÄÜÔÚlinuxÖÕ¶ËÕÒµ½µÄλÖã¬ÀûÓÃlinuxÃüÁîÔËÐÐOracle¿Í»§¶Ë£¬Ò»°ãÇé¿ö϶¼²»»áÔËÓô˷½Ê½À´´ò¿ªOracle¿Í»§¶Ë£¬½¨ÒéʹÓõÚÒ»ÖÖ¡¢µÚ¶þÖÖÆô¶¯·½Ê½¡£
SqlDeveloper£¨linuxϵͳÉÏÐÞ¸ÄÎļþµÄ¶ÁȡȨÏÞ£©
ÖÕ¶Ë ¡ú cd sqldevelper ¡ú chmod 777 *.sh
¡ú . /sqldeveloper.sh | bash sqldeveloper.sh | sh sqldeveloper.sh

¶þ¡¢¼àÌýÆ÷

¼àÌý¿Í»§¶ËÁ¬½ÓÇëÇó,λÓÚ·þÎñ¶ËµÄ¡¢¶ÀÁ¢ÔËÐеÄÒ»¸öºǫ́½ø³Ì,Ò»µ©½¨Á¢·þÎñÆ÷Óë¿Í»§¶ËµÄÁ¬½Ó£¬Ö®ºó¿Í»§¶ËÓë·þÎñµÄͨÐŲ»ÔÙͨ¹ýËü

ÃüÁîÐд°¿Ú ¡ú sqlplus£ºÆô¶¯Ò»¸öSQL*Plus¿Í»§¶Ë¹¤¾ß

»ò ¡ú sqlplus username/password [as sysdba]

»ò ¡ú sqlplus /nolog£º´ò¿ªSQL*Plus,µ«²»µÇ¼

¡ú conn username/password@192.168.7.58:1521/orcl

¡ú disconnect ¶Ï¿ªÁ¬½Ó

spool ÃüÁîҪдµ½µÄÎļþµÄ·¾¶£»×¢Ò⣺µ±Ê¹ÓÃÍê±ÏºóҪʹÓÃspool off£»

conn /as sysdba ²Ù×÷ϵͳÑéÖ¤

Oracle µÇ¼ÑéÖ¤£º²Ù×÷ϵͳ¡¢Êý¾Ý¿âÑéÖ¤¡¢ÃÜÂëÎļþ

Èý¡¢È¨ÏÞ

OracleÔÚ¸Õ¿ªÊ¼µÄÈëÃÅ֪ʶ·½ÃæºÍmysqlÔÚȨÏÞ·½ÃæÓÐ׎ϴóµÄ²»Í¬£¬ÔÚOracleÖм¸ºõËùÓеIJÙ×÷£¨´´½¨Óû§¡¢´´½¨±í¡¢É¾³ý±íµÈµÈһЩÔöɾ¸Ä²éÔÚûÓлñµÃȨÏÞ֮ǰÊDz»ÔÊÐíÖ´Ðеģ©ÔÚûÓлñµÃȨÏÞ֮ǰ¶¼²»Äܹ»Ö´ÐУ¬ÔÚmysqlÖÐÔòûÓÐÄÇôÑϸñ£¬ËùÒÔÓбØÒªÏÈÁ˽âÒ»ÏÂOracleÖÐȨÏ޵ķÖÅä¡£

ÔÚ´´½¨Óû§Ö®Ç°£¬¿ÉÒԵǼϵͳ¹ÜÀíԱȨÏÞÈ¥´´½¨Ò»¸öÓû§£¨ÒòΪϵͳ¹ÜÀíÔ±ÓÐÕâ¸öȨÏÞ£©£¬ÏÂÃæÊÇÈýÖÖÓû§·ÖÀࣺ

sys£» //ϵͳ¹ÜÀíÔ±£¬ÓµÓÐ×î¸ßȨÏÞ
system£»//±¾µØ¹ÜÀíÔ±£¬´Î¸ßȨÏÞ
scott£» //ÆÕͨÓû§£¬ÃÜÂëĬÈÏΪtiger,ĬÈÏδ½âËø

ÔڵǽµÄʱºò£¬¿ÉÒÔÑ¡ÔñÏÂÃæÈýÖֵǼ·½Ê½£º

sqlplus / as sysdba£» //µÇ½sysÕÊ»§
sqlplus sys as sysdba£»//ͬÉÏ
sqlplus scott/tiger£» //µÇ½ÆÕͨÓû§scott

±ÈÈç´´½¨Ò»¸öÓû§ºÍ¸ø´´½¨µÄÓû§·ÖÅäȨÏÞ´úÂëʾÀý£º£¨ÏÂÃæ´´½¨Óû§ÊÇÔÚϵͳ¹ÜÀíÔ±µÄÉí·ÝÏ´´½¨µÄ£¬²¢ÇÒϵͳ¹ÜÀíÔ±±¾ÉíÒ²ÓÐÕâ¸öȨÏÞ£¬µ«ÊÇÈç¹ûûÓи³ÓèÆÕͨÓû§´´½¨Óû§µÄȨÏÞ£¬ÄÇôÆÕͨÓû§´´½¨Óû§Õâ¸ö²Ù×÷¾Í»áʧ°Ü¡££©

3.1¡¢ÔÚOracleÖд´½¨Ò»¸öÐÂÓû§

(Óû§Ãû£ºzhangsan ÃÜÂ룺zhangsan£¬ ĬÈϵǽÊÇÔÚusers±íÖУ¬²¢·ÖÅä10MµÄ¿Õ¼ä)

¡¤ÏÈÓ󬼶¹ÜÀíÔ±Á¬ÉÏ£ºsqlplus
¡¤ÊäÈëÓû§Ãû¼°ÃÜÂ룺scott/tiger
¡¤Á¬½Ó³É¹¦ºó£¬Ê¹Óãºconn sys /sysdba as sysdba;µÇÈ볬¼¶¹ÜÀíÔ±ºó£¬ÊäÈëÈçÏÂÃüÁ
create user zhangsan identified by zhangsan default tablespace users quota 10M on users
·ÖÅäȨÏÞ¸øÐÂÓû§zhangsan
grant create session, create table, create view to zhangsan

ÏÂÃæÓï¾ä¿ÉÒÔÔÚsysϵͳ¹ÜÀíÔ±µÇ¼µÄÇé¿öÏ´´½¨µÄ

create user Óû§Ãû identified by ÃÜÂë; //ÔÚ¹ÜÀíÔ±ÕÊ»§Ï£¬´´½¨Óû§
alert user scott identified by tiger; //ÐÞ¸ÄÃÜÂë

ϵͳȨÏÞ: ÔÊÐíÓû§Ö´ÐÐÌØ¶¨µÄÊý¾Ý¿â¶¯×÷£¬Èç´´½¨±í¡¢´´½¨Ë÷Òý¡¢Á¬½ÓʵÀýµÈ

¶ÔÏóȨÏÞ: ÔÊÐíÓû§²Ù×ÝÒ»Ð©ÌØ¶¨µÄ¶ÔÏó£¬Èç¶ÁÈ¡ÊÓͼ£¬¿É¸üÐÂijЩÁС¢Ö´Ðд洢¹ý³ÌµÈ

3.2ϵͳȨÏ޺ͶÔÏóȨÏÞ:

£¨1£©ÏµÍ³È¨ÏÞ£º

grant ȨÏÞÁÐ±í£¬.. to username [with admin option ͬʱ»ñµÃȨÏÞ·ÖÅäȨ];
revoke ȨÏÞÁÐ±í£¬.. from usernam;
a.³£¼ûµÄϵͳȨÏÞ
CREATE SESSION ´´½¨»á»°
CREATE SEQUENCE ´´½¨ÐòÁÐ
CREATE SYNONYM ´´½¨Í¬Ãû¶ÔÏó
CREATE TABLE ÔÚÓû§Ä£Ê½Öд´½¨±í
CREATE ANY TABLE ÔÚÈκÎģʽÖд´½¨±í
DROP TABLE ÔÚÓû§Ä£Ê½ÖÐɾ³ý±í
DROP ANY TABLE ÔÚÈκÎģʽÖÐɾ³ý±í
CREATE PROCEDURE ´´½¨´æ´¢¹ý³Ì
EXECUTE ANY PROCEDURE Ö´ÐÐÈκÎģʽµÄ´æ´¢¹ý³Ì
CREATE USER ´´½¨Óû§
DROP USER ɾ³ýÓû§
b.ÊÚÓèÓû§ÏµÍ³È¨ÏÞ
GRANT privilege [, privilege...] TO user [, user| role , PUBLIC ...]
[WITH ADMIN OPTION];
PUBLIC ËùÓÐÓû§
WITH ADMIN OPTION ʹÓû§Í¬Ñù¾ßÓзÖÅäȨÏÞµÄȨÀû£¬¿É½«´ËȨÏÞÊÚÓè±ðÈË
c.ʹÓÃϵͳȨÏÞ
--ʹÓÃrobinson¾ßÓд´½¨»á»°¡¢´´½¨±í
SQL > CREATE TABLE tb1 AS SELECT * FROM USER_TABLES; --ÏÂÃæÌáʾûÓÐȨÏÞÔÚ users±í¿Õ¼ä´´½¨¶ÔÏó
CREATE TABLE tb1 AS SELECT * FROM USER_ TABLES
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'
SQL > CONN sys as sysdba; --ʹÓÃsys ÕÊ»§µÇ½²¢Îªrobinson ÔÚusers ±í¿Õ¼äÖ¸¶¨Åä¶îºó¿ÉÒÔ´´½¨±ítb1
Enter password:
Connected .
SQL > ALTER USER robinson QUOTA 10M ON USERS;
User altered.
SQL > CONN robinson /lion;
Connected.
SQL > CREATE TABLE tb1 AS SELECT * FROM USER_TABLES;
Table created.
d.²é¿´ÏµÍ³È¨ÏÞ
dba_ sys_ privs --Õë¶ÔËùÓÐÓû§±»ÊÚÓèµÄϵͳȨÏÞ
user_ sys_ privs --Õë¶Ôµ±Ç°µÇ½Óû§±»ÊÚÓèµÄϵͳȨÏÞ
e.»ØÊÕϵͳȨÏÞ
REVOKE {privilege | role} FROM {user_name | role_name | PUBLIC}
--ÏÂÃæµÄʾÀýÖв¢Ã»ÓлØÊÕµôÔ­À´ÓÉscottÊÚÓè¸ø robisnon EXECUTE ANY PROCEDURE µÄȨÏÞ
SQL > REVOKE EXECUTE ANY PROCEDURE FROM scott;
Revoke succeeded.
SQL > select grantee,privilege,admin_ option from dba_sys_privs
2 where grantee in ('SCOTT','ROBINSON') and privilege = 'EXECUTE ANY PROCEDURE'
3 order by grantee;
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
ROBINSON EXECUTE ANY PROCEDURE NO
×¢Ò⣺¶ÔÓÚʹÓà with admin option Ϊij¸öÓû§ÊÚÓèϵͳȨÏÞ £¬ÄÇô¶ÔÓÚ±»Õâ¸öÓû§ÊÚÓèÏàͬȨÏÞµÄËùÓÐ
Óû§À´Ëµ £¬È¡Ïû¸ÃÓû§µÄϵͳȨÏÞ²¢²»»á¼¶ÁªÈ¡ÏûÕâЩÓû§µÄÏàͬȨÏÞ

£¨2£©¶ÔÏóȨÏÞ

grant ȨÏÞ on ¶ÔÏóÃû to username[with grant option ͬʱ»ñµÃȨÏÞ·ÖÅäȨ];

revoke ȨÏÞ on ¶ÔÏóÃû from username;

²»Í¬µÄ¶ÔÏó¾ßÓв»Í¬µÄ¶ÔÏóȨÏÞ,¶ÔÏóµÄÓµÓÐÕßÓµÓÐËùÓÐȨÏÞ,¶ÔÏóµÄÓµÓÐÕß¿ÉÒÔÏòÍâ·ÖÅäȨÏÞ.¹ØÓÚÊÚÓèϵͳȨÏÞÓëÊÚÓè¶ÔÏóȨÏÞµÄÓï·¨²îÒ죺ÊÚÓè¶ÔÏóȨÏÞʱÐèÒªÖ¸¶¨¹Ø¼ü×ÖON£¬´Ó¶øÄܹ»È·¶¨È¨ÏÞËùÓ¦ÓõĶÔÏó.¶ÔÓÚ±íºÍÊÓͼ¿ÉÒÔÖ¸¶¨Ìض¨µÄÁÐÀ´ÊÚȨ¡£

¶ÔÏóÊÚȨʾÀý

SQL> SHOW USER;
USER is "SCOTT"
SQL> GRANT SELECT ON emp TO robinson;
Grant succeeded.
SQL> GRANT UPDATE(sal,mgr) ON emp TO robinson WITH GRANT OPTION;
Grant succeeded.
--д´½¨Ò»¸öÓû§john£¬Ê¹ÓÃrobinsonÕË»§ÊÚÓè¸üÐÂscott.emp(sal,mgr)µÄȨÏÞ
SQL> CREATE USER john IDENTIFIED BY john;
User created.
SQL> GRANT CREATE SESSION TO john;
Grant succeeded.
SQL> CONN ROBINSON/LION
Connected.
SQL> GRANT UPDATE(sal,mgr) ON scott.emp TO john; --ÊÚÓèscott.emp(sal,mgr)µÄ¸üÐÂȨÏÞ
Grant succeeded.
SQL> UPDATE scott.emp SET sal = sal + 100 WHERE ename = 'SCOTT'; --³É¹¦¸üÐÂ
1 row updated.
--ÏòÊý¾Ý¿âÖÐËùÓÐÓû§·ÖÅäȨÏÞ
SQL> GRANT SELECT ON dept TO PUBLIC£»
Grant succeeded.
c.²éѯȨÏÞ·ÖÅäÇé¿ö
Êý¾Ý×ÖµäÊÓͼ ÃèÊö
ROLE_SYS_PRIVS ½ÇɫӵÓеÄϵͳȨÏÞ
ROLE_TAB_PRIVS ½ÇɫӵÓеĶÔÏóȨÏÞ
USER_TAB_PRIVS_MADE ²éѯÊÚ³öÈ¥µÄ¶ÔÏóȨÏÞ(ͨ³£ÊÇÊôÖ÷×Ô¼º²é£©
USER_TAB_PRIVS_RECD Óû§ÓµÓеĶÔÏóȨÏÞ
USER_COL_PRIVS_MADE Óû§·ÖÅä³öÈ¥µÄÁеĶÔÏóȨÏÞ
USER_COL_PRIVS_RECD Óû§ÓµÓеĹØÓÚÁеĶÔÏóȨÏÞ
USER_SYS_PRIVS Óû§ÓµÓеÄϵͳȨÏÞ
USER_TAB_PRIVS Óû§ÓµÓеĶÔÏóȨÏÞ
USER_ROLE_PRIVS Óû§ÓµÓеĽÇÉ«
-²éѯÒÑÊÚÓèµÄ¶ÔÏóȨÏÞ(¼´Ä³¸öÓû§¶ÔÄÄЩ±í¶ÔÄÄЩÓû§¿ª·ÅÁ˶ÔÏóȨÏÞ)
SQL> SELECT * FROM user_tab_privs_made; --ÏÂÃæÊÇscottÓû§¿ª·ÅµÄ¶ÔÏóȨÏÞ
GRANTEE TABLE_NAME GRANTOR PRIVILEGE GRA HIE
-------------------- ------------------------ ------------------------- -------------------- --- ---
PUBLIC DEPT SCOTT SELECT NO NO
ROBINSON EMP SCOTT SELECT NO NO
--²éѯÁÐÉÏ¿ª·ÅµÄ¶ÔÏóȨÏÞ
SQL> SELECT * FROM user_col_privs_made;
GRANTEE TABLE_NAME COLUMN_NAME GRANTOR PRIVILEGE GRA
-------------------- -------------------- --------------------- -------------- -------------------- ---
ROBINSON EMP SAL SCOTT UPDATE YES
JOHN EMP MGR ROBINSON UPDATE NO
ROBINSON EMP MGR SCOTT UPDATE YES
JOHN EMP SAL ROBINSON UPDATE NO
--²éѯÒѽÓÊܵĶÔÏóÌØÈ¨(¼´Ä³¸öÓû§±»ÊÚÓèÁËÄÄЩ±íÉϵÄÄÄЩ¶ÔÏóÌØÈ¨)
SQL> SELECT * FROM user_tab_privs_recd;
OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE
-------------------- -------------------- ------------------------------ -------------------- --- ---
SCOTT EMP SCOTT SELECT NO NO
--²éѯÓû§ÒѽÓÊÜÁеĶÔÏóȨÏÞ
SQL> SELECT * FROM user_col_privs_recd;
OWNER TABLE_NAME COLUMN_NAME GRANTOR PRIVILEGE GRA
-------------------- ------------------ ---------------- -------------------- -------------------- ---
SCOTT EMP MGR SCOTT UPDATE YES
SCOTT EMP SAL SCOTT UPDATE YES
d.ÊջضÔÏóȨÏÞ
ʹÓÃREVOKE Óï¾äÊÕ»ØÈ¨ÏÞ
ʹÓÃWITH GRANT OPTION ×Ó¾äËù·ÖÅäµÄȨÏÞͬÑù±»ÊÕ»Ø
REVOKE {privilege [, privilege...]|ALL}
ON object
FROM {user[, user...]|role|PUBLIC}
[CASCADE CONSTRAINTS];
CASCADE CONSTRAINTS Ϊ´¦ÀíÒýÓÃÍêÕûÐÔʱÐèÒª
--ÊÕ»ØÈ¨ÏÞʾÀý
SQL> conn scott/tiger;
Connected.
SQL> REVOKE SELECT ON emp FROM robinson;
Revoke succeeded.
SQL> REVOKE UPDATE(sal,mgr) ON emp FROM robinson; --×¢Òâ´Ë´¦µÄÌáʾrevokeµÄÊÇÕû¸ö±í£¬¶ø·ÇÁÐ
REVOKE UPDATE(sal,mgr) ON emp FROM robinson
*
ERROR at line 1:
ORA-01750: UPDATE/REFERENCES may only be REVOKEd from the whole table, not by column
SQL> REVOKE UPDATE ON emp FROM robinson;
Revoke succeeded.
--Óû§robinsonµÄupdate ȨÏÞ±»revoke,Ôø¼¶Áª¸³ÓèjohnµÄȨÏÞÒ²±»Êջأ¬
--ÈçÏÂÌáʾ±í¡¢ÊÓͼ²»´æÔÚ£¬user_col_privs_recdÖÐÎ޼Ǽ
SQL> CONN john/john;
Connected.
SQL> UPDATE scott.emp SET sal = sal - 100 WHERE ename = 'SCOTT';
UPDATE scott.emp SET sal = sal - 100 WHERE ename = ' SCOTT '
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> SELECT * FROM user_col_privs_recd;
no rows selected
×¢Ò⣺Èç¹ûÈ¡Ïûij¸öÓû§µÄ¶ÔÏóȨÏÞ£¬¶ÔÓÚ¸ÃÓû§Ê¹ÓÃwith grant optionÊÚÓèÆäËüÓû§ÏàͬȨÏÞÀ´Ëµ£¬½«¼¶ÁªÉ¾³ýÕâЩÓû§È¨ÏÞ
e.ÆäËü
¼ì²éDBAȨÏÞµÄÓû§
select * from dba_role_ privs where granted_ role = ' DBA ';
²é¿´Óû§¾ßÓеÄϵͳȨÏÞ £º
SELECT * FROM session_ privs;

ÔÚOracleȨÏÞÖ®¼ä´æÔÚ´«µÝÐÔ£º
¼´Óû§A½«È¨ÏÞÊÚÓèB£¬B¿ÉÒÔ½«²Ù×÷µÄȨÏÞÔÙÊÚÓèC £¬ÃüÁîÈçÏ£º
grant alert table on tablename to zhangsan with admin option£»//¹Ø¼ü×Ö with admin optionͬʱ»ñµÃȨÏÞ·ÖÅäȨ
grant alert table on tablename to zhangsan with grant option£»//¹Ø¼ü×Ö with grant optionЧ¹ûºÍadminÀàËÆ
¼´with admin option ÔÊÐíµ±Ç°Óû§A½«È¨ÏÞ¸³ÓèB¡£

3.3ȨÏÞ×ܽá

1.ʹÓÃcreate userÓï¾ä´´½¨Óû§£¬alter userÓï¾äÐÞ¸ÄÓû§£¬ÆäÓï·¨´óÖÂÏàͬ
drop user username [CASCADE] »áɾ³ýÓû§ËùÓµÓеÄËùÓжÔÏó¼°Êý¾Ý
2.ϵͳȨÏÞÔÊÐíÓû§ÔÚÊý¾Ý¿âÖÐÖ´ÐÐÌØ¶¨µÄ²Ù×÷£¬ÈçÖ´ÐÐDDLÓï¾ä¡£
with admin option ʹµÃ¸ÃÓû§¾ßÓн«×ÔÉí»ñµÃµÄȨÏÞÊÚÓèÆäËüÓû§µÄ¹¦ÄÜ
µ«ÊÕ»ØÏµÍ³È¨ÏÞʱ£¬²»»á´ÓÆäËüÕÊ»§¼¶ÁªÈ¡ÏûÔø±»ÊÚÓèµÄÏàͬȨÏÞ
3.¶ÔÏóȨÏÞÔÊÐíÓû§¶ÔÊý¾Ý¿â¶ÔÏóÖ´ÐÐÌØ¶¨µÄ²Ù×÷£¬ÈçÖ´ÐÐDML Óï¾ä¡£
with grant option ʹµÃ¸ÃÓû§¾ßÓн«×ÔÉí»ñµÃµÄ¶ÔÏóȨÏÞÊÚÓèÆäËüÓû§µÄ¹¦ÄÜ
µ«ÊջضÔÏóȨÏÞʱ£¬»á´ÓÆäËüÕÊ»§¼¶ÁªÈ¡ÏûÔø±»ÊÚÓèµÄÏàͬȨÏÞ
4.ϵͳȨÏÞÓë¶ÔÏóȨÏÞÊÚÓèʱµÄÓï·¨²îÒìΪ¶ÔÏóȨÏÞʹÓÃÁËON object_ name ×Ó¾ä
5. PUBLIC ΪËùÓеÄÓû§
6. ALL£º¶ÔÏóȨÏÞÖеÄËùÓжÔÏóȨÏÞ

ËÄ¡¢Óû§

MySQL£¬Ò»¸öÓû§£¬¶à¸öÊý¾Ý¿â£¬Ã¿¸öÊý¾Ý¿âÓµÓи÷×ÔµÄ±í£¬¶øOracleÒ»¸öÊý¾Ý¿â£¬¶à¸öÓû§£¬Ã¿¸öÓû§ÓµÓи÷×ÔµÄ±í£¨Êý¾Ý¿â¶ÔÏó£©
sys £¨ÏµÍ³¹ÜÀíÔ±ÓµÓÐ×î¸ßȨÏÞ£© as sysdba
system //±¾µØ¹ÜÀíÔ± ´Î¸ßȨÏÞ
scott£» //ÆÕͨÓû§£¬ÃÜÂëĬÈÏΪtiger,ĬÈÏδ½âËø
´´½¨Óû§£º
create user username identified by password [account lock | unlock]
[password expire ÉèÖÃÃÜÂë¹ýÆÚ];
½âËø|¼ÓËø
alter user username account unlock | lock;
ÐÞ¸ÄÃÜÂë
·Ç¹ÜÀíÔ±Óû§µÇ¼״̬£ºpassword ¡ú ¾É¿ÚÁî ¡ú пÚÁî £ºÐ޸ĵ±Ç°Óû§µÄÃÜÂë
¹ÜÀíÔ±µÇ¼״̬£ºpassword username ¡ú пÚÁî £ºÐÞ¸ÄÖ¸¶¨Óû§µÄÃÜÂë
alter user username identified by newpassword;
ɾ³ýÓû§
drop user username [cascade];
cascade±íʾ¼¶ÁªÉ¾³ýÓû§µÄËùÓжÔÏó£¬É¾Óû§Ê±£¬Ò»Æðɾ³ý¸ÃÓû§µÄ¶ÔÏó
ɾ³ýµ±Ç°ÕýÔÚÁ¬½ÓµÄÓû§
v$session ´æ·ÅµÄÊÇÒѾ­Á¬½ÓµÄÓû§µÄÐÅÏ¢
select sid£¬serial#£¬username from v$session where username = ¡®¡¯;
alter system kill session 'sid£¬serial#';
drop user username [cascade];
²é¿´µ±Ç°µÄµÇ¼µÄÓû§
show user;

¹ØÓÚÓû§³£¼û²Ù×÷´úÂ루ÏÂÃæµÄ´úÂë¶¼ÊÇ¿ÉÒÔÔËÐеģ¬ÔÚwindows²Ù×÷ϵͳÉÏ´óÐ´Ð¡Çø·Ö²»Ãô¸Ð£¬ËùÒԴ󲿷ִóдµÄ¹Ø¼üÃüÁîµ¥´Ê¶¼¿ÉÒÔСд£©£º

1.ÐÞ¸ÄÓû§
ÐÞ¸ÄÓû§µÄÓ﷨ͬ´´½¨Óû§£¬½ö½ö½²¹Ø¼ü×Öcreate Ìæ»»Îªalter£¬alter user¿ÉÒÔÐ޸ijýÓû§ÃûÖ®ÍâµÄÈÎÒ»ÊôÐÔ
ALTER USER robinson ACCOUNT LOCK;
2.ÐÞ¸ÄÃÜÂë
DBA ¿ÉÒÔ´´½¨Óû§ºÍÐÞ¸ÄÃÜÂë
Óû§±¾ÈË¿ÉÒÔʹÓÃALTER USER Óï¾äÐÞ¸ÄÃÜÂë
SQL > ALTER user robinson IDENTIFIED BY newpassword;
3.ɾ³ýÓû§£º
DROP USER username [CASCADE]
CASECADE Á¬Í¬Óû§´´½¨µÄ¶ÔÏóÒ»²¢É¾³ý£¬Èç¹û¸ÃÓû§´´½¨Á˶ÔÏó£¬Òª¼ÓCASCADEɾ³ý£¬·ñÔòɾ³ý²»µô
ÁíÍ⣬²»ÄÜɾ³ýµ±Ç°ÕýÔÚÓëORACLE·þÎñÆ÷ÏàÁ¬µÄÓû§¡£
4.¸Ä±äÓû§ÔÚ±í¿Õ¼äÉϵÄÅä¶î£º
ALTER USER username QUOTA 0 ON system;
ALTER USER scott QUOTA UNLIMITED ON USERS;
ALTER USER dog QUOTA 30M ON system;
5.²é¿´Óû§±í¿Õ¼äÅä¶î£¨dba_ts_quotas)£º
SQ L> SELECT USERNAME,TABLESPACE_NAME,MAX_BYTES /1024 / 1024 " Max MB"
2 FROM dba_ts_quotas WHERE USERNAME='SCOTT';
USERNAME TABLESPACE_NAME Max MB
------------------------------ --------------------------
SCOTT SYSTEM 30
6.²é¿´Ìض¨¶ÔÏóÏÂÓû§ËùÓµÓеĶÔÏó
ʹÓÃdba_objectsÊÓͼ
SQL> SELECT owner,object_ name, object_ type FROM dba_ objects WHERE owner= 'SCOTT';

Îå¡¢½ÇÉ«ÓëȨÏÞ

½ÇÉ«»ù±¾ÄÚÈÝÈçÏ£º
grant ½ÇÉ« to username;
revoke ½ÇÉ« from username;
Ò»¸ö½ÇÉ«ÊǶà¸öȨÏ޵ļ¯ºÏ
ϵͳԤ¶¨Òå½ÇÉ«
connect Á¬½Ó
resource ·ÃÎÊ×ÊԴȨÏÞ£¬·ÃÎÊ±í¡¢ÐòÁУ¬²»°üÀ¨create session
dba ÓµÓÐËùÓÐȨÏÞ
×Ô¶¨Òå½ÇÉ«
create role ½ÇÉ«Ãû
grant ȨÏÞÁбí|½ÇÉ«Áбí to ½ÇÉ«Ãû

ÿ¸öOracleÓû§¶¼ÓÐÒ»¸öÃû×ֺͿÚÁî,²¢ÓµÓÐһЩÓÉÆä´´½¨µÄ±í¡¢ÊÓͼºÍÆäËû×ÊÔ´¡£Oracle½ÇÉ«£¨role£©¾ÍÊÇÒ»×éȨÏÞ£¨privilege£© (»òÕßÊÇÿ¸öÓû§¸ù¾ÝÆä״̬ºÍÌõ¼þËùÐèµÄ·ÃÎÊÀàÐÍ)¡£Óû§¿ÉÒÔ¸ø½ÇÉ«ÊÚÓè»ò¸³ÓèÖ¸¶¨µÄȨÏÞ£¬È»ºó½«½ÇÉ«¸³¸øÏàÓ¦µÄÓû§¡£Ò»¸öÓû§Ò²¿ÉÒÔÖ±½Ó¸øÆäËûÓû§ÊÚ È¨¡£

Êý¾Ý¿âϵͳȨÏÞ£¨Database System Privilege£©ÔÊÐíÓû§Ö´ÐÐÌØ¶¨µÄÃüÁ¡£ÀýÈ磬CREATE TABLEȨÏÞÔÊÐíÓû§´´½¨±í£¬GRANT ANY PRIVILEGE ȨÏÞÔÊÐíÓû§ÊÚÓèÈκÎϵͳȨÏÞ¡£

Êý¾Ý¿â¶ÔÏóȨÏÞ£¨Database Object Privilege£©Ê¹µÃÓû§Äܹ»¶Ô¸÷¸ö¶ÔÏó½øÐÐijЩ²Ù×÷¡£ÀýÈçDELETEȨÏÞÔÊÐíÓû§É¾³ý±í»òÊÓͼµÄÐУ¬SELECTȨÏÞÔÊÐíÓû§Í¨¹ýselect´Ó ±í¡¢ÊÓͼ¡¢ÐòÁУ¨sequences£©»ò¿ìÕÕ £¨snapshots£©ÖвéѯÐÅÏ¢¡£

£¨1£©¡¢3ÖÖ±ê×¼½ÇÉ«

QracleΪÁ˼æÈÝÒÔǰµÄ°æ±¾£¬ÌṩÁËÈýÖÖ±ê×¼µÄ½ÇÉ«£¨role£©£ºCONNECT¡¢RESOURCE ºÍDBA¡£

1. CONNECT Role(Á¬½Ó½ÇÉ«)
ÁÙʱÓû§£¬ÌرðÊÇÄÇЩ²»ÐèÒª½¨±íµÄÓû§£¬Í¨³£Ö»¸³ÓèËûÃÇCONNECTrole¡£CONNECTÊÇʹÓÃOracleµÄ¼òµ¥È¨ÏÞ£¬ÕâÖÖȨÏÞÖ»ÓÐÔÚ¶ÔÆäËûÓû§ µÄ±íÓзÃÎÊȨʱ£¬°üÀ¨select¡¢insert¡¢updateºÍdeleteµÈ£¬²Å»á±äµÃÓÐÒâÒå¡£ÓµÓÐCONNECT roleµÄÓû§»¹Äܹ»´´½¨±í¡¢ÊÓͼ¡¢ÐòÁУ¨sequence£©¡¢´Ø£¨cluster£©¡¢Í¬Òå´Ê£¨synonym £©¡¢»á»°£¨session£©ºÍÓëÆäËûÊý¾Ý¿âµÄÁ´£¨link£©¡£
2. RESOURCE Role(×ÊÔ´½ÇÉ«)
¸ü¿É¿¿ºÍÕýʽµÄÊý¾Ý¿âÓû§¿ÉÒÔÊÚÓèRESOURCE role¡£RESOURCEÌṩ¸øÓû§ÁíÍâµÄȨÏÞÒÔ´´½¨ËûÃÇ×Ô¼ºµÄ±í¡¢ÐòÁС¢¹ý³Ì£¨procedure£©¡¢´¥·¢Æ÷£¨trigger£©¡¢Ë÷Òý£¨index£©ºÍ´Ø£¨cluster£©¡£
3. DBA Role(Êý¾Ý¿â¹ÜÀíÔ±½ÇÉ«)
DBA roleÓµÓÐËùÓеÄϵͳȨÏÞ----°üÀ¨ÎÞÏÞÖÆµÄ¿Õ¼äÏÞ¶îºÍ¸øÆäËûÓû§ÊÚÓè¸÷ÖÖȨÏÞµÄÄÜÁ¦¡£SYSTEMÓÉDBAÓû§ÓµÓС£ÏÂÃæ½éÉÜһЩDBA¾­³£Ê¹ÓõĵäÐÍȨÏÞ?br> A.grant£¨ÊÚȨ£©ÃüÁî
ÏÂÃæ¶Ô¸Õ²Å´´½¨µÄÓû§user01ÊÚȨ£¬ÃüÁîÈçÏ£º
grant connect, resource to user01;
B.revoke£¨³·Ïû£©È¨ÏÞ
ÒÑÊÚÓèµÄȨÏÞ¿ÉÒÔ³·Ïû¡£ÀýÈç³·Ïû£¨1£©ÖеÄÊÚȨ£¬ÃüÁîÈçÏ£º
revoke connect, resource from user01;
Ò»¸ö¾ßÓÐDBA½ÇÉ«µÄÓû§¿ÉÒÔ³·ÏûÈκαðµÄÓû§ÉõÖÁ±ðµÄDBAµÄCONNECT¡¢RESOURCE ºÍDBAµÄÆäËûȨÏÞ¡£µ±È»£¬ÕâÑùÊǺÜΣÏյģ¬Òò´Ë£¬³ý·ÇÕæÕýÐèÒª£¬DBAȨÏÞ²»Ó¦Ëæ±ãÊÚÓèÄÇЩ²»ÊǺÜÖØÒªµÄÒ»°ãÓû§¡£
³·ÏûÒ»¸öÓû§µÄËùÓÐȨÏÞ£¬²¢²»Òâζ×Å´ÓOracleÖÐɾ³ýÁËÕâ¸öÓû§£¬Ò²²»»áÆÆ»µÓû§´´½¨µÄÈÎºÎ±í£»Ö»ÊǼòµ¥½ûÖ¹Æä¶ÔÕâЩ±íµÄ·ÃÎÊ¡£ÆäËûÒª·ÃÎÊÕâЩ±íµÄÓû§¿ÉÒÔÏóÒÔǰÄÇÑùµØ·ÃÎÊÕâЩ±í¡£

£¨2£©¡¢´´½¨½ÇÉ«

³ýÁËÇ°Ãæ½²µ½µÄÈýÖÖϵͳ½ÇÉ«¡ª-CONNECT¡¢RESOURCEºÍDBA£¬Óû§»¹¿ÉÒÔÔÚOracle´´½¨×Ô¼ºµÄrole¡£Óû§´´½¨µÄrole¿ÉÒÔÓÉ ±í»òϵͳȨÏÞ»òÁ½ÕßµÄ×éºÏ¹¹³É¡£ÎªÁË´´½¨role£¬Óû§±ØÐë¾ßÓÐCREATE ROLEϵͳȨÏÞ¡£ÏÂÃæ¸ø³öÒ»¸öcreate roleÃüÁîµÄʵÀý£º

create role STUDENT;
ÕâÌõÃüÁî´´½¨ÁËÒ»¸öÃûΪ STUDENTµÄ role¡£
Ò»µ©´´½¨ÁËÒ»¸örole£¬Óû§¾Í¿ÉÒÔ¸øËûÊÚȨ¡£¸øroleÊÚȨµÄgrantÃüÁîµÄÓï·¨Óë¶Ô¶ÔÓû§µÄÓï·¨Ïàͬ¡£ÔÚ¸øroleÊÚȨʱ£¬ÔÚgrantÃüÁîµÄto×Ó¾äÖÐҪʹÓÃroleµÄÃû³Æ£¬ÈçÏÂËùʾ£º
grant select on CLASS to STUDENT;

ÏÖÔÚ£¬ÓµÓÐSTUDENT½ÇÉ«µÄËùÓÐÓû§¶¼¾ßÓжÔCLASS±íµÄselectȨÏÞ¡£

£¨3£©¡¢É¾³ý½ÇÉ«

Ҫɾ³ý½ÇÉ«£¬¿ÉÒÔʹÓà drop roleÃüÁÈçÏÂËùʾ£º
drop role STUDENT;
   
8552 ´Îä¯ÀÀ       28
Ïà¹ØÎÄÕÂ

»ùÓÚEAµÄÊý¾Ý¿â½¨Ä£
Êý¾ÝÁ÷½¨Ä££¨EAÖ¸ÄÏ£©
¡°Êý¾Ýºþ¡±£º¸ÅÄî¡¢ÌØÕ÷¡¢¼Ü¹¹Óë°¸Àý
ÔÚÏßÉ̳ÇÊý¾Ý¿âϵͳÉè¼Æ ˼·+Ч¹û
 
Ïà¹ØÎĵµ

GreenplumÊý¾Ý¿â»ù´¡Åàѵ
MySQL5.1ÐÔÄÜÓÅ»¯·½°¸
ijµçÉÌÊý¾ÝÖÐ̨¼Ü¹¹Êµ¼ù
MySQL¸ßÀ©Õ¹¼Ü¹¹Éè¼Æ
Ïà¹Ø¿Î³Ì

Êý¾ÝÖÎÀí¡¢Êý¾Ý¼Ü¹¹¼°Êý¾Ý±ê×¼
MongoDBʵս¿Î³Ì
²¢·¢¡¢´óÈÝÁ¿¡¢¸ßÐÔÄÜÊý¾Ý¿âÉè¼ÆÓëÓÅ»¯
PostgreSQLÊý¾Ý¿âʵսÅàѵ