[Oracle-Sql] C.7 User Creation and Management

Date: 2023.06.28 Updated: 2023.06.28

Categories: Oracle-Sql

📋 This is my note-taking from what I learned in the class “Introduction To Database Concept”

Data Security

User accounts provide a method of authentication
They can grant access to specific objects
They identify owners of objects

Creating a User

The CREATE USER command gives each user a user name and password

CREATE USER username IDENTIFIED BY password;

Assigning User Privileges

There are two types of privileges:

System privileges

Allow access to the database and execution of DDL operations

Object privileges

Allow a user to perform DML and query operations

Even with a valid user name and password, a user still needs the CREATE SESSION privilege to connect to a database

CREATE USER super IDENTIFIED BY abcd1234 PASSWORD EXPIRE;

System Privileges

Affect a user’s ability to create, alter, and drop objects
Use of ANY keyword with an object privilege (INSERT ANY TABLE) is considered a system privilege
List of all available system privileges available through SYSTEM_PRIVILEGE_MAP

System Privilege Map

Granting System Privileges

System privileges are given through the GRANT command

GRANT systemprivilege, ...
TO username|rolename, ...
[WITH ADMIN OPTION];

GRANT clause – identifies system privileges being granted
TO clause – identifies receiving user or role
WITH ADMIN OPTION clause – allows a user to grant privilege to other database users

Object Privileges

SELECT – display data from table, view, or sequence
INSERT – insert data into table or view
UPDATE – change data in a table or view
DELETE – remove data from a table or view
INDEX – create an index for a table
ALTER – change definition of table or view
REFERENCES – reference a table when creating a FOREIGN KEY constraint

Granting Object Privileges

Object privileges are given through the GRANT command

GRANT {objectprivilege|ALL} [(columnname), objectprivilege (columnname)]
ON objectname
TO {username|rolename|PUBLIC}
[WITH GRANT OPTION];

GRANT clause – identifies object privileges
ON clause – identifies object
TO clause – identifies user or role receiving privilege
WITH GRANT OPTION clause – gives a user the ability to assign the same privilege to other users

GRANT Command Examples

Example 1:

Ron Thomas needs the ability to select rows from and insert rows into the CUSTOMERS table, which is in the SCOTT schema.

GRANT select, insert ON scott,customers TO rthomas;

Example 2:

Ron Thomas needs to be able to select any data from the CUSTOMERS table but be able to modify only the Lastname and Firstname columns.

GRANT select, update(lastname, firstname) ON scott.customers TO rthomas;

Password Management

To change a user password, use the PASSWORD command or the ALTER USER command

ALTER USER rthomas IDENTIFIED BY monster42truck;

Utilizing Roles

A role is a group, or collection, of privileges

CREATE ROLE orderentry;

GRANT select, insert, update
ON scott.customers
TO orderentry;

GRANT select, insert, update
ON scott.orders
TO orderentry;

GRANT select, insert, update
ON scott.orderitems
TO orderentry;

Roles can be assigned to users or other roles:

GRANT orderentry
TO rthomas;

A user can be assigned several roles
All roles can be enabled at one time
Only one role can be designated as the default role for each user
Default role can be assigned through the ALTER USER command

ALTER ROLE rolename IDENTIFIED BY password;

Roles can be modified with the ALTER ROLE command
Roles can be assigned passwords

Viewing Privilege Information

ROLE_SYS_PRIVS lists all system privileges assigned to a role
SESSION_PRIVS lists a user’s currently enabled roles

ROLE_TAB_PRIVS Example

Removing Privileges and Roles

Revoke system privileges with the REVOKE command

REVOKE systemprivilege, ...
FROM username|rolename;

Revoking an object privilege – if the privilege was originally granted using WITH GRANT OPTION, the effect cascades and is revoked from subsequent recipients

REVOKE objectprivilege, ...
ON objectname
FROM username|rolename;

REVOKE rolename
FROM username|rolename;

Dropping a Role

Users receiving privileges via a role that is dropped will no longer have those privileges available

DROP ROLE rolename;

DROP ROLE orderentry;

Dropping a User

The DROP USER command is used to remove a user account

DROP USER username;

Summary

Database account management is only one facet of data security
A new user account is created with the CREATE USER command
- The IDENTIFIED BY clause contains the password for the account
System privileges are used to grant access to the database and to create, alter, and drop database objects
The CREATE SESSION system privilege is required before a user can access his account on the Oracle server
The system privileges available in Oracle 11g can be viewed through the SYSTEM_PRIVILEGE_MAP
Object privileges allow users to manipulate data in database objects
Privileges are given through the GRANT command
The ALTER USER command, combined with the PASSWORD EXPIRE clause, can be used to force a user to change her password upon the next attempted login to the database
The ALTER USER command, combined with the IDENTIFIED BY clause, can be used to change a user’s password
- Privileges can be assigned to roles to make the administration of privileges easier
Roles are collections of privileges
The ALTER USER command, combined with the DEFAULT ROLE keywords, can be used to assign a default role(s) to a user
Privileges can be revoked from users and roles using the REVOKE command
Roles can be revoked from users using the REVOKE command
A role can be deleted using the DROP ROLE command
A user account can be deleted using the DROP USER command

C.7 Demo

-- Week 07

-- USER CREATION AND MANAGEMENT (CHATPER 5)

CREATE USER rthomas  IDENTIFIED BY centennial;
ALTER USER rthomas IDENTIFIED BY centennialcollege;
DROP USER rthomas ;

-- Granting and Revoking Privileges

GRANT SELECT,INSERT ON customers to rthomas WITH GRANT OPTION;
GRANT CREATE SESSION TO rthomas;
REVOKE INSERT ON CUSTOMERS FROM rthomas;
REVOKE CREATE SESSION FROM rthomas;

-- Granting and Revoking Roles;

CREATE ROLE orderentry;
GRANT orderentry TO rthomas;
ALTER USER rthomas DEFAULT ROLE orderentry;
SET ROLE dba;
ALTER ROLE orderentry IDENTIFIED BY password;
REVOKE orderentry from rthomas;
DROP ROLE orderentry;

-- user vs roles

-- create a user
create user vijiprof identified by centennial ;

-- CHANGE YOUR USER ACCOUNT PASSWORD
alter user COMP122_M20_NUMBER IDENTIFIED BY NEWPASSWORD;

DROP USER vijiprof;

-- GRANT PRIVILEGES
GRANT SELECT ,INSERT,UPDATE ON CUSTOMERS
TO COMP122_M20_NUMBER WITH GRANT OPTION;

-- CHECK THE PRIVILEGES
SELECT * FROM SYS.USER_SYS_PRIVS;
SELECT * FROM USER_SYS_PRIVS;
SELECT * FROM ROLE_TAB_PRIVS;

select * from system_privilege_map;
SELECT NAME FROM SYSTEM_PRIVILEGE_MAP;
SELECT * FROM USER_ROLE_PRIVS;

-- RESTRICT TO SPECIFIC COLUMNS
SELECT * FROM CUSTOMERS ;
GRANT UPDATE(ADDRESS,CITY,STATE,ZIP) ON
VIJI.CUSTOMERS TO THIAGO;

REVOKE UPDATE(ADDRESS,CITY,STATE,ZIP) ON
VIJI.CUSTOMERS FROM THIAGO;

GRANT SELECT,INSERT(ADDRESS,CITY,STATE,ZIP) ON
VIJI.CUSTOMERS TO THIAGO;

-- ROLES
CREATE ROLE STUDENTACCESS;

--ASSIGN ROLES TO DIFFERENT USERS
GRANT STUDENTACCESS TO IMTIAJ;
GRANT STUDENTACCESS TO TINGLI;
GRANT STUDENTACCESS TO TIMHUN;

ALTER USER IMITAJ DEFAULT ROLE DBA;

SET ROLE DBA;

-- TAKE BACK THE PRIVILEGES
REVOKE  STUDENTACCESS FROM TINGLI;

-- DROP THE ROLE
DROP ROLE STUDENTACCESS;

SELECT COUNT(NAME) FROM SYSTEM_PRIVILEGE_MAP;

GRANT SELECT ,INSERT,UPDATE ON CUSTOMERS
TO COMP122_M23_vi_WITH GRANT OPTION;

SELECT * FROM COMP122_M23_VI_.BOOKS;

INSERT INTO COMP122_M23_VI_.BOOKS VALUES (34234234,
'ORACLE12C',SYSDATE,1,100,105,10,'COMPUTER');

SELECT * FROM CUSTOMERS;

SELECT * FROM ROLE_TAB_PRIVS;

DELETE FROM COMP122_M23_VI_.BOOKS
WHERE TITLE='ORACLE12C';

REVOKE  SELECT ,INSERT,UPDATE ON
CUSTOMERS FROM
COMP122_M23_VI_;

Seyeon