Users
1 min readCreate user
CREATE USER <USERNAME> IDENTIFIED BY <PASSWORD> DEFAULT TABLESPACE <TABLESPACE_NAME> TEMPORARY TABLESPACE ;
Drop user
drop user <USERNAME> cascade;
Alter user
alter user <USERNAME> identified by (account un\lock);
Backup user
export NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252
exp /@ file=.dmp log=.log buffer=1000000 owner= consistent=y
Failed logins
See failed logins (keep in mind this resets every time a successful login occurs)
select name, lcount from user$ where lcount >0 order by lcount asc;
How to get the last password changed time for a oracle user
SELECT name, ctime,ptime
FROM sys.user$
order by ptime;
CTIME Indicates – Creation Time
PTIME Indicates – Password Change Time
This is the query the metric is executing. An incident is triggered if CNT_DIFF > 0 which means that there has been at least one unsuccessful login in the past 10 minutes with more than 5 total unsuccessful logins.
select now.name, now.lcount, now.lcount - previous.lcount as cnt_diff from
(select name, lcount from sys.user$ where lcount > 5) now,
(select name, lcount from sys.user$ as of timestamp
(systimestamp - interval '10' minute) where lcount > 5) previous
where now.name = previous.name
and (select INSTANCE_NUMBER from v$instance) = (select max(inst_id) from gv$instance)
View privs
select GRANTED_ROLE from DBA_ROLE_PRIVS where Grantee='';
select PRIVILEGE from DBA_SYS_PRIVS where Grantee='';
Grant
GRANT to '';
Revoke
Revoke from '';
