User related Scripts:
------------------------
Script to take user password backup in Oracle 11g
$ sqlplus "/as sysdba"
set pages 50000 lines 32767
select 'alter user ' || name || ' identified by values "' || password || "';' from sys.user$
where name in ('&name');
USER DDL
SET LONG 99999999
select dbms_metadata.get_ddl('USER','&username') from dual;
How to who has changed the user password and when
How to who has changed the user password and when
$ sqlplus "/as sysdba"
SYS> create table sachin.log (msg varchar2(1000));
Table created.
SYS> create or replace procedure sachin.p (who in varchar2, what in varchar2)
2 is
3 pragma autonomous_transaction;
4 begin
5 insert into sachin.log values (who||' modifies '||what||'''s password at '||systimestamp);
6 commit;
7 end;
8 /
Procedure created.
SYS> CREATE OR REPLACE FUNCTION verify_function
2 (username varchar2,
3 password varchar2,
4 old_password varchar2)
5 RETURN boolean IS
6 BEGIN
7 sachin.p (user, username);
8 RETURN(TRUE);
9 END;
10 /
Function created.
SYS> ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION verify_function;
Profile altered.
SYS> alter user sachin identified by sachin;
User altered.
SYS> select * from sachin.log;
MSG
--------------------------------------------------------------------------------------------------------------------
SYS modifies sachin's password at 23/04/1998 18:54:34.390 +01:00
1 row selected.
SYS> connect sachin/sachin
Connected.
sachin> password
Password changed
sachin> select * from sachin.log;
MSG
--------------------------------------------------------------------------------------------------------------------
SYS modifies sachin's password at 23/04/1998 18:54:34.390 +01:00
sachin modifies sachin's password at 23/04/1998 18:55:04.093 +01:00
2 rows selected.
USER last login details
SELECT TO_CHAR(TIMESTAMP#,'MM/DD/YY HH:MI:SS') TIMESTAMP,
USERID, AA.NAME ACTION FROM SYS.AUD$ AT, SYS.AUDIT_ACTIONS AA
WHERE AT.ACTION# = AA.ACTION
and AA.name='LOGON'
and userid in
('&User_id')
ORDER BY TIMESTAMP# DESC;
select OS_USERNAME,action_name,USERNAME,to_char(timestamp, 'DD MON YYYY hh24:mi') logon_time,
to_char(logoff_time,'DD MON YYYY hh24:mi') logoff
from dba_audit_session where username ='&user'
AND (timestamp > (sysdate - 61))
order by logon_time,username,timestamp,logoff_time;
GRANT SELECT ON SCHEMA OBJECTS
GRANT SELECT ON SCHEMA OBJECTS TO USER THROUGH ROLE
set heading off;
select 'grant select on '||owner|| '.' ||object_name || to ROLE_NAME;' from dba_objects where owner='SCHEMA_NAME';
grant ROLE_NAME to USER_NAME;
Find a role
select * from dba_roles where role like '&role'
/
Show what roles are granted to a userselect grantee,granted_role,admin_option from dba_role_privs
where grantee like ('&username')
/
Show what table privileges are granted to a role
select owner || '.' || table_name "TABLE",column_name,privilege,grantable
from role_tab_privs where role like '&role'
/
Show what system privileges are granted to a role
select privilege,admin_option from role_sys_privs where role like '&role'
/
GRANT READ WRITE ON SCHEMA OBJECTS
select 'grant read,write on '||owner||'.'||object_name||' to '||'YOURREADWRITE_USER'||';' from all_objects where
owner='&YOUROBJECT_OWNER';
GRANT USER ACCESS ON SCHEMA
user needs read only access on existing schema
----------------------------------------------
select 'grant '||decode(object_type,'TABLE','select','VIEW','select','SEQUENCE','SELECT ','PROCEDURE','EXECUTE
','PACKAGE','EXECUTE ','TRIGGER','EXECUTE ','FUNCTION','EXECUTE ')|| ' on '||owner||'.'||object_name||' to target user'
from dba_objects where OWNER='source user' and object_type not in ('INDEX','PACKAGE BODY','DATABASE LINK','LOB');
user needs full access on existing schema
-----------------------------------------
select 'grant '||decode(object_type,'TABLE','select,insert,delete,update ','VIEW','select,insert,delete,update
','SEQUENCE','SELECT ','PROCEDURE','EXECUTE ','PACKAGE','EXECUTE ','TRIGGER','EXECUTE ','FUNCTION','EXECUTE ')|| ' on '||
owner||'.'||object_name||' to targetuser' from dba_objects where OWNER='source user' and object_type not in
('INDEX','PACKAGE BODY','DATABASE LINK','LOB');
Related:
-------
Running the SQL*Plus script below (substituting &Owner and &NewUser) will produce a listing of all the permissions to allow
the New User to access all the objects owned by OWNER. Review the output of the script and then run it to Grant the new
permissions to NewUser.
Set pagesize 0
define OWNER=
define NEWUSER=
Spool new_grants.txt
Select
decode(OBJECT_TYPE,
'TABLE','GRANT SELECT, INSERT, UPDATE, DELETE , REFERENCES ON '||'&OWNER'||'.',
'VIEW','GRANT SELECT ON '||'&OWNER'||'.',
'SEQUENCE','GRANT SELECT ON '||'&OWNER'||'.',
'PROCEDURE','GRANT EXECUTE ON '||'&OWNER'||'.',
'PACKAGE','GRANT EXECUTE ON '||'&OWNER'||'.',
'FUNCTION','GRANT EXECUTE ON '||'&OWNER'||'.' )||object_name||' TO &NewUser;'
From USER_OBJECTS where OBJECT_TYPE IN ( 'TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE', 'PACKAGE', 'FUNCTION')
Order By OBJECT_TYPE;
Spool Off;
exit
DBA Audit Trail
col USERNAME for a10
col OS_USERNAME for a10
col USERHOST for a15
col ACTION_NAME for a11
set pages 200 lines 100
alter session set nls_date_format='DD-MON-YY HH24:MI:SS';
select USERNAME,OS_USERNAME,USERHOST,TIMESTAMP,ACTION_NAME, LOGOFF_TIME from dba_audit_trail where username='&USERNAME'
order by USERHOST;
USER
Schema DDL
-------------
set pagesize 0
SET LONG 9999999
select dbms_metadata.get_ddl('USER','&USERNAME') FROM DUAL;
Schema Status
-------------
select username,account_status,default_tablespace,temporary_tablespace from dba_users where username='&username';
select username,tablespace_name,decode(max_bytes,-1,'unlimited',ceil(max_bytes / 1024 / 1024) || 'M' ) "QUOTA in MB" from
dba_ts_quotas where username='&username';
select grantee,granted_role || ' ' || decode(admin_option, 'NO', '', 'YES', 'with admin option') "GRANTED ROLE" from
dba_role_privs where grantee='&username';
select grantee,privilege || ' ' || decode(admin_option, 'NO', '', 'YES', 'with admin option') "PRIVILEGE" from
dba_sys_privs where grantee='&username';
select owner,sum(bytes)/1024/1024/1024 "SIZE in GB" from dba_segments where owner='&owner';
select owner,object_type,count(*) from dba_objects where owner='&owner' group by owner, object_type;
All Users passwords backup
--------------------------------
spool ./users_pwd_backup.log
select 'alter user '||username||' identified by values '||password||';' from dba_users; ( For 10g database)
select 'alter user '||name||' identified by values '||password||';' from SYS.USER$; ( For 11g database)
spool off
/
All tables owned by a user
--------------------------
set pages 9999 lines 300
col owner format a15
col tablespace_name format a15
col segment_name format a40
col segment_type format a20
col PARTITION_NAME format a20
col mb format 999,999,999
select owner
, tablespace_name
, segment_name
, segment_type
, PARTITION_NAME
, ceil(sum(bytes) / 1024 / 1024) "Size in MB"
from dba_segments
where owner like '&user'
and segment_type = 'TABLE'
group by segment_name
order by ceil(sum(bytes) / 1024 / 1024) desc
/
All schema object details in a tablespace
-----------------------------------------
set pages 9999 lines 300
col owner format a15
col tablespace_name format a15
col segment_name format a40
col segment_type format a20
col PARTITION_NAME format a20
col mb format 999,999,999
select owner
, tablespace_name
, segment_name
, segment_type
, PARTITION_NAME
, ceil(sum(bytes) / 1024 / 1024) "Size in MB"
from dba_segments
where tablespace_name like '&tablespace_name'
group by segment_name
order by ceil(sum(bytes) / 1024 / 1024) desc
/
Total space allocated by Owner:
-------------------------------
set pages 9999 lines 300
col owner format a15
col segment_name format a30
col segment_type format a15
col tablespace_name format a20
col mb format 999,999,999
select owner
, segment_name
, segment_type
, tablespace_name
, mb
from (
select owner
, segment_name
, segment_type
, tablespace_name
, bytes / 1024 / 1024 "SIZE in MB"
from dba_segments
order by bytes desc
)
/
schemas in a tablespace
-----------------------
set pages 999
col "size MB" format 999,999,999
col "Objects" format 999,999,999
select obj.owner "Owner"
, obj_cnt "Objects"
, decode(seg_size, NULL, 0, seg_size) "size MB"
from (select owner, count(*) obj_cnt from dba_objects group by owner) obj
, (select owner, ceil(sum(bytes)/1024/1024) seg_size
from dba_segments group by owner) seg
where obj.owner = seg.owner(+)
order by 3 desc ,2 desc, 1
/
------------------------
Script to take user password backup in Oracle 11g
$ sqlplus "/as sysdba"
set pages 50000 lines 32767
select 'alter user ' || name || ' identified by values "' || password || "';' from sys.user$
where name in ('&name');
USER DDL
SET LONG 99999999
select dbms_metadata.get_ddl('USER','&username') from dual;
How to who has changed the user password and when
How to who has changed the user password and when
$ sqlplus "/as sysdba"
SYS> create table sachin.log (msg varchar2(1000));
Table created.
SYS> create or replace procedure sachin.p (who in varchar2, what in varchar2)
2 is
3 pragma autonomous_transaction;
4 begin
5 insert into sachin.log values (who||' modifies '||what||'''s password at '||systimestamp);
6 commit;
7 end;
8 /
Procedure created.
SYS> CREATE OR REPLACE FUNCTION verify_function
2 (username varchar2,
3 password varchar2,
4 old_password varchar2)
5 RETURN boolean IS
6 BEGIN
7 sachin.p (user, username);
8 RETURN(TRUE);
9 END;
10 /
Function created.
SYS> ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION verify_function;
Profile altered.
SYS> alter user sachin identified by sachin;
User altered.
SYS> select * from sachin.log;
MSG
--------------------------------------------------------------------------------------------------------------------
SYS modifies sachin's password at 23/04/1998 18:54:34.390 +01:00
1 row selected.
SYS> connect sachin/sachin
Connected.
sachin> password
Password changed
sachin> select * from sachin.log;
MSG
--------------------------------------------------------------------------------------------------------------------
SYS modifies sachin's password at 23/04/1998 18:54:34.390 +01:00
sachin modifies sachin's password at 23/04/1998 18:55:04.093 +01:00
2 rows selected.
USER last login details
SELECT TO_CHAR(TIMESTAMP#,'MM/DD/YY HH:MI:SS') TIMESTAMP,
USERID, AA.NAME ACTION FROM SYS.AUD$ AT, SYS.AUDIT_ACTIONS AA
WHERE AT.ACTION# = AA.ACTION
and AA.name='LOGON'
and userid in
('&User_id')
ORDER BY TIMESTAMP# DESC;
select OS_USERNAME,action_name,USERNAME,to_char(timestamp, 'DD MON YYYY hh24:mi') logon_time,
to_char(logoff_time,'DD MON YYYY hh24:mi') logoff
from dba_audit_session where username ='&user'
AND (timestamp > (sysdate - 61))
order by logon_time,username,timestamp,logoff_time;
GRANT SELECT ON SCHEMA OBJECTS
GRANT SELECT ON SCHEMA OBJECTS TO USER THROUGH ROLE
set heading off;
select 'grant select on '||owner|| '.' ||object_name || to ROLE_NAME;' from dba_objects where owner='SCHEMA_NAME';
grant ROLE_NAME to USER_NAME;
Find a role
select * from dba_roles where role like '&role'
/
Show what roles are granted to a userselect grantee,granted_role,admin_option from dba_role_privs
where grantee like ('&username')
/
Show what table privileges are granted to a role
select owner || '.' || table_name "TABLE",column_name,privilege,grantable
from role_tab_privs where role like '&role'
/
Show what system privileges are granted to a role
select privilege,admin_option from role_sys_privs where role like '&role'
/
GRANT READ WRITE ON SCHEMA OBJECTS
select 'grant read,write on '||owner||'.'||object_name||' to '||'YOURREADWRITE_USER'||';' from all_objects where
owner='&YOUROBJECT_OWNER';
GRANT USER ACCESS ON SCHEMA
user needs read only access on existing schema
----------------------------------------------
select 'grant '||decode(object_type,'TABLE','select','VIEW','select','SEQUENCE','SELECT ','PROCEDURE','EXECUTE
','PACKAGE','EXECUTE ','TRIGGER','EXECUTE ','FUNCTION','EXECUTE ')|| ' on '||owner||'.'||object_name||' to target user'
from dba_objects where OWNER='source user' and object_type not in ('INDEX','PACKAGE BODY','DATABASE LINK','LOB');
user needs full access on existing schema
-----------------------------------------
select 'grant '||decode(object_type,'TABLE','select,insert,delete,update ','VIEW','select,insert,delete,update
','SEQUENCE','SELECT ','PROCEDURE','EXECUTE ','PACKAGE','EXECUTE ','TRIGGER','EXECUTE ','FUNCTION','EXECUTE ')|| ' on '||
owner||'.'||object_name||' to targetuser' from dba_objects where OWNER='source user' and object_type not in
('INDEX','PACKAGE BODY','DATABASE LINK','LOB');
Related:
-------
Running the SQL*Plus script below (substituting &Owner and &NewUser) will produce a listing of all the permissions to allow
the New User to access all the objects owned by OWNER. Review the output of the script and then run it to Grant the new
permissions to NewUser.
Set pagesize 0
define OWNER=
define NEWUSER=
Spool new_grants.txt
Select
decode(OBJECT_TYPE,
'TABLE','GRANT SELECT, INSERT, UPDATE, DELETE , REFERENCES ON '||'&OWNER'||'.',
'VIEW','GRANT SELECT ON '||'&OWNER'||'.',
'SEQUENCE','GRANT SELECT ON '||'&OWNER'||'.',
'PROCEDURE','GRANT EXECUTE ON '||'&OWNER'||'.',
'PACKAGE','GRANT EXECUTE ON '||'&OWNER'||'.',
'FUNCTION','GRANT EXECUTE ON '||'&OWNER'||'.' )||object_name||' TO &NewUser;'
From USER_OBJECTS where OBJECT_TYPE IN ( 'TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE', 'PACKAGE', 'FUNCTION')
Order By OBJECT_TYPE;
Spool Off;
exit
DBA Audit Trail
col USERNAME for a10
col OS_USERNAME for a10
col USERHOST for a15
col ACTION_NAME for a11
set pages 200 lines 100
alter session set nls_date_format='DD-MON-YY HH24:MI:SS';
select USERNAME,OS_USERNAME,USERHOST,TIMESTAMP,ACTION_NAME, LOGOFF_TIME from dba_audit_trail where username='&USERNAME'
order by USERHOST;
USER
Schema DDL
-------------
set pagesize 0
SET LONG 9999999
select dbms_metadata.get_ddl('USER','&USERNAME') FROM DUAL;
Schema Status
-------------
select username,account_status,default_tablespace,temporary_tablespace from dba_users where username='&username';
select username,tablespace_name,decode(max_bytes,-1,'unlimited',ceil(max_bytes / 1024 / 1024) || 'M' ) "QUOTA in MB" from
dba_ts_quotas where username='&username';
select grantee,granted_role || ' ' || decode(admin_option, 'NO', '', 'YES', 'with admin option') "GRANTED ROLE" from
dba_role_privs where grantee='&username';
select grantee,privilege || ' ' || decode(admin_option, 'NO', '', 'YES', 'with admin option') "PRIVILEGE" from
dba_sys_privs where grantee='&username';
select owner,sum(bytes)/1024/1024/1024 "SIZE in GB" from dba_segments where owner='&owner';
select owner,object_type,count(*) from dba_objects where owner='&owner' group by owner, object_type;
All Users passwords backup
--------------------------------
spool ./users_pwd_backup.log
select 'alter user '||username||' identified by values '||password||';' from dba_users; ( For 10g database)
select 'alter user '||name||' identified by values '||password||';' from SYS.USER$; ( For 11g database)
spool off
/
All tables owned by a user
--------------------------
set pages 9999 lines 300
col owner format a15
col tablespace_name format a15
col segment_name format a40
col segment_type format a20
col PARTITION_NAME format a20
col mb format 999,999,999
select owner
, tablespace_name
, segment_name
, segment_type
, PARTITION_NAME
, ceil(sum(bytes) / 1024 / 1024) "Size in MB"
from dba_segments
where owner like '&user'
and segment_type = 'TABLE'
group by segment_name
order by ceil(sum(bytes) / 1024 / 1024) desc
/
All schema object details in a tablespace
-----------------------------------------
set pages 9999 lines 300
col owner format a15
col tablespace_name format a15
col segment_name format a40
col segment_type format a20
col PARTITION_NAME format a20
col mb format 999,999,999
select owner
, tablespace_name
, segment_name
, segment_type
, PARTITION_NAME
, ceil(sum(bytes) / 1024 / 1024) "Size in MB"
from dba_segments
where tablespace_name like '&tablespace_name'
group by segment_name
order by ceil(sum(bytes) / 1024 / 1024) desc
/
Total space allocated by Owner:
-------------------------------
set pages 9999 lines 300
col owner format a15
col segment_name format a30
col segment_type format a15
col tablespace_name format a20
col mb format 999,999,999
select owner
, segment_name
, segment_type
, tablespace_name
, mb
from (
select owner
, segment_name
, segment_type
, tablespace_name
, bytes / 1024 / 1024 "SIZE in MB"
from dba_segments
order by bytes desc
)
/
schemas in a tablespace
-----------------------
set pages 999
col "size MB" format 999,999,999
col "Objects" format 999,999,999
select obj.owner "Owner"
, obj_cnt "Objects"
, decode(seg_size, NULL, 0, seg_size) "size MB"
from (select owner, count(*) obj_cnt from dba_objects group by owner) obj
, (select owner, ceil(sum(bytes)/1024/1024) seg_size
from dba_segments group by owner) seg
where obj.owner = seg.owner(+)
order by 3 desc ,2 desc, 1
/
No comments:
Post a Comment