How to create new users and grant them permissions in the Oracle Autonomous Database?

Managing user roles in Autonomous Database differs slightly from what we encounter in a traditional Oracle on-premise database. It's worth noting at the outset that we don't have access to SYSTEM and SYS users. When we create a database, we only get access to the ADMIN user. We should only use this user's account to manage the database, just as we do with the SYSTEM and SYS users. Below I describe the process of creating a new user, DBUSER1, whose permissions will be modeled on the permissions of the ADMIN user:

1. Initially, we create a user in the database.

CREATE USER dbuser1 IDENTIFIED BY hardPasswd1000;

2. To verify what permissions ADMIN has, we can perform:

SELECT * FROM dba_sys_privs WHERE grantee = 'ADMIN';
SELECT * FROM dba_tab_privs WHERE grantee = 'ADMIN';
SELECT * FROM dba_role_privs WHERE grantee = 'ADMIN';

If we want to copy the ADMIN user's roles, we execute the following code:

SELECT 'GRANT ' || granted_role || ' TO DBUSER1;'
FROM dba_role_privs
WHERE grantee = 'ADMIN';

3. Copy and execute the result of the above query. Below I present an example query result:

GRANT RESOURCE TO DBUSER1;
GRANT PDB_DBA TO DBUSER1;
GRANT AUDIT_ADMIN TO DBUSER1;
GRANT APEX_ADMINISTRATOR_READ_ROLE TO DBUSER1;
GRANT ODI_LINEAGE_USER TO DBUSER1;
GRANT OML_DEVELOPER TO DBUSER1;
...

4. After executing the above SQL, we can log in to the newly created account and check permissions using:

SELECT * FROM session_roles;

If we notice that we do not have the same roles as the ADMIN user, because, for example, we logged in earlier than we added these roles to the DBUSER1 user, we should execute the following SQL, which will activate all the roles belonging to the user in the current session:

SET ROLE ALL;

In a situation where we would like to limit our roles in an active session to specific one, we can perform:

SET ROLE <role_name>;

For example:

SET ROLE DWROLE;

Let's now see the effect of our actions.

Before:

After SET ROLE DWROLE:

In addition, we can specify the default role that will be assigned to the user when a new session is created. The following code is used for this:

ALTER USER DBUSER1 DEFAULT ROLE <role_name>;

The entire SQL to copy is below:

-- Create a new user named 'dbuser1' with the given password
CREATE USER dbuser1 IDENTIFIED BY hardPasswd1000;

-- Select all roles that have been granted to 'ADMIN', 
-- then create SQL statements granting the same roles to 'DBUSER1'
SELECT 'GRANT ' || granted_role || ' TO DBUSER1;'
FROM dba_role_privs
WHERE grantee = 'ADMIN';

-- Select all system privileges granted to 'ADMIN'
SELECT * FROM dba_sys_privs WHERE grantee = 'ADMIN';

-- Select all table privileges granted to 'ADMIN'
SELECT * FROM dba_tab_privs WHERE grantee = 'ADMIN';

-- Select all role privileges granted to 'ADMIN'
SELECT * FROM dba_role_privs WHERE grantee = 'ADMIN';

-- Select all system privileges granted to 'DBUSER1'
SELECT * FROM dba_sys_privs WHERE grantee = 'DBUSER1';

-- Select all table privileges granted to 'DBUSER1'
SELECT * FROM dba_tab_privs WHERE grantee = 'DBUSER1';

-- Select all role privileges granted to 'DBUSER1'
SELECT * FROM dba_role_privs WHERE grantee = 'DBUSER1';

-- Set all roles as default for 'DBUSER1'
ALTER USER DBUSER1 DEFAULT ROLE ALL;

-- Delete user 'dbuser1'
DROP USER dbuser1;

-- Select all roles available in the current session
SELECT * FROM session_roles;

-- Activate all available roles
SET ROLE ALL;

Addendum:

Oracle has several built-in views that store information about permissions. Three of them - DBA_SYS_PRIVS, DBA_TAB_PRIVS, and DBA_ROLE_PRIVS - are most commonly used, but there are many others that can provide even more details.

Below is a list describing the above-mentioned views and some of the lesser-known but also very useful ones:

• DBA_TAB_PRIVS contains information about all granted object permissions.
• DBA_SYS_PRIVS contains information about granted system permissions to users and roles.
• DBA_ROLE_PRIVS contains information about roles granted to users and other roles.
• DBA_ROLES contains a list of all roles in the database, which is useful for understanding the overall permission structure.
• USER_SYS_PRIVS contains information about system permissions granted to the current user.
• USER_TAB_PRIVS contains information about granted object permissions, where the current user is the beneficiary, donor, or owner.
• DBA_COL_PRIVS contains information about column-level permissions, which allows for precise access management to data.
• ALL_TAB_PRIVS_MADE and ALL_TAB_PRIVS_RECD contain information about table permissions granted by the user (_MADE) and granted to the user (_RECD).
• DBA_PROXIES contains information about users who have permissions to act on behalf of other users, which is useful in complex corporate environments.
• ROLE_ROLE_PRIVS, ROLE_SYS_PRIVS, ROLE_TAB_PRIVS contain information about permissions assigned to roles, which helps understand what permissions are inherited by users based on their roles.
• SESSION_PRIVS and SESSION_ROLES contain information about permissions and roles of the current session. They are extremely useful during live permission troubleshooting.
• USER_PRIVILEGE_MAP contains a mapping of permission names to permission numbers, which is useful for understanding the exact meaning of individual permissions.

Remember that access to some of these views may require special permissions. The availability of views may also depend on the specific version of Oracle you are using. Therefore, it is always worth checking the latest official Oracle documentation to get the most current and detailed information.

Knowledge about these tables and views is key to effective permission management in Oracle. It provides control and predictability that are essential for maintaining the security and stability of your database.

Additionally, for clarification:

In Oracle, the prefixes USER_, DBA_, and ALL_ are used to define the scope of permission-related views:

• USER_ views: These views provide information about objects and permissions that are directly related to the current user. For example, USER_TAB_PRIVS shows permissions to tables that are granted to the current user, on which he is a donor or which he owns.
• DBA_ views: These views provide database-level information. They are usually only available to users with administrator (DBA) permissions. For example, DBA_ROLE_PRIVS displays information about all roles granted to all users and other roles across the entire database.
• ALL_ views: These views provide information about objects to which the current user has access, regardless of whether they own them or not. For example, ALL_TAB_PRIVS shows all object permissions to which the current user has access.

The difference between these three types of views lies in the level of detail of the information they offer, as well as the permissions needed to view them. The choice between them depends on the specific needs of user permission analysis.