Oracle Database/Administering Users
Within Oracle, users may be managed through the webpage under the administration section, and within the Database Users subsection.
- 1 Creating and managing database user accounts
- 2 Creating and managing roles
- 3 Revoking privileges
- 4 Modifying privileges
- 5 Deleting a role
- 6 Controlling resource usage by users
- 7 Applying the principle privilege
- 8 Managing accounts
- 9 Implementing standard password security features
- 10 Auditing database activity
- 11 Registering for security updates
- 12 Differentiating system privileges from object privileges
- 13 Granting privileges on tables
- 14 Viewing privileges in the data dictionary
- 15 Distinguishing between privileges and roles
- 16 References
Creating and managing database user accounts
New users can be created by an administrator or a user who has a "CREATE USER" privilege using Oracle Enterprise Manager GUI by clicking on the "Create" link in the "Users" section . Enter the username and password for the new user. You can also set the password to expire (where the user must change the password on the next login), and whether an account is locked (to prevent a user from connecting to the system).
In most cases, users should belong to the Connect role. If the user needs to create tables or have more advanced capability, the user should be placed within the Resource role. Database admins should appear in the DBA role. If desired, users may be given individual permissions within Directly Grant Privileges section.
Alternatively, users can also be created using the "CREATE USER" SQL command.
CREATE USER UserTest IDENTIFIED BY MyComplexPassword DEFAULT TABLESPACE Wikibooks PASSWORD EXPIRE QUOTA UNLIMITED ON Wikibooks; GRANT CONNECT TO UserTest;
After creating the user, the "CONNECT" role needs to be granted for the user to connect to the database.
SELECT * FROM ALL_USERS
When a user no longer needs to be present within the database, you click the drop button within the web interface.
To script it:
DROP USER UserTest;
To remove the attached objects to the user:
DROP USER UserTest CASCADE;
Creating and managing roles
The roles list is available with:
SELECT * FROM DBA_ROLES;
CONNECT RESOURCE DBA SELECT_CATALOG_ROLE EXECUTE_CATALOG_ROLE DELETE_CATALOG_ROLE EXP_FULL_DATABASE IMP_FULL_DATABASE LOGSTDBY_ADMINISTRATOR DBFS_ROLE AQ_ADMINISTRATOR_ROLE AQ_USER_ROLE DATAPUMP_EXP_FULL_DATABASE DATAPUMP_IMP_FULL_DATABASE ADM_PARALLEL_EXECUTE_TASK GATHER_SYSTEM_STATISTICS XDB_WEBSERVICES_OVER_HTTP RECOVERY_CATALOG_OWNER SCHEDULER_ADMIN HS_ADMIN_SELECT_ROLE HS_ADMIN_EXECUTE_ROLE HS_ADMIN_ROLE OEM_ADVISOR OEM_MONITOR PLUSTRACE CTXAPP XDBADMIN XDB_SET_INVOKER AUTHENTICATEDUSER XDB_WEBSERVICES XDB_WEBSERVICES_WITH_PUBLIC APEX_ADMINISTRATOR_ROLE
Personalized roles can be created:
CREATE ROLE MyRole; GRANT CONNECT TO MyRole; GRANT DBA TO MyROLE;
Then it's possible to use it with the users:
GRANT MyROLE TO UserTest
We can revoke a privilege from a user or a role:
REVOKE CONNECT FROM MyRole;
We can change a password with:
ALTER ROLE MyRole IDENTIFIED BY MyPassword;
Deleting a role
We can remove a role:
DROP ROLE MyRole;
Controlling resource usage by users
| This section is a stub.
You can help Wikibooks by expanding it.
Applying the principle privilege
Implementing standard password security features
Auditing database activity
Registering for security updates
Differentiating system privileges from object privileges
Granting privileges on tables
Viewing privileges in the data dictionary
Distinguishing between privileges and roles