Oracle SQL Tutorial -9 Managing User Access

Melike Duran

5 years ago

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.