如何创建一个Oracle存储过程来锁定超过90天未登录数据库的用户帐户 [英] How to create an Oracle stored procedure to lock user accounts not logged onto database for more than 90 days

问题描述

如何创建一个Oracle存储过程来锁定90天内未登录数据库的用户帐户?

How do I create an Oracle Stored Procedure to lock user accounts that's not logged onto the database for more than 90 days?

我假设我可以创建一个配置文件对象并在数据库中列出资源参数，但是我使用的是Oracle 11g，看来该功能不存在.

I assumed that I could create a Profile Object and list the Resource Parameters in my database but I am using Oracle 11g and it appears that feature doesn't exist.

这是我到目前为止尝试过的:

This is what I tried so far:

select username from dba_audit_trail
where  action_name = 'LOGON'
group  by username
having max(timestamp) < sysdate - 90

我还尝试创建一个配置文件，尝试使用INACTIVE_ACCOUNT_TIME作为我的资源参数，但是它在Oracle 11g中不存在:

I also attempted to create a profile attempting to use INACTIVE_ACCOUNT_TIME as my Resource Parameters but it does not exist in Oracle 11g:

CREATE PROFILE time_limit LIMIT 
INACTIVE_ACCOUNT_TIME 90;

推荐答案

对于12c，它可以是:

For 12c it can be:

begin
for x in 
(select username
  from dba_users
  where last_login < sysdate - 90) 
loop
  execute immediate 'alter user ' || x.username || ' account lock';
end loop;
end;

如果您使用的是11g，则可以尝试查询循环:

If you're using 11g you may try your query for loop:

begin
for x in 
(select username from dba_audit_trail
 where  action_name = 'LOGON'
 group  by username
 having max(timestamp) < sysdate - 90) 
loop
  execute immediate 'alter user ' || x.username || ' account lock';
end loop;
end;

这篇关于如何创建一个Oracle存储过程来锁定超过90天未登录数据库的用户帐户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！