ORACLE 10G OCP笔记

第1页用户管理系统权限conn/assysdba;createusert1identifiedbyt1;createusert2identifiedbyt2;grantconnecttot1,t2;grantcreatetable,createviewtot1withadminoption;connt1/t1grantcreatetabletot2;conn/assysdba;select*fromdba_sys_privswheregranteein('T1','T2');GRANTEEPRIVILEGEADM-------------------------------------------------------------------------T1CREATETABLEYEST1CREATEVIEWYEST2CREATETABLENOwithadminoption选项，可使被授予权限者有权转授其他人revokecreatetable,createviewfromt1;select*fromdba_sys_privswheregranteein('T1','T2');GRANTEEPRIVILEGEADM-------------------------------------------------------------------------T2CREATETABLENO不会级联删除已经授予的系统权限对象权限conntest/testgrantselectonttot1withgrantoption;connt1/t1grantselectontest.ttot2;conntest/testselect*fromdba_tab_privswheregranteein('T1','T2');GRANTEEOWNERTABLE_NAMEGRANTORPRIVILEGE---------------------------------------------------------------------------------------------------------------------------------T2TESTTT1SELECTT1TESTTTESTSELECTrevokeselectontfromt1;select*fromdba_tab_privswheregranteein('T1','T2');SQLselect*fromdba_tab_privswheregranteein('T1','T2');未选定行级联删除已经授予的对象权限createrolettt;grantcreatetable,createuser,createsessiontotttwithadminoption;grantselectontest.ttotttwithgrantoption;SQLgrantselectontest.ttotttwithgrantoption;grantselectontest.ttotttwithgrantoption*第1行出现错误:ORA-01926:无法将WITHGRANTOPTIONGRANT角色无法使用withgrantoption选项给角色授权grantselectontest.ttottt;createuserdidentifiedbyd;createusereidentifiedbye;grantttttodwithadminoption;connd/dgrantttttoe;select*fromtest.t;SQLselect*fromtest.t;AB--------------------测试111conn/assysdbaselect*fromdba_role_privswheregranteein('D','E');第2页GRANTEEGRANTED_ROLEADMDEF------------------------------------------------------------------ETTTNOYESDTTTYESYESdroprolettt;select*fromdba_role_privswheregranteein('D','E');SQLselect*fromdba_role_privswheregranteein('D','E');未选定行connd/dSQLconnd/dERROR:ORA-01045:用户D没有CREATESESSION权限;登录被拒绝角色级联删除配置文件profiledropprofiletest_profilecascade;createprofiletest_profileLIMITSESSIONS_PER_USER1CPU_PER_CALL10CPU_PER_SESSIONUNLIMITEDLOGICAL_READS_PER_CALL1000LOGICAL_READS_PER_SESSIONUNLIMITEDCONNECT_TIME1000IDLE_TIME1;select*fromdba_profileswhereprofile='TEST_PROFILE';PROFILERESOURCE_NAMERESOURCELIMIT-------------------------------------------------------------------------------------------------TEST_PROFILECOMPOSITE_LIMITKERNELDEFAULTTEST_PROFILESESSIONS_PER_USERKERNEL1TEST_PROFILECPU_PER_SESSIONKERNELUNLIMITEDTEST_PROFILECPU_PER_CALLKERNEL10TEST_PROFILELOGICAL_READS_PER_SESSIONKERNELUNLIMITEDTEST_PROFILELOGICAL_READS_PER_CALLKERNEL1000TEST_PROFILEIDLE_TIMEKERNEL1TEST_PROFILECONNECT_TIMEKERNEL1000TEST_PROFILEPRIVATE_SGAKERNELDEFAULTTEST_PROFILEFAILED_LOGIN_ATTEMPTSPASSWORDDEFAULTTEST_PROFILEPASSWORD_LIFE_TIMEPASSWORDDEFAULTPROFILERESOURCE_NAMERESOURCELIMIT-------------------------------------------------------------------------------------------------TEST_PROFILEPASSWORD_REUSE_TIMEPASSWORDDEFAULTTEST_PROFILEPASSWORD_REUSE_MAXPASSWORDDEFAULTTEST_PROFILEPASSWORD_VERIFY_FUNCTIONPASSWORDDEFAULTTEST_PROFILEPASSWORD_LOCK_TIMEPASSWORDDEFAULTTEST_PROFILEPASSWORD_GRACE_TIMEPASSWORDDEFAULTSESSIONS_PER_USER用户最大并发会话数CPU_PER_CALL单语句的最大CPU时间，超过语句会终止（单位：百分之一秒）LOGICAL_READS_PER_CALL单语句的最大读取数据块数（包括读缓存和物理磁盘），没完成语句终止，数据回滚CONNECT_TIME连接的最长时间（单位：分钟）IDLE_TIME不执行任何数据活动的情况下保持连接的最长时间（单位：分钟）alterusertestprofiletest_profile;为用户指定配置文件showparameterresource_limitNAMETYPEVALUE-----------------------------------------------------------------------------resource_limitbooleanFALSEaltersystemsetresource_limit=TRUE;只有resource_limit为TRUE时，资源限制才有效conntest/testSQLconntest/testERROR:ORA-02391:超出同时存在的SESSIONS_PER_USER限制因为设定只能有一个会话，所以出错保护数据库安全select*froma.t;第3页X----------12alteruseraaccountlock;conna/aSQLconna/aERROR:ORA-28000:帐户已被锁定conntest/testselect*froma.t;X----------12账户被锁定，其模式仍可访问showparameterO7_DICTIONARY_ACCESSIBILITYNAMETYPEVALUE-----------------------------------------------------------------------------O7_DICTIONARY_ACCESSIBILITYbooleanFALSEO7_DICTIONARY_ACCESSIBILITY=FALSEANY权限不被授予SYS拥有的对象,反之可以SELECTANYDICTIONARY权限可使得用户查看数据字典和动态视图PUBLIC所拥有权限selectcount(*)fromdba_tab_privswheregrantee='PUBLIC';COUNT(*)----------21111系统默认给各用户开放的对象数（比较危险）selecttable_namefromdba_tab_privswheregrantee='PUBLIC'andprivilege='EXECUTE'andtable_namelike'UTL%';TABLE_NAME------------------------------UTL_COLLUTL_COMPRESSUTL_DBWSUTL_ENCODEUTL_FILEUTL_GDKUTL_HTTPUTL_I18NUTL_INADDRUTL_LMSUTL_MATCHTABLE_NAME------------------------------UTL_NLAUTL_NLA_ARRAY_DBLUTL_NLA_ARRAY_FLTUTL_NLA_ARRAY_INTUTL_RAWUTL_REFUTL_SMTPUTL_TCPUTL_URLUTL_FILE操作文件UTL_TCP打开服务器TCP端口.....比较危险的程序包UTL_FILE_DIRshowparameterutl_file_dirNAMETYPEVALUE-----------------------------------------------------------------------------utl_file_dirstringutl_file可操作在utl_file_dir定义的目录，唯一的限制：Oracle拥有者必须能够访问所列出的目录utl_file默认为NULLaltersystemsetutl_file_dir='I:\','G:\'scope=spfile;设置生效数据库需重启第4页showparameterutl_file_dirNAMETYPEVALUE-----------------------------------------------------------------------------utl_file_dirstringI:\,G:\