In Oracle 11g User Management, each Oracle database has a list of valid database users. To access a database, a user must run a database application and connect to the database instance using a valid username defined in the database. This section explains how to manage users for a database
The following are some of the activities we perform on user management.
How to create a user in Oracle database
|
1 2 3 4 5 6 7 |
SYS> SYS> create user TESTUSER identified by TESTUSER default tablespace USERTS; User created. SYS> |
How to assign default tablespace and quota to a user
|
1 2 3 4 5 6 7 |
SYS> SYS> alter user TESTUSER default tablespace USERTS quota 5M on USERTS; User altered. SYS> |
How to grant unlimited tablespace to a user in Oracle 11g
|
1 2 3 4 5 |
SYS> SYS> grant unlimited tablespace to TESTUSER; Grant succeeded. |
How to assign a temporary tablespace and a profile to a user
|
1 2 3 4 5 6 7 |
SYS> SYS> alter user TESTUSER temporary tablespace TEMP profile default; User altered. SYS> |
How to grant role and privilege to a user in Oracle 11g
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
SYS> SYS> SYS> create role userrole; Role created. SYS> grant create session, create table to userrole; Grant succeeded. SYS> grant userrole to TESTUSER; Grant succeeded. SYS> grant create materialized view to TESTUSER; Grant succeeded. SYS> |
How to Grant the admin option
|
1 2 3 4 5 6 7 |
SYS> SYS> grant create table to TESTUSER with admin option; Grant succeeded. |
How to create a profile and assign it to user
|
1 2 3 4 5 6 7 8 9 |
SYS> create profile profile1 limit idle_time 30; Profile created. SYS> alter user TESTUSER profile profile1; User altered. |
How to revoke privileges and roles from oracle user
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
SYS> SYS> revoke create table from userrole; Revoke succeeded. SYS> drop profile PROFILE1; drop profile PROFILE1 * ERROR at line 1: ORA-02382: profile PROFILE1 has users assigned, cannot drop without CASCADE SYS> drop profile PROFILE1 cascade; Profile dropped. |
How to drop a user
|
1 2 3 4 5 |
SYS> SYS> drop user TESTUSER cascade; User dropped. |
Some of the important dictionary views include
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
ALL_USERS DBA_USERS USER_USERS DBA_USERS_WITH_DEFFPWD RESOURCE_COST USER_RESOURCE_LIMITS V$SESSION ALL_TAB_PRIVS DBA_TAB_PRIVS DBA_ROLES DBA_ROLE_PRIVS DBA_SYS_PRIVS USER_TS_QUOTAS DBA_TS_QUOTAS DBA_PROFILES USER_PASSWORD_LIMITS V$SESSTAT ROLE_SYS_PRIVS ROLE_TAB_PRIVS SESSION_ROLES SESSION_PRIVS |
Words from dbapath
Thank you for giving your valuable time to read the above information. I hope the content served your purpose in reaching out the blog.
Suggestions for improvement of the blog are highly appreciable. Please contact us for any information/suggestion/feedback.
If you want to be updated with all our articles
please follow us on Facebook | Twitter
Please subscribe to our newsletter.