Managing User Access

Hi ,

in this article , I will tell you about user creation and user access .

USER ACCESS

Each user is logged into the system with their user name and password . After this permission , all the actions of the users are subject to authorization control .

SYSTEM AUTHORITIES

Most of the system privileges are used by DBAs . To give an example of these powers :

  • User creation ,

  • User deletion ,

  • Deleting a table ,

  • Indexing ,

  • Role creation ,

  • Directory deletion .

CREATE USERS

DBAs perform user creation . The user name and password are given when creating the user , and everything else is given by default .

CREATE USER user 

IDENTIFIED BY password;

SYSTEM AUTHORITIES FOR USERS

The first user is created , then the DBAs give the desired system privileges to the users . Since the authorizations given are important , care must be taken when authorizing them .

GRANT PRIVILEGE[ , privilege..]

TO user [ ,user | role, PUBLIC..];
  • We have created a user above , now let’s authorize the user we created :

System privileges for a developer :

  • CREATE TABLE

  • CREATE INDEX

  • DROP INDEX

  • CREATE SESSION

  • ….

  • Now let’s create a user and give the user connect and resource rights :

Now let’s write our query into the table :

We got an error because we don’t have authority .

ROLE

  • **Roles are typically created and managed by DBAs .

  • A definition of authority that can be assigned to users or other roles .

  • It facilitates compliance and reporting of rules on who , what and how .

ROLE BUILDING AND AUTHORIZATION

  • Now let’s create a role for developers :

Our role is now ready to authorize :

  • Now let’s give this role to the user named mdrn that we created before and after that mdrn user will have all authority or privileges to be given to developer_role .

OBJECTIVES

The owner of the object has all the authority over the object , and at the same time , the object owner can authorize others for that object .

  • SELECT TABLE

  • INSERT TABLE

  • UPDATE TABLE

  • EXECUTE

  • …….

To give an example of how to use :

GRANT object_priv [(columns)] 

ON object 

TO {user | role | PUBLIC}

[ WITH GRANT OPTION] ;
  • Now let’s create object privileges for the user :

WITH GRANT OPTION

  • It is used to give our authority to another person .

AUTHORIZATION

We revoke the authority given to the user or role with the REVOKE command .

REVOKE {privilege [ , privilege ...] | ALL }

ON object

FROM {user [ , user ...] | role | PUBLIC }

[CASCADE CONTRAINTS];

Let us revoke the privileges we have already granted to MDRN :

See you in my next post.