This article is about oracle daily maintaince, mainly contains dba_users, v$session and dba_profiles the three parts.

Manage oracle users

Each Oracle database has a list of valid database users. To access a database, a user must run a database application and connect to the database instance using a valid user name defined in the database.

See the below example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
DEFINE USERNAME = 'BD_ROGER'

-- drop user completely
DROP USER &USERNAME CASCADE;

-- create user completely
CREATE USER &USERNAME
IDENTIFIED BY &USERNAME
DEFAULT TABLESPACE DATAUSR
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;

-- 2 Roles for &USERNAME
GRANT RESOURCE TO &USERNAME;
GRANT CONNECT TO &USERNAME;
ALTER USER &USERNAME DEFAULT ROLE ALL;

-- 12 System Privileges for &USERNAME
GRANT CREATE ANY TABLE TO &USERNAME;
GRANT CREATE ANY INDEX TO &USERNAME;
GRANT LOCK ANY TABLE TO &USERNAME;
GRANT CREATE ANY TRIGGER TO &USERNAME;
GRANT CREATE SEQUENCE TO &USERNAME;
GRANT CREATE VIEW TO &USERNAME;
GRANT MANAGE TABLESPACE TO &USERNAME;
GRANT UNLIMITED TABLESPACE TO &USERNAME;
GRANT CREATE JOB TO &USERNAME;
GRANT SELECT ANY TABLE TO &USERNAME;
GRANT DROP ANY TABLE TO &USERNAME;
GRANT ALTER ANY TABLE TO &USERNAME;

-- 2 Object Privileges for &USERNAME
GRANT EXECUTE ON SYS.DBMS_CRYPTO TO &USERNAME;
GRANT EXECUTE ON SYS.DBMS_REDEFINITION TO &USERNAME;
GRANT EXECUTE on DBMS_AQADM to &USERNAME;
GRANT EXECUTE on DBMS_AQ to &USERNAME;
GRANT CREATE MATERIALIZED VIEW to &USERNAME;

Users and Profiles overview

1
2
3
4
5
6
7
8
9
10
11
12
13
14
TABLE NAME 			DESCRIPTION
----------- --------------------------------------------
DBA_USERS Describes all users of the database
ALL_USERS Lists users visible to the current user, but does not describe them
USER_USERS Describes only the current user
DBA_TS_QUOTAS/USER_TS_QUOTAS Describes tablespace quotas for users
USER_PASSWORD_LIMITS Describes the password profile parameters that are assigned to the user
USER_RESOURCE_LIMITS Displays the resource limits for the current user
DBA_PROFILES Displays all profiles and their limits
RESOURCE_COST Lists the cost for each resource
V$SESSION Lists session information for each current session, includes user name
V$SESSTAT Lists user session statistics
V$STATNAME Displays decoded statistic names for the statistics shown in the V$SESSTAT view
PROXY_USERS Describes users who can assume the identity of other users
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
--list all users with account status and profile
SELECT USERNAME, PROFILE, ACCOUNT_STATUS FROM DBA_USERS;

-- list expired user and combine sql to execute
select 'alter user ' || username || ' identified by ' || username || ' account unlock;'
from dba_users
where account_status like '%EXPIRED%' and Username like '%ROGER';

-- list currently database profiles
select profile, resource_name, resource_type, limit from dba_profiles;

PROFILE RESOURCE NAME TYPE LIMIT
---------------------------------------------------
DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED
DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED
DEFAULT CPU_PER_SESSION KERNEL UNLIMITED
DEFAULT CPU_PER_CALL KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED
DEFAULT IDLE_TIME KERNEL UNLIMITED
DEFAULT CONNECT_TIME KERNEL UNLIMITED
DEFAULT PRIVATE_SGA KERNEL UNLIMITED
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10
DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL
DEFAULT PASSWORD_LOCK_TIME PASSWORD 1
DEFAULT PASSWORD_GRACE_TIME PASSWORD 7


-- PS. EXPIRED(GRACE) - 宽限期限已过期
-- modify profile password never expired
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

see the example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Profile Resource Limits
CREATE PROFILE app_user LIMIT
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL 3000
CONNECT_TIME 45
LOGICAL_READS_PER_SESSION DEFAULT
LOGICAL_READS_PER_CALL 1000
PRIVATE_SGA 15K
COMPOSITE_LIMIT 5000000;


-- Profile Password Limits
CREATE PROFILE app_user2 LIMIT
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LIFE_TIME 60
PASSWORD_REUSE_TIME 60
PASSWORD_REUSE_MAX 5
PASSWORD_VERIFY_FUNCTION verify_function
PASSWORD_LOCK_TIME 1/24
PASSWORD_GRACE_TIME 10;

Sessions

1
2
3
4
5
6
-- list all session with status, os run user, machine name, and which program
select sid, serial#, username, status, osuser, machine, program, module from v$session;


-- kill session by sid & serial# value
ALTER SYSTEM KILL SESSION '6, 16409' IMMEDIATE;

Privilege and Role overview

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
# Table Name - Description

DBA_COL_PRIVS
ALL_COL_PRIVS
USER_COL_PRIVS
# DBA view describes all column object grants in the database.
# ALL view describes all column object grants for which the current user or PUBLIC is the object owner, grantor, or grantee. # USER view describes column object grants for which the current user is the object owner, grantor, or grantee.

ALL_COL_PRIVS_MADE
USER_COL_PRIVS_MADE
# ALL view lists column object grants for which the current user is object owner or grantor.
# USER view describes column object grants for which the current user is the grantor.

ALL_COL_PRIVS_RECD
USER_COL_PRIVS_RECD
# ALL view describes column object grants for which the current user or PUBLIC is the grantee.
# USER view describes column object grants for which the current user is the grantee.

DBA_TAB_PRIVS
ALL_TAB_PRIVS
USER_TAB_PRIVS
# DBA view lists all grants on all objects in the database.
# ALL view lists the grants on objects where the user or PUBLIC is the grantee.
# USER view lists grants on all objects where the current user is the grantee.

ALL_TAB_PRIVS_MADE
USER_TAB_PRIVS_MADE
# ALL view lists the all object grants made by the current user or made on the objects owned by the current user.
# USER view lists grants on all objects owned by the current user.

ALL_TAB_PRIVS_RECD
USER_TAB_PRIVS_RECD
# ALL view lists object grants for which the user or PUBLIC is the grantee.
# USER view lists object grants for which the current user is the grantee.

DBA_ROLES
# This view lists all roles that exist in the database.

DBA_ROLE_PRIVS
USER_ROLE_PRIVS
# DBA view lists roles granted to users and roles.
# USER view lists roles granted to the current user.

DBA_SYS_PRIVS
USER_SYS_PRIVS
# DBA view lists system privileges granted to users and roles.
# USER view lists system privileges granted to the current user.

ROLE_ROLE_PRIVS
# This view describes roles granted to other roles.
# Information is provided only about roles to which the user has access.

ROLE_SYS_PRIVS
# This view contains information about system privileges granted to roles.
# Information is provided only about roles to which the user has access.

ROLE_TAB_PRIVS
# This view contains information about object privileges granted to roles.
# Information is provided only about roles to which the user has access.

SESSION_PRIVS
# This view lists the privileges that are currently enabled for the user.

SESSION_ROLES
# This view lists the roles that are currently enabled to the user.

Resources:
http://kannan-oracle.blogspot.com/2012/06/how-to-show-user-profile-and-change-its.html