Within Oracle, users may be managed through the webpage under the administration section, and within the Database Users subsection.

Contents

Creating and managing database user accountsEdit

Adding usersEdit

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.

Eg:

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.

Listing usersEdit

SELECT * FROM ALL_USERS

Removing usersEdit

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[1]:

DROP USER UserTest CASCADE;

Creating and managing rolesEdit

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[2]:

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

Revoking privilegesEdit

We can revoke a privilege from a user or a role:

REVOKE CONNECT FROM MyRole;


Controlling resource usage by usersEdit

Applying the principle privilegeEdit

Managing accountsEdit

Implementing standard password security featuresEdit

Auditing database activityEdit

Registering for security updatesEdit

Differentiating system privileges from object privilegesEdit

Granting privileges on tablesEdit

Viewing privileges in the data dictionaryEdit

Distinguishing between privileges and rolesEdit


ReferencesEdit

Read in another language