首页 » ORACLE 9i-23ai » How to limit program access to database(限止连接数据库使用程序)

How to limit program access to database(限止连接数据库使用程序)

  • 2013/01/06
  • ORACLE 9i-23ai
  • 449 views
  • How to limit program access to database(限止连接数据库使用程序)已关闭评论

有时出于安全考虑限制个别用户只允许特定程序连接或连接数，比如下面的例子是只允许ANBOB用户使用pl/sql dev连接此数据库。

/* Formatted on 2013-1-6 11:12:03 (QP5 v5.185.11230.41888) */
CREATE OR REPLACE TRIGGER BLOCK_TOOLS_LOGON
   AFTER LOGON
   ON DATABASE
DECLARE
   l_exception   EXCEPTION;
   l_prog        VARCHAR2 (30);
   PRAGMA EXCEPTION_INIT (l_exception, -20110);
BEGIN
   IF (SYS_CONTEXT ('USERENV', 'SESSION_USER') IN ('ANBOB'))       --user list
   THEN
      SELECT program
        INTO l_prog
        FROM sys.v_$session
       WHERE audsid = USERENV ('SESSIONID');

      IF UPPER (l_prog) NOT LIKE 'PLSQLDEV%'
      THEN
         RAISE l_exception;
      END IF;
   END IF;
EXCEPTION
   WHEN l_exception
   THEN
      RAISE_APPLICATION_ERROR (
         -20110,
            'Account :'
         || SYS_CONTEXT ('USERENV', 'SESSION_USER')
         || ' try to logon the DB with '
         || l_prog
         || '  action not allowed. Please contact your DBA to help you|');
END;
/

sys@ANBOB>conn anbob/anbob
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20110: Account :ANBOB try to logon the DB with sqlplus@dbserver1 (TNS V1-V3) action not allowed. Please contact your DBA to help you|
ORA-06512: at line 21

Extend read:
当为用户设置了sessions_per_user 1 profile，用pl/sql dev连接容易报 ora-02391,原因在plsql的配置问题，一些版本默认一个窗口一个session,设置在toos–>perf–>session model, multi change to single.

打赏

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：