Subscribe

Share

Database, SQL and PL/SQL

Limited Profiles and Private References

Part 13 in a second series on the basics of the relational database and SQL

By Melanie Caffrey

January/February 2018

This article is the thirteenth, and last, in a series that helps you build on the fundamentals that you learned in the 12-part SQL 101 series in Oracle Magazine. The previous Beyond SQL 101 article, “Becoming Privileged and Creating Synonymously,” taught you how to create, alter, and drop users and how users relate to schemas. You learned what system privileges and object privileges are and how privileges are granted and revoked. Additionally, you saw that roles are collections of privileges that can be granted and revoked in a similar fashion as individual privileges. Lastly, you witnessed how privileges and roles should be granted on a discretionary as-needed basis for security and ease of administration.

In this article, you will

  • Learn more about object privileges
  • Discover the difference between public and private synonyms
  • See how to extend privileges to others
  • Get an introduction to profiles

To try out the examples in this series, you need access to an Oracle Database instance. If necessary, download and install an Oracle Database edition for your operating system. I recommend installing Oracle Database, Enterprise Edition 12c Release 2 (12.2.0.1.0). If you install the Oracle Database software, choose the installation option that enables you to create and configure a database. A new database, including sample user accounts and their associated schemas, will be created for you. (Note that SQL_201 is the user account to use for the examples in this series; it’s also the schema in which you’ll create database tables and other objects.) When the installation process prompts you to specify schema passwords, enter and confirm passwords for the SYS and SYSTEM users and make a note of them.

Finally—whether you installed the database software from scratch or have access to an existing Oracle Database instance—download, unzip, and execute the SQL script to create the tables for the SQL_201 schema that are used for this article’s examples. (View the script in a text editor for execution instructions.)

The Object of Your Intention

As you learned in the previous Beyond SQL 101 article, “Becoming Privileged and Creating Synonymously,” object privileges allow you the right to perform actions including but not limited to

  • Querying a table not owned by the current database user
  • Performing data manipulation language commands on such a table
  • Accessing an integer from a sequence not owned by the current database user

Listing 1 re-creates the BEYOND_101 user and demonstrates the result received when the user attempts to query the EMPLOYEE table. Although the table exists in schema SQL_201, the query result is

ORA-00942: table or view does not exist

This error means that until the BEYOND_101 user has been granted the appropriate access for the object, Oracle Database does not know that the table or view is an object upon which the BEYOND_101 user may perform actions. This user must be granted the necessary object privilege from the table’s owner.

Code Listing 1: A user must be granted query access to a database table the user does not own

SQL> set lines 60
SQL> connect / as sysdba
Connected.
SQL> alter session set container=bynd;

Session altered.

SQL> CREATE USER beyond_101                          
  2  IDENTIFIED BY 201
  3  DEFAULT TABLESPACE users
  4  TEMPORARY TABLESPACE temp
  5  QUOTA 100M ON USERS; 

User created.

SQL> GRANT CREATE SESSION, CREATE TABLE TO beyond_101;

Grant succeeded.

SQL> connect beyond_101@bynd
Enter password: 
Connected.

SQL> select first_name||' '||last_name
  2    from employee
  3  order by last_name;
  from employee
       *
ERROR at line 2:
ORA-00942: table or view does not exist

Listing 2 shows how the SQL_201 user grants the necessary object privilege to the BEYOND_101 user so that the query may succeed. Note, however, that after the SQL_201 user has granted the SELECT object privilege for the EMPLOYEE table to the BEYOND_101 user, when the BEYOND_101 user reattempts the query action, the action results in the same error as that received in Listing 1. The same error takes place because, in the absence of a synonym (discussed later in this article), each Oracle Database user must qualify the name of any object it does not own. Listing 3 demonstrates how to qualify the name of the EMPLOYEE table so that the BEYOND_101 user may successfully query it. To qualify the name of an object, you provide the schema name and the object name separated by the period (.) character, for example, SQL_201.EMPLOYEE.

Code Listing 2: The SQL_201 user grants the SELECT object privilege for the EMPLOYEE table to the BEYOND_101 user

SQL> connect sql_201@bynd
Enter password: 
Connected.

SQL> grant select on employee to beyond_101;

Grant succeeded.

SQL> connect beyond_101@bynd
Enter password: 
Connected.
SQL> select first_name||' '||last_name
  2    from employee
  3  order by last_name;
  from employee
       *
ERROR at line 2:
ORA-00942: table or view does not exist

Code Listing 3: Qualify the name of the queried table by appending the schema name

SQL> select first_name||' '||last_name
  2    from sql_201.employee
  3  order by last_name;

FIRST_NAME||''||LAST_NAME
————————————————————————————————————————————————————————————
Lori Dovichi
Emily Eckhardt
Roger Friedli
Betsy James
Thomas Jeffrey
Sasha Meyer
Matthew Michaels
Frances Newton
Donald Newton
Don Rose
Gerald Sowell
Mary Streicher
Marcy Tamra
Theresa Wong
mark leblanc
michael peterson

16 rows selected.

By Any Other Name

If you do not wish to qualify an object name for which you’ve been granted access, you can create a synonym. A synonym is an alias for a qualified object name. Listing 4 shows the SQL statement you use to create a synonym. The syntax, at its simplest, is

CREATE [OR REPLACE] SYNONYM  FOR 

Code Listing 4: Create a synonym to use as an alias for a qualified object name

SQL> connect / as sysdba
Connected.

SQL> alter session set container=bynd;

Session altered.

SQL> grant create synonym to beyond_101;

Grant succeeded.

SQL> connect beyond_101@bynd;
Enter password: 
Connected.

SQL> CREATE SYNONYM employee FOR sql_201.employee;

Synonym created.

SQL> select first_name||' '||last_name
  2    from employee
  3  order by last_name;

FIRST_NAME||''||LAST_NAME
————————————————————————————————————————————————————————————
Lori Dovichi
Emily Eckhardt
Roger Friedli
Betsy James
Thomas Jeffrey
Sasha Meyer
Matthew Michaels
Frances Newton
Donald Newton
Don Rose
Gerald Sowell
Mary Streicher
Marcy Tamra
Theresa Wong
mark leblanc
michael peterson

16 rows selected.

A synonym created using this syntax is called a private synonym, because it is private to the schema that created it. No other schema can use the synonym created in Listing 4, because it is a synonym created within the BEYOND_101 schema as another name for the SQL_201.EMPLOYEE qualified table name. All synonyms are private unless you specify the PUBLIC keyword. A public synonym is visible to every user in your database. For example, the SQL_201 and BEYOND_101 users are able to query the DBA_TABLES data dictionary view without having to qualify the view name with the SYS schema name, because public synonyms were created for all of the data dictionary views when the database and the data dictionary were created.

It is good practice to never create public synonyms for your application objects. Suppose you have a table named PRODUCT in your schema, but a table named PRODUCT also exists in another schema. Also suppose that the PRODUCT table in the other schema has a public synonym that points to it. When you issue a statement against the PRODUCT table without qualifying the schema, Oracle Database will first try to access the PRODUCT table within your schema. If it is unable to access the PRODUCT table within your schema, it will then attempt to refer to the public synonym for the PRODUCT table in the other schema. If the two PRODUCT tables are structurally different or contain different data, trying to access them without qualifying them could yield unexpected or erroneous results.

An Extension of Rights

To grant an object privilege to another user, you must either

  • Be the owner of the object, or
  • Have received the privilege with the additional keywords WITH GRANT OPTION

The WITH GRANT OPTION addition to an object privilege grant allows the grantee the ability to pass that same privilege on to others. Listing 5 illustrates how the WITH GRANT OPTION privilege addition allows one user the ability to pass an object privilege on to another. The BEYOND_101 user does not have the ability to query the DBA_TABLES data dictionary view. The SQL_201 user tries to grant the access, but has access only to query the view, not to grant access to it to others.

The SYS user is the owner of the data dictionary and, therefore, is able to grant query access to the SQL_201 user with the ability to pass that object privilege on to others. After the SQL_201 user has been granted an object privilege WITH GRANT OPTION, it can pass that privilege on to others, even though it does not own the object for which the grant is being made.

Code Listing 5: A user granted the WITH GRANT OPTION privilege addition can pass an object privilege to another user

SQL> connect beyond_101@bynd
Enter password: 
Connected.

SQL> select table_name
  2    from dba_tables
  3   where owner = 'SQL_201';
  from dba_tables
       *
ERROR at line 2:
ORA-00942: table or view does not exist

--The SQL_201 user is able to query from the DBA_TABLES data dictionary view
SQL> connect sql_201@bynd
Enter password: 
Connected.

SQL> select table_name
  2    from dba_tables
  3   where owner = 'SQL_201'
  4  order by table_name;

TABLE_NAME
————————————————————————————————————————————————————————————
ANNUAL_REVIEW
DEPARTMENT
EMPLOYEE
EMPLOYEE_CTAS
EMPLOYEE_EXTRA
EMPLOYEE_IDENTITY
EMPLOYEE_SUBSET

7 rows selected.

--The SQL_201 user has been granted query access, but cannot yet pass it on
SQL> grant select on dba_tables to beyond_101;
grant select on dba_tables to beyond_101
                *
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> connect / as sysdba
Connected.

SQL> alter session set container=bynd;

Session altered.

--The SYS user grants the SQL_201 user the ability to pass on the object privilege
SQL> grant select on dba_tables to sql_201 WITH GRANT OPTION;

Grant succeeded.

SQL> connect sql_201@bynd
Enter password: 
Connected.

--This time, the grant succeeds
SQL> grant select on dba_tables to beyond_101;

Grant succeeded.

SQL> connect beyond_101@bynd
Enter password: 
Connected.

--Similarly, now the BEYOND_101 user can query the DBA_TABLES data dictionary view
SQL> select table_name
  2    from dba_tables
  3   where owner = 'SQL_201'
  4  order by table_name;

TABLE_NAME
————————————————————————————————————————————————————————————
ANNUAL_REVIEW
DEPARTMENT
EMPLOYEE
EMPLOYEE_CTAS
EMPLOYEE_EXTRA
EMPLOYEE_IDENTITY
EMPLOYEE_SUBSET

7 rows selected.

Listing 6 demonstrates how the WITH ADMIN OPTION privilege addition can be used to allow a user to pass system privileges on to others as well. The BEYOND_101 user unsuccessfully attempts to create a sequence for a newly created table. After the SYS user grants the SQL_201 user the ability to create sequences and pass such a privilege on to others, the SQL_201 user grants the CREATE SEQUENCE system privilege to the BEYOND_101 user, and the BEYOND_101 user is then able to successfully create a sequence.

Code Listing 6: A user granted the WITH ADMIN OPTION privilege addition can pass a system privilege to another user

SQL> create table test_me (id number);

Table created.

--The BEYOND_101 user has not been granted the ability to create a sequence
SQL> create sequence test_me_seq;
create sequence test_me_seq
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> connect / as sysdba
Connected.
SQL> alter session set container=bynd;

Session altered.

SQL> grant create sequence to sql_201 WITH ADMIN OPTION;

Grant succeeded.

SQL> connect SQL_201@bynd
Enter password: 
Connected.

SQL> grant create sequence to beyond_101;

Grant succeeded.

SQL> connect beyond_101@bynd
Enter password: 
Connected.

--This time, the sequence is created successfully.
SQL> create sequence test_me_seq;

Sequence created.

 

Knowing Your Limits

By default, every Oracle Database user account is constrained in terms of the amount of database resources it is allowed to consume and how its password features are managed. These constraints are managed through a named profile. A profile can enforce settings including but not limited to the following:

  • How long a user account may be logged in to the database but not actively working before the account is automatically logged out
  • How many times a user may attempt to log in to the database with an incorrect password before the account is locked
  • How many individual sessions a single user can be logged in to for the database at any given time

The statements in Listing 7 illustrate how to discover the profile currently associated with a given user account, as well as how to determine the limit restrictions assigned to the profile. The SQL_201 user has been assigned the DEFAULT profile.

Every user is assigned the DEFAULT profile unless another named profile is explicitly assigned to the user during account creation or through an ALTER USER statement. Currently, the SQL_201 user can have no more than 10 failed login attempts before its user account is locked, and it must be unlocked by a privileged user such as a database administrator. Additionally, the SQL_201 user is currently allowed to be connected to the database but not actively executing any statements for an unlimited amount of time. These two limit settings are outlined in bold in the final result set for Listing 7.

Code Listing 7: Discover the name of and resource limits for the profile assigned to the SQL_201 user

SQL> connect / as sysdba
Connected.
SQL> alter session set container=bynd;

Session altered.

SQL> select profile
  2    from dba_users
  3   where username = 'SQL_201';

PROFILE
————————————————————————————————————————————————————————————
DEFAULT

SQL> select resource_name, resource_type, limit  
  2    from dba_profiles
  3   where profile = 'DEFAULT'
  4  order by resource_type, resource_name;

RESOURCE_NAME             RESOURCE LIMIT
————————————————————————— ———————— ——————————
COMPOSITE_LIMIT           KERNEL   UNLIMITED
CONNECT_TIME              KERNEL   UNLIMITED
CPU_PER_CALL              KERNEL   UNLIMITED
CPU_PER_SESSION           KERNEL   UNLIMITED
IDLE_TIME                 KERNEL   UNLIMITED
LOGICAL_READS_PER_CALL    KERNEL   UNLIMITED
LOGICAL_READS_PER_SESSION KERNEL   UNLIMITED
PRIVATE_SGA               KERNEL   UNLIMITED
SESSIONS_PER_USER         KERNEL   UNLIMITED
FAILED_LOGIN_ATTEMPTS     PASSWORD 10
PASSWORD_GRACE_TIME       PASSWORD 7
PASSWORD_LIFE_TIME        PASSWORD 180
PASSWORD_LOCK_TIME        PASSWORD 1
PASSWORD_REUSE_MAX        PASSWORD UNLIMITED
PASSWORD_REUSE_TIME       PASSWORD UNLIMITED
PASSWORD_VERIFY_FUNCTION  PASSWORD NULL

16 rows selected.

The statement in Listing 8 demonstrates how to create a profile and assign password and kernel limit restrictions to it. The statement in Listing 9 outlines how to explicitly assign a profile to a user. The setting for FAILED_LOGIN_ATTEMPTS has been reduced to five, and the setting for IDLE_TIME has been reduced to 20 minutes. Note that any limit not explicitly set in the SQL_201_PROFILE profile exists with the value of “DEFAULT.” A profile limit that has a DEFAULT value has the same value as that specified for the same-named limit in the DEFAULT profile.

Code Listing 8: Create a profile with assigned password and kernel setting limits

SQL> connect / as sysdba
Connected.
SQL> alter session set container=bynd;

Session altered.

SQL> CREATE PROFILE sql_201_profile
  2  LIMIT
  3  FAILED_LOGIN_ATTEMPTS 5
  4  IDLE_TIME             20;

Profile created.

Code Listing 9: Assign a profile to a user and review all assigned settings

SQL> alter user sql_201 PROFILE sql_201_profile;

User altered.

SQL> select resource_name, resource_type, limit
  2    from dba_profiles
  3   where profile = 'SQL_201_PROFILE'
  4  order by resource_type, resource_name;

RESOURCE_NAME             RESOURCE LIMIT
————————————————————————— ———————— ——————————
COMPOSITE_LIMIT           KERNEL   DEFAULT
CONNECT_TIME              KERNEL   DEFAULT
CPU_PER_CALL              KERNEL   DEFAULT
CPU_PER_SESSION           KERNEL   DEFAULT
IDLE_TIME                 KERNEL   20
LOGICAL_READS_PER_CALL    KERNEL   DEFAULT
LOGICAL_READS_PER_SESSION KERNEL   DEFAULT
PRIVATE_SGA               KERNEL   DEFAULT
SESSIONS_PER_USER         KERNEL   DEFAULT
FAILED_LOGIN_ATTEMPTS     PASSWORD 5
PASSWORD_GRACE_TIME       PASSWORD DEFAULT
PASSWORD_LIFE_TIME        PASSWORD DEFAULT
PASSWORD_LOCK_TIME        PASSWORD DEFAULT
PASSWORD_REUSE_MAX        PASSWORD DEFAULT
PASSWORD_REUSE_TIME       PASSWORD DEFAULT
PASSWORD_VERIFY_FUNCTION  PASSWORD DEFAULT

16 rows selected.

 

Conclusion

This article taught you more about object privileges. You learned how to grant them and how grantees might need to qualify an object name with its schema name in order to take advantage of their granted access. Additionally, you explored the difference between public and private synonyms, and why it is good practice to defer to using private synonyms in your application code. You discovered how to extend privileges to others when privileges have been granted with the WITH GRANT OPTION or WITH ADMIN OPTION options. Lastly, you saw how profiles are used to limit a user’s resource and password settings.

This article concludes the Beyond SQL 101 series. You’ve learned basic relational database concepts and many SQL coding constructs beyond those discussed in the SQL 101 series. Be sure to continue to read the documentation and try existing and new Oracle Database features. Thank you for being readers of Oracle Magazine and of the Beyond SQL 101 series. As you continue writing SQL, my hope is that you enjoy it as much as I do.

Next Steps

READ SQL 101, Parts 1–12.

LEARN more about relational database design and concepts.

Photography by Ray Ng