User Management

 
How to create users in the Oracle database?

create user AMEY identified by "Test123$" DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;
grant connect, resource to AMEY;
grant create session to AMEY;

Creating and assigning profile to user:

CREATE PROFILE AMEYS_PROFILE LIMIT

  FAILED_LOGIN_ATTEMPTS 3  -- Account locked after 3 failed logins.

  PASSWORD_LOCK_TIME 5     -- Number of days account is locked for. UNLIMITED required explicit unlock by DBA.

  PASSWORD_LIFE_TIME 30    -- Password expires after 90 days.

  PASSWORD_GRACE_TIME 3    -- Grace period for password expiration.

  PASSWORD_REUSE_TIME 120  -- Number of days until a specific password can be reused. UNLIMITED means never.

  PASSWORD_REUSE_MAX 10    -- The number of changes required before a password can be reused. UNLIMITED means never.

/

ALTER USER AMEY PROFILE AMEYS_PROFILE;

how to get metadata of user?
set lines 200
set long 1000
set pagesize 1000
select dbms_metadata.get_ddl('USER','AMEY')|| '; '  from dual;
select 'grant ' || privilege || ' to ' || grantee ||'; ' from dba_sys_privs where grantee='AMEY' order by grantee;
select 'grant ' || granted_role || ' to ' || grantee ||'; ' from dba_role_privs where grantee='AMEY' order by grantee;
select 'Grant ' || PRIVILEGE  || ' on ' || OWNER || '.' || TABLE_NAME || ' to ' || grantee  ||'; '  from dba_TAB_PRIVS where GRANTEE in ('AMEY');

How to get the Size of database user?

select sum(bytes)/1024/1024 from dba_segments where owner='AMEY';
Or
select sum(bytes)/1024/1024/1024 from dba_segments where owner='&1';

How to get encrypted password of Oracle User?
select name, password from sys.user$ where name='AMEY';

How to put the same password for the user?

CREATE USER AT
  IDENTIFIED BY VALUES '28D48922ED178DA7'
  DEFAULT TABLESPACE AT
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT
  ACCOUNT UNLOCK;

how to check objects of user?
select OBJECT_TYPE,count(OBJECT_NAME) from dba_objects where owner='&1' group by OBJECT_TYPE;

Object Count for user:

select count(1) from dba_objects where owner='AMEY';

How Grant Read-Only access to one user to Another?
select 'grant select on ' ||OWNER||'.'||TABLE_NAME|| ' to AMIT;' from dba_tables where owner='AMEY';

To Check provided Grants:
select * from dba_tab_privs where grantee='AMIT' and grantor='AMEY';