1-- 1st Step
2CREATE USER user_name IDENTIFIED BY password;
3-- 2nd step
4GRANT RESOURCE TO user_name;
5GRANT CONNECT TO user_name;
6GRANT CREATE VIEW TO user_name;
7GRANT CREATE SESSION TO user_name;
8GRANT UNLIMITED TABLESPACE TO user_name;
9
1-- Oracle - User / schema creation
2CREATE USER MY_USER IDENTIFIED BY "MyPassword"
3DEFAULT TABLESPACE USERS
4TEMPORARY TABLESPACE TEMP PROFILE DEFAULT ACCOUNT UNLOCK;
5GRANT CONNECT TO MY_USER; -- To connect to DB
6GRANT RESOURCE TO MY_USER; -- Access / modify
7GRANT MY_ROLE TO MY_USER; -- Grants a role (to be defined)
8ALTER USER MY_USER DEFAULT ROLE "RESOURCE","CONNECT","MY_ROLE";
9-- Proxy access
10ALTER USER MY_USER GRANT CONNECT THROUGH MY_OTHER_USER;
11-- Checks privileges:
12SELECT PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE = MY_USER;