This is the 12th day of my participation in the September Challenge.

Chapter 13 SQL Command CREATE ROLE

Create the role

The outline

CREATE ROLE role-name
Copy the code

parameter

• role-name– The name of the role to be created, which is an identifier. Role names are case insensitive.

describe

The CREATE ROLE command creates a ROLE. A role is a set of naming rights that can be assigned to multiple users. A role can be assigned to multiple users, and a user can be assigned to multiple roles. Roles are available system-wide; they are not limited to a specific namespace.

The role name can be any valid identifier of up to 64 characters. Role names must follow the identifier naming convention. Role names can contain Unicode characters. Role names are case insensitive. If the support delimited Identifier configuration option is selected (the default setting), the role name can be a delimited identifier enclosed in quotes. If it is a delimited identifier, the role name can be an SQL reserved word. It can contain periods (.) , caret (^), and two-character arrow sequence (->). It cannot contain comma (,) or colon (:) characters. It can start with any valid character other than an asterisk (*).

When a role is initially created, it is just a name; It has no permissions. To add permissions to roles, use the GRANT command. You can also assign one or more roles to roles using the GRANT command. This allows the creation of a role hierarchy.

SQL issues a SQLcode-118 error if you call CREATE ROLE to CREATE an existing ROLE. By calling the $SYSTEM. SQL. Security. RoleExists () method to determine whether it is existing:

  WRITE $SYSTEM.SQL.Security.RoleExists("%All"),!
  WRITE $SYSTEM.SQL.Security.RoleExists("Madmen")
Copy the code

This method returns 1 if the specified role exists, and 0 if it does not. Role names are case insensitive.

To DROP a ROLE, run the DROP ROLE command.

permissions

The CREATE ROLE command is a privileged operation. Before using CREATE ROLE in embedded SQL, you need to log in as a user with %Admin_Secure:USE permission. Otherwise, a SQLCODE-99 error (privilege conflict) will result. Assign users with appropriate permissions using the $system.security.login () method:

   DO $SYSTEM.Security.Login(username,password)
   &sql(      )
Copy the code

You must have %Service_Login: Use permission to invoke the $system.security.login () method.

The sample

The following example attempts to create a role named BkUser. The user “Fred” in the first example does not have role creation privileges. The user “_system” in the second example does have CREATE ROLE authority.

ClassMethod CreateRole(a)
{
	d $SYSTEM.Security.Login("yao"."YaoPassword")
	&sql(
		CREATE ROLE BkUser
	)
	if SQLCODE = -99{ w ! ."No permission to create roles" 
	} elseif SQLCODE = -118{ w ! ."The character already exists." 
	} else{ w ! ."Create a role. The error code is:",SQLCODE 
	}
}

Copy the code

ClassMethod CreateRole1(a)
{
	d $SYSTEM.Security.Login("_SYSTEM"."SYS")
Main
	&sql(
		CREATE ROLE BkUser
	)
	if SQLCODE = -99{ w ! ."No permission to create roles" 
	} elseif SQLCODE = -118{ w ! ."The character already exists." 
	} else{ w ! ."Create a role. The error code is:",SQLCODE 
	}
Cleanup
	s toggle = $RANDOM(2)
	if toggle = 0{ &sql( DROP ROLE BkUser ) w ! ."Delete role error code:",SQLCODE
	} else{ w ! ."Not deleted"
		q 
	}
}
Copy the code