最近群里好多人讨论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技巧】相关文章：