Creating a Read-Only Database User - Revision history

Russ horton at 16:15, 14 March 2013

2013-03-14T16:15:31Z

← Older revision		Revision as of 16:15, 14 March 2013
Line 1:		Line 1:
	=Overview=		=Overview=
	Your CMS database is pre-configured with two database user accounts, dbo and cmsuser. The dbo user allows full access to the database including the ability to create/edit/drop tables, views, stored procedures, etc~~. Really a form of Admin~~. The cmsuser database user, on the other hand, is a normal user with read/write permissions but cannot alter the database structure.		Your CMS database is pre-configured with two database user accounts, dbo and cmsuser. The '''dbo''' (admin) user allows full access to the database including the ability to create/edit/drop tables, views, stored procedures, etc. The '''cmsuser''' database user, on the other hand, is a normal user with read/write permissions but cannot alter the database structure.

	In some cases you may wish to create an additional user that has just read-only access and no write permissions. Typically this would be done for report writing although in many report writing situations you'd also want that user to have Execute permissions to make use of functions and stored procedures in the database. The SQL provided below can be run in Sybase's utility called dbisqlc.exe found in the \NewHaven Software\sa12\bin\ directory. Running this statement requires you be connected as the dbo database user.		In some cases you may wish to create an additional user that has just read-only access and no write permissions. Typically this would be done for report writing although in many report writing situations you'd also want that user to have Execute permissions to make use of functions and stored procedures in the database. The SQL provided below can be run in Sybase's utility called dbisqlc.exe found in the \NewHaven Software\sa12\bin\ directory. Running this statement requires you be connected as the dbo database user.

Russ horton at 01:04, 6 March 2013

2013-03-06T01:04:12Z

← Older revision		Revision as of 01:04, 6 March 2013
Line 4:		Line 4:
	In some cases you may wish to create an additional user that has just read-only access and no write permissions. Typically this would be done for report writing although in many report writing situations you'd also want that user to have Execute permissions to make use of functions and stored procedures in the database. The SQL provided below can be run in Sybase's utility called dbisqlc.exe found in the \NewHaven Software\sa12\bin\ directory. Running this statement requires you be connected as the dbo database user.		In some cases you may wish to create an additional user that has just read-only access and no write permissions. Typically this would be done for report writing although in many report writing situations you'd also want that user to have Execute permissions to make use of functions and stored procedures in the database. The SQL provided below can be run in Sybase's utility called dbisqlc.exe found in the \NewHaven Software\sa12\bin\ directory. Running this statement requires you be connected as the dbo database user.

			=SQL to Create the Read-Only Database User=
	Note the username and password around the 7th line. You may alter these to use whatever username and password you like.		Note the username and password around the 7th line. You may alter these to use whatever username and password you like.

	~~=SQL to Create the Read-Only Database User=~~

	<source lang="SQL">		<source lang="SQL">

Russ horton at 01:03, 6 March 2013

2013-03-06T01:03:49Z

← Older revision		Revision as of 01:03, 6 March 2013
Line 5:		Line 5:

	Note the username and password around the 7th line. You may alter these to use whatever username and password you like.		Note the username and password around the 7th line. You may alter these to use whatever username and password you like.

			=SQL to Create the Read-Only Database User=

	<source lang="SQL">		<source lang="SQL">

Russ horton: moved Creatong a Read-Only Database User to Creating a Read-Only Database User

2013-03-06T01:00:34Z

moved Creatong a Read-Only Database User to Creating a Read-Only Database User

← Older revision	Revision as of 01:00, 6 March 2013
(No difference)

Russ horton: /* Overview */

2013-03-06T00:59:12Z

Overview

← Older revision		Revision as of 00:59, 6 March 2013
Line 3:		Line 3:

	In some cases you may wish to create an additional user that has just read-only access and no write permissions. Typically this would be done for report writing although in many report writing situations you'd also want that user to have Execute permissions to make use of functions and stored procedures in the database. The SQL provided below can be run in Sybase's utility called dbisqlc.exe found in the \NewHaven Software\sa12\bin\ directory. Running this statement requires you be connected as the dbo database user.		In some cases you may wish to create an additional user that has just read-only access and no write permissions. Typically this would be done for report writing although in many report writing situations you'd also want that user to have Execute permissions to make use of functions and stored procedures in the database. The SQL provided below can be run in Sybase's utility called dbisqlc.exe found in the \NewHaven Software\sa12\bin\ directory. Running this statement requires you be connected as the dbo database user.

			Note the username and password around the 7th line. You may alter these to use whatever username and password you like.

	<source lang="SQL">		<source lang="SQL">
Line 11:		Line 13:
	declare procname varchar(255);		declare procname varchar(255);

	SET username = '~~testme~~';		SET username = 'CMSVIEW';
	SET pass = '~~testing~~';		SET pass = 'CMSVIEW';

	if not exists (select 1 from sysuser where user_name = username) then		if not exists (select 1 from sysuser where user_name = username) then

Russ horton: Created page with '=Overview= Your CMS database is pre-configured with two database user accounts, dbo and cmsuser. The dbo user allows full access to the database including the ability to create/e…'

2013-03-06T00:56:57Z

Created page with '=Overview= Your CMS database is pre-configured with two database user accounts, dbo and cmsuser. The dbo user allows full access to the database including the ability to create/e…'

New page

=Overview=
Your CMS database is pre-configured with two database user accounts, dbo and cmsuser. The dbo user allows full access to the database including the ability to create/edit/drop tables, views, stored procedures, etc. Really a form of Admin. The cmsuser database user, on the other hand, is a normal user with read/write permissions but cannot alter the database structure.

In some cases you may wish to create an additional user that has just read-only access and no write permissions. Typically this would be done for report writing although in many report writing situations you'd also want that user to have Execute permissions to make use of functions and stored procedures in the database. The SQL provided below can be run in Sybase's utility called dbisqlc.exe found in the \NewHaven Software\sa12\bin\ directory. Running this statement requires you be connected as the dbo database user.

<source lang="SQL">
BEGIN
declare username varchar(128);
declare pass varchar(128);
declare tablename varchar(255);
declare procname varchar(255);

SET username = 'testme';
SET pass = 'testing';

if not exists (select 1 from sysuser where user_name = username) then
EXECUTE IMMEDIATE 'CREATE USER ' + username + ' IDENTIFIED BY ' + pass;
end if;

FOR t AS table_cursor INSENSITIVE CURSOR FOR
SELECT ST.*
FROM SYSTABLE ST
JOIN SYSUSER SU ON SU.USER_ID = ST.CREATOR
WHERE SU.USER_NAME = 'dbo'
DO
set tablename = TABLE_NAME;
if exists (select 1 from SYSTABLEPERM p
JOIN SYSUSER SU ON SU.USER_ID = p.grantee
join SYSTABLE ST on st.table_id = p.stable_id
where su.user_name = username and st.table_name = tablename) then
MESSAGE 'TABLE REVOKE ALL ON ' + tablename + ' FROM ' + username TO CLIENT;
EXECUTE IMMEDIATE 'REVOKE ALL ON ' + TABLE_NAME + ' FROM ' + username;
end if;
MESSAGE 'TABLE GRANT SELECT ON ' + tablename + ' TO ' + username TO CLIENT;
EXECUTE IMMEDIATE 'GRANT SELECT ON ' + TABLE_NAME + ' TO ' + username;
END FOR;

FOR p AS proc_cursor INSENSITIVE CURSOR FOR
SELECT SP.*
FROM SYSPROCEDURE SP
JOIN SYSUSER SU ON SU.USER_ID = SP.CREATOR
WHERE SU.USER_NAME = 'dbo'
DO
set procname = PROC_NAME;
if exists (select 1 from SYSPROCPERM p
JOIN SYSUSER SU ON SU.USER_ID = p.grantee
join SYSPROCEDURE sp on sp.proc_id = p.proc_id
where su.user_name = username and sp.proc_name = procname) then
MESSAGE 'PROC REVOKE EXECUTE ON ' + procname + ' FROM ' + username TO CLIENT;
EXECUTE IMMEDIATE 'REVOKE EXECUTE ON ' + PROC_NAME + ' FROM ' + username;
end if;
MESSAGE 'PROC GRANT EXECUTE ON ' + procname + ' TO ' + username TO CLIENT;
EXECUTE IMMEDIATE 'GRANT EXECUTE ON ' + PROC_NAME + ' TO ' + username;
END FOR;
END
</source>