oracle create user - ghdrako/doc_snipets GitHub Wiki

Utworzenie user-a

CREATE USER nowy_user IDENTIFIED BY MocneHaslo123
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON USERS;

-- wymuszenie wygasniecia hasla
ALTER USER nowy_user PASSWORD EXPIRE;

-- Role standardowe
GRANT CONNECT TO nowy_user;
GRANT RESOURCE TO nowy_user;

-- Nadanie uprawnień systemowych
GRANT CREATE SESSION TO nowy_user;
GRANT SELECT ANY TABLE TO nowy_user;
--GRANT UNLIMITED TABLESPACE TO nowy_user;
--GRANT SELECT ANY DICTIONARY TO nowy_user;

ANALIZA ISTNIEJĄCYCH UŻYTKOWNIKÓW

1. Lista aktywnych użytkowników aplikacyjnych (pomijamy Oracle systemowe)

COLUMN username FORMAT A20
COLUMN account_status FORMAT A20
COLUMN default_tablespace FORMAT A20
COLUMN temporary_tablespace FORMAT A20
COLUMN created FORMAT A20

-- Zapytanie
SELECT username,
       account_status,
       default_tablespace,
       temporary_tablespace,
       created
FROM dba_users
WHERE username NOT IN ('SYS', 'SYSTEM')
  AND ORACLE_MAINTAINED = 'N'
ORDER BY created DESC;

COLUMN grantee FORMAT A20
COLUMN granted_role FORMAT A20

SELECT grantee, granted_role, admin_option
FROM dba_role_privs
WHERE grantee IN (
    SELECT username FROM dba_users WHERE ORACLE_MAINTAINED = 'N'
)
ORDER BY grantee;

SELECT grantee, privilege
FROM dba_sys_privs
WHERE grantee IN (
    SELECT role FROM dba_roles
    WHERE ORACLE_MAINTAINED = 'N'
)
ORDER BY grantee;