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. Former Oracle ACE, Computer Scientist, MSc in Data Science, over ten years of experience in companies such as IBM, Epico Tech, and Playtech based in three different countries (Brazil, Hungary, and Sweden) and joined projects remotely in many others. I am super excited to show you my interest in Databases, Cloud, Data Science, Data Engineering, Bigdata, AI, Programming, Software Engineering, and data in general.
(Continue reading)