What you must to have in mind:
– How to create an user;
– Conceive grants for the created user;
– Manage those grants through roles;
– Query the information regarding the created user using views;
– Understanding about local and common users also local and common profiles;
– Manage tablespaces for the created user.
— Users:
“Common users perform administrative tasks for a CDB.”
Local users exists only in the PDBs.
“The root has its own data files, and each PDB has its own data files.”
“There is a default temporary tablespace for the root and for each PDB.”
Grant the CDB_DBA role to CDB administrators.
Grant the PDB_DBA role to PDB administrators.
– Change parameter in the current container or in all:
CONTAINER = { CURRENT | ALL }
“For common user names, names for user-created common users must begin with C## (or c##).”
“For local user names, the name cannot start with C## (or c##)”
Sys user password must be change using the orapw file.
common_user_prefix = parameter who determinate the prefix.
— Privileges / roles
“To find the system privileges that have been granted to a user, you can query the DBA_SYS_PRIVS data dictionary view.”
– SET CONTAINER_DATA = Clause used in the create user statement who allows the created user to have access in others PDBs as the root user.
“You can find the administrative privileges that a user has by querying the V$PWFILE_USERS dynamic view, which lists users in the password file.”
“To find the system privileges that have been granted to a user, you can query the DBA_SYS_PRIVS data dictionary view.”
— Backups
“The SYSBACKUP administrative privilege is used to perform backup and recovery operations from either Oracle Recovery Manager (RMAN) and or through SQL*Plus.
To connect to the database as SYSBACKUP using a password, you must create a password file for it.”
SELECT * FROM SESSION_PRIVS: Select to see all roles.
— Useful views:
CDB_CONTAINER_DATA
{USER|ALL|DBA|CDB}_ROLES
{USER|ALL|DBA|CDB}_COL_PRIVS
{USER|ALL}_COL_PRIVS_MADE
{USER|ALL}_COL_PRIVS_RECD
{USER|ALL}_TAB_PRIVS_MADE
{USER|ALL}_TAB_PRIVS_RECD
{USER|DBA|CDB}_SYS_PRIVS
{USER|DBA|CDB}_ROLE_PRIVS
CDB_ROLES
system_privilege_map
table_privilege_map
CDB_USERS
CDB_SYS_PRIVSCDB_TAB_PRIVS
ROLE_TAB_PRIVS
ROLE_SYS_PRIVS
SESSION_ROLES
If there is a COMMON column, means that the privilege, when granted, becomes a common or local privilege.
Database Security Guide -> 2 Managing Security for Oracle Database Users (The bests examples about users, roles, grants).
Database Administrator’s Guide -> 40 Administering a CDB with SQL*Plus (great examples about local and common users)s
Database Administrator’s Guide -> 42 Administering PDBs with SQL*Plus
Database Administrator’s Guide -> 43 Viewing Information About CDBs and PDBs with SQL*Plus
Database Administrator’s Guide -> 7 Managing Users and Securing the Database
Database Security Guide -> 4 Configuring Privilege and Role Authorization (The best one to privileges and roles) Bom para criar o artigo “O que se nao deve fazer como DBA que visa a seguranca do banco de dados”
About Common Roles and Local Roles
Hi! I am Bruno, a Brazilian born and bred, and I am also a naturalized Swedish citizen. I am a former Oracle ACE and, to keep up with academic research, I am a Computer Scientist with an MSc in Data Science and another MSc in Software Engineering. I have over ten years of experience working with companies such as IBM, Epico Tech, and Playtech across three different countries (Brazil, Hungary, and Sweden), and I have joined projects remotely in many others. I am super excited to share my interests in Databases, Cybersecurity, Cloud, Data Science, Data Engineering, Big Data, AI, Programming, Software Engineering, and data in general.
(Continue reading)