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';
