Administrator's work - authorizations in Oracle

Working with permissions is a basic part of database administration. Authorizations are assigned depending on the scope of work performed by database users. They are also a kind of database security element that precedes other software security elements. It makes a difference whether the user only needs to read or write, or whether he needs to manage the entire database or only part of it. Permissions are assigned accordingly. In DB Oracle, we know 3 types of authorizations:

1. System-Ability to perform a task in the database
2. Object-Ability to perform a task on a database object/schema
3. Role-Collection of one or more system authorizations and/or object authorizations and/or other roles

System permissions

There are more than 100 system authorizations that can be granted to one user or scheme if we talk about other DB languages, the most famous are create session, create table, create view, create sequence, create synonym... The difference between system and object authorization is that system authorization allows access to the object as a whole and object authorization to individual parts, i.e. I have, for example, total access to all tables with the possibility of writing or only to one without writing. When the word ANY is added when assigning authorization, the user can e.g. create a table in any scheme. When the word PUBLIC is added, all users can e.g. create a table in any schema, which is more or less an authorization worthy of an administrator or system user, which is the default in Oracle. When WITH ADMIN OPTION is added, the user can assign the assigned authorization to other users. PROCEDURE for adding system permissions:

1. To create a user, we must enable its creation in SQL PLUS: alter session set "_ORACLE_SCRIPT"=true;
2. A USER is created in SQL PLus, eg: Create user user_name identified by password
3. The creation of a session, i.e. a connection with the DB, is granted. Grant create session to user_name;
4. Tablespace is allocated Grant unlimited tablespace to user_name;
5. Access to creating tables is granted: Grant create table to user_name;

!If the authorization (Revoke CREATE TABLE from user_name) is revoked, the authorization is not automatically revoked from other users to whom it was assigned!

Object permissions

They enable access to individual parts of the object. They are automatically owned by the user (schema) who created the object, he assigns permissions to other users / or the system user assigns them. Table permissions include: SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, INDEX, ALL. Table permissions can also be defined at the column level (INSERT, UPDATE, REFERENCES). When WITH GRANT OPTION is added, the user can grant the assigned permission to others user (eg Grant select on table_name to user_name WITH GRANT OPTION). It is important to mention. that if the table is dropped, all permissions are dropped and if recreate can be done, they must be reassigned, but when FLASHBACK TABLE ... BEFORE DROP can be done, the permissions remain. Examples of object permissions:

1. for table reading -> Grant select on table_name to user_name
2. for writing to the table -> Grant insert on table_name to user_name
3. for all authorizations for writing and modifying or deleting the table -> Grant ALL on table_name to user_name
4. record setting in one column -> Grant insert(column_NAME) on table_name to user_name

!Unlike sys. authorized if the authorization is revoked (revoke from..) from 1 user, the authorization will be automatically revoked from other users to whom the user assigned the authorization!

Permissions for Roles

Roles represent an organized system so that we can group otherwise scattered authorizations (for example, we create a role to create DML in tables) The authority assigned to a role exists separately from the authority assigned to a user. The role is an important security element in the database and its name should correspond to what it can do. For example when we have an HR scheme created by the system, by creating a role e.g. We will give HR_USER the option to read only to everyone who has the given permission. If we want to find all the roles in the database, we use select * from role_sys_privs under the system user; Historically, Oracle assigns the following roles:

1. CONNECT It includes the following system permissions: ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE VIEW --> the grant connect to user_name is granted and the user can now create everything
2. RESOURCE Includes the following system privileges: CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER, CREATE TYPE
3. DBA All system privileges WITH ADMIN OPTION--> grant dba to user_name is granted and the user can now administer /with this carefully

! If the authorization is removed from the role, the authorization will be automatically removed from all users to whom it was assigned (just like with normal authorizations)!

Interesting: Role names exist outside of user names, so they can be the same as object names (tables, views...), but this is not recommended. If we have e.g. table authorizations (DML) assigned for the role, if the table is dropped and we assign the authorizations again, we do not need to add the authorizations for the user again, nor create the role (i.e. we skip 2 steps) Let's look at the following example:

1. drop table hr.emp;
2. create table hr.emp as select * from HR.employees;
3. create role EMPDML; - this doesn't have to be created during drop
anymore
4. grant update,insert,delete on hr.emp to EMPDML;
5. grant EMPDML to op;
6. delete from hr.emp;

This way we will see all users and settings in the database under the sys account

SELECT * FROM (SELECT CONNECT_BY_ROOT grantee grantee, privileges REPLACE( REGEXP_REPLACE(SYS_CONNECT_BY_PATH(granteE, '/'), '^/[^/]*'), '/', ' --> ') ROLES, owner table_name, column_name FROM (SELECT PRIVILEGE, GRANTEE, OWNER, TABLE_NAME, NULL column_name FROM DBA_TAB_PRIVS WHERE owner NOT IN (SYSTEM, WMSYS, SYSMAN, MDSYS, ORDSYS, XDB, WKSYS, EXFSYS, OLAPSYS) ,'DBSNMP','DMSYS','CTXSYS','WK_TEST','ORDPLUGINS','OUTLN','ORACLE_OCM','APPQOSSYS') UNION SELECT PRIVILEGE, GRANTEE, OWNER, TABLE_NAME, column_name FROM DBA_COL_PRIVS WHERE owner NOT IN ('SYS','SYSTEM','WMSYS','SYSMAN','MDSYS','ORDSYS','XDB','WKSYS','EXFSYS', OLAPSYS, DBSNMP, DMSYS, CTXSYS, WK_TEST, ORDPLUGINS, OUTLN, ORACLE_OCM, APPQOSSYS) UNION SELECT GRANTED_ROLE, GRANTEE, NULL, NULL, NULL FROM DBA_ROLE_PRIVS WHERE GRANTEE NOT IN ('SYS','SYSTEM','WMSYS','SYSMAN','MDSYS','ORDSYS','XDB','WKSYS','EXFSYS' OLAPSYS, DBSNMP, DMSYS, CTXSYS, WK_TEST, ORDPLUGINS, OUTLN, ORACLE_OCM, APPQOSSYS) ) T START WITH grantee IN (SELECT username FROM dba_users) CONNECT BY PRIOR PRIVILEGE = GRANTEE) WHERE table_name IS NOT NULL AND grantee != OWNER ORDER BY grantee, owner table_name, column_name, privileges;

Summary

We used examples to show how authorizations work in Oracle. We found out the difference between system and object permissions by assigning roles. Based on the examples, everyone can create their own database with the necessary grants.