Category Archives: Users

11g, 12c, Administration, hp-ux, linux, Users

Change time zone of Oracle RDBMS on Linux OS

May 11, 2017 Larry Catt Leave a comment

This procedure demonstrates the method to change the time zone setting of an Oracle RDBMS installed on a Linux server. NOTE: Oracle RDBMS by default take the time of the OS which they are installed on. Due to the number and variations LINUX distributions, this procedure may not work on all LINUX installs. This procedure was performed on Oracle Enterprise Linux 7.2 (OEL 7.2).

Logon to your Linux server as root.

[larry@linux2 ~]$ sudo su

[sudo] password for larry:

[root@linux2 larry]#

Change user to Oracle software owner and logon to SQLPLUS.

[root@linux2 etc]# su – oracle

Last login: Wed Nov 30 16:36:03 UTC 2016 on pts/1

[oracle@linux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Nov 30 16:38:13 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

Show the current date and time zone for the Oracle database with command: select systimestamp from dual;

SQL> select systimestamp from dual;

SYSTIMESTAMP

—————————————————————————

30-NOV-16 04.40.33.575810 PM +00:00

SQL>

Exit out of SQLPLUS and change user to root.

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[oracle@linux2 ~]$ su –

Password:

Last login: Wed Nov 30 16:38:00 UTC 2016 on pts/1

[root@linux2 ~]#

Change to directory /etc

[root@linux2 ~]# cd /etc

[root@linux2 etc]#

Display the currently set system time with command date.

[root@linux2 etc]# date

Wed Nov 30 12:39:08 UTC 2016

[root@linux2 etc]#

List the current setting to link /etc/localtime and remove link /etc/localtime, using the “rm –f” command.

[root@linux2 etc]# ls -l localtime

lrwxrwxrwx. 1 root root 23 Nov 30 14:22 localtime -> /usr/share/zoneinfo/UTC

[root@linux2 etc]# rm -rf localtime

[root@linux2 etc]#

Create new link to directory /usr/share/zoneinfo/<your time zone> with you correct timezone. In this example we are changing to EST.

ln -s /usr/share/zoneinfo/EST localtime

[root@linux2 etc]# ls -l localtime

lrwxrwxrwx. 1 root root 23 Nov 30 09:23 localtime -> /usr/share/zoneinfo/EST

[root@linux2 etc]#

Execute linux date command to see new timezone.

[root@linux2 etc]# date

Wed Nov 30 11:43:59 EST 2016

[root@linux2 etc]#

Logon as the oracle software owner and logon to SQLPLUS.

[root@linux2 etc]# su – oracle

Last login: Wed Nov 30 11:41:44 EST 2016 on pts/1

[oracle@linux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Nov 30 11:45:13 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

To verify the new timezone update has been completed, execute the SQL statement: select systimestamp from dual;

SQL> select systimestamp from dual;

SYSTIMESTAMP

—————————————————————————

30-NOV-16 11.46.25.722487 AM -05:00

SQL>

This completes update of timezone for Oracle RDBMS on a Linux platform.

Larry Catt

OCP

12c, Administration, privileges, Users

Oracle 12c – User privilege analysis

April 11, 2017 Larry Catt Leave a comment

Oracle 12c gives the ability to perform a user privilege analysis to bounce against granted privileges. This allows you to reduce granted privilege down to what the individual user actually needs and no more. This article gives a procedure to perform User privilege analysis.

Logon oracle server as the oracle software owner than logon to sqlplus with sysdba privilegs. If this is done by none privileged user account, the user must have the CAPTURE_ADMIN role granted.

[root@linux2 ~]# su – oracle

Last login: Thu Dec 15 07:50:00 EST 2016 on pts/1

Enter database to use:

1 – ORCL

2 – CDB1

Option >

[oracle@linux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 15 13:05:59 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

SQL>

Define the privilege analysis policy with the CREATE_CAPTURE procedure of DBMS_PRIVILEGE_CAPTURE package. This example below will capture all privileges used.

BEGIN

DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(

name => ‘user_priv_capture’,

description => ‘Capture the privileges by user test’,

type => DBMS_PRIVILEGE_CAPTURE.G_DATABASE);

END;

Enable the privilege analysis policy.

SQL> EXEC DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE (‘user_priv_capture’);

PL/SQL procedure successfully completed.

SQL>

Logon as the user test and perform DML processing.

SQL> connect test/test

Connected.

SQL> insert into test.test_times(pers_id, first_name) values(99,’test’);

1 row created.

SQL> commit;

Commit complete.

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

[oracle@linux2 ~]$

Logon as sysdba and disable the privilege analysis policy’s recording of privilege use.

[oracle@linux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 15 13:25:29 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

SQL> EXEC DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE (‘user_priv_capture’);

PL/SQL procedure successfully completed.

SQL>

Generate privilege analysis results.

SQL> EXEC DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT (‘user_priv_capture’);

PL/SQL procedure successfully completed.

SQL>

Query the table dba_used_privs to see privileges used during the capture period.

SQL> column username format a12

column sys_priv format a15

column object_owner format a10

column object_name format a25

set pagesize 500

SELECT username, sys_priv, object_owner, object_name FROM dba_used_privs WHERE capture = ‘user_priv_capture’;

SQL> SQL> SQL> SQL> SQL>

USERNAME SYS_PRIV OBJECT_OWN OBJECT_NAME

———— ————— ———- ————————-

TEST CREATE SESSION

TEST SYS USER_TABLES

APEX_040200 SYS DBMS_OUTPUT

TEST CREATE SESSION

TEST SYS DUAL

APEX_040200 SYS DBMS_OUTPUT

TEST CREATE SESSION

TEST SYS DUAL

APEX_040200 SYS DEFAULT_JOB_CLASS

TEST CREATE SESSION

TEST SYSTEM PRODUCT_PRIVS

TEST SYS DBMS_APPLICATION_INFO

17 rows selected.

SQL>

Optionally, drop the privilege analysis policy.

EXEC DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE (‘user_priv_capture’);

Larry Catt

OCP

12c, Administration, privileges, Users

Oracle 12c – User privilege analysis overview

March 18, 2017 Larry Catt Leave a comment

Oracle 12c contains the feature to analyze the privileges actually used by an individual user account in order to implement least privilege policies within the RDBMS. This article gives an overview of User privilege analysis in Oracle 12c.

Privilege analysis is provided by Oracle Data Vault and it allows for creation of profile which captures system and object privileges used by a user.
This profile can be used to bounce used privileges against granted privileges.
You can reduce privileges that are not in use through this analysis.
It is possible to perform privilege analysis with or without have Database Vault configured and enabled.
It is possible to administer privilege analysis by EM Cloud Control or the package DBMS_PRIVILEGE_CAPTURE
The role CAPTURE_ADMIN grants execute privilege on DBMS_PRIVILEGE_CAPTURE package and select on views to results.
The DBMS_PRIVILEGE_CAPTURE package allows you to create/enable/disable/drop privilege analysis policies.
Only one privilege analysis policy can be enabled in the database at a time, but the DBMS_PRIVILEGE_CPATURE.G_DATABASE privilege analysis can be done with one other user defined policy at the same time.
Analysis policies running will still be running after DB restart.
Privilege analysis policies must be disabled, before reports can be generated.
Policies must be disabled before they are dropped.
Dropping a privilege policy, also drops all collected data by that policy.
List of some view available with Privilege Analysis:
1. DBA_PRIV_CAPTURES — Lists information about existing privilege analysis policies
2. DBA_USED_PRIVS — Lists the privileges that have been used for reported privilege analysis policies
3. DBA_UNUSED_PRIVS — Lists the privileges that have not been used for reported privilege analysis policies
4. DBA_USED_OBJPRIVS — Lists the object privileges that have been used for reported privilege analysis policies. It does not include the object grant paths.
5. DBA_UNUSED_OBJPRIVS — Lists the object privileges that have not been used for reported privilege analysis policies. It does not include the object privilege grant paths.
6. DBA_USED_SYSPRIVS — Lists the system privileges that have been used for reported privilege analysis policies. It does not include the system privilege grant paths.
7. DBA_UNUSED_SYSPRIVS — Lists the system privileges that have not been used for reported privilege analysis policies. It does

Larry Catt

OCP

12c, security, Users

Oracle 12c – Create the password file

February 4, 2017 Larry Catt Leave a comment

Oracle uses a password file to restrict access to database for users with elevated permissions. Password file is required for users with the following privileges: SYSDBA, SYSOPER, SYSBACKUP, SYSDG, SYSKM. If the password file is lost or corrupted, it must be recreated with the utility ORAPWD. Syntax of ORAPWD is:

ORAPWD File=<filename> [ENTRIES=number_users] [FORCE={Y/N}] [IGNORECASE={Y/N}]

Command options for ORAPWD are:

FILE – Name of the file, can include complete directory or file is written in current directory.
ENTRIES – Max number of entries (user accounts) to add. This is users with any of the privileges of SYSDBA, SYSOPER, SYSBACKUP, SYSDG, SYSKM
FORCE – if Y command will overwrite existing password file.
IGNORECASE – If Y password will not be case sensitive.
FORMAT – When set to 12 which is the default, will create file in 12c format. 12C format is required for SYSBACKUP, SYSDG, SYSKM admin privilege support. IF set to less, then file will only support for SYSDBA and SYSOPER privileges.
SYSBACKUP – If Y creates a SYSBACKUP entry in password file.
SYSDG – If Y creates a SYSDG entry in password file.
SYSDM – If Y creates a SYSKM entry in password file.

example of use of password file creation for a database with ORACLE_SID of db1:

orapwd File=orapwdb1 Entries=10

The initialization parameter REMOTE_LOGIN_PASSWORDFILE controls the connection to the password file with the following options:

NONE – Database will not recognize any password file.
EXCLUSIVE – Password file can be used with only one database. It allows you to add multiple users to the database of which any can have one of the privileges: SYSDBA, SYSOPER, SYSBACKUP, SYSDG, SYSKM. This is the default.
SHARED – Password file can be used by multiple databases running on the same server or multiple instances in a RAC database. Shared password files are read-only and cannot be modified. All users needing access must be must be added while the REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE, then change the init parameter back to SHARED.

Larry Catt

OCP

12c, CDB, Multitenant, Users

Creating Common Users in Oracle Multitenant Databases

January 14, 2017 Larry Catt Leave a comment

In Oracle 12c multitenant databases, a user defined in the CDB is considered a Common user. Common users with correct privileges can access all PDBs in the CDB. This article looks at the creation of Common users and their use in the multitenant databases.

Logon to your server as the oracle software owner.

[larry@linux2 ~]$ su – oracle

Password:

Last login: Wed Oct 26 10:07:02 EDT 2016 on pts/2

[oracle@linux2 ~]$

Logon to oracle as the sys user.

[oracle@linux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Oct 26 14:30:43 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

Create the user cdb_dba

SQL> create user c##cdb_dba identified by password;

User created.

Just as in a traditional Oracle database a user has no privileges until they are granted and what a user can do is limited by these privileges. But an additional clause exists in CDB and PDB which determines where a common user can use their privileges, this clause is CONTAINER.

Larry Catt

OCP

12c, CDB, Multitenant, PDB, privileges, Users

Users accounts in Multitenant Database

November 19, 2016 Larry Catt Leave a comment

Oracle 12c multitenant database consist of a CDB which houses one or more PDB which house application data. Now this design has direct implications on user accounts and how they are implemented. This article attempts to briefly cover the implementation of user accounts in the Oracle 12c multitenant architecture.

Oracle 12c multitenant database have two class of user accounts: Common and Local. Common users belong to the root container (CDB) and have full access to PDBs within the CDB. Local users belong a particular PDB and do not have access to the root container (CDB) which houses them. They following rules govern User accounts in the multitenant database architecture:

Common user have the same identity in the root and every PDB.
Common users can connect to root and PDB.
Common users connected to root can perform ALTER PLUGGABLE DATABASE, CREATE USER/ROLE that affect multiple PDBs.
Local users apply to only one PDB.
Local users cannot connect to CDB their PDB is contained in.
Local users cannot cross PDBs but same username can exist in two different PDB in same CDB.
Most privileges only apply in current container. IE user must first connect to container to query tables in that container and they cannot do it from the root.

Larry Catt

OCP

12c, CDB, Multitenant, PDB, Users

Container Clause for Common Users in Oracle Multitenant Databases

September 8, 2016 Larry Catt Leave a comment

In Oracle 12c multitenant databases, a user defined in the CDB is considered a Common user. Common users with correct privileges can access all PDBs in the CDB. The restriction of which database a user can and cannot access in the multitenant database architecture is governed by the CONATINER clause. This article looks at the creation of Common users and the use of the container clause to allow access to multiple PDBs.

Logon to your server as the oracle software owner.

[larry@linux2 ~]$ su – oracle

Password:

Last login: Wed Jul 26 10:07:02 EDT 2016 on pts/2

[oracle@linux2 ~]$

Logon to oracle as the sys user.

[oracle@linux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 26 14:30:43 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

Create the user c##cdb_dba. NOTE: users names in the root container must begin with c##… or C##…

SQL> create user c##cdb_dba identified by password;

User created.

Just as in a traditional Oracle database a user has no privileges until they are granted and what a user can do is limited by these privileges. But an additional clause exists in CDB and PDB which determines where a common user can use their privileges, this clause is CONTAINER.

In this first example, we will use the clause CONTAINER=CURRENT, meaning that the privilege is available in the root container only.

SQL> grant all privileges to c##cdb_dba container=CURRENT;

Grant succeeded.

Now logon to the user c##cdb_dba and attempt to create another user account and see that you can connect to the root containe.

SQL> connect c##cdb_dba/password

Connected.

SQL>

In this database we have two PDBs: PDB1 and PDB2, attempt to connect to both PDBs and not that the operation fails. But you can reconnect to root CDB CDB$ROOT.

SQL> alter session set container=PDB1;

ERROR:

ORA-01031: insufficient privileges

SQL> alter session set container=PDB2;

ERROR:

ORA-01031: insufficient privileges

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL>

Reconnect as the sys user and grant “all privileges” with a container = ALL. Meaning that you can connect to both root, pdb1 and pdb2. At the root level you can specify ALL and CURRENT for the clause CONTAINER. Now you can connect to all containers in PDB

SQL> grant all privileges to c##cdb_dba container=all;

Grant succeeded.

SQL> connect c##cdb_dba/password

Connected.

SQL> alter session set container=pdb1;

Session altered.

SQL> alter session set container=pdb2;

Session altered.

SQL> alter session set container=cdb$root;

Session altered.

SQL>

Larry Catt

OCP

12c, CDB, perl, Users

Local Users in Oracle Multitenant Databases

December 11, 2015 Larry Catt Leave a comment

In an Oracle 12c multitenant database, Local users are accounts created at the PDB level and they have no rights at the CDB level. This article demonstrate the use of Local user accounts. NOTE: To create a local user you must be connected to that PDB and user is only valid in that PDB. You can have same user name in multiple PDBs in the same

Logon to your server as oracle software owner.

[larry@linux2 ~]$ su – oracle

Password:

Last login: Wed Oct 26 10:07:02 EDT 2016 on pts/2

[oracle@linux2 ~]$

Logon to your CDB as an administrator, in this example we logon as CDB admin account c##cdb_dba.

SQL> connect c##cdb_dba/password as sysdba

Connected.

SQL>

Open the PDB pdb1 and pdb2 with the alter pluggable database command.

SQL> alter pluggable database all open;

Pluggable database altered.

SQL>

Connect to the PDB pdb1 to create local user in this single PDB.

SQL> alter session set container=pdb1;

Session altered.

SQL> create user user_pdb identified by password;

User created.

SQL>

Grant resource, create session to user user_pdb.

SQL> grant resource to user_pdb;

Grant succeeded.

SQL> grant create session to user_pdb;

Grant succeeded.

SQL>

Connect to pdb1 as the user user_pdb.

SQL> connect user_pdb/password@pdb1

Connected.

SQL>

Currently user user_pdb is only a local user for the PDB pdb1 not for the CDB CDB$ROOT or the PDB pdb2. This can be show by attempting to connect to both will fail.

SQL> connect user_pdb/password@pdb2

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL> connect user_pdb2/password@cdb1

ERROR:

ORA-01017: invalid username/password; logon denied

SQL> connect user_pdb/password@pdb1

Connected.

SQL>

As an admin user connect to PDB pdb2 and attempt to connect as the user ‘user_pdb’ NOTE: That this fails because no user ‘user_pdb’ exists in this PDB.

SQL> alter session set container=pdb2;

Session altered.

SQL> show user

USER is “C##CDB_DBA”

SQL> connect user_pdb/password

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL>

Reconnect as the admin user and create a user ‘user_pdb’ in PDB pdb2

SQL> connect C##CDB_DBA/password

Connected.

SQL> show con_name

CON_NAME

——————————

CDB$ROOT

SQL> alter session set container=pdb2;

Session altered.

SQL> show con_name

CON_NAME

——————————

PDB2

SQL> create user user_pdb identified by password;

User created.

SQL> grant dba to user_pdb;

Grant succeeded.

SQL> grant create session to user_pdb;

Grant succeeded.

SQL> connect user_pdb/password@pdb2;

Connected.

SQL>

This completes local user creation in PDBs.

Larry Catt

OCP

12c, CDB, Multitenant, PDB, privileges, Users

Privileges of Local Users in Oracle Multitenant Database

October 2, 2015 Larry Catt Leave a comment

In Oracle Multitenant Databases, local users only have access and privileges to a single PDB and not the CDB. You can have the same user name across multiple PDBs housed in the same CDB, but the users share no privileges and only shares a common name. This procedure give a brief overview of local user privileges in Oracle 12c Multitenant Databases. Listed below are the stated restriction on local user privileges.

A privilege granted locally can only be used in that container, even if granted by common user.
Both common and local users can grant privileges locally.
Both common and local users can grant privileges to other common or local users.
Grantor must connect to container and specify CONTAINER=CURRENT clause.
Common and Local user can grant privileges locally if they have appropriate privileges.

A privilege granted locally can only be used in that container, even if granted by common user.

As the common user c##dba logon to pdb1 and pdb2 creating users user_local.

SQL> connect c##dba/password@pdb1

Connected.

SQL> create user user_local identified by password;

User created.

SQL> connect c##dba/password@pdb2

Connected.

SQL> create user user_local identified by password;

User created.

SQL>

Now connect to pdb1 as c##dba and grant the user user_local the DBA role. Connect as user_local and create an additional local user user_test. NOTE: The user user_local can successfully connect to pdb1 and create an additional local user in this PDB.

SQL> connect c##dba/password@pdb1

Connected.

SQL> grant dba to user_local;

Grant succeeded.

SQL> connect user_local/password@pdb1

Connected.

SQL> create user user_test identified by password;

User created.

SQL>

Now attempt to connect to pdb2 with local user user_local. NOTE: The user user_local does not even have permission to connect to pdb2, even though the user exists in pdb2.

SQL> connect user_local/password@pdb2

ERROR:

ORA-01045: user USER_LOCAL lacks CREATE SESSION privilege; logon denied

Warning: You are no longer connected to ORACLE.

SQL>

Both common and local users can grant privileges locally.

Using local_user created above from pdb1, you can grant privileges to the newly created user user_test logon as local_user.

SQL> connect user_local/password@pdb1

Connected.

SQL> grant resource to user_test;

Grant succeeded.

SQL>

Both common and local users can grant privileges to other common or local users.

Using local_user created above from pdb1, you can grant privileges to the newly created user user_test logon as local_user.

SQL> connect user_local/password@pdb1

Connected.

SQL> grant resource to user_test;

Grant succeeded.

SQL>

Grantor must connect to container and specify CONTAINER=CURRENT clause or specify no CONTAINER clause. CONTAINER=ALL is not available in PDB.

As the user_local grant resource to user_test without a container clause and CONTAINER=ALL clause.

SQL> connect user_local/password@pdb1

Connected.

SQL> grant resource to user_test;

Grant succeeded.

SQL> grant resource to user_test container=all;

grant resource to user_test container=all

ERROR at line 1:

ORA-65029: a Local User may not grant or revoke a Common Privilege or Role

SQL>

Common and Local user can grant privileges locally if they have appropriate privileges.

Local user_local can create additional users in PDB pdb1 once the user has been granted the DBA role.

SQL> connect c##dba/password@pdb1

Connected.

SQL> grant dba to user_local;

Grant succeeded.

SQL> connect user_local/password@pdb1

Connected.

SQL> create user user_test identified by password;

User created.

SQL>

This completes the overview of privileges for local users in Oracle 12c Multitenant Database.

Larry Catt

OCP

12c, CDB, Multitenant, privileges, Users

Privileges of Common Users in Oracle Multitenant Database

September 21, 2015 Larry Catt Leave a comment

Privileges for Common Users in Oracle 12c multitenant database architecture have unique constraints allowing for privileges at the CDB level and the PDB levels. Common users may or may not have privileges at all containers in the multitenant database or they may be restricted to only a few. Additionally, privileges maybe restricted to objects in individual containers. The following are general rules governing privileges of common users and we will examine each rule in the below procedures:

A privilege granted commonly can be used in every container and future container.
Only common users can grant common privileges.
Granter of common privileges must connect to root and specify CONTAINER=ALL clause.
Can include system and object privilege.
Privileges should never be given to public.

A privilege granted commonly can be used in every container and future container.

Using the common user C##DBA we will create the user at the CDB level and grant DBA privileges with the CONTAINER clause equal to all.

SQL> show con_name

CON_NAME

——————————

CDB$ROOT

SQL> create user c##dba identified by password;

User created.

SQL> grant dba to c##dba container=all;

Grant succeeded.

SQL> connect c##dba/password@cdb1;

Connected.

SQL> create user c##user identified by password container=all;

User created.

SQL>

Now alter the session to container PDB1 as the same user and create a user there. NOTE: Permissions are good both in CDB1 and PDB1 for Common users. This is true because container clause was set to ALL.

SQL> show user

USER is “C##DBA”

SQL> alter session set container=PDB1;

Session altered.

SQL> show user

USER is “C##DBA”

SQL> create user pdb1_user identified by password;

User created.

SQL>

Only common users can grant common privileges.

As the common user c##dba grant resource to common user c##user.

SQL> connect c##dba/password@cdb1;

Connected.

SQL> grant resource to c##user;

Grant succeeded.

SQL>

Granter of common privileges must connect to root and specify CONTAINER=ALL clause for privileges to be granted in all containers.

This one is a little tricky because it appears that you can do it, however the key is common privilege, you can have privileges in the CDB and a few PDB and it not be common. This procedure shows an example of this scenario.
Connect to PDB1 as the common user c##dba and grant dba to the user c##user.

SQL> connect c##dba/password@pdb1

Connected.

SQL> grant dba to c##user;

Grant succeeded.

SQL>

Now a user with common privilege of DBA should be able to connect to any PDB in the CDB, however when we try to connect to pdb2, it fails. Further more when we look at the view dba_role_privs, we note that user C##USER does have DBA role, but not commonly.

SQL> connect c##user/password@pdb2

ERROR:

ORA-01045: user C##USER lacks CREATE SESSION privilege; logon denied

Warning: You are no longer connected to ORACLE.

SQL> connect c##dba/password@pdb1

Connected.

SQL> column grantee format a30

select grantee, common from dba_role_privs where grantee=’C##USER’;

SQL>

GRANTEE COM

—————————— —

C##USER NO

SQL>

Now logon to the root container as the C##DBA user and grant the DBA role to C##USER. Then view the dba_role_privs table and attempt to connect to PDB2.

SQL> connect c##dba/password@cdb1

Connected.

SQL> grant dba to c##user container=all;

Grant succeeded.

SQL> column grantee format a30

select grantee, common from dba_role_privs where grantee=’C##USER’;

SQL>

GRANTEE COM

—————————— —

C##USER NO

C##USER YES

SQL> connect c##user/password@pdb2

Connected.

SQL>

Can include system and object privilege.

Revoke all rights from user C##USER. If you are following from the above example, the user C##USER has been granted the DBA role twice locally and once commonly. So revoke Common grant at ROOT container and local root grant. Then connect to PDB1 to remove local grant at PDB1.

SQL> connect c##dba/password@cdb1

Connected.

SQL> column grantee format a30

select grantee, common from dba_role_privs where grantee=’C##USER’;

SQL>

GRANTEE COM

—————————— —

C##USER NO

C##USER YES

SQL> revoke dba from c##user container=ALL;

Revoke succeeded.

SQL> column grantee format a30

select grantee, common from dba_role_privs where grantee=’C##USER’;

SQL>

GRANTEE COM

—————————— —

C##USER NO

SQL> revoke dba from c##user container=current;

Revoke succeeded.

SQL> column grantee format a30

select grantee, common from dba_role_privs where grantee=’C##USER’;

SQL>

GRANTEE COM

—————————— —

C##USER NO

SQL> connect c##dba/password@pdb1

Connected.

SQL> revoke dba from c##user;

Revoke succeeded.

SQL>

SQL> column grantee format a30

select grantee, common from dba_role_privs where grantee=’C##USER’;

SQL>

no rows selected

SQL>

Now reconnect to C##DBA at the root level and grant connect and select on V$SESSIONS commonly the user C##USER. To test common privilege to objects.

SQL> connect c##dba/password@cdb1

Connected.

SQL> grant create session to c##user container=all;

Grant succeeded.

SQL> grant select on sys.v_$session to c##user container=all;

Grant succeeded.

SQL>

Connect the C##USER and execute a select statement on v$session and v$sql objects to validate access from root container. NOTE: We are able to access object v$session but not v$sql.

SQL> connect c##user/password@cdb1

Connected.

SQL> select count(*) from v$session;

COUNT(*)

———-

SQL> select count(*) from v$sql;

select count(*) from v$sql

ERROR at line 1:

ORA-00942: table or view does not exist

SQL>

Connect to PDB1 and repeat the above queries to validate common access privileges to objects.

SQL> connect c##user/password@pdb1

Connected.

SQL> select count(*) from v$session;

COUNT(*)

———-

SQL> select count(*) from v$sql;

select count(*) from v$sql

ERROR at line 1:

ORA-00942: table or view does not exist

SQL>

Privileges should never be given to public from common account.

Once a privilege is granted to public, all users have access to that privilege, which is a huge security risk. However, there is no restrictions built into Oracle to prevent this from occurring. We will demonstrate this by the creation of new user c##dba2, grant the new user no privileges, grant dba role to public, connect to both the CDB and PDB, and finally create a user. This is extremely dangerous, because granting privileges to public grants access to system and object privileges to every user. Finally we will revoke DBA role from public and note that the new user c##dba2 can no longer connect.

SQL> connect / as sysdba

Connected.

SQL> alter session set container=cdb$root;

Session altered.

SQL> create user c##dba2 identified by password;

User created.

SQL> grant dba to public container=all;

Grant succeeded.

SQL> connect c##dba2/password@cdb1

Connected.

SQL> connect c##dba2/password@pdb1

Connected.

SQL> create user testing identified by password;

User created.

SQL> connect / as sysdba

Connected.

SQL> revoke dba from public container=all;

Revoke succeeded.

SQL> connect c##dba2/password@cdb1

ERROR:

ORA-01045: user C##DBA2 lacks CREATE SESSION privilege; logon denied

Warning: You are no longer connected to ORACLE.

SQL>

This completes overview of Privileges for Common Users in Oracle Multitenant Databases.

Larry Catt

OCP

Catt Solutions

Category Archives: Users

Change time zone of Oracle RDBMS on Linux OS

Oracle 12c – User privilege analysis

Oracle 12c – User privilege analysis overview

Oracle 12c – Create the password file

Creating Common Users in Oracle Multitenant Databases

Users accounts in Multitenant Database

Container Clause for Common Users in Oracle Multitenant Databases

Local Users in Oracle Multitenant Databases

Privileges of Local Users in Oracle Multitenant Database

Privileges of Common Users in Oracle Multitenant Database

Oracle tips and tricks.