Oracle SQL Tutorial -9 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;
CREATE USER MELIKE
IDENTIFIED BY "Sifre124";

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 :

GRANT CREATE TABLE TO MELIKE;

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 :

CREATE USER MDRN IDENTIFIED BY Medu_0123456;

GRANT CONNECT,RESOURCE TO MDRN;

Now let’s write our query into the table :

SELECT * FROM ADMIN.NEW_TABLO;

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 :

CREATE ROLE DEVELOPER_ROLE;

Our role is now ready to authorize :

GRANT SELECT ANY TABLE TO DEVELOPER_ROLE;
GRANT UPDATE ANY TABLE,INSERT ANY TABLE TO DEVELOPER_ROLE;

  • 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 .

GRANT DEVELOPER_ROLE TO MDRN;

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 :

GRANT DELETE ON ADMIN.NEW_TABLE TO MDRN;
GRANT INSERT,UPDATE ON ADMIN.NEW_TABLE TO MDRN;
GRANT CREATE ANY SEQUENCE TO MDRN;
GRANT CREATE TABLE TO MDRN;

WITH GRANT OPTION

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

GRANT DELETE ON ADMIN.NEW_TABLE TO MDRN WITH GRANT OPTION;
GRANT INSERT,UPDATE ON ADMIN.NEW_TABLE TO MDRN WITH GRANT OPTION;

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 :

REVOKE DELETE ON ADMIN.NEW_TABLE FROM MDRN;

See you in my next post.