oracle的一些tips技巧

最近群里好多人讨论oracle安全问题,今天找了些资料学习了下 获取Oracle当前会话的一些属性(对于sql注射的环境判断很有用哦) SYS_CONTEXT函数返回是与上下文名称空间相关的属性值。这个函数可以用在SQL和PL/SQL语句中。
注意:SYS_CONTEXT返回的是用户会话期间的属性，所以，你无法使用它在并行的查询或实时应用集群环境。 对于名称空间和变量,你可以定义它（们）为常量字符串，也可以定义一个变量，来代替名称空间或属性的字符串。名称空间必须是已经在数据库中定义好的,并且相关的参数和值已经被指定到DBMS_SESSION中。名称空间必须是一个合法的SQL标识符。变量名字可以是任意的字符串。它们不区分大小写,但是长度不能超出30个字节。 函数返回的数据类型是VARCHAR2，回归值的缺省最大长度是256个字节。你也可以通过设置函数参数length来修改这个缺省长度值。值的合法的长度范围是1到4000字节。(如果你指定的值不在这个范围内,Oracle将使用缺省长度。) Oracle9i提供了一个内置的"USERENV"名称空间,用来表示当前的会话信息。该名称空间预定义的参数如表1,表的最后一列标识了返回值的长度。 语法：
SYS_CONTEXT(namespace,attribute[,length]) 例子：
selectSYS_CONTEXT('USERENV','TERMINAL')TERMINAL,
SYS_CONTEXT('USERENV','LANGUAGE')LANGUAGE,
SYS_CONTEXT('USERENV','SESSIONID')SESSIONID,
SYS_CONTEXT('USERENV','INSTANCE')INSTANCE,
SYS_CONTEXT('USERENV','ENTRYID')ENTRYID,
SYS_CONTEXT('USERENV','ISDBA')ISDBA,
SYS_CONTEXT('USERENV','NLS_TERRITORY')NLS_TERRITORY,
SYS_CONTEXT('USERENV','NLS_CURRENCY')NLS_CURRENCY,
SYS_CONTEXT('USERENV','NLS_CALENDAR')NLS_CALENDAR,
SYS_CONTEXT('USERENV','NLS_DATE_FORMAT')NLS_DATE_FORMAT,
SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE')NLS_DATE_LANGUAGE,
SYS_CONTEXT('USERENV','NLS_SORT')NLS_SORT,
SYS_CONTEXT('USERENV','CURRENT_USER')CURRENT_USER,
SYS_CONTEXT('USERENV','CURRENT_USERID')CURRENT_USERID,
SYS_CONTEXT('USERENV','SESSION_USER')SESSION_USER,
SYS_CONTEXT('USERENV','SESSION_USERID')SESSION_USERID,
SYS_CONTEXT('USERENV','PROXY_USER')PROXY_USER,
SYS_CONTEXT('USERENV','PROXY_USERID')PROXY_USERID,
SYS_CONTEXT('USERENV','DB_DOMAIN')DB_DOMAIN,
SYS_CONTEXT('USERENV','DB_NAME')DB_NAME,
SYS_CONTEXT('USERENV','HOST')HOST,
SYS_CONTEXT('USERENV','OS_USER')OS_USER,
SYS_CONTEXT('USERENV','EXTERNAL_NAME')EXTERNAL_NAME,
SYS_CONTEXT('USERENV','IP_ADDRESS')IP_ADDRESS,
SYS_CONTEXT('USERENV','NETWORK_PROTOCOL')NETWORK_PROTOCOL,
SYS_CONTEXT('USERENV','BG_JOB_ID')BG_JOB_ID,
SYS_CONTEXT('USERENV','FG_JOB_ID')FG_JOB_ID,
SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE')AUTHENTICATION_TYPE,
SYS_CONTEXT('USERENV','AUTHENTICATION_DATA')AUTHENTICATION_DATA
FROMDUAL; 下面的语句返回登录用户的名字: CONNECTOE/OE
selectSYS_CONTEXT('USERENV','SESSION_USER')
FROMDUAL;
SYS_CONTEXT('USERENV','SESSION_USER')
网上找的
[code]AUTHENTICATION_DATA
Databeingusedtoauthenticatetheloginuser.ForX.503certificateauthenticatedsessions,thisfieldreturnsthecontextofthecertificateinHEX2format.
256
Note:YoucanchangethereturnvalueoftheAUTHENTICATION_DATAattributeusingthelengthparameterofthesyntax.Valuesofupto4000areaccepted.ThisistheonlyattributeofUSERENVforwhichOracleimplementssuchachange.
AUTHENTICATION_TYPE
Howtheuserwasauthenticated: DATABASE:username/passwordauthentication OS:operatingsystemexternaluserauthentication NETWORK:networkprotocolorANOauthentication PROXY:OCIproxyconnectionauthentication 30 BG_JOB_ID
JobIDofthecurrentsessionifitwasestablishedbyanOraclebackgroundprocess.Nullifthesessionwasnotestablishedbyabackgroundprocess.
30 CLIENT_INFO
Returnsupto64bytesofusersessioninformationthatcanbestoredbyanapplicationusingtheDBMS_APPLICATION_INFOpackage.
64 CURRENT_SCHEMA
Nameofthedefaultschemabeingusedinthecurrentschema.ThisvaluecanbechangedduringthesessionwithanalterSESSIONSETCURRENT_SCHEMAstatement.
30 CURRENT_SCHEMAID
Identifierofthedefaultschemabeingusedinthecurrentsession.
30 CURRENT_USER
Thenameoftheuserwhoseprivilegethecurrentsessionisunder.
30 CURRENT_USERID
UserIDoftheuserwhoseprivilegethecurrentsessionisunder
30 DB_DOMAIN
DomainofthedatabaseasspecifiedintheDB_DOMAINinitializationparameter.
256 DB_NAME
NameofthedatabaseasspecifiedintheDB_NAMEinitializationparameter
30 ENTRYID
Theavailableauditingentryidentifier.YoucannotusethisoptionindistributedSQLstatements.TousethiskeywordinUSERENV,theinitializationparameterAUDIT_TRAILmustbesettotrue.
30 EXTERNAL_NAME
Externalnameofthedatabaseuser.ForSSLauthenticatedsessionsusingv.503certificates,thisfieldreturnsthedistinguishedname(DN)storedintheusercertificate.
256 FG_JOB_ID
JobIDofthecurrentsessionifitwasestablishedbyaclientforegroundprocess.Nullifthesessionwasnotestablishedbyaforegroundprocess.
30 HOST
Nameofthehostmachinefromwhichtheclienthasconnected.
54 INSTANCE
Theinstanceidentificationnumberofthecurrentinstance.
30 IP_ADDRESS
IPaddressofthemachinefromwhichtheclientisconnected.
30 ISDBA
TRUEifyoucurrentlyhavetheDBAroleenabledandFALSEifyoudonot.
30 LANG
TheISOabbreviationforthelanguagename,ashorterformthantheexisting'LANGUAGE'parameter.
62 LANGUAGE
Thelanguageandterritorycurrentlyusedbyyoursession,alongwiththedatabasecharacterset,inthisform: language_territory.characterset
52 NETWORK_PROTOCOL
Networkprotocolbeingusedforcommunication,asspecifiedinthe'PROTOCOL=protocol'portionoftheconnectstring.
256 NLS_CALENDAR
Thecurrentcalendarofthecurrentsession.
62 NLS_CURRENCY
Thecurrencyofthecurrentsession.
62 NLS_DATE_FORMAT
Thedateformatforthesession.
62 NLS_DATE_LANGUAGE
Thelanguageusedforexpressingdates.
62 NLS_SORT
BINARYorthelinguisticsortbasis.
62 NLS_TERRITORY
Theterritoryofthecurrentsession.
62 OS_USER
Operatingsystemusernameoftheclientprocessthatinitiatedthedatabasesession
30 PROXY_USER
NameofthedatabaseuserwhoopenedthecurrentsessiononbehalfofSESSION_USER.
30 PROXY_USERID
IdentifierofthedatabaseuserwhoopenedthecurrentsessiononbehalfofSESSION_USER.
30 SESSION_USER
Databaseusernamebywhichthecurrentuserisauthenticated.Thisvalueremainsthesamethroughoutthedurationofthesession.
30 SESSION_USERID
Identifierofthedatabaseusernamebywhichthecurrentuserisauthenticated.
30 SESSIONID
Theauditingsessionidentifier.YoucannotusethisoptionindistributedSQLstatements.
30 TERMINAL
Theoperatingsystemidentifierfortheclientofthecurrentsession.IndistributedSQLstatements,thisoptionreturnstheidentifierforyourlocalsession.Inadistributedenvironment,thisissupportedonlyforremoteselectstatements,notforremoteinsert,update,ordeleteoperations.(Thereturnlengthofthisparametermayvarybyoperatingsystem.)

以上就是oracle的一些tips技巧。一个人，只要知道付出爱与关心，她内心自然会被爱与关心充满。更多关于oracle的一些tips技巧请关注haodaima.com其它相关文章！