oracle-用户相关笔记

发表于 分类于

记录与oracle 用户相关的笔记，用户信息查询、修改、解锁、过期等。

查询所有用户

1
select * from all_users
1
select * from dba_users;

解锁用户

1
SQL> alter user scott account unlock;

修改密码

1
2
SQL> alter user system identified by manager;
User altered.

查询当前用户

1
2
3
4
SQL> select user from dual;
USER
------------------------------
SCOTT

查看用户过期时间

注意用户名为大写

1
2
3
4
5
SQL> select username, account_status, EXPIRY_DATE from dba_users where username='HIOP';

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DATE
------------------------------ -------------------------------- -----------
HIOP                           OPEN                             2020/3/15 1

用户过期解决

使用 sysdba 角色进行操作

1
2
3
4
5
6
7
SQL> connect sys/secret_password as sysdba;

解锁用户
SQL> alter user sysman account unlock ;

修改原密码，改成旧的也可以
SQL> alter user DBUSER identified by newpa$$word;

将用户密码设置为永不过期

1
2
3
4
5
6
7
8
9
10
11
12
13
SQL> alter profile DEFAULT limit PASSWORD_REUSE_TIME unlimited;

Profile altered

SQL> alter profile DEFAULT limit PASSWORD_LIFE_TIME  unlimited;

Profile altered

SQL> select username, account_status, EXPIRY_DATE from dba_users where username='HIOP';

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DATE
------------------------------ -------------------------------- -----------
HIOP                           OPEN
0%
隐藏