Create a user
In order to create a user for the Oracle database, we have to log in as sys, then issue a command that gives the user a new name and a login password:
CREATE USER username IDENTIFIED BY password;
Granting permissions to the user
The next step is to give the newly created user the appropriate permissions. Below
Here are some example permissions:
CREATE TABLE – allows you to create tables
CREATE SESSION – connect to the database
CREATE VIEW – allows you to create a view
CREATE USER – allows you to create new users
DROP USER – allows you to delete users
SELECT – view the table
UPDATE – Allows you to change the values in the table
INSERT – allows you to add new rows to the table
DELETE – Deletes rows in the table
To grant us privileges enter:
GRANT permission TO user_name;
In order to change the password for the user, we issue the following command:
ALTER USER username identified by new_password;
RECEIVING YOUR POWER OF USE
To receive user privileges, type the command:
REVOKE permission FROM user_name;
To delete a user, enter the command:
DROP USER username;
This removal is possible when a user does not have any objects in his schema.
DROP USER CASCADE username;
This way we remove all objects added / created by a particular user
SENDING ROLLS TO USERS
By giving the user a role, we give it a set of permissions included in the role. The most important roles are:
DBA – the role that the user has. It has all the basic permissions needed to
CONNECT, RESOURCE – these two roles are usually given to users as soon as they are created. They allow you
Connect to the database (CONNECT), and create objects within your schema.
PUBLIC – This role is given to each user by default. Giving some
Everyone will be given this role.
GRANT CONNECT, RESOURCE TO user_name;
CREATING YOUR OWN ROLE
In addition to predefined roles, there are also roles created by ourselves (or other users).
Create a command:
CREATE ROLE root_name;
we remove the role:
DROP ROLE root_name;
Thanks to this treatment, the user will not be able to use the privileges associated with it until he or she has entered a password. Both object and system commands can be present in the role. Other roles may be included.
Permissions are given by the command:
GRANT privilege TO root_name;
GRANT CREATE TABLE is the name of the root;
CHECKING THE ROLE AND RIGHTS OF ROLE
To find out what roles are assigned to us, look in the user_role_privs dictionary:
SELECT * FROM USER_ROLE_PRIVS;
As a SYS user, you can also check what roles were given to all users:
SELECT * FROM DBA_ROLE_PRIVS;
Of course, we can also make normal selections on this view, so we can easily see what roles are available
have been assigned to the selected user.
We can also check what system permissions are assigned to the role:
SELECT * FROM ROLE_SYS_PRIVS;
In this view, however, we will see only system privileges. To see object permissions, we look
to the ROLE_TAB_PRIVS dictionary
SELECT * FROM ROLE_TAB_PRIVS;
RECEIVING ROLLS FOR USERS
To receive a role to the user, we call the command:
REVOKE role name FROM username;
1. Create a role called accounting and the second named uber_exiegowa.
2. Add a role to the connect and resource role.
3. Add the privilege to read the employees table in the hr diagram. To the role
add a bookkeeping role, plus the ability to change and delete
the contents of this table.
Basic profile information
To enable us to modify the profiles we have to start with the command:
ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;
SESSIONS_PER_USER – The number that determines the maximum number of user sessions.
CPU_PER_CALL – number (hundredths of seconds) specifying the maximum CPU time allocated to the session,
CONNECT_TIME – specifies the maximum length of the user session, which is expressed in minutes,
IDLE_TIME – maximum idle time in minutes, (if the user waits “idle” for
long query is not included in IDLE_TIME)
LOGICAL_READS_PER_SESSION – maximum number of blocks (in RAM and disk space)
hard) that can be used for one query (where the query is divided into parsing,
execution, return results)
LOGICAL_READS_PER_CALL – maximum number of blocks needed per query (where the query is split
to parse, execute, return results),
PRIVATE_SGA – Maximum number of bytes (in bytes) a session can consume from a SGA shared pool.
You can add the prefix “K” or “M” to the number in kilobytes or megabytes,
FAILED_LOGIN_ATTEMPTS – The maximum number of unsuccessful login attempts after it has been exceeded
will be blocked,
PASSWORD_LIFE_TIME – maximum password lifetime after login is not possible until
password will not be changed
PASSWORD_REUSE_TIME – time in days after which the password can be reused (when changing
PASSWORD_REUSE_MAX – minimum number of password changes after which the password can be reused, after assigning this option to any number, PASSWORD_REUSE_TIME must be set to “unlimited”
PASSWORD_LOCK_TIME – Number of days after which your account will be unlocked, after the maximum number of unsuccessful logins,
PASSWORD_GRACE_TIME – maximum password lifetime, in days,
PASSWORD_VERIFY_FUNCTION – a password validation script, it is interesting to put any program written in PL / SQL and this gives you great verification capabilities. Exemplary scenarios “the user can only log in from 8 to 16”, or “if the user’s login is different than Bartek then return a random exception and break the session”;
Each database has a profile named DEFAULT that is associated with each user by default. Each subsequent profile inherits from the DEFAULT profile. For example, if a new profile does not have a defined IDLE_TIME parameter, then it is UNLIMITED from the DEFAULT profile. The DEFAULT profile of each rule assigns a UNLIMITED value, which is an undefined parameter, ie a constraint bar. The DEFAULT profile is assigned to each user by default. The command returning the list of profiles installed in the Oracle database looks like this:
SELECT * FROM DBA_PROFILES;
A profile can be created, associated with the user, and received by the user at any time. Changed or new profiles are valid since the new session has been established. The sample command creating a new profile named “profile1” looks like this:
CREATE PROFILE profile1 LIMIT
Once you create a profile, you can associate it with the user and it will take effect once the new session has started. You may only have one profile at a time. Every one overwrites the previous one. The following is a command that assigns a profile to the user:
ALTER USER <user> PROFILE; </ user>
To change profile named prof1, you can use the command:
ALTER PROFILE prof1 LIMIT IDDLE_TIME 20;
It changes the maximum idle time of the user for 20 minutes. The change will take effect once the new session has been established by the user.
Deleting a profile
The profile can be deleted (provided it is not associated with the user) using the command:
To delete a profile associated with a user, add the CASCADE option:
DROP PROFILE CASCADE;
When you receive a profile, for example, by deleting it, the DEFAULT profile will automatically be assigned to it, which will take effect once the new session is established. DEFAULT profile can not be deleted.
1. Create a profile named agent. Allow 3 or more logins to fail. After three
Unsuccessful attempts to lock user account.
2. Add the settings to the agent profile so that the user has to change the password every week.
3. Associate with the newly created user profile in the profile.
4. Change the idle_time parameter for the in profile and observe what happens to the sessions users.