I've generated a report that will need to display different information
based upon where it is being run. For instance, if the report is executing
on our DVLP server, i need a title to say "Developement". Is there is simple
way to determine what environment the report is being run in? Check the URL?
Server variables?
Thanks,
--
Brian Grant
Senior Programmer
SI International
www.si-intl.com=System.Environment.MachineName
Warning: You'll need to give expression host FullTrust in order to use this
expression on report server, which may be a security risk.
If your SQL Server is on the same machine, creating a dataset against it and
using SELECT @.@.servername would be a better approach.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"G" <brian.grant@.si-intl-kc.com> wrote in message
news:uZDVm2lbEHA.3988@.tk2msftngp13.phx.gbl...
> I've generated a report that will need to display different information
> based upon where it is being run. For instance, if the report is executing
> on our DVLP server, i need a title to say "Developement". Is there is
simple
> way to determine what environment the report is being run in? Check the
URL?
> Server variables?
> Thanks,
> --
> Brian Grant
> Senior Programmer
> SI International
> www.si-intl.com
>sql
Showing posts with label upon. Show all posts
Showing posts with label upon. Show all posts
Thursday, March 29, 2012
Sunday, March 25, 2012
Determine Duration between Two Dates
All
I have a table of members and I would like to determine how long each member
has been a member based upon the current date. I would like the result
retuned in the Number of Years, Months and Days ie. 5y 6m 26d. Any
assistance would be appreciated.
CREATE TABLE member
(
MemberID INT,
DateJoined SMALLDATETIME
)
INSERT INTO member SELECT 1, '1999-01-11 00:00:00'
INSERT INTO member SELECT 2, '2004-12-26 00:00:00'
INSERT INTO member SELECT 3, '2005-01-01 00:00:00'
Desired Result:
1 1999-01-11 2005-10-13 6y 9m 2d
2 2004-12-26 2005-10-13 0y 10m 18d
3 2005-01-01 2005-10-13 0y 10m 12d
ThanksHi David,
There must be a simpler way than this, but what the heck...
SELECT id, dt, y, m,
diff_d - CASE WHEN dt_ym + diff_d > today THEN 1 ELSE 0 END AS d
FROM
(
SELECT *, DATEDIFF(day, dt_ym, today) AS diff_d
FROM
(
SELECT id, dt, today, y, m,
DATEADD(month, m, dt_y) AS dt_ym
FROM
(
SELECT id, dt, today, y, dt_y,
m_diff - CASE WHEN DATEADD(month, m_diff, dt_y) > today
THEN 1 ELSE 0 END AS m
FROM
(
SELECT *, DATEDIFF(month, dt_y, today) AS m_diff
FROM
(
SELECT *, DATEADD(year, y, dt) AS dt_y
FROM
(
SELECT id, dt, today,
y_diff - CASE WHEN DATEADD(year, y_diff, dt) > today
THEN 1 ELSE 0 END AS y
FROM
(
SELECT *, DATEDIFF(year, dt, today) AS y_diff
FROM
(
SELECT MemberID AS id, DateJoined AS dt,
CAST(CONVERT(VARCHAR(8), GETDATE(), 112) AS DATETIME) AS today
FROM member
) AS D1
) AS D2
) AS D3
) AS D4
) AS D5
) AS D6
) AS D7
) AS D8;
BG, SQL Server MVP
www.SolidQualityLearning.com
Join us for the SQL Server 2005 launch at the SQL W
in Israel!
[url]http://www.microsoft.com/israel/sql/sqlw/default.mspx[/url]
"David" <David@.discussions.microsoft.com> wrote in message
news:44CF768B-1213-453F-8A62-024857AF5089@.microsoft.com...
> All
> I have a table of members and I would like to determine how long each
> member
> has been a member based upon the current date. I would like the result
> retuned in the Number of Years, Months and Days ie. 5y 6m 26d. Any
> assistance would be appreciated.
> CREATE TABLE member
> (
> MemberID INT,
> DateJoined SMALLDATETIME
> )
> INSERT INTO member SELECT 1, '1999-01-11 00:00:00'
> INSERT INTO member SELECT 2, '2004-12-26 00:00:00'
> INSERT INTO member SELECT 3, '2005-01-01 00:00:00'
>
> Desired Result:
> 1 1999-01-11 2005-10-13 6y 9m 2d
> 2 2004-12-26 2005-10-13 0y 10m 18d
> 3 2005-01-01 2005-10-13 0y 10m 12d
>
> Thanks|||Hi David
Probably you can check the link.
http://chanduas.blogspot.com/2005/0...lating-age.html
This is not the exact solution but can give u an idea
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"David" wrote:
> All
> I have a table of members and I would like to determine how long each memb
er
> has been a member based upon the current date. I would like the result
> retuned in the Number of Years, Months and Days ie. 5y 6m 26d. Any
> assistance would be appreciated.
> CREATE TABLE member
> (
> MemberID INT,
> DateJoined SMALLDATETIME
> )
> INSERT INTO member SELECT 1, '1999-01-11 00:00:00'
> INSERT INTO member SELECT 2, '2004-12-26 00:00:00'
> INSERT INTO member SELECT 3, '2005-01-01 00:00:00'
>
> Desired Result:
> 1 1999-01-11 2005-10-13 6y 9m 2d
> 2 2004-12-26 2005-10-13 0y 10m 18d
> 3 2005-01-01 2005-10-13 0y 10m 12d
>
> Thanks
I have a table of members and I would like to determine how long each member
has been a member based upon the current date. I would like the result
retuned in the Number of Years, Months and Days ie. 5y 6m 26d. Any
assistance would be appreciated.
CREATE TABLE member
(
MemberID INT,
DateJoined SMALLDATETIME
)
INSERT INTO member SELECT 1, '1999-01-11 00:00:00'
INSERT INTO member SELECT 2, '2004-12-26 00:00:00'
INSERT INTO member SELECT 3, '2005-01-01 00:00:00'
Desired Result:
1 1999-01-11 2005-10-13 6y 9m 2d
2 2004-12-26 2005-10-13 0y 10m 18d
3 2005-01-01 2005-10-13 0y 10m 12d
ThanksHi David,
There must be a simpler way than this, but what the heck...
SELECT id, dt, y, m,
diff_d - CASE WHEN dt_ym + diff_d > today THEN 1 ELSE 0 END AS d
FROM
(
SELECT *, DATEDIFF(day, dt_ym, today) AS diff_d
FROM
(
SELECT id, dt, today, y, m,
DATEADD(month, m, dt_y) AS dt_ym
FROM
(
SELECT id, dt, today, y, dt_y,
m_diff - CASE WHEN DATEADD(month, m_diff, dt_y) > today
THEN 1 ELSE 0 END AS m
FROM
(
SELECT *, DATEDIFF(month, dt_y, today) AS m_diff
FROM
(
SELECT *, DATEADD(year, y, dt) AS dt_y
FROM
(
SELECT id, dt, today,
y_diff - CASE WHEN DATEADD(year, y_diff, dt) > today
THEN 1 ELSE 0 END AS y
FROM
(
SELECT *, DATEDIFF(year, dt, today) AS y_diff
FROM
(
SELECT MemberID AS id, DateJoined AS dt,
CAST(CONVERT(VARCHAR(8), GETDATE(), 112) AS DATETIME) AS today
FROM member
) AS D1
) AS D2
) AS D3
) AS D4
) AS D5
) AS D6
) AS D7
) AS D8;
BG, SQL Server MVP
www.SolidQualityLearning.com
Join us for the SQL Server 2005 launch at the SQL W
in Israel![url]http://www.microsoft.com/israel/sql/sqlw
/default.mspx[/url]"David" <David@.discussions.microsoft.com> wrote in message
news:44CF768B-1213-453F-8A62-024857AF5089@.microsoft.com...
> All
> I have a table of members and I would like to determine how long each
> member
> has been a member based upon the current date. I would like the result
> retuned in the Number of Years, Months and Days ie. 5y 6m 26d. Any
> assistance would be appreciated.
> CREATE TABLE member
> (
> MemberID INT,
> DateJoined SMALLDATETIME
> )
> INSERT INTO member SELECT 1, '1999-01-11 00:00:00'
> INSERT INTO member SELECT 2, '2004-12-26 00:00:00'
> INSERT INTO member SELECT 3, '2005-01-01 00:00:00'
>
> Desired Result:
> 1 1999-01-11 2005-10-13 6y 9m 2d
> 2 2004-12-26 2005-10-13 0y 10m 18d
> 3 2005-01-01 2005-10-13 0y 10m 12d
>
> Thanks|||Hi David
Probably you can check the link.
http://chanduas.blogspot.com/2005/0...lating-age.html
This is not the exact solution but can give u an idea
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"David" wrote:
> All
> I have a table of members and I would like to determine how long each memb
er
> has been a member based upon the current date. I would like the result
> retuned in the Number of Years, Months and Days ie. 5y 6m 26d. Any
> assistance would be appreciated.
> CREATE TABLE member
> (
> MemberID INT,
> DateJoined SMALLDATETIME
> )
> INSERT INTO member SELECT 1, '1999-01-11 00:00:00'
> INSERT INTO member SELECT 2, '2004-12-26 00:00:00'
> INSERT INTO member SELECT 3, '2005-01-01 00:00:00'
>
> Desired Result:
> 1 1999-01-11 2005-10-13 6y 9m 2d
> 2 2004-12-26 2005-10-13 0y 10m 18d
> 3 2005-01-01 2005-10-13 0y 10m 12d
>
> Thanks
Sunday, March 11, 2012
Detaching a database and CHECKPOINT
Hi,
can someone confirm that SQL Server 2000 (SP3a) ALWAYS does a CHECKPOINT
upon detaching a database, so that the MDF/NDF files are "self-supporting"?
That would mean I can delete/rename the LDF file and have it recreated by
SQL
Server upon attach.
Of course I can checkpoint manually, but I'd expect the detach procedure to
that automatically for me (after prohibiting any new txns).
I'm pretty sure for SQL Server 2005 I have read such a statement in either
the official BOL or an article (pointers appreciated, though) but for SQL2K
I have have not found such information...
Thanx,
HansAs far as I know, once a database is cleanly detached using sp_detach_db,
then it can be attached without a log file. It has always worked for me.
There's an undocumented trace flag 3502, when turned on, it adds an entry to
the SQL Server error log, everytime a checkpoint occurs in any database. I
used this trace flag and found that a check point occured everytime I tried
to detach a database.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Hans Dingemans" <hans_dingemans@.hotmail.com> wrote in message
news:%23V$CtDihGHA.896@.TK2MSFTNGP02.phx.gbl...
Hi,
can someone confirm that SQL Server 2000 (SP3a) ALWAYS does a CHECKPOINT
upon detaching a database, so that the MDF/NDF files are "self-supporting"?
That would mean I can delete/rename the LDF file and have it recreated by
SQL
Server upon attach.
Of course I can checkpoint manually, but I'd expect the detach procedure to
that automatically for me (after prohibiting any new txns).
I'm pretty sure for SQL Server 2005 I have read such a statement in either
the official BOL or an article (pointers appreciated, though) but for SQL2K
I have have not found such information...
Thanx,
Hans|||>> trace flag 3502
Ah, that's the kind of information I was looking for. This indeed helps a
lot. Thanx!
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%239otSzjhGHA.1276@.TK2MSFTNGP03.phx.gbl...
> As far as I know, once a database is cleanly detached using sp_detach_db,
> then it can be attached without a log file. It has always worked for me.
> There's an undocumented trace flag 3502, when turned on, it adds an entry
> to
> the SQL Server error log, everytime a checkpoint occurs in any database. I
> used this trace flag and found that a check point occured everytime I
> tried
> to detach a database.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Hans Dingemans" <hans_dingemans@.hotmail.com> wrote in message
> news:%23V$CtDihGHA.896@.TK2MSFTNGP02.phx.gbl...
> Hi,
> can someone confirm that SQL Server 2000 (SP3a) ALWAYS does a CHECKPOINT
> upon detaching a database, so that the MDF/NDF files are
> "self-supporting"?
> That would mean I can delete/rename the LDF file and have it recreated by
> SQL
> Server upon attach.
> Of course I can checkpoint manually, but I'd expect the detach procedure
> to
> that automatically for me (after prohibiting any new txns).
> I'm pretty sure for SQL Server 2005 I have read such a statement in either
> the official BOL or an article (pointers appreciated, though) but for
> SQL2K
> I have have not found such information...
> Thanx,
> Hans
>
>
can someone confirm that SQL Server 2000 (SP3a) ALWAYS does a CHECKPOINT
upon detaching a database, so that the MDF/NDF files are "self-supporting"?
That would mean I can delete/rename the LDF file and have it recreated by
SQL
Server upon attach.
Of course I can checkpoint manually, but I'd expect the detach procedure to
that automatically for me (after prohibiting any new txns).
I'm pretty sure for SQL Server 2005 I have read such a statement in either
the official BOL or an article (pointers appreciated, though) but for SQL2K
I have have not found such information...
Thanx,
HansAs far as I know, once a database is cleanly detached using sp_detach_db,
then it can be attached without a log file. It has always worked for me.
There's an undocumented trace flag 3502, when turned on, it adds an entry to
the SQL Server error log, everytime a checkpoint occurs in any database. I
used this trace flag and found that a check point occured everytime I tried
to detach a database.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Hans Dingemans" <hans_dingemans@.hotmail.com> wrote in message
news:%23V$CtDihGHA.896@.TK2MSFTNGP02.phx.gbl...
Hi,
can someone confirm that SQL Server 2000 (SP3a) ALWAYS does a CHECKPOINT
upon detaching a database, so that the MDF/NDF files are "self-supporting"?
That would mean I can delete/rename the LDF file and have it recreated by
SQL
Server upon attach.
Of course I can checkpoint manually, but I'd expect the detach procedure to
that automatically for me (after prohibiting any new txns).
I'm pretty sure for SQL Server 2005 I have read such a statement in either
the official BOL or an article (pointers appreciated, though) but for SQL2K
I have have not found such information...
Thanx,
Hans|||>> trace flag 3502
Ah, that's the kind of information I was looking for. This indeed helps a
lot. Thanx!
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%239otSzjhGHA.1276@.TK2MSFTNGP03.phx.gbl...
> As far as I know, once a database is cleanly detached using sp_detach_db,
> then it can be attached without a log file. It has always worked for me.
> There's an undocumented trace flag 3502, when turned on, it adds an entry
> to
> the SQL Server error log, everytime a checkpoint occurs in any database. I
> used this trace flag and found that a check point occured everytime I
> tried
> to detach a database.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Hans Dingemans" <hans_dingemans@.hotmail.com> wrote in message
> news:%23V$CtDihGHA.896@.TK2MSFTNGP02.phx.gbl...
> Hi,
> can someone confirm that SQL Server 2000 (SP3a) ALWAYS does a CHECKPOINT
> upon detaching a database, so that the MDF/NDF files are
> "self-supporting"?
> That would mean I can delete/rename the LDF file and have it recreated by
> SQL
> Server upon attach.
> Of course I can checkpoint manually, but I'd expect the detach procedure
> to
> that automatically for me (after prohibiting any new txns).
> I'm pretty sure for SQL Server 2005 I have read such a statement in either
> the official BOL or an article (pointers appreciated, though) but for
> SQL2K
> I have have not found such information...
> Thanx,
> Hans
>
>
Friday, February 17, 2012
Design issue please help
Hi all,
I do have four combination key for eg:1111-1111-1111-1111
the values can range from 0-0, based upon this i can create any number
of keys eg:1234-4567-4352-6534, these four values comes from different
tables, while joining the four i insert the new combination in a new
table .
In order to give security, i can give access rights to each parts for
eg: a user can have rights to 1234, but dont have the rights to 4567.
Iam using these keys in lot of my records , but i need to give a
checking based on the user , if he does not have the rights to a
particular segment , he wont be able to view , nor edit the record.
How do i implement this security on segment based,
Thanks in advance
Cheers
thomsonthomson
Look, I don't know your business requirements and obviously it depends on
bit permit me to suggest something
Please look at application role implemented by SQL Server as well as visit
on Vyas's greate web site to read below article
<http://vyaskn.tripod.com/sql_server...t_practices.htm> --se
curity best practices
"thomson" <saintthomson@.yahoo.com> wrote in message
news:1114056973.285659.247590@.g14g2000cwa.googlegroups.com...
> Hi all,
> I do have four combination key for eg:1111-1111-1111-1111
> the values can range from 0-0, based upon this i can create any number
> of keys eg:1234-4567-4352-6534, these four values comes from different
> tables, while joining the four i insert the new combination in a new
> table .
>
> In order to give security, i can give access rights to each parts for
> eg: a user can have rights to 1234, but dont have the rights to 4567.
> Iam using these keys in lot of my records , but i need to give a
> checking based on the user , if he does not have the rights to a
> particular segment , he wont be able to view , nor edit the record.
>
> How do i implement this security on segment based,
> Thanks in advance
> Cheers
> thomson
>|||Dear Thomson,
The same problem i faced in my project and we have implemented in the
project manually[no supported my MS SQL].
For segment based manual security U need to have a separate table for this
which will let U know the which Key is accced by which User
KeyVal UserID SecurityLevel(RoleID)
1234 1 1
1234 3 2
6534 1 4
... ... ...
... ... ...
... ... ...
Hee SecurityLevel(RoleID) field has the ID/Level for Security.Security Level
may be like enum... or as u wish.
I think From this table you help you in getting the user ID with their
rights and role for a desired Keyval.
Am i clear to you ?
Please let me know.
NB: Please reply me on rakesh.ranjan@.3i-infotech.com
Thanking you
Rakesh Ranjan
rakesh.ranjan@.3i-infotech.com
Mumbai[India]
"thomson" wrote:
> Hi all,
> I do have four combination key for eg:1111-1111-1111-1111
> the values can range from 0-0, based upon this i can create any number
> of keys eg:1234-4567-4352-6534, these four values comes from different
> tables, while joining the four i insert the new combination in a new
> table .
>
> In order to give security, i can give access rights to each parts for
> eg: a user can have rights to 1234, but dont have the rights to 4567.
> Iam using these keys in lot of my records , but i need to give a
> checking based on the user , if he does not have the rights to a
> particular segment , he wont be able to view , nor edit the record.
>
> How do i implement this security on segment based,
> Thanks in advance
> Cheers
> thomson
>|||Dear Rakesh,
I think you got my point would you please explain it to me
the role of KeyVal and the Security Role id in Detail
Regards
thomson
RakeshRanjan wrote:
> Dear Thomson,
> The same problem i faced in my project and we have implemented in the
> project manually[no supported my MS SQL].
> For segment based manual security U need to have a separate table for
this
> which will let U know the which Key is accced by which User
> KeyVal UserID SecurityLevel(RoleID)
> 1234 1 1
> 1234 3 2
> 6534 1 4
> ... ... ...
> ... ... ...
> ... ... ...
> Hee SecurityLevel(RoleID) field has the ID/Level for
Security.Security Level
> may be like enum... or as u wish.
>
> I think From this table you help you in getting the user ID with
their
> rights and role for a desired Keyval.
> Am i clear to you ?
> Please let me know.
> NB: Please reply me on rakesh.ranjan@.3i-infotech.com
> Thanking you
> Rakesh Ranjan
> rakesh.ranjan@.3i-infotech.com
> Mumbai[India]
>
> "thomson" wrote:
>
number
different
new
for
4567.|||One more thing is that If you have mentioned the KeyVal value, as the
one like 1111-1111-1111-1111, Then the issue is there can be a Hypen
between segments, or there can be any other Characters in between the
segments, So how will i tackle this
Regards
thomson|||Hi Tjhomson,
Key val is nothing but ur primary Key of all other table which make ur newly
generated Segmentkey(1234-4567-4352-6534).
You can have any charecter other than "-", It depends on you, how you want
to program it. This will be treated as a charecter for spliting your segment
key (which u have created from the four IDs).
Here the SecurityLevel(RoleID) field is rthe ID(Primary Key) of ur
security/role table which a user has, so that u can come to know the level o
f
user security.
Hope this much help u in better understanding. For further clarification
mail me on rakesh.ranjan@.3i-infotech.com
With regards,
Rakesh.
"thomson" wrote:
> Dear Rakesh,
> I think you got my point would you please explain it to me
> the role of KeyVal and the Security Role id in Detail
> Regards
> thomson
> RakeshRanjan wrote:
>
> this
> Security.Security Level
> their
> number
> different
> new
> for
> 4567.
>|||Hi rakesh,
The problem lies here that i dont want to give the security
to the whole key 1111-1111-1111-1111.
My issue is i have to give the security to each segments eg:1111
this 4 key can be generated by any one, its a one time setup, Then i
have to give security to each segments for each user.
suppose if i create a segment 1111-2222-3333-4444.
If i dont have the access to 1111, what i should not able to access the
records having 1111-2222-3333-4444 or 2222-1111-3333-4444, cos i dont
have access to the particular segment 1111.
Thanks in advance
thomson
thomson wrote:
> One more thing is that If you have mentioned the KeyVal value, as the
> one like 1111-1111-1111-1111, Then the issue is there can be a Hypen
> between segments, or there can be any other Characters in between the
> segments, So how will i tackle this
> Regards
> thomson|||Hello Thomson,
Its little bit different than what I have Implemented. Though no issue , I
have a clue.
As u said the segment "1111-2222-3333-4444" is one time creation. Even
multiple time , I have no issue at all.
What u need to do is, after creation of the key "1111-2222-3333-4444" split
it into four parts i.e. 1111, 2222, 3333, 4444 and then Insert four records
in the said table with their rights( as i explained earlier), Now you know
which key has what access. so can can proceed further as per ur requirement.
For further please chat with me on mail "rakesh.ranjan@.3i-infotech.com".
With Regards,
Rakesh R.
"thomson" wrote:
> Hi rakesh,
> The problem lies here that i dont want to give the security
> to the whole key 1111-1111-1111-1111.
> My issue is i have to give the security to each segments eg:1111
> this 4 key can be generated by any one, its a one time setup, Then i
> have to give security to each segments for each user.
> suppose if i create a segment 1111-2222-3333-4444.
> If i dont have the access to 1111, what i should not able to access the
> records having 1111-2222-3333-4444 or 2222-1111-3333-4444, cos i dont
> have access to the particular segment 1111.
> Thanks in advance
>
> thomson
> thomson wrote:
>|||thomson,
You'll have to parse the key. If it's just 4 4-character pieces, it's
not hard.
select key from tableOfKeys
where not exists (
select deniedPermission from Permissions
where Permissions.theUser = @.theUser
and deniedPermission in (
substring(key,1,4),
substring(key,6,4),
substring(key,11,4),
substring(key,16,4)
)
)
If permissions are assigned positively, it's something like
select key from tableOfKeys
where not exists (
select keyPermission from (
select substring(key,1,4) union all
select substring(key,6,4) union all
select substring(key,11,4) union all
select substring(key,16,4)
) as K(keyPermission)
where keyPermission not in (
select permission from Permissions
where permission.theUser = @.theUser
)
)
[both untested]
Steve Kass
Drew University
thomson wrote:
>Hi all,
> I do have four combination key for eg:1111-1111-1111-1111
>the values can range from 0-0, based upon this i can create any number
>of keys eg:1234-4567-4352-6534, these four values comes from different
>tables, while joining the four i insert the new combination in a new
>table .
>
> In order to give security, i can give access rights to each parts for
>eg: a user can have rights to 1234, but dont have the rights to 4567.
>Iam using these keys in lot of my records , but i need to give a
>checking based on the user , if he does not have the rights to a
>particular segment , he wont be able to view , nor edit the record.
>
>How do i implement this security on segment based,
>Thanks in advance
>Cheers
>thomson
>
>
I do have four combination key for eg:1111-1111-1111-1111
the values can range from 0-0, based upon this i can create any number
of keys eg:1234-4567-4352-6534, these four values comes from different
tables, while joining the four i insert the new combination in a new
table .
In order to give security, i can give access rights to each parts for
eg: a user can have rights to 1234, but dont have the rights to 4567.
Iam using these keys in lot of my records , but i need to give a
checking based on the user , if he does not have the rights to a
particular segment , he wont be able to view , nor edit the record.
How do i implement this security on segment based,
Thanks in advance
Cheers
thomsonthomson
Look, I don't know your business requirements and obviously it depends on
bit permit me to suggest something
Please look at application role implemented by SQL Server as well as visit
on Vyas's greate web site to read below article
<http://vyaskn.tripod.com/sql_server...t_practices.htm> --se
curity best practices
"thomson" <saintthomson@.yahoo.com> wrote in message
news:1114056973.285659.247590@.g14g2000cwa.googlegroups.com...
> Hi all,
> I do have four combination key for eg:1111-1111-1111-1111
> the values can range from 0-0, based upon this i can create any number
> of keys eg:1234-4567-4352-6534, these four values comes from different
> tables, while joining the four i insert the new combination in a new
> table .
>
> In order to give security, i can give access rights to each parts for
> eg: a user can have rights to 1234, but dont have the rights to 4567.
> Iam using these keys in lot of my records , but i need to give a
> checking based on the user , if he does not have the rights to a
> particular segment , he wont be able to view , nor edit the record.
>
> How do i implement this security on segment based,
> Thanks in advance
> Cheers
> thomson
>|||Dear Thomson,
The same problem i faced in my project and we have implemented in the
project manually[no supported my MS SQL].
For segment based manual security U need to have a separate table for this
which will let U know the which Key is accced by which User
KeyVal UserID SecurityLevel(RoleID)
1234 1 1
1234 3 2
6534 1 4
... ... ...
... ... ...
... ... ...
Hee SecurityLevel(RoleID) field has the ID/Level for Security.Security Level
may be like enum... or as u wish.
I think From this table you help you in getting the user ID with their
rights and role for a desired Keyval.
Am i clear to you ?
Please let me know.
NB: Please reply me on rakesh.ranjan@.3i-infotech.com
Thanking you
Rakesh Ranjan
rakesh.ranjan@.3i-infotech.com
Mumbai[India]
"thomson" wrote:
> Hi all,
> I do have four combination key for eg:1111-1111-1111-1111
> the values can range from 0-0, based upon this i can create any number
> of keys eg:1234-4567-4352-6534, these four values comes from different
> tables, while joining the four i insert the new combination in a new
> table .
>
> In order to give security, i can give access rights to each parts for
> eg: a user can have rights to 1234, but dont have the rights to 4567.
> Iam using these keys in lot of my records , but i need to give a
> checking based on the user , if he does not have the rights to a
> particular segment , he wont be able to view , nor edit the record.
>
> How do i implement this security on segment based,
> Thanks in advance
> Cheers
> thomson
>|||Dear Rakesh,
I think you got my point would you please explain it to me
the role of KeyVal and the Security Role id in Detail
Regards
thomson
RakeshRanjan wrote:
> Dear Thomson,
> The same problem i faced in my project and we have implemented in the
> project manually[no supported my MS SQL].
> For segment based manual security U need to have a separate table for
this
> which will let U know the which Key is accced by which User
> KeyVal UserID SecurityLevel(RoleID)
> 1234 1 1
> 1234 3 2
> 6534 1 4
> ... ... ...
> ... ... ...
> ... ... ...
> Hee SecurityLevel(RoleID) field has the ID/Level for
Security.Security Level
> may be like enum... or as u wish.
>
> I think From this table you help you in getting the user ID with
their
> rights and role for a desired Keyval.
> Am i clear to you ?
> Please let me know.
> NB: Please reply me on rakesh.ranjan@.3i-infotech.com
> Thanking you
> Rakesh Ranjan
> rakesh.ranjan@.3i-infotech.com
> Mumbai[India]
>
> "thomson" wrote:
>
number
different
new
for
4567.|||One more thing is that If you have mentioned the KeyVal value, as the
one like 1111-1111-1111-1111, Then the issue is there can be a Hypen
between segments, or there can be any other Characters in between the
segments, So how will i tackle this
Regards
thomson|||Hi Tjhomson,
Key val is nothing but ur primary Key of all other table which make ur newly
generated Segmentkey(1234-4567-4352-6534).
You can have any charecter other than "-", It depends on you, how you want
to program it. This will be treated as a charecter for spliting your segment
key (which u have created from the four IDs).
Here the SecurityLevel(RoleID) field is rthe ID(Primary Key) of ur
security/role table which a user has, so that u can come to know the level o
f
user security.
Hope this much help u in better understanding. For further clarification
mail me on rakesh.ranjan@.3i-infotech.com
With regards,
Rakesh.
"thomson" wrote:
> Dear Rakesh,
> I think you got my point would you please explain it to me
> the role of KeyVal and the Security Role id in Detail
> Regards
> thomson
> RakeshRanjan wrote:
>
> this
> Security.Security Level
> their
> number
> different
> new
> for
> 4567.
>|||Hi rakesh,
The problem lies here that i dont want to give the security
to the whole key 1111-1111-1111-1111.
My issue is i have to give the security to each segments eg:1111
this 4 key can be generated by any one, its a one time setup, Then i
have to give security to each segments for each user.
suppose if i create a segment 1111-2222-3333-4444.
If i dont have the access to 1111, what i should not able to access the
records having 1111-2222-3333-4444 or 2222-1111-3333-4444, cos i dont
have access to the particular segment 1111.
Thanks in advance
thomson
thomson wrote:
> One more thing is that If you have mentioned the KeyVal value, as the
> one like 1111-1111-1111-1111, Then the issue is there can be a Hypen
> between segments, or there can be any other Characters in between the
> segments, So how will i tackle this
> Regards
> thomson|||Hello Thomson,
Its little bit different than what I have Implemented. Though no issue , I
have a clue.
As u said the segment "1111-2222-3333-4444" is one time creation. Even
multiple time , I have no issue at all.
What u need to do is, after creation of the key "1111-2222-3333-4444" split
it into four parts i.e. 1111, 2222, 3333, 4444 and then Insert four records
in the said table with their rights( as i explained earlier), Now you know
which key has what access. so can can proceed further as per ur requirement.
For further please chat with me on mail "rakesh.ranjan@.3i-infotech.com".
With Regards,
Rakesh R.
"thomson" wrote:
> Hi rakesh,
> The problem lies here that i dont want to give the security
> to the whole key 1111-1111-1111-1111.
> My issue is i have to give the security to each segments eg:1111
> this 4 key can be generated by any one, its a one time setup, Then i
> have to give security to each segments for each user.
> suppose if i create a segment 1111-2222-3333-4444.
> If i dont have the access to 1111, what i should not able to access the
> records having 1111-2222-3333-4444 or 2222-1111-3333-4444, cos i dont
> have access to the particular segment 1111.
> Thanks in advance
>
> thomson
> thomson wrote:
>|||thomson,
You'll have to parse the key. If it's just 4 4-character pieces, it's
not hard.
select key from tableOfKeys
where not exists (
select deniedPermission from Permissions
where Permissions.theUser = @.theUser
and deniedPermission in (
substring(key,1,4),
substring(key,6,4),
substring(key,11,4),
substring(key,16,4)
)
)
If permissions are assigned positively, it's something like
select key from tableOfKeys
where not exists (
select keyPermission from (
select substring(key,1,4) union all
select substring(key,6,4) union all
select substring(key,11,4) union all
select substring(key,16,4)
) as K(keyPermission)
where keyPermission not in (
select permission from Permissions
where permission.theUser = @.theUser
)
)
[both untested]
Steve Kass
Drew University
thomson wrote:
>Hi all,
> I do have four combination key for eg:1111-1111-1111-1111
>the values can range from 0-0, based upon this i can create any number
>of keys eg:1234-4567-4352-6534, these four values comes from different
>tables, while joining the four i insert the new combination in a new
>table .
>
> In order to give security, i can give access rights to each parts for
>eg: a user can have rights to 1234, but dont have the rights to 4567.
>Iam using these keys in lot of my records , but i need to give a
>checking based on the user , if he does not have the rights to a
>particular segment , he wont be able to view , nor edit the record.
>
>How do i implement this security on segment based,
>Thanks in advance
>Cheers
>thomson
>
>
Subscribe to:
Posts (Atom)