.::: Check user, priviledge & how to copy user to another server in Oracle Database :::.

 

1. Sample user aisyah

SELECT * FROM DBA_USERS WHERE USERNAME = 'AISYAH';
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'AISYAH';
SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'AISYAH';
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = 'AISYAH';
SELECT * FROM DBA_TS_QUOTAS WHERE USERNAME = 'AISYAH';

 
 

 

 

 

2. select info password

SELECT DBMS_METADATA.GET_DDL('USER', 'AISYAH') AS USER_DDL FROM DUAL;  

3. sample all user
SELECT username,created FROM all_users;
SELECT * FROM all_users where inherited='NO';
SELECT username, account_status, created,expiry_date, profile,last_login FROM dba_users where inherited='NO';
 

4. seakan akan create user

-- USER SQL
SELECT
  'ALTER USER "' || USERNAME || '" ' ||
  'DEFAULT TABLESPACE "' || DEFAULT_TABLESPACE || '" ' ||
  'TEMPORARY TABLESPACE "' || TEMPORARY_TABLESPACE || '" ' ||
  (CASE WHEN ACCOUNT_STATUS = 'LOCKED' THEN 'ACCOUNT LOCK;' ELSE 'ACCOUNT UNLOCK;' END) AS USER_SCRIPT
FROM DBA_USERS
WHERE USERNAME = 'AISYAH';

-- Quotas
SELECT 'ALTER USER "' || USERNAME || '" QUOTA UNLIMITED ON "' || TABLESPACE_NAME || '";' AS QUOTA_SCRIPT
FROM DBA_TS_QUOTAS
WHERE USERNAME = 'AISYAH';

-- Roles
SELECT 'ALTER USER "' || GRANTEE || '" DEFAULT ROLE ' || LISTAGG('"' || GRANTED_ROLE || '"', ',') WITHIN GROUP (ORDER BY GRANTED_ROLE) || ';' AS ROLE_SCRIPT
FROM DBA_ROLE_PRIVS
WHERE GRANTEE = 'AISYAH'
GROUP BY GRANTEE;

SELECT
  'ALTER USER "' || USERNAME || '" ' ||
  'DEFAULT TABLESPACE "' || DEFAULT_TABLESPACE || '" ' ||
  'TEMPORARY TABLESPACE "' || TEMPORARY_TABLESPACE || '" ' ||
  (CASE WHEN ACCOUNT_STATUS = 'LOCKED' THEN 'ACCOUNT LOCK;' ELSE 'ACCOUNT UNLOCK;' END) AS SCRIPT
FROM DBA_USERS
WHERE USERNAME = 'AISYAH'

UNION ALL

-- Quotas
SELECT
  'ALTER USER "' || USERNAME || '" QUOTA UNLIMITED ON "' || TABLESPACE_NAME || '";' AS SCRIPT
FROM DBA_TS_QUOTAS
WHERE USERNAME = 'AISYAH'

UNION ALL

-- Roles
SELECT
  'ALTER USER "' || GRANTEE || '" DEFAULT ROLE ' ||
  LISTAGG('"' || GRANTED_ROLE || '"', ',') WITHIN GROUP (ORDER BY GRANTED_ROLE) || ';' AS SCRIPT
FROM DBA_ROLE_PRIVS
WHERE GRANTEE = 'AISYAH'
GROUP BY GRANTEE;
 

5. from SQL Developer