Subscribe

Share

Database, SQL and PL/SQL

Becoming Privileged and Creating Synonymously

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

By Melanie Caffrey

November/December 2017

This article is the 12th in a series that helps you build on the fundamentals you learned in the 12-part SQL 101 series in Oracle Magazine. The previous Beyond SQL 101 article, “Setting Parameters for Dynamic Productivity,” taught you how to pass parameters to database script files. You learned how to save the results from a database script execution to an .lst or .txt file with the SQL*Plus SPOOL command. Additionally, you saw how to save database script execution results to an .html file by using the SQL*Plus SET MARKUP HTML command functionality. You also learned how to control aspects of the SQL*Plus execution environment with various common SET commands. Last, you explored how to generate SQL statements at runtime with dynamic SQL and how to document such scripts with comments.

In this article you will

  • Learn how to create, alter, and drop users and schemas
  • Discover the difference between system privileges and object privileges
  • See how to GRANT and REVOKE privileges
  • Get an introduction to roles

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 used for this article’s examples. (View the script in a text editor for execution instructions.)

A Named Collection

For the examples in this series’s articles, you log in to the Oracle Database instance as a database user, SQL_201, to create objects (such as tables, views, indexes, and sequences) and then populate or manipulate these objects. The collection of objects a user creates and maintains is known as a schema. This article’s SQL_201 user owns the SQL_201 schema—the user and schema name are the same. Because each schema is owned by a single Oracle Database user account, the terms schema and user are often used interchangeably.

Listing 1 illustrates the relationship between the SQL_201 user and its associated schema. The query of the USER_OBJECTS data dictionary view in Listing 1 returns schema object information for the currently logged-in database user. All of the objects returned in the result set belong to the SQL_201 schema. Recall from the script you use to create the tables for the SQL_201 schema—used for this article’s examples—that you create a database user with the CREATE USER data definition language (DDL) command. Recall also from the same creation script that you were logged in as a user other than SQL_201 to create the SQL_201 user.

Listing 1: Query the USER_OBJECTS data dictionary to return schema object information for the currently logged-in database user.

SQL> select object_type, object_name
  2    from user_objects
  3  order by object_type, object_name;

OBJECT_TYPE          OBJECT_NAME
——————————————       —————————————————————————————
INDEX                DEPARTMENT_NAME_LOCATION_UK 
INDEX                DEPARTMENT_PK
INDEX                DEP_WAGE_INCREASE_I
INDEX                EMPLOYEE_PK
INDEX                EMPLOYEE_WAGE_INC_WORTH_BMI
INDEX                EMP_DEPT_FK
INDEX                EMP_HIRE_DATE_I
INDEX                SYS_C0010551
SEQUENCE             EMPLOYEE_ID_SEQ
SEQUENCE             ISEQ$$_94754
TABLE                ANNUAL_REVIEW
TABLE                DEPARTMENT
TABLE                EMPLOYEE
TABLE                EMPLOYEE_CTAS
TABLE                EMPLOYEE_EXTRA
TABLE                EMPLOYEE_IDENTITY
TABLE                EMPLOYEE_SUBSET
VIEW                 EMP_MANAGER_OVERVIEW
VIEW                 EMP_WAGE_INCREASE_IT_VW

A Privileged Existence

To create a new user, the current user must have the CREATE USER system privilege. (Privileges will be discussed later in this article.) To see what privileges are currently associated with the SQL_201 user, you can run the query in Listing 2 as the SQL_201 user. Currently the only privilege explicitly granted to the SQL_201 user is CREATE VIEW. If you are not the administrator of the database you are using to run this series articles’ examples, you may need your database administrator’s assistance to run the first five statements in Listing 3.

Listing 2: See what system privileges have been granted to the SQL_201 user.

SQL> select privilege
  2    from user_sys_privs;

PRIVILEGE
————————————————————————————————————————
CREATE VIEW

Listing 3: A privileged database user grants system privileges to other database users.

--Connect as a privileged user in order to grant necessary 
privileges to SQL_201
SQL> connect / as sysdba
Connected.

--This step is only necessary if you are using pluggable 
databases (also known as containers)
SQL> alter session set container=bynd;

Session altered.

SQL> select user                 
  2    from dual;

USER
———————————————————————————————
SYS

SQL> grant create user to sql_201;

Grant succeeded.

SQL> grant select any dictionary to sql_201;

Grant succeeded.

--Reconnect as SQL_201 to continue this article's exercises
SQL> connect sql_201@bynd
Enter password: 
Connected.

SQL> select privilege
  2    from user_sys_privs
  3  order by privilege;

PRIVILEGE
————————————————————————————————————————
CREATE USER
CREATE VIEW
SELECT ANY DICTIONARY

To grant the CREATE USER system privilege to the SQL_201 user, you must be logged in to the database as a privileged user with the ability to grant this privilege to another user. Listing 3 demonstrates that the SYS user has the ability to grant that privilege to the SQL_201 user.

Using the command

connect / as sysdba

is a shorthand method of connecting to the database as the SYS user. The SYS user is the most privileged Oracle Database user. It is a default database user created at the time an Oracle database instance is created, and it not only is automatically granted the SYSDBA system privilege but also owns the data dictionary.

In the example in Listing 3, the SYS user grants not only the CREATE USER system privilege to the SQL_201 user but also the SELECT ANY DICTIONARY system privilege. The SELECT ANY DICTIONARY system privilege enables the SQL_201 user to query many of the DBA_ and V$ data dictionary views as well as the USER_ and ALL_ views. For more information about which data dictionary views are excluded by default from the SELECT ANY DICTIONARY system privilege, see the Oracle Database Security Guide. Note that only trusted users who are to be tasked with database administration activities requiring them to create users should be given the CREATE USER system privilege. (It is not a privilege generally granted to a database developer.) Similarly, the SELECT ANY DICTIONARY system privilege should be granted with discretion as well. In most situations, a database administrator should grant SELECT access to only those data dictionary views that developers need beyond those they already have access to, such as USER_ and ALL_. Grants given to developers should be doled out on a case-by-case basis as determined by the database administrator.

The query in Listing 4 demonstrates a useful query of the DBA_USERS data dictionary view. The information obtained from the query in Listing 4 is used to help construct the input to the keywords for the statement in Listing 5. The statement in Listing 5 demonstrates DDL commands for creating an Oracle Database user. Every Oracle Database user must have a username and a password.

Listing 4: Obtain assigned tablespace information for users from the DBA_USERS data dictionary view.

SQL> select username, default_tablespace, temporary_tablespace
  2    from dba_users 
  3   where username = 'SQL_201';

USERNAME     DEFAULT_TABLESPACE     TEMPORARY_TABLESPACE
————————     ————————————————————   ——————————————————————
SQL_201      USERS                  TEMP

Listing 5: Create a new user, and assign tablespaces and quota.

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

User created.

(Note that there are exceptions to this database password requirement. You may also create a user that connects and is authenticated either via the operating system, a directory service such as Oracle Internet Directory, or a trusted certificate, among other authentication methods. However, these methods of database authentication should be administered with great care and are beyond the scope of this article. To learn more about these methods of authentication and others, see the Oracle Database Security Guide.)

Additionally, if the user is to create objects (tables, indexes, views, and so on), it should be assigned a default tablespace, where such objects are then stored. A space usage quota is assigned with the keywords

QUOTA {integer[K|M] | UNLIMITED} ON <tablespace name> 

The temporary tablespace assignment determines where data sorting that cannot be performed in memory is done. (A temporary tablespace is also used to store information including, but not limited to, the rows of temporary tables, hash aggregations used for GROUP BY and UNIQUE operations, and hash joins to join larger datasets. To learn about any of these additional uses for a temporary tablespace, search for the relevant keywords in the database documentation.) The statement in Listing 5 illustrates a new user, BEYOND_101, being created with the password 201. The keywords IDENTIFIED BY, followed by a password, assign the password to the user being created. The tablespace, USERS, is assigned as the default tablespace, and the temporary tablespace, TEMP, is assigned as the temporary tablespace. Finally, the user is granted a quota of 100 megabytes of storage space for its objects in the USERS tablespace.

The Circle of Life

Although the BEYOND_101 user has been created successfully, it lacks the necessary system privilege to log in to the database. Listing 6 demonstrates what happens if the user attempts to log in to the database without appropriate permissions in place. Listing 7 shows which grant is necessary to give the BEYOND_101 user the ability to create a session within the database. The CREATE SESSION system privilege is granted to the BEYOND_101 user via the GRANT <privilege> TO <username> syntax. Additionally, the CREATE TABLE system privilege is granted to the BEYOND_101 user, demonstrating that multiple system privileges can be conferred in a single grant statement. Listing 7 also demonstrates how the BEYOND_101 user is now able to successfully log in to the database and create and populate a table stored in the USERS tablespace.

Listing 6: User must be granted a system privilege to log in to the database.

SQL> connect beyond_101@bynd
Enter password: 
ERROR:
ORA-01045: user BEYOND_101 lacks CREATE SESSION privilege; logon denied

Listing 7: User can now log in to the database and create a table in its schema.

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

Session altered.

SQL> GRANT CREATE SESSION, create table TO beyond_101;

Grant succeeded.

SQL> connect beyond_101@bynd

Enter password: 
Connected.
SQL> create table test_me (id number, name varchar2(10));

Table created.

SQL> insert into test_me (id, name) values (1, 'Try it');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test_me;

       ID NAME
————————— ——————————
        1 Try it

Listing 8 outlines how a user can be altered by a change in its quota and password. Note how the act of changing the quota to zero kilobytes enables the user to create a table in the USERS tablespace but results in an error when the user tries to insert data into the table. A user can create a table in its default tablespace even when it has no assigned quota, because, by default, Oracle Database does not look to reduce quota usage for the user until a table segment has been created for a table. (A segment is a logical storage structure that stores a schema’s object data—in this case, table data—within the schema’s default tablespace.) Users are able to create a table within the USERS tablespace without error because of Oracle Database’s default deferred segment creation behavior. To learn more about deferred segment creation, see the Oracle Database Administrator’s Guide.

Listing 8: Alter a user with the ALTER USER data definition language command.

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

Session altered.

SQL> ALTER USER beyond_101 quota 0K on users;

User altered.

SQL> connect beyond_101@bynd
Enter password: 
Connected.
SQL> create table test_again (id number);

Table created.

SQL> insert into test_again (id) values (2);
insert into test_again (id) values (2)
            *
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'USERS'

Listing 9 demonstrates what happens if you attempt to drop a user that owns schema objects. The error returned means that schema objects were found for the user named in the DROP USER statement. Listing 10 shows the statement that should be used when you want to drop a user along with its schema objects. Required use of the CASCADE option protects you from accidentally dropping a user whose schema objects are valuable to your database. The DROP USER command without the CASCADE option drops the user only if that user does not own any objects. Note also that once you drop the user, you are no longer able to retrieve information about that user or its schema objects from the data dictionary.

Listing 9: Trying to drop a user that owns schema objects results in an error.

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

Session altered.

SQL> select object_type, object_name
  2    from dba_objects
  3   where owner = 'BEYOND_101'
  4  order by object_type, object_name;

OBJECT_TYPE          OBJECT_NAME
———————————————      —————————————————
TABLE                TEST_AGAIN
TABLE                TEST_ME


SQL> DROP USER beyond_101;
DROP USER beyond_101
*
ERROR at line 1:
ORA-01922: CASCADE must be specified to drop 'BEYOND_101'

Listing 10: Use the CASCADE option when dropping a user that owns schema objects.

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

Session altered.

SQL> DROP USER beyond_101 CASCADE;

User dropped.

SQL> select object_type, object_name
  2    from dba_objects
  3   where owner = 'BEYOND_101'
  4  order by object_type, object_name;

no rows selected

SQL> select *       
  2    from dba_users
  3   where username = 'BEYOND_101';

no rows selected

Rights of Execution

A privilege is a right to execute a certain type of SQL statement. The two types of privileges are system (introduced in the first half of this article) and object. The right to create a table or an index is an example of a system privilege. System privileges are often rights to execute DDL commands but can also be rights to execute data manipulation language (DML) commands, such as SELECT ANY DICTIONARY or DELETE ANY TABLE. The rights to query a particular table not owned by the current database user, to perform DML commands on such a table, or to access an integer from a sequence (again, not owned by the current database user) are all examples of object privileges.

Recall that in Listing 7 it was necessary to grant the CREATE SESSION system privilege to the BEYOND_101 user before it could log in to the database. Two individual system privileges were granted to the BEYOND_101 user: CREATE SESSION and CREATE TABLE. Although it is possible to individually grant each system privilege to each user, it may be simpler and easier for a database administrator if such privileges are instead granted through roles. A role is a collection of privileges that can be granted to a user via GRANT keyword syntax similar to that used for granting privileges. Consider the example in Listing 11.

Listing 11: Collect system privileges into a role, and grant the role to users.

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

Session altered.

SQL> CREATE ROLE system_privs_for_201;

Role created.

SQL> grant create user, select any dictionary to system_privs_for_201;

Grant succeeded.

SQL> grant system_privs_for_201 to sql_201;

Grant succeeded.

SQL> select grantee, granted_role
  2    from dba_role_privs
  3   where grantee = 'SQL_201';

GRANTEE           GRANTED_ROLE
———————————       ———————————————————————
SQL_201           SYSTEM_PRIVS_FOR_201
SQL_201           RESOURCE
SQL_201           CONNECT

In Listing 11, because the SQL_201 user has not been granted the CREATE ROLE system privilege, a privileged user must be used to create the SYSTEM_PRIVS_FOR_201 role. Next, the two system privileges already granted to the SQL_201 user are granted to the new role. Finally, the new role is granted to the SQL_201 user. The query of DBA_ROLE_PRIVS confirms that the SYSTEM_PRIVS_FOR_201 role has been granted to the SQL_201 user. This query also illustrates that the SQL_201 user has been granted two additional roles: RESOURCE and CONNECT. The queries in Listing 12 show which privileges have been granted to the RESOURCE and CONNECT roles. Recall from the script you used to create the tables for the SQL_201 schema used for this article’s examples that one of the statements executed was

grant connect, resource to sql_201;

Listing 12: Display the system privileges granted to the CONNECT and RESOURCE roles.

SQL> select grantee, privilege
  2    from dba_sys_privs
  3   where grantee in ('CONNECT', 'RESOURCE')
  4  order by grantee, privilege;

GRANTEE                 PRIVILEGE
———————————————         ——————————————————
CONNECT                 CREATE SESSION
CONNECT                 SET CONTAINER
RESOURCE                CREATE CLUSTER
RESOURCE                CREATE INDEXTYPE
RESOURCE                CREATE OPERATOR
RESOURCE                CREATE PROCEDURE
RESOURCE                CREATE SEQUENCE
RESOURCE                CREATE TABLE
RESOURCE                CREATE TRIGGER
RESOURCE                CREATE TYPE

When you executed the above statement, because the SQL_201 user was granted the CONNECT and RESOURCE roles, the user received the ability to perform many of the actions it has performed in this series’s articles, including CREATE SESSION, CREATE TABLE, and CREATE SEQUENCE. Listing 13 demonstrates how to revoke a granted privilege from a user. This command can also be used to revoke a privilege or set of privileges from a role.

Listing 13: Revoke a privilege or a set of privileges from a user or a role.

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

Session altered.

SQL> REVOKE create user from sql_201;

Revoke succeeded.

Conclusion

This article 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 similar fashion to individual privileges. Last, you witnessed how privileges and roles should be granted on a discretionary as-needed basis for security and ease-of-administration purposes.

In the next article in this series, you’ll learn more about object privileges and how to create and alter profiles and synonyms.

Next Steps

READ SQL 101, Parts 1–12.

READ more Beyond SQL 101.

DOWNLOAD the sample script for this article.

Photography by Ray Ng