SQLPlus Security.docx
《SQLPlus Security.docx》由会员分享，可在线阅读，更多相关《SQLPlus Security.docx（12页珍藏版）》请在冰点文库上搜索。
SQLPlusSecurity
SQL*PlusSecurity
Thischapterdescribestheavailablemethodsforcontrollingaccesstodatabasetables,SQL*PlusandiSQL*Pluscommands,andiSQL*Plusaccess.Itcoversthefollowingtopics:
∙PRODUCT_USER_PROFILETable
∙DisablingSQL*Plus,SQL,andPL/SQLCommands
∙CreatingandControllingRoles
∙DisablingCommandswithSQLPLUS-RESTRICT
∙ProgramArgumentSecurity
∙iSQL*PlusSecurity
PRODUCT_USER_PROFILETable
SQL*PlususesthePRODUCT_USER_PROFILE（PUP）table,atableintheSYSTEMaccount,toprovideproduct-levelsecuritythatsupplementstheuser-levelsecurityprovidedbytheSQLGRANTandREVOKEcommandsanduserroles.
DBAscanusethePUPtabletodisablecertainSQLandSQL*PluscommandsintheSQL*Plusenvironmentonaper-userbasis.SQL*Plus—notOracleDatabase—enforcesthissecurity.DBAscanevenrestrictaccesstotheGRANT,REVOKE,andSETROLEcommandstocontrolusers'abilitytochangetheirdatabaseprivileges.
SQL*PlusreadsrestrictionsfromthePUPtablewhenauserlogsintoSQL*Plusandmaintainsthoserestrictionsforthedurationofthesession.ChangestothePUPtablewillonlytakeeffectthenexttimetheaffecteduserslogintoSQL*Plus.
WhenSYSTEM,SYS,orauserauthenticatingwithSYSDBAorSYSOPERprivilegesconnectsorlogsin,SQL*PlusdoesnotreadthePUPtable.Therefore,norestrictionsapplytotheseusers.
ThePUPtableappliesonlytothelocaldatabase.Ifaccessingobjectsonaremotedatabasethroughadatabaselink,thePUPtablefortheremotedatabasedoesnotapply.Theremotedatabasecannotextracttheusernameandpasswordfromthedatabaselinkinordertodeterminethatuser'sprofileandprivileges.
CreatingthePUPTable
YoucancreatethePUPtablebyrunningthescriptnamedPUPBLDwiththeextensionSQLasSYSTEM.Theexactformatofthefileextensionandthelocationofthefilearesystemdependent.SeeyourDBAformoreinformation.
Note:
Ifthetableiscreatedincorrectly,allusersotherthanprivilegeduserswillseeawarningwhenconnectingtoOracleDatabasethatthePUPtableinformationisnotloaded.
PUPTableStructure
ThePUPtablehasthefollowingcolumns:
PRODUCTNOTNULLVARCHAR2（30）
USERIDVARCHAR2（30）
ATTRIBUTEVARCHAR2（240）
SCOPEVARCHAR2（240）
NUMERIC_VALUENUMBER（15,2）
CHAR_VALUEVARCHAR2（240）
DATE_VALUEDATE
LONG_VALUELONG
DescriptionandUseofPUPColumns
ThefollowinglistdescribeseachcolumninthePUPtable:
PUPColumn
Description
PRODUCT
Mustcontaintheproductname（inthiscase"SQL*Plus"）.YoucannotenterwildcardsorNULLinthiscolumn.
USERID
Mustcontaintheusername（uppercase）oftheuserforwhomyouwishtodisablethecommand.Todisablethecommandformorethanoneuser,useSQLwildcards（%）ormakemultipleentries.Thus,allofthefollowingentriesarevalid:
∙HR
∙CLASS1
∙CLASS%（alluserswhosenamesstartwithCLASS）
∙%（allusers）
ATTRIBUTE
Mustcontainthename（inuppercase）oftheSQL,SQL*Plus,orPL/SQLcommandtodisable（forexample,RUN）.Ifyouaredisablingarole,itmustcontainthecharacterstring"ROLES".Youcannotenterawildcard.See"PUPTableAdministration"foralistofSQLandSQL*Pluscommandsyoucandisable.See"CreatingandControllingRoles"forinformationonhowtodisablearole.
SCOPE
Notused,itisrecommendedthatyouenterNULL.Otherproductsmaystorespecificfilerestrictionsorotherdatainthiscolumn.
NUMERIC_VALUE
Notused,itisrecommendedthatyouenterNULL.Otherproductsmaystorenumericvaluesinthiscolumn.
CHAR_VALUE
Mustcontainthecharacterstring"DISABLED"todisableaSQL,SQL*Plus,orPL/SQLcommand.Ifyouaredisablingarole,itmustcontainthenameoftheroleyouwishtodisable.Youcannotuseawildcard.See"DisablingCommandswithSQLPLUS-RESTRICT"forinformationondisablingarole.
DATE_VALUE
Notused,itisrecommendedthatyouenterNULL.OtherproductsmaystoreDATEvaluesinthiscolumn.
LONG_VALUE
Notused,itisrecommendedthatyouenterNULL.OtherproductsmaystoreLONGvaluesinthiscolumn.
PUPTableAdministration
TheDBAusernameSYSTEMownsandhasallprivilegesonthePUPtable.OtherOracleDatabaseusernamesshouldhaveonlySELECTaccesstothistable,whichenablesaviewofrestrictionsforthatusernameandthoserestrictionsassignedtoPUBLIC.ThescriptPUPBLD.SQL,whenrun,grantsSELECTaccessonthePUPtabletoPUBLIC.
DisablingSQL*Plus,SQL,andPL/SQLCommands
TodisableaSQLorSQL*Pluscommandforagivenuser,insertarowcontainingtheuser'susernameintheUseridcolumn,thecommandnameintheAttributecolumn,andDISABLEDintheChar_Valuecolumn.TheScope,Numeric_Value,andDate_ValuecolumnsshouldcontainNULL.Forexample:
PRODUCTUSERIDATTRIBUTESCOPENUMBERICCHARDATELONG
VALUEVALUEVALUEVALUE
---------------------------------------------------
SQL*PlusHRHOSTDISABLED
SQL*Plus%INSERTDISABLED
SQL*Plus%UPDATEDISABLED
SQL*Plus%DELETEDISABLED
Tore-enablecommands,deletetherowcontainingtherestriction.
SQL*PlusCommandsThatCanBeDisabled
ACCEPT
DEFINE
PASSWORD
SHUTDOWN
APPEND
DEL
PAUSE
SPOOL
ARCHIVELOG
DESCRIBE
PRINT
START（@,@@）
ATTRIBUTE
DISCONNECT
PROMPT
STARTUP
BREAK
EDIT
RECOVER
STORE
BTITLE
EXECUTE
REMARK
TIMING
CHANGE
EXIT/QUIT
REPFOOTER
TTITLE
CLEAR
GET
REPHEADER
UNDEFINE
COLUMN
HELP（?
）
RUN
VARIABLE
COMPUTE
HOST
SAVE
WHENEVEROSERROR
CONNECT
INPUT
SET
WHENEVERSQLERROR
COPY
LIST（;）
SHOW
SQLCommandsThatCanBeDisabled
ALTER
DELETE
MERGE
SETCONSTRAINTS
ANALYZE
DISASSOCIATE
NOAUDIT
SETROLE
ASSOCIATE
DROP
PURGE
SETTRANSACTION
AUDIT
EXPLAIN
RENAME
TRUNCATE
CALL
FLASHBACK
REVOKE
UPDATE
COMMENT
GRANT
ROLLBACK
VALIDATE
COMMIT
INSERT
SAVEPOINT
na
CREATE
LOCK
SELECT
na
YoucandisablethefollowingPL/SQLcommands:
PL/SQLCommandsThatCanBeDisabled
BEGIN
DECLARE
na
na
Notes:
∙DisablingHOSTdisablestheoperatingsystemaliasforHOST,suchas$onWindows,and!
onUNIX.
∙DisablingLISTdisables;andnumbers（numbersenteredtogotothatlineinascript）.
∙YoumustdisableHELPand?
separatelytodisableaccesstocommand-linehelp.
∙DisablingtheSQL*PlusSETcommandalsodisablesSQLSETCONSTRAINTS,SETROLEandSETTRANSACTION.
∙DisablingSQL*PlusSTARTalsodisables@and@@.
∙DisablingBEGINandDECLAREdoesnotpreventtheuseofSQL*PlusEXECUTEtorunPL/SQL.EXECUTEmustbedisabledseparately.
∙DisablingEXIT/QUITisnotrecommended.Ifdisabled,terminateacommand-linesessionbysendinganEOFcharactersuchasCtrl+DinUNIXorCtrl+ZinWindows.TerminateaWindowsGUIsessionwithFile>Exit.Otherwise,terminateasessionbyterminatingtheSQL*Plusprocess.Ifdisabled,usingEXIT/QUITtoterminatethecurrentlyrunningscriptiniSQL*Plusisalsodisabled.Ifdisabled,theEXIToperationinWHENEVEROSERRORandWHENEVERSQLERRORisalsodisabled.
Example10-1SettingRestrictionsinthePUPTable
ThisisanexampleofhowtoinsertarowintothePUPtabletorestricttheuserHRfromusingtheSELECTstatement:
1.LoginasSYSTEMwiththecommand
2.SQLPLUSSYSTEM/your_password
3.InsertarowintothePUPtablewiththecommand:
4.INSERTINTOPRODUCT_USER_PROFILE
5.VALUES（'SQL*Plus','HR','SELECT',NULL,NULL,'DISABLED',NULL,NULL）;
6.ConnectasHRandtrytoSELECTsomething:
7.CONNECTHR/your_password;
8.SELECT*FROMEMP_DETAILS_VIEW;
Thiscommandcausesthefollowingerrormessage:
SP2-0544:
CommandSELECTdisabledinProductUserProfile
9.TodeletethisrowandremovetherestrictionfromtheuserHR,CONNECTagainasSYSTEMandenter:
10.DELETEFROMPRODUCT_USER_PROFILEWHEREUSERID='HR';
CreatingandControllingRoles
YoucanuseSQLcommandstocreateandcontrolaccesstorolestoprovidesecurityforyourdatabasetables.Bycreatingaroleandthencontrollingwhohasaccesstoit,youcanensurethatonlycertainusershaveaccesstoparticulardatabaseprivileges.
RolesarecreatedandusedwiththeSQLCREATE,GRANT,andSETcommands:
∙Tocreatearole,youusetheCREATEcommand.Youcancreateroleswithorwithoutpasswords.
∙Tograntaccesstoroles,youusetheGRANTcommand.Inthisway,youcancontrolwhohasaccesstotheprivilegesassociatedwiththerole.
∙Toaccessroles,youusetheSETROLEcommand.Ifyoucreatedtherolewithapassword,theusermustknowthepasswordinordertoaccesstherole.
Formoreinformationaboutroles,seeyourOracleDatabaseSQLReference,yourOracleDatabaseAdministrator'sGuide,andyourOracleDatabaseConceptsmanual.
DisablingSETROLE
FromSQL*Plus,userscansubmitanySQLcommand.Incertainsituations,thiscancausesecurityproblems.Unlessyoutakeproperprecautions,ausercoulduseSETROLEtoaccessprivilegesobtainedthroughanapplicationrole.Withtheseprivileges,theymightissueSQLstatementsfromSQL*Plusthatcouldwronglychangedatabasetables.
TopreventapplicationusersfromaccessingapplicationrolesinSQL*Plus,youcanusethePUPtabletodisabletheSETROLEcommand.YoualsoneedtodisabletheBEGINandSQL*PlusEXECUTEcommandstopreventapplicationuserssettingapplicationrolesthroughaPL/SQLblock.ThisgivesaSQL*PlususeronlythoseprivilegesassociatedwiththerolesenabledwhentheystartedSQL*Plus.Formoreinformationaboutthecreationandusageofuserroles,seeyourOracleDatabaseSQLReferenceandOracleDatabaseAdministrator'sGuide.
DisablingUserRoles
Todisablearoleforagivenuser,insertarowinthePUPtablecontainingtheuser'susernameintheUseridcolumn,"ROLES"intheAttributecolumn,andtherolenameintheChar_Valuecolumn.
Note:
Whenyouenter"PUBLIC"or"%"for
升级会员 