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
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.
DISCLAIMER: We've captured these popular historical magazine articles for your reference. Links etc contained within these article possibly won't work. These articles are provided as-is with no guarantee that the information within them is the best advice for current versions of the Oracle Database.