±à¼ÍƼö: |
±¾ÎÄÀ´×ÔÓÚ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;
|