User access to database within limits of time-CSDN博客

本文展示了如何在Oracle数据库中创建用户、授予权限，并通过数据库触发器实现特定时间段内禁止用户登录的功能。具体步骤包括使用SQL*Plus连接到数据库、创建新用户、设置登录限制等。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

C:\Documents and Settings\Administrator>sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jul 28 14:56:58 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create user kam identified by kam;

User created.

SQL> grant connect, resource to kam;

Grant succeeded.

SQL> conn kam/kam
Connected.
SQL> disc
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options
SQL> conn / as sysdba
Connected.
SQL> CREATE OR REPLACE TRIGGER limit_connection
2 AFTER LOGON ON DATABASE
3 BEGIN
4 IF USER = 'KAM' THEN
5 IF to_number(TO_CHAR (SYSDATE, 'hh24')) BETWEEN 8 AND 22
6 THEN
7 RAISE_APPLICATION_ERROR(-20998,' Dear user '||USER||'! You can''t login between 08 and 22');
8 END IF;
9 END IF;
10 END limit_connection;
11 /

Trigger created.

SQL> select to_char(sysdate,'hh24') from dual;

TO
--
23

SQL> conn kam/kam
Connected.
SQL> select to_char(sysdate,'hh24') from dual;

TO
--
18

SQL> conn kam/kam
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20998: Dear user KAM! You can't login between 08 and 22
ORA-06512: at line 5

Warning: You are no longer connected to ORACLE.