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.