Database corrupted: control

Showing posts with label control. Show all posts

Thursday, March 8, 2012

Database Access Control

VB.Net / SQL 2K
We are developing a VB.Net application and the question is regarding the
Login screen
We have a table which stores the access-level for each users.
Here is our requirement.
1> Need to restrict users with readonly access when connected to the
database NOT thru the application.
2> Users will gain proper access after logging into the application.
3> Once the user close the application, access-level back to 'read-only'
The whole point is to restrict users not to directly modify data outside
application.
Thanks
John BThis is a multi-part message in MIME format.
--030706090505000407000501
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Check out Application Roles
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_89ir.asp).
Basically, create one or more app roles in the database with the
different SQL permissions necessary granted to each one (eg. you may
have a standard user role and a special admin role). When a user
authenticates through your VB app, change the permissions of the SQL
client connection with sp_setapprole (the SPID gets a whole new set of
permissions associated with the app role that completely overrides the
DB user's permissions). The new set of permissions will remain in force
until the client connection drops out (ie. disconnects).
So you can just have a normal DB role, in which all DB users are
members, that has very limited permissions (something similar to
db_datareader + db_denydatawriter) and one or more application roles in
the DB that have much less restrictive permissions (like db_datareader +
db_datawriter, but obviously more granular that that) based on the
users' access levels stored in your access-level table.
HTH
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
MS User wrote:
>VB.Net / SQL 2K
>We are developing a VB.Net application and the question is regarding the
>Login screen
>We have a table which stores the access-level for each users.
>Here is our requirement.
>1> Need to restrict users with readonly access when connected to the
>database NOT thru the application.
>2> Users will gain proper access after logging into the application.
>3> Once the user close the application, access-level back to 'read-only'
>The whole point is to restrict users not to directly modify data outside
>application.
>Thanks
>John B
>
>
>
--030706090505000407000501
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Check out Application Roles (<a
href="http://links.10026.com/?link=http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_89ir.asp</a>). ">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_89ir.asp">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_89ir.asp</a>). 
 
Basically, create one or more app roles in the database with the
different SQL permissions necessary granted to each one (eg. you may
have a standard user role and a special admin role). When a user
authenticates through your VB app, change the permissions of the SQL
client connection with sp_setapprole (the SPID gets a whole new set of
permissions associated with the app role that completely overrides the
DB user's permissions). The new set of permissions will remain in
force until the client connection drops out (ie. disconnects). 
 
So you can just have a normal DB role, in which all DB users are
members, that has very limited permissions (something similar to
db_datareader + db_denydatawriter) and one or more application roles in
the DB that have much less restrictive permissions (like db_datareader
+ db_datawriter, but obviously more granular that that) based on the
users' access levels stored in your access-level table. 
 
HTH 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">mailto:mike.hodgson@.mallesons.nospam.com</a>
| W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
MS User wrote:
<blockquote cite="midOxaNNRQJFHA.3340@.TK2MSFTNGP14.phx.gbl" type="cite">
<pre wrap="">VB.Net / SQL 2K
We are developing a VB.Net application and the question is regarding the
Login screen
We have a table which stores the access-level for each users.
Here is our requirement.
1> Need to restrict users with readonly access when connected to the
database NOT thru the application.
2> Users will gain proper access after logging into the application.
3> Once the user close the application, access-level back to 'read-only'
The whole point is to restrict users not to directly modify data outside
application.
Thanks
John B
</pre>
</blockquote>
</body>
</html>
--030706090505000407000501--|||You achieve this through SQL Server's built-in security. Deny all
permissions on tables and grant users execute permission only on SPs.
All data access should be through parameterized SPs so that you can
apply your own rules and ensure the user only touches what they should.
--
David Portas
SQL Server MVP
--|||Suggest you have the application log on to the database with it's own logon,
which has read/write access. And give individual users their own logins
which have read-only access...
ALso, I strongly recommend that ALL access be allowed only through Stored
Procs, and direct access to tables be either prohibited, or restricted to
read-only, to all except for a narrrow group.
"MS User" wrote:
> VB.Net / SQL 2K
> We are developing a VB.Net application and the question is regarding the
> Login screen
> We have a table which stores the access-level for each users.
> Here is our requirement.
> 1> Need to restrict users with readonly access when connected to the
> database NOT thru the application.
> 2> Users will gain proper access after logging into the application.
> 3> Once the user close the application, access-level back to 'read-only'
> The whole point is to restrict users not to directly modify data outside
> application.
> Thanks
> John B
>
>
>|||Unless you are in the mood to do User Security, have your AD team create
Windows Global Groups, one each for each type of access. Grant these as
logins and map them to the database.
Create user-defined database roles and put security on these roles. Create
one each for each of the Windows Global Groups above.
Now, it is a one-to-one mapping from Windows Groups to SQL Server Database
Roles.
In your case, you could just have one database role, then make that role a
member of the system defined db_datareader and db_datadenywriter roles.
The above is for ad-hoc user access only.
For coded solutions, have your application use Windows Authentication and
grant that account as an SS login, mapped to the database.
Create another database role for the application. Make the role a member of
the system defined database roles db_datadenyreader and db_datadenywriter.
Then use stored procedures exclusively. Grant the user defined role execute
rights on all the stored procedures.
Sincerely,
Anthony Thomas
"MS User" <sqlman@.sql.com> wrote in message
news:OxaNNRQJFHA.3340@.TK2MSFTNGP14.phx.gbl...
VB.Net / SQL 2K
We are developing a VB.Net application and the question is regarding the
Login screen
We have a table which stores the access-level for each users.
Here is our requirement.
1> Need to restrict users with readonly access when connected to the
database NOT thru the application.
2> Users will gain proper access after logging into the application.
3> Once the user close the application, access-level back to 'read-only'
The whole point is to restrict users not to directly modify data outside
application.
Thanks
John B

Database Access Control

Tuesday, February 14, 2012

data transform and compare with in a query

Okay all I have a problem. I have two list of adresses and phone
numbers. I do not have control over the contents of the lists The only
unique field between the two is the phone number. I need to be able to
inner join the two lists on phone number.

This would normally be straigt forward but the problem is that they are
formated different and one of them does't even have a control on the
formating.

*Phone numbers are US phone numbers only.

The one list (table) that does have a control uses this format

AAA3334444

where AAA is the area code
333 is the 3 digit prefix
4444 is the four digit suffix

the second list (table) does not have any standardized formating and
can be filled with extraneous spaces, parentheses and dashes not to
mention the leading 1 in some instances.

I thought that I could do some kind of regular expression to do a
comparison but I havn't as yet found a good resource to tell me how to
do it or if it is even possible. Or maybe break up the one I know has
a standardized format into something like this:

'%AAA%333%4444%' and doing my comparison that way. however It is very
important that only those list items in both list that are truly the
same place be listed.

suggestions and solutions are apreciated"Dan Gidman" <danatcofo@.gmail.com> wrote in message
news:1109353284.020286.122200@.g14g2000cwa.googlegr oups.com...
> Okay all I have a problem. I have two list of adresses and phone
> numbers. I do not have control over the contents of the lists The only
> unique field between the two is the phone number. I need to be able to
> inner join the two lists on phone number.
> This would normally be straigt forward but the problem is that they are
> formated different and one of them does't even have a control on the
> formating.
> *Phone numbers are US phone numbers only.
> The one list (table) that does have a control uses this format
> AAA3334444
> where AAA is the area code
> 333 is the 3 digit prefix
> 4444 is the four digit suffix
> the second list (table) does not have any standardized formating and
> can be filled with extraneous spaces, parentheses and dashes not to
> mention the leading 1 in some instances.
> I thought that I could do some kind of regular expression to do a
> comparison but I havn't as yet found a good resource to tell me how to
> do it or if it is even possible. Or maybe break up the one I know has
> a standardized format into something like this:
> '%AAA%333%4444%' and doing my comparison that way. however It is very
> important that only those list items in both list that are truly the
> same place be listed.
> suggestions and solutions are apreciated

It probably depends how bad the data is - if the number and type of the
unwanted characters is relatively predictable, then you can achieve quite a
lot with REPLACE():

create table #t (pnum varchar(20))

insert into #t select '1-800-456 7890 '
insert into #t select '(800)- 456 7890'
insert into #t select '1 800 456 7890'
insert into #t select '+1 800 (456) 7890 '
insert into #t select '(800) (456) 7890'

select pnum from #t

/* Strip out unwanted characters */
update #t set pnum = replace(pnum, ' ', '')
update #t set pnum = replace(pnum, '(', '')
update #t set pnum = replace(pnum, ')', '')
update #t set pnum = replace(pnum, '-', '')
update #t set pnum = replace(pnum, '+', '')

/* Remove leading 1 */
update #t set pnum = stuff(pnum, 1, 1, '')
where left(pnum,1) = '1'
and len(pnum) = 11

select pnum from #t

But if it gets much more complicated than that, I would consider using
bcp.exe to export the data, clean it up with an external script in a
language that has better string functions than TSQL, then load it again.

It is possible to use regexes in TSQL, by instantiating the VBScript Regex
object using the sp_OA% procedures, but it's rather clumsy and requires
sysadmin permissions, so it's probably not a good general solution.

Simon|||I have achieved some good success with nesting using the replace
function like you have suggested it. It occured to me after doing my
initial post.

replace(replace(replace(replace(number, '(',''), ')',''), '-',''), '
','')

this actually works very well. still not getting the results I need
however. I think that I next need to check for a 1 at the begining of
the string and remove it.

after that I think if I just grab the first 10 digits it will get
something I can compare against|||I think I found the answer to my own question. so far this little sql
statement is a wonder worker for cleaning up the data.

SELECT PhoneNumber = CASE WHEN SUBSTRING(temper.PhoneNumber,1,1) = '1'
THEN SUBSTRING(temper.PhoneNumber,2,11) ELSE
SUBSTRING(temper.PhoneNumber,1,10) END FROM (SELECT
Replace(Replace(Replace(Replace(Replace(LatestTWPr ops.PhoneNumber,'
',''),'-',''),'(',''),')',''),'/','') AS PhoneNumber FROM LatestTWProps
WHERE countryCode = 'US' AND PhoneNumber IS NOT NULL) AS temper

Thursday, March 8, 2012

Database Access Control

Database Access Control

Database Access Control

Database Access Control

Tuesday, February 14, 2012

data transform and compare with in a query

Database corrupted

Blog Archive

About Me