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

1Ôª 10Ôª 50Ôª





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



  ÇóÖª ÎÄÕ ÎÄ¿â Lib ÊÓÆµ iPerson ¿Î³Ì ÈÏÖ¤ ×Éѯ ¹¤¾ß ½²×ù Model Center   Code  
»áÔ±   
   
 
     
   
 ¶©ÔÄ
  ¾èÖú
±í¿Õ¼ä¡¢Óû§¡¢È¨ÏÞ¡¢½ÇÉ« (Êý¾Ý¿ØÖÆÓïÑÔ-DCL-grant,revoke)
 
  2187  次浏览      27
 2018-7-4
 
±à¼­ÍƼö:
±¾ÎÄÀ´×ÔÓÚcsdn,½éÉÜÁ˵ǽÊý¾Ý¿â£¬±í¿Õ¼ä£¬´´½¨Óû§£¬¸øÓû§ÊÚȨ /½ÇÉ«£¬»ØÊÕÓû§È¨ÏÞ/½ÇÉ«µÈ¡£

1¡¢µÇ½Êý¾Ý¿â

SQLPLUS SYS AS SYSDBA; // µÇ½SYSÕÊ»§

SQLPLUS Óû§Ãû/ÃÜÂë@SID; // µÇ½ÆÕͨÓû§

ÀýÈ磺SQLPLUS LAOSAN/LAOSAN@ORCL

Á¬½Óµ½ÁíÍâÒ»¸öÓû§

CONN Óû§Ãû/ÃÜÂë;

ÀýÈ磺CONN LAOSAN/LAOSAN;

2¡¢±í¿Õ¼ä

ORACLEµÄ±í¿Õ¼ä·ÖÁ˼¸ÖÖÀàÐÍ£¬ÁÙʱ±í¿Õ¼ä[TEMPFILE]£¬Óû§±í¿Õ¼ä [DATAFILE]£¬´óÎļþ±í¿Õ¼ä[ BIGFILE ] µÈ£¬´´½¨±í¿Õ¼ä£¬Ò»°ã¶¼ÊÇ DBA¸ÉµÄÊÂÇé¡£

´´½¨ÁÙʱ±í¿Õ¼ä£¬¼¸¸ö²ÎÊýÒªÖØµã×¢ÒâÒ»ÏÂ

SIZE *[K,M]±í¿Õ¼äµÄ³õʼ´óС£¬µ¥Î»¿ÉÒÔÊÇ[K,M]

MAXSIZE *[K,M]±í¿Õ¼äµÄ×î´ó´æ´¢Öµ£¬MAXSIZE UNLIMITED ÊÇÖ¸±í¿Õ¼äµÄ´óС²»ÊÜÏÞÖÆ¡£

AUTOEXTEND ON NEXT *[K,M] ÉèÖñí¿Õ¼ä×Ô¶¯À©Õ¹

EXTENT MANAGEMENT LOCAL±¾µØ¹ÜÀí±í¿Õ¼ä

EXTENTÊÇ"Çø¼ä"µÄÒâ˼,ÔÚORACLEÊý¾Ý¿âÖУºEXTENT MANAGEMENT ÓÐÁ½ÖÖ·½Ê½ EXTENT MANAGEMENT LOCAL(±¾µØ¹ÜÀí); EXTENT MANAGEMENT DICTIONARY(Êý¾Ý×Öµä¹ÜÀí)¡£Ä¬ÈϵÄÊÇLOCAL±¾µØ¹ÜÀí±í¿Õ¼äÓÐ×Ô¶¯·ÖÅä(AUTOALLOCATE)ºÍͳһ´óС·ÖÅä(UNIFORM)Á½ÖÖ¿Õ¼ä·ÖÅ䷽ʽ£¬×Ô¶¯·ÖÅ䷽ʽ(AUTOALLOCATE)ÊÇÓÉϵͳÀ´×Ô¶¯¾ö¶¨EXTENTS´óС£¬¶øÍ³Ò»´óС·ÖÅä(UNIFORM)ÔòÊÇÓÉÓû§Ö¸¶¨EXTENTS´óС¡£ÕâÁ½ÖÖ·ÖÅ䷽ʽ¶¼Ìá¸ßÁ˿ռä¹ÜÀíЧÂÊ¡£UNIFORM£ºÄ¬ÈÏΪ1M´óС£¬ÔÚTEMP±í¿Õ¼äÀïΪĬÈϵ쬵«ÊDz»Äܱ»Ó¦ÓÃÔÚUNDO±í¿Õ¼ä¡£

--´´½¨

CREATE TEMPORARY TABLESPACE temptest

tempFile

'E:\database\temptest01.dbf' SIZE 64M AUTOEXTEND ON NEXT 32M MAXSIZE 1024M,

'E:\database\temptest02.dbf' SIZE 64M AUTOEXTEND ON NEXT 32M MAXSIZE 1024M,

'E:\database\temptest03.dbf' SIZE 64M AUTOEXTEND ON NEXT 32M MAXSIZE 1024M EXTENT MANAGEMENT LOCAL ;

-- TEMPORARYÔÚ´´½¨ÁÙʱ±í¿Õ¼äʱ²ÅÐèÒª£¬ÀýÈç´´½¨Óû§±í¿Õ¼äÈçÏ£º

CREATE TABLESPACE usertest

dataFile

'E:\database\usertest01.dbf' SIZE 64M AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED ,

'E:\database\usertest02.dbf' SIZE 64M AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED ,

'E:\database\usertest03.dbf' SIZE 64M AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL ;

--Ôö¼Ó±í¿Õ¼äÎļþ¡¢ÐÞ¸Ä

ALTER TABLESPACE temptest

ADD tempFile

'E:\database\ temptest04.dbf' SIZE 64M AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED;

--ɾ³ý

DROP TABLESPACE temptest INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

--²é¿´±í¿Õ¼ä

SELECT * FROM DBA_TABLESPACES;

²é¿´µ±Ç°Óû§µÄȱʡ±í¿Õ¼ä

SELECT USERNAME,DEFAULT_TABLESPACE FROM USER_USERS;

3¡¢Óû§

´´½¨Óû§

´´½¨Óû§Ê±£¬ÐèҪעÒâÈçϼ¸¸ö²ÎÊý

IDENTIFIED BY *** Óû§µÄÃÜÂë

DEFAULT TABLESPACE *** ĬÈÏÓû§±í¿Õ¼ä

TEMPORARY TABLESPACE *** ĬÈÏÁÙʱ¿Õ¼ä

CREATE USER laosan

IDENTIFIED BY ls0123

DEFAULT TABLESPACE usertest

TEMPORARY TABLESPACE temptest;

CREATE USER Óû§Ãû IDENTIFIED BY ÃÜÂë; --Èç¹û²»Ö¸¶¨±í¿Õ¼ä£¬Ä¬ÈÏ´´½¨ÔÚSYSTEM±í¿Õ¼äÏÂ

ÀýÈ磺CREATE USER LAOSAN IDENTIFIED BY LAOSAN DEFAULT TABLESPACE LAOSAN;

ÐÞ¸ÄÃÜÂë

ALTER USER Óû§Ãû IDENTIFIED BY ÐÂÃÜÂë;

ÀýÈ磺ALTER USER laosan IDENTIFIED BY ls4567;

ÐÞ¸ÄÓû§È±Ê¡±í¿Õ¼ä

ALTER USER laosan DEFAULT TABLESPACE user_data;

ÐÞ¸ÄÓû§ÁÙʱ±í¿Õ¼ä

ALTER USER laosan TEMPORARY TABLESPACE temp_data;

½«Óû§¼ÓËø

ALTER USER laosan ACCOUNT LOCK; // ¼ÓËø

ALTER USER laosan ACCOUNT UNLOCK; // ½âËø

ɾ³ýÓû§

DROP USER Óû§Ãû;

ÀýÈ磺DROP USER laosan;

ɾ³ýÓû§ÒÔ¼°¸úÓû§¹ØÁªµÄ¶ÔÏó

DROP USER LAOSAN CASCADE;

²é¿´ËùÓÐÓû§

SELECT * FROM ALL_USERS;

²é¿´µ±Ç°Óû§

SHOW USER;

Á¬½Óµ½ÁíÍâÒ»¸öÓû§

CONN Óû§Ãû/ÃÜÂë;

ÀýÈ磺CONN laosan/laosan;

²éѯÓû§»á»°ÐÅÏ¢

SELECT USERNAME, SID, SERIAL#, MACHINE FROM V$SESSION;

ɾ³ýÓû§»á»°ÐÅÏ¢

ALTER SYSTEM KILL SESSION 'sid, serial#';

²éѯÓû§SQLÓï¾ä

SELECT USER_NAME, SQL_TEXT FROM V$OPEN_CURSOR;

4¡¢¸øÓû§ÊÚȨ/½ÇÉ«

OracleµÄÌØÊâȨÏÞ

ϵͳȨÏÞUNLIMITED TABLESPACEÊÇÒþº¬ÔÚDBA, RESOURCE½ÇÉ«ÖеÄÒ»¸öϵͳȨÏÞ. µ±Óû§µÃµ½DBA»òRESOURCEµÄ½Çɫʱ, UNLIMITED TABLESPACEϵͳȨÏÞÒ²ÒþʽÊÜȨ¸øÓû§.

ϵͳȨÏÞUNLIMITED TABLESPACE²»Äܱ»ÊÚÓèROLE, ¿ÉÒÔ±»ÊÚÓèÓû§.

ϵͳȨÏÞUNLIMITED TABLESPACE²»»áËæ×ÅRESOURCE, DBA±»ÊÚÓèROLE¶øÊÚÓè¸øÓû§.

OracleÌØÊâ½ÇÉ«

DBA½ÇÉ«£¬ÊÇÊÚȨÊý¾Ý¿â¹ÜÀíÔ±µÄȨÏÞ

CONNECT½ÇÉ«£¬ ÊÇÊÚÓè×îÖÕÓû§µÄµäÐÍȨÀû£¬×î»ù±¾µÄ Ò»¸ö(CREATE SESSION)

RESOURCE½ÇÉ«£¬ÊÇÊÚÓ迪·¢ÈËÔ±µÄ ĬÈÏÓа˸öȨÏÞ(CREATE SEQUENCE,CREATE TRIGGER,CREATE CLUSTER,CREATE PROCEDURE,CREATE TYPE,CREATE OPERATOR,CREATE TABLE,CREATE INDEXTYPE)

EXP_FULL_DATABASE½ÇÉ«£¬ÓµÓе¼³öÊý¾Ý¿âµÄȨÏÞ

IMP_FULL_DATABASE½ÇÉ«£¬ÓµÓе¼ÈëÊý¾Ý¿âµÄȨÏÞ

Óï·¨£º

GRANT ȨÏÞÀàÐÍ, ȨÏÞÀàÐÍ TO Óû§Ãû1, Óû§Ãû2;

GRANT ȨÏÞÀàÐÍ, ȨÏÞÀàÐÍ ±íÃû TO Óû§Ãû1, Óû§Ãû2;

ÀýÈ磺GRANT CONNECT, RESOURCE TO LAOSAN;

³£ÓõÄϵͳȨÏÞ¼¯ºÏÓÐÒÔÏÂÈý¸ö:

CONNECT(»ù±¾µÄÁ¬½Ó), RESOURCE(³ÌÐò¿ª·¢), DBA(Êý¾Ý¿â¹ÜÀí)

³£ÓõÄÊý¾Ý¶ÔÏóȨÏÞÓÐÒÔÏÂÎå¸ö:

ALL ON Êý¾Ý¶ÔÏóÃû, SELECT ON Êý¾Ý¶ÔÏóÃû, UPDATE ON Êý¾Ý¶ÔÏóÃû, DELETE ON Êý¾Ý¶ÔÏóÃû, INSERT ON Êý¾Ý¶ÔÏóÃû, ALTER ON Êý¾Ý¶ÔÏóÃû

GRANT ALL TO PUBLIC;//ÊÚÓèËùÓÐȨÏÞ(ALL)¸øËùÓÐÓû§(PUBLIC)

--ÊÚȨ

GRANT

CONNECT,

RESOURCE,

--DBA,

--UNLIMITED TABLESPACE,

CREATE SESSION,

CREATE ANY SEQUENCE,

CREATE ANY TABLE,

CREATE ANY VIEW ,

CREATE ANY INDEX,

CREATE ANY PROCEDURE,

CREATE ANY DIRECTORY,

ALTER SESSION,

ALTER ANY SEQUENCE,

ALTER ANY TABLE,

--ALTER ANY VIEW , --²»ÄÜÐÞ¸ÄÊÓͼ

ALTER ANY INDEX,

ALTER ANY PROCEDURE,

--ALTER ANY DIRECTORY, --²»ÄÜÐÞ¸ÄĿ¼

--DROP SESSION, --²»ÄÜɾ³ýSESSION

DROP ANY SEQUENCE,

DROP ANY TABLE,

DROP ANY VIEW ,

DROP ANY INDEX,

DROP ANY PROCEDURE,

DROP ANY DIRECTORY,

SELECT ANY TABLE,

SELECT ANY DICTIONARY,

INSERT ANY TABLE,

UPDATE ANY TABLE,

DELETE ANY TABLE,

DEBUG ANY PROCEDURE,

DEBUG CONNECT SESSION,

EXP_FULL_DATABASE,

IMP_FULL_DATABASE

TO laosan;

5¡¢»ØÊÕÓû§È¨ÏÞ/½ÇÉ«

REVOKE ȨÏÞÀàÐÍ, ȨÏÞÀàÐÍ FROM Óû§Ãû;

REVOKE ȨÏÞÀàÐÍ, ȨÏÞÀàÐÍ ±íÃû FROM Óû§Ãû1, Óû§Ãû2;

6¡¢²éѯÓû§È¨ÏÞ/½ÇÉ«

ÒÔϵIJéѯÖУ¬»ù±¾¶¼ÒªÒÔDBAµÄ½ÇÉ«½øÐвéѯ

²é¿´Óû§ÓµÓÐÄÇЩ½ÇÉ«

SELECT * FROM DBA_ROLE_PRIVS A WHERE A.GRANTEE='laosan';

--»ò

SELECT * FROM DBA_SYS_PRIVS A WHERE A.GRANTEE='laosan';

²é¿´½ÇɫӵÓÐÄÇЩȨÏÞ

SELECT ROLE, PRIVILEGE FROM ROLE_SYS_PRIVS WHERE ROLE='RESOURCE'; --RESOURCE£¬CONNECT£¬DBA

--»ò

SELECT GRANTEE,PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE='RESOURCE';

7¡¢È¨ÏÞ´«µÝ

¼´Óû§A½«È¨ÏÞÊÚÓèB£¬B¿ÉÒÔ½«²Ù×÷µÄȨÏÞÔÙÊÚÓèC£¬ÃüÁîÈçÏ£º

GRANT ALERT TABLE ON tablename TO laosan WITH ADMIN OPTION;//¹Ø¼ü×Ö WITH ADMIN OPTION

GRANT ALERT TABLE ON tablename TO laosan WITH GRANT OPTION;//¹Ø¼ü×Ö WITH GRANT OPTIONЧ¹ûºÍADMINÀàËÆ

WITH ADMIN OPTION ÓÃÓÚϵͳȨÏÞÊÚȨ£¬WITH GRANT OPTION ÓÃÓÚ¶ÔÏóÊÚȨ

µ«¸øÒ»¸öÓû§ÊÚÓèϵͳȨÏÞ´øÉÏWITH ADMIN OPTION ʱ£¬´ËÓû§¿É°Ñ´ËϵͳȨÏÞÊÚÓèÆäËûÓû§»ò½ÇÉ«£¬µ«ÊÕ»ØÕâ¸öÓû§µÄϵͳȨÏÞʱ£¬Õâ¸öÓû§ÒѾ­ÊÚÓèÆäËûÓû§»ò½ÇÉ«µÄ´ËϵͳȨÏÞ²»»áÒò´«²¥ÎÞЧ£¬ÈçÊÚÓèAϵͳȨÏÞCREATE SESSION WITH ADMIN OPTION,È»ºóAÓÖ°ÑCREATE SESSIONȨÏÞÊÚÓèB,µ«¹ÜÀíÔ±ÊÕ»ØAµÄCREATE SESSIONȨÏÞʱ£¬BÒÀȻӵÓÐCREATE SESSIONµÄȨÏÞ£¬µ«¹ÜÀíÔ±¿ÉÒÔÏÔʽÊÕ»ØB CREATE SESSIONµÄȨÏÞ£¬¼´Ö±½ÓREVOKE CREATE SESSION FROM B.

¶øWITH GRANT OPTIONÓÃÓÚ¶ÔÏóÊÚȨʱ£¬±»ÊÚÓèµÄÓû§Ò²¿É°Ñ´Ë¶ÔÏóȨÏÞÊÚÓèÆäËûÓû§»ò½ÇÉ«£¬²»Í¬µÄÊǵ«¹ÜÀíÔ±ÊÕ»ØÓÃWITH GRANT OPTIONÊÚȨµÄÓû§¶ÔÏóȨÏÞʱ£¬È¨ÏÞ»áÒò´«²¥¶øÊ§Ð§£¬ÈçGRANT SELECT ON TABLE WITH GRANT OPTION TO A,AÓû§°Ñ´ËȨÏÞÊÚÓèB£¬µ«¹ÜÀíÔ±ÊÕ»ØAµÄȨÏÞʱ£¬BµÄȨÏÞÒ²»áʧЧ£¬µ«¹ÜÀíÔ±²»¿ÉÒÔÖ±½ÓÊÕ»ØBµÄSELECT ON TABLE ȨÏÞ¡£

8¡¢½ÇÉ«

ϵͳԤ¶¨Òå½ÇÉ«

CONNECT, RESOURCE, DBA ÕâЩԤ¶¨Òå½ÇÉ«Ö÷ÒªÊÇΪÁËÏòºó¼æÈÝ¡£ÆäÖ÷ÒªÊÇÓÃÓÚÊý¾Ý¿â¹ÜÀí¡£

DELETE_CATALOG_ROLE£¬ EXECUTE_CATALOG_ROLE£¬ SELECT_CATALOG_ROLE ÕâЩ½ÇÉ«Ö÷ÒªÓÃÓÚ·ÃÎÊÊý¾Ý×ÖµäÊÓͼºÍ°ü¡£

EXP_FULL_DATABASE£¬ IMP_FULL_DATABASE ÕâÁ½¸ö½ÇÉ«ÓÃÓÚÊý¾Ýµ¼Èëµ¼³ö¹¤¾ßµÄʹÓá£

AQ_USER_ROLE£¬ AQ_ADMINISTRATOR_ROLE AQ:Advanced Query¡£ÕâÁ½¸ö½ÇÉ«ÓÃÓÚoracle¸ß¼¶²éѯ¹¦ÄÜ¡£

SNMPAGENT ÓÃÓÚoracle enterprise managerºÍIntelligent Agent

RECOVERY_CATALOG_OWNER ÓÃÓÚ´´½¨ÓµÓлָ´¿âµÄÓû§¡£

HS_ADMIN_ROLE ʹÓÃOracleµÄÒì¹¹·þÎñ¹¦ÄܵÄÒ»¸öDBAÐèÒªÕâ¸ö½ÇÉ«À´·ÃÎÊÊý¾Ý×ÖµäÏàÓ¦µÄ±í¡£

½ÇÉ«¼´È¨Ï޵ļ¯ºÏ£¬¿ÉÒÔ°ÑÒ»¸ö½ÇÉ«ÊÚÓè¸øÓû§

CREATE ROLE myrole;//´´½¨½ÇÉ«

GRANT CREATE SESSION TO myrole;//½«´´½¨SESSIONµÄȨÏÞÊÚÓèMYROLE

GRANT myrole TO laosan;//ÊÚÓèlaosanÓû§MYROLEµÄ½ÇÉ«

DROP ROLE myrole;//ɾ³ý½ÇÉ«

ÐÞ¸ÄÖ¸¶¨Óû§£¬ÉèÖÃÆäĬÈϽÇÉ«

ALTER USER user1 DEFAULT ROLE role1;

ALTER USER user1 DEFAULT ROLE ALL EXCEPT role1;

½ÇÉ«µÄȨÏÞ²éѯ

SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE='½ÇÉ«Ãû';

´´½¨´øÓпÚÁîµÄ½ÇÉ«(ÔÚÉúЧ´øÓпÚÁîµÄ½Çɫʱ±ØÐëÌṩ¿ÚÁî)

CREATE ROLE role1 IDENTIFIED BY password1;

Ð޸ĽÇÉ«£ºÊÇ·ñÐèÒª¿ÚÁî

ALTER ROLE role1 NOT IDENTIFIED;

ALTER ROLE role1 IDENTIFIED BY password1;

²éѯµ±Ç°Óû§±»ÊÚÓèµÄ½ÇÉ«

SELECT * FROM SESSION_ROLES ORDER BY ROLE;

   
2187 ´Îä¯ÀÀ       27
Ïà¹ØÎÄÕÂ

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

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

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