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;

Password change

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;

USER REMOVAL

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

database administration.

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;

Eg.

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;

exercises

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.

PROFILES

Basic profile information

To enable us to modify the profiles we have to start with the command:

ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;

System rules:

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,

Password rules:

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)

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”;

DEFAULT Profile

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;

\

Create 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

SESSIONS_PER_USER 3

FAILED_LOGIN_ATTEMPTS 3

IDLE_TIME 30;

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>

Profile change

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:

DROP PROFILE;

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.

exercises

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.