Updated February 27, 2023
Introduction to Oracle Users
Oracle User can be defined as someone who wants to connect to the Oracle database and to complete that operation of using or connecting to any Oracle database of any version, the person should have an account created in the Oracle database stored in the server or local computer and should also have a unique password to get actually get access to the account or schema (schema means oracle user plus the collection of data objects owned by a particular user) owned by that user.
Users Operations & Roles in Oracle
In the earlier section of this article, we discussed about the definition of Oracle User. Now we will try to learn about the operation and roles of the user.
1. CREATE A USER
So first of all we are going to create an Oracle User.
Syntax:
create user <user_name> identified by "<password>"
Now let us look at an example to create a user using the CREATE USER statement present in oracle. Let us create a user nil_user own with password as ‘India’. One important point to note is that to create a user in a schema we must have a system privilege. The system privilege is very important as anybody just cannot create a user as it is a very powerful privilege. In general, in an organization, only the DBAs and security administrators have this privilege to create a user. Let us look at the query for the same.
Query:
create user nil_user identified by "India!";
In the above query, the name of the user is nil_user and the password for that user is ‘India’.
A user created can also be given special privileges. The statement we use is GRANT in order to achieve that. For example, we can use the GRANT statement to give create session System privilege to our newly created User nil_user. Let us also look at the query for the same.
Query:
GRANT create session TO nil_user;
2. ALTER A USER
in the previous example, we saw how we can use the CREATE USER statement to create a new user but only if we have system privilege. In this point, we will discuss how we can alter the user. There are a few important points that we need to remember when we try to use ALTER USER statements. It is a good practice to change user password and for that, no special privilege is required but in case we need to change any other option which comes under the user security domain then in that case we need to have ALTER USER system privilege. In general, in most organizations, DBA people and Security Administrators generally have the privilege to allow any modification of any user-related security system. One more important point is that if we alter the user security system while a current session is running, the changes will affect from the future user sessions and not the current session.
In this example, we will alter an existing user named ‘SYSTEM’ and change the password to ‘SYS. Let us now create a simple query for the same.
Query:
ALTER USER SYSTEM
IDENTIFIED BY SYS;
Output:
As we can see in the screenshot above the Script output displays that the user SYSTEM has been altered.
3. GETTING THE CURRENT USER
In this section, we are going to discuss that how are we going to extract the user name of the current user in which we are working. So, for that, we are going to use the USER function to get the user id from the currently running session.
In this example, we are going to get the user id of the currently running session. Let us create a SELECT query for the same.
Query:
select user from dual;
Output:
As we can see in the above screenshot that the query on execution displays the user SYS as the result.
4. DROP A USER
In this section, we are going to drop a user. In the earlier sections of this article, we have seen that we had created, altered a user, and similarly we can also drop a user. So, when we drop a user, the user and its associated schema (schema means oracle user plus the collection of data objects owned by a particular user) are removed from data dictionary, and in case we have any user-related objects then those objects are also dropped. There are many important points that we need to remember when we are dropping the user. A user that is currently connected to the database just cannot be dropped. If we want to drop a current user then we have to terminate the user session and we can use ALTER SYSTEM statement to terminate the user session.
Another important point is that all users cannot simply execute the DROP USER statement. We need to have DROP USER privilege to execute the statement. As it is a security-related issue, In general, most of the organizations have security administrators who have the DROP USER privilege. We should never drop the SYS or SYSTEM user as doing so will actually corrupt the database.
In this example, we are going to drop the user ‘nil_user’ from the database.
Query:
DROP USER nil_user;
In case we have any dependent objects of the user we are deleting then we can use CASCADE option to drop the user and all the dependent schema objects including foreign keys will be dropped. Let us create a query for that.
Query:
DROP USER nil_user cascade;
Conclusion
In this article, we discussed the definition of Oracle User at the beginning. Later on in the article, we discussed about the various operations and roles or privileges that one needs to do certain operations like CREATE, ALTER and DROP a User along with examples for each operation.
Recommended Articles
This is a guide to Oracle Users. Here we discuss the definition of Oracle Users and its syntax. We also discussed the various operations and roles in Oracle along with examples. You can also go through our suggested articles to learn more –
